Een onderdeel van het maken van prognoses is het berekenen van toekomstige rente en aflossing van een annuïteitenlening. Daartoe zal een volledig aflossingsschema opgesteld moeten worden welke desgewenst afgedrukt moeten worden voor de betreffende maanden. Het model zal dynamisch van aard zijn, zodat alleen die regels getoond mogen worden voor de gekozen looptijd.
Een annuïteitenlening wordt maandelijks door middel van een vaste betaling vereffend welke bestaat uit een rentedeel en een aflossingsbestanddeel. In het begin van de looptijd wordt meer rente betaald dan aan het einde van de looptijd. Grafisch gezien zien de componenten gedurende looptijd er als volgt uit:
Invoeren variabelen
We beginnen met het invoeren van de volgende variabelen:
Cellen B1 tot en met B5 zijn inputvariabelen en cellen B6-B7 zijn berekende variabelen. Alle variabelen zijn voorzien van een bereiknaam. Markeer daartoe cellen A1 tot en met B7 en kies uit het Lint: Formules à Gedefinieerde waarden à Maken o.b.v. selectie à Linkerkolom à OK.
Cel B2 heeft een aangepaste opmaak. Als u met cursor op deze cel op Control-1 klikt dan kunt u zien dat de celopmaak als volgt is: 0 “years”.
De berekende variabelen bevatten de volgende formuled:
EndDate =ZELFDE.DAG(StartDate;Years*Payments_per_Year)
Nbr Pmt=Years*Payments_per_Year
In het rekenmodel gaan we uit van een maximale looptijd van 30 jaar oftewel 360 maanden.
Indeling aflossingsschema
Het aflossingsschema heeft de volgende indeling:
U dient de cijfers 1 tot en met 360 in te vullen vanaf cellen E1 tot en met E361.
De volgende formules worden in het aflossingsschema toegepast:
- D1=StartDate
- Markeer cellen D3 tot en met D361: =DATUM(JAAR(D2);MAAND(D2)+1;DAG(D2)) en klik op Control-Enter.
- Markeer cellen F2 tot en met F361 en voer de volgende formule in: =BET(Annual_Interest_Rate/Payments_per_Year;Years*Payments_per_Year;Amount) en klik op Control-Enter.
- Markeer cellen G2 tot en met G361 en voer de volgende formule in: =ALS.FOUT(PBET(Annual_Interest_Rate/Payments_per_Year;E2;Years*Payments_per_Year;Amount);0) en klik op Control-Enter.
- Markeer cellen H2 tot en met H361 en voer de volgende formule in: =ALS.FOUT(IBET(Annual_Interest_Rate/Payments_per_Year;E2;Years*Payments_per_Year;Amount);0)
- I2 =Amount+G2
- Markeer cellen I3 tot en met I361 en voer de volgende formule in: =I2+G3 en klik op Control-Enter.
Voorwaardelijke opmaak
Om ervoor te zorgen dat alleen de actieve regels getoond worden die betrekking hebben op de looptijd, dienen we de overbodige regels te verbergen door middel van voorwaardelijke opmaak. Via Start à Stijlen à Voorwaardelijke Opmaak à Regels beheren geeft u de volgende instellingen in:
Gebruik witte achtergrond en witte lettertype, witte rand en witte opvulling.
Auteur: Tony de Jonker
Dit artikel is verschenen in cm: 2019 afl. 1
