Het opstellen van een annuïtair aflossingsschema heeft nogal wat voeten in de aarde aangezien het bereik van het aflossingsschema handmatig aangepast moet worden op basis van de ingevoerde looptijd. Met behulp van de nieuwe dynamische matrixformules behoort dit tot het verleden.

Uitgangspunten

U voert de volgende variabelen in:

U voorziet de variabelen van een bereiknaam. Markeer daartoe cellen B2 tot en met C7 en kies uit het Lint: Formules > Gedefinieerde namen > Maken o.b.v. selectie > Linkerkolom > OK.

Voer de volgende formules in:

• LooptijdMaanden =LooptijdJaren*12
• MaandAnnuïteit =-BET(JaarRentePercentage/12;LooptijdMaanden;Hoofdsom)

Lees ook: Excel: Opstellen van een dynamische annuïteitenlening

Indeling aflossingsschema

Het aflossingsschema heeft de volgende structuur:

U vult in rij 3 de volgende formules/waarden in:

• E3= 0
• F3= StartDatum
• G3= #N/B
• H3= #N/B
• I3= #N/B
• J3= Hoofdsom

Vanaf rij 4 vult u de volgende dynamische matrixformules in:

• Perioden: E4= REEKS(LooptijdMaanden;1;DATUM(JAAR(StartDatum);MAAND(StartDatum)+E4#;1);1)
• Datum: F4= REEKS(LooptijdMaanden;1;MaandAnnuïteit;0)
• Annuïteit: G4= REEKS(LooptijdMaanden;1;MaandAnnuïteit;0)
• Rente: H4= -IBET(JaarRentePercentage/12;E4#;MaandAnnuïteit;Hoofdsom)
• Aflossing: I4= -PBET(JaarRentePercentage/12;E4#;LooptijdMaanden;Hoofdsom)
• Saldo: J4= Hoofdsom-SUBTOTAAL(9;VERSCHUIVING(I4;0;0;E4#;1))
• Saldo met CUM.HOOFDSOM: K4=Hoofdsom+CUM.HOOFDSOM(JaarRentePercentage/12;LooptijdMaanden;Hoofdsom;1;REEKS(LooptijdMaanden;1;1;1);0)

De dynamische matrixformules worden automatisch naar beneden doorgevoerd.

Toelichting dynamische matrixformules

Reeks: deze kolom is de ankerkolom op basis waarvan de overige kolommen gebaseerd zijn en waardoor de cellen automatisch naar beneden worden doorgevoerd. Het aantal rijen wordt bepaald door de variabele LooptijdMaanden.

Datum: Het aantal rijen wordt bepaald door de variabele LooptijdMaanden. De begindatum start met de StartDatum, waarbij de maand wordt bepaald via het volgnummer van de periode: DATUM(JAAR(StartDatum); MAAND(StartDatum)+E4#. Het #-teken geeft aan dat de formule naar beneden moet worden doorgevoerd.

Annuïteit: Het aantal rijen wordt bepaald door de variabele LooptijdMaanden.

Rente: Met de functie IBET berekent u het rentedeel van een annuïteit voor een gewenste termijn, die refereert naar de corresponderende periode in cel E4. Het #-teken geeft aan dat de formule naar beneden moet worden doorgevoerd.

Aflossing: Met de functie PBET berekent u het rentedeel van een annuïteit voor een gewenste termijn, dit refereert naar de corresponderende periode in cel E4. Het #-teken geeft aan dat de formule naar beneden moet worden doorgevoerd.

Saldo: =Hoofdsom-SUBTOTAAL(9;VERSCHUIVING(I4;0;0;E4#;1)).  Het cijfer 9 in de SUBTOTAAL-functie geeft aan de SOM berekend wordt.

Via de VERSCHUIVING functie wordt een dynamisch bereik gemarkeerd via de hoogte, welke refereert naar de corresponderende periode in cel E4. Het #-teken geeft aan dat de formule naar beneden moet worden doorgevoerd.

Saldo met CUM.HOOFDSOM Met de functie CUM.HOOFSOM wordt de cumulatieve terugbetaalde hoofdsom berekend. Dit bedrag wordt afgetrokken van de hoofdsom om het saldo te berekenen.

Beide Saldo-kolommen leiden tot hetzelfde resultaat.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2021, afl. 9.

Hier leest u meer Excel tips en trucs door Tony De Jonker

 

Volg CMWeb op LinkedIn!