Een aflossingsschema maken in Excel: de stappen uitgelegd

Een aflossingsschema biedt een helder financieel stappenplan en laat zien hoe elke aflossing wordt opgesplitst in rente en aflossing totdat je schuld volledig is afbetaald.

Met de ingebouwde functies van Excel kun je gedetailleerde aflossingsschema’s maken om de levenscyclus van je lening te visualiseren, weloverwogen financiële beslissingen te nemen en zelfs opties voor vervroegd aflossen te verkennen.

In dit artikel leer je hoe je vanaf nul een compleet aflossingsschema in Excel opbouwt. We behandelen essentiële functies zoals PMT(), IPMT() en PPMT(), begeleiden je stap voor stap en verkennen praktische scenario’s om je financiële gereedschapskist te versterken.

Wat is een aflossingsschema precies?

Een aflossingsschema is een volledige tabel met periodieke leningbetalingen, waarin wordt weergegeven welk deel van elke betaling uit aflossing en welk deel uit rente bestaat totdat de lening aan het einde van de looptijd is afbetaald.

Als je een lening afsluit, bepalen een paar kernelementen hoe je betalingen werken:

• Aflossing: Het oorspronkelijke geleende bedrag dat je moet terugbetalen
• Rente: De kosten van het lenen, berekend als een percentage van de hoofdsom
• Betalingsperiode: Hoe vaak je betaalt (maandelijks, tweewekelijks, enz.)
• Looptijd van de lening: De totale periode waarin je de lening terugbetaalt

Voor de meeste standaardleningen—zoals hypotheken, autoleningen en persoonlijke leningen—blijven de betalingen gedurende de looptijd gelijk. Wat echter per betaling verandert, is de verhouding tussen rente en aflossing. In de beginfase gaat een groter percentage van elke betaling naar rente. Naarmate je vordert in de looptijd, gaat een groter deel naar het verminderen van de hoofdsom.

Het primaire doel van een aflossingsschema is transparantie bieden in je lening. Je ziet exact hoeveel rente je in de tijd betaalt, hoe snel je vermogen opbouwt en hoe extra betalingen je aflossingstermijn beïnvloeden. Voor bedrijven dienen deze schema’s ook voor boekhoudkundige en fiscale planning.

De Excel-functies en -formules die je moet kennen

Excel heeft niet één enkele ingebouwde "amortization"-functie. In plaats daarvan bouw je een aflossingsschema met specifieke financiële functies die samenwerken om verschillende aspecten van je betalingen te berekenen. Dit zijn de kernfuncties die je nodig hebt:

PMT()-functie

De functie PMT() berekent de betaling voor een lening op basis van constante betalingen en een constante rente:

=PMT(rate, nper, pv, [fv], [type])

• rate: De rente per periode (jaarrente gedeeld door betalingsfrequentie)

• nper: Het totale aantal betalingsperioden

• pv: De contante waarde (leningbedrag)

• [fv]: Optionele toekomstige waarde (meestal 0 voor volledig afgeloste leningen)

• [type]: Optioneel; 0 voor betaling aan het einde van de periode, 1 voor het begin van de periode

Om een maandelijkse betaling te berekenen voor een hypotheek van $250.000 tegen 4,5% jaarrente gedurende 30 jaar: 

=PMT(4.5%/12, 30*12, 250000)

Dit geeft -$1.266,71 terug, wat de kasuitstroom voor de lener weergeeft.

Voor een uitgebreide uitleg van de functie PMT(), inclusief veelvoorkomende fouten en geavanceerde voorbeelden, kun je onze volledige PMT()-gids voor Excel raadplegen.

IPMT()-functie

De functie IPMT() berekent het rentecomponent van een specifieke betaling:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: De specifieke betalingsperiode die je berekent

De parameter per is essentieel omdat deze bepaalt van welke periode je het rentebedrag berekent—anders dan PMT(), dat dezelfde betalingssom geeft voor alle perioden, variëren de uitkomsten van IPMT() sterk per opgegeven periode, omdat de rentecomponenten in de loop van de lening afnemen.

Dit laat precies zien welk deel van een specifieke betaling naar rente gaat.

PPMT()-functie

De functie PPMT() berekent het aflossingsgedeelte (hoofdsom) van een specifieke betaling:

=PPMT(rate, per, nper, pv, [fv], [type])

Net als bij IPMT() varieert het aflossingsbedrag per periode: eerdere betalingen hebben een kleiner aflossingsdeel en latere betalingen een groter. Dit toont hoeveel van een betaling je leningsaldo verlaagt.

Aangepaste formules

Per betalingsperiode wil je het volgende bijhouden:

Resterende schuld: Het uitstaande leningbedrag na elke betaling

Remaining balance = Previous balance - Current principal payment

Cumulatieve aflossing: Totale aflossing tot nu toe

Cumulative principal = Previous cumulative principal + current principal payment

Cumulatieve rente: Totale rente tot nu toe

Formula: Previous cumulative interest + current interest payment

Round()-functie

Zelfs kleine afrondingsverschillen kunnen zich in een aflossingsschema opstapelen:

=ROUND(value, 2)

Dit zorgt ervoor dat alle bedragen op de cent nauwkeurig zijn afgerond.

Absolute vs. relatieve verwijzingen

Bij het bouwen van je aflossingstabel moet je:

• Absolute verwijzingen ($) gebruiken voor vaste waarden zoals rente en leningbedrag

• Relatieve verwijzingen gebruiken voor waarden die per periode veranderen

De onderstaande formule gebruikt bijvoorbeeld absolute verwijzingen voor rente, looptijd en bedrag, maar een relatieve verwijzing voor de betalingsperiode:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Stapsgewijze handleiding voor het maken van het aflossingsschema

Nu je de belangrijkste functies kent, gaan we een aflossingsschema vanaf nul opbouwen. Deze walkthrough maakt een volledige tabel die elke betaling gedurende de levensduur van je lening bijhoudt.

1. Leninggegevens invoeren

Voer je leningdetails in de daarvoor bestemde invoercellen in, zoals in het onderstaande voorbeeld:

• Leningbedrag: $250.000 (cel B1)
• Jaarlijkse rente: 4,5% (cel B2)
• Looptijd van de lening: 30 jaar (cel B3)
• Betalingsfrequentie: Maandelijks (cel B4)

De invoerparameters voor de lening instellen. Afbeelding door de auteur. 

2. De maandelijkse betaling berekenen

Bereken in cel B5 de maandelijkse betaling met de PMT-functie: =PMT(B2/12, B3*12, B1)

Deze formule geeft -$1.266,71 terug, wat je maandelijkse betaling is. Het minteken geeft aan dat dit een kasuitstroom (betaling) is.

De kolomkoppen en formules opzetten. Afbeelding door de auteur.

3. De eerste rij van het schema maken

Vervolgens maken we de volgende koppen en vullen we de eerste rij gegevens in (rijen 7 en 8):  

• Periode: Vul 1 in cel A8 in

• Betalingsbedrag: =$B$5 in cel B8 (verwijzing naar de berekende maandbetaling)

• Rentebetaling: =IPMT($B$2/12, A8, $B$3*12, $B$1) in cel C8

• Aflossing (hoofdsom): =PPMT($B$2/12, A8, $B$3*12, $B$1) in cel D8

• Resterende schuld: =$B$1+D8 in cel E8 (oorspronkelijke lening plus aflossing; dit werkt omdat D8 een negatief getal oplevert en zo effectief het aflossingsbedrag van de lening aftrekt)

• Cumulatieve aflossing: =D8 in cel F8 (gelijk aan de aflossing in de eerste rij)

• Cumulatieve rente: =C8 in cel G8 (gelijk aan de rentebetaling in de eerste rij)

De uitsplitsing van de eerste betaling. Afbeelding door de auteur.

4. Het schema voltooien

Voor de tweede rij (rij 9):

• Periode: =A8+1 in cel A9 (stap met 1 omhoog)

• Betalingsbedrag: Kopieer de formule van B8 naar B9

• Rentebetaling: =IPMT($B$2/12, A9, $B$3*12, $B$1) in cel C9

• Aflossing (hoofdsom): =PPMT($B$2/12, A9, $B$3*12, $B$1) in cel D9

• Resterende schuld: =E8+D9 in cel E9 (vorig saldo plus huidige aflossing)

• Cumulatieve aflossing: =F8+D9 in cel F9 (vorig totaal plus huidige aflossing)

• Cumulatieve rente: =G8+C9 in cel G9 (vorig totaal plus huidige rente)

Let op dat in zowel IPMT() als PPMT() A9 als relatieve verwijzing (zonder dollartekens) wordt gebruikt. Als je de formule naar beneden kopieert, wordt deze automatisch A10, A11, enz., en berekent zo telkens de juiste periode.

Formules instellen die je naar beneden kunt kopiëren. Afbeelding door de auteur.

De eerste betalingen in het aflossingsschema. Afbeelding door de auteur.

Selecteer alle cellen in rij 9 en sleep naar rij 367 (voor een maandelijkse lening van 30 jaar met 360 betalingen).

Naarmate je vordert in de looptijd, gaat er meer van elke betaling naar aflossing dan naar rente. Afbeelding door de auteur.

5. De laatste betaling controleren

In een goed opgebouwd aflossingsschema brengt de laatste betaling de resterende schuld exact naar nul, en is de cumulatieve aflossing gelijk aan het oorspronkelijke leningbedrag.

De laatste betaling brengt de resterende schuld exact naar nul. Afbeelding door de auteur.

Zoals je in de laatste rij ziet, is de resterende schuld $0,00 (gemarkeerd) en is de cumulatieve aflossing $250.000, gelijk aan ons oorspronkelijke leningbedrag. Dit bevestigt dat ons aflossingsschema klopt.

Bij het controleren van het eindsaldo kan het nuttig zijn om de functie ROUND() toe te passen op sleutelberekeningen. Kleine decimale verschillen door de rekenprecisie van Excel kunnen ervoor zorgen dat het eindsaldo een minieme restwaarde toont in plaats van exact nul. Met =ROUND(value, 2) blijven alle bedragen consistent met gangbare financiële rapportagestandaarden.

Aanvullende zaken om te overwegen

Een standaard schema is het startpunt. Overweeg het volgende om je model flexibeler te maken:

Betalingsvariaties beheren

Aflossingsschema’s worden nog waardevoller wanneer je ze aanpast om verschillende betalingsscenario’s te verkennen. Maak voor extra aflossingen een extra invoercel en pas je formule voor aflossing (hoofdsom) aan om dit bedrag mee te nemen. Zo zie je hoe extra betalingen zowel de looptijd als de totale rente verlagen. Voor leningen met slottermijnen (balloon payments) of variabele rente loont het om aparte secties in je schema te maken die renteaanpassingen op specifieke momenten weerspiegelen. Je kunt Excel’s Scenario Manager gebruiken om verschillende renteresultaten te vergelijken en hun impact op je totale kosten te zien.

Leesbaarheid en analyse verbeteren

Goede opmaak verandert je aflossingsschema van een simpele tabel in een inzichtelijk financieel hulpmiddel. Pas valutaopmaak met consistente decimalen toe op alle bedragen en gebruik voorwaardelijke opmaak om mijlpalen te markeren—zoals wanneer je de helft van de hoofdsom hebt afgelost of bepaalde vermogensdrempels bereikt. Het is ook belangrijk te begrijpen hoe verschillende rente-opbouwmethoden je betalingen beïnvloeden. Dagelijkse opbouw (vaak bij studieleningen) berekent rente op basis van 365 dagen per jaar, terwijl veel hypotheken een maandelijkse methode gebruiken. Documenteer welke methode je schema gebruikt om nauwkeurigheid te waarborgen bij vergelijking met werkelijke afschriften.

Aanpassen voor specifieke leningen

Verschillende leningen hebben unieke kenmerken die aanpassingen aan je basissjabloon kunnen vereisen. Voeg bij hypotheken met een depotrekening (escrow) kolommen toe om verzekerings- en belastingcomponenten naast aflossing en rente bij te houden. Bij leningen met negatieve aflossing (waar betalingen de opgebouwde rente niet dekken) pas je de berekening van de resterende schuld aan door onbetaalde rente aan de hoofdsom toe te voegen. Met de optionele betalingsmoment-parameter (type) in Excel’s financiële functies kun je betalingen aan het begin of einde van perioden modelleren—handig voor leases en sommige zakelijke leningen waar de timing afwijkt van standaard consumentenleningen.

Conclusie

Een aflossingsschema in Excel geeft je waardevol inzicht in je leningen, verder dan wat de meeste kredietverstrekkers bieden. Door je eigen schema te bouwen, zie je precies hoe elke betaling je saldo beïnvloedt, begrijp je de werkelijke kosten van lenen in de tijd en kun je weloverwogen beslissen over vervroegd aflossen. De vaardigheden die je hierbij ontwikkelt—het gebruiken van financiële functies in Excel, dynamische formules maken en informatieve tabellen ontwerpen—gaan veel verder dan leninganalyse en zijn toepasbaar op talloze aspecten van persoonlijke en zakelijke financiële planning.

Wil je verder gaan dan aflossing en completere financiële modellen bouwen—zoals inkomstenprognoses, investeringen evalueren en what-if-scenario’s draaien—bekijk dan onze cursus Financial Modeling in Excel. Een mooie volgende stap om je spreadsheetvaardigheden voor financiële analyse te versterken. En onthoud: hoe comfortabeler je wordt met deze financiële berekeningen, hoe beter je in staat bent je financiële toekomst in eigen hand te nemen—of je nu persoonlijke schulden beheert, zakelijke investeringen plant of klanten adviseert over hun financiële opties.