Leningen en aflossingen in Excel

In dit artikel leert u hoe u een realistisch aflossingschema opstelt. Het resultaat, een compleet uitgewerkt Excel-werkboek, is ook te downloaden. Het artikel is bedoeld voor wie al de nodige ervaring met Excel heeft. U kunt het best eerst het werkboek downloaden en aan de hand van de tekst leren begrijpen hoe het werkboek is opgebouwd en hoe het werkt.

 

In het bedrag van afbetaling van een lening zit een deel aflossing en een deel rente. Aan het begin van de looptijd betaalt u relatief meer rente dan aflossing. Aan het einde van de looptijd is dit juist andersom. Dit heeft vooral fiscale redenen, omdat u in het begin meer rente kunt aftrekken. Het vaste betalingsbedrag noemen we ook wel annuïteit. Wat het aandeel rente en het aandeel aflossing in dit vaste bedrag is, wordt door deze applicatie van jaar tot jaar berekend.

 

In vele voorbeeldberekeningen wordt eenvoudigheidshalve uitgegaan van het feit dat het rentepercentage over de gehele looptijd onveranderd blijft. In werkelijkheid kan de rente in het geval van langlopende leningen zoals hypotheken maar gedurende een bepaalde, meestal vooraf overeengekomen tijd constant zijn, en na verloop van tijd kan het rentepercentage aan de nieuwe rentestand worden aangepast.

Open het gedownloade werkboek. Krijgt u een melding over een te hoge macro-beveiliging, kies dan eerst Extra >> Macro >> Beveiliging en verlaag de beveiliging naar Laag of Gemiddeld. Open het werkboek opnieuw.

 

Het werkboek telt twee belangrijke werkbladen: Input en Aflossingsschema. Op het werkblad Input vult u de gegevens van de lening in, zoals het hypotheek- of geleende bedrag, de rente, de looptijd in jaren et cetera. Direct na elke verandering wordt de tabel op werkblad Aflossingsschema bijgewerkt, maar voor een volledige bijwerking moet u op de knop Bereken klikken. Daarmee activeert u een macro die de termijnen berekent.

 

 

De kolommen in deze tabel moet u als volgt interpreteren:

 

Het schema is afgebakend tot 25 jaar. Hebt u een lening voor 10 jaar, dan worden de resultaten in de cellen van jaar 11 tot en met 25 door de Bereken-knop automatisch onzichtbaar gemaakt. Doorgaans is het bedrag in de laatste cel 0 (nul), maar soms blijft er een restbedrag over, dat dan nog verschuldigd is.

 

Met behulp van slimme formules is een dynamisch aflossingschema te maken. Dat wil zeggen dat wanneer bepaalde inputvariabelen wijzigen het aflossingschema automatisch wordt herberekend. Een belangrijk uitgangspunt bij het maken van een rekenmodel is het scheiden van de inputvariabelen en de output.

 

 

 

 

 

 

 

 

 

 

Voor de feitelijke berekeningen van aflossingen/rente en het afbeelden van het aflossingsschema is een apart blad aangemaakt, genaamd Aflossingsschema. Dit blad ziet er als volgt uit:

 

 

De cijfers in de kolommen C en D (rente% en aantal perioden) moeten handmatig worden ingevoerd. De rest van de kolommen worden door middel van formules berekend. Het schema bevat de volgende formules:

 

 

 

 

 

 

Werkt u met de Nederlandstalige versie van Excel, dan moet u de scheidingstekens (hier een puntkomma) vervangen door een komma en de functienamen. De equivalente functienamen zijn:

 

U dient het aantal regels van het aflossingsschema aan te passen aan de looptijd zoals genoemd in het Inputblad. Dit gaat vrij eenvoudig. U kunt de formules van regel 4 zoveel regels naar beneden kopiëren als nodig is.

 

Let op:

 

De hier besproken methode stelt u in staat om vlot aflossingschema's op te stellen die rekening houden met variabele rente en verschillende looptijden. Het voorbeeld sluit derhalve goed aan bij de realiteit. Het rekenmodel kan door middel van VBA nog verder worden geautomatiseerd waardoor u met een druk op de knop bijvoorbeeld wordt begeleid om de rentepercentages per vaste rentetermijn in te geven.