Met een aflossingsschema in Excel zie je precies hoeveel rente en aflossing je elke maand betaalt en wat je restschuld is. In dit artikel leer je stap voor stap hoe je een professioneel aflossingsschema maakt met de BET-, IBET- en PBET-functies.
Wat is een aflossingsschema in Excel?
Een aflossingsschema (ook wel amortisatieschema genoemd) is een overzicht waarin je per periode ziet:
- De maandelijkse betaling (annuiteit)
- Het rentedeel van die betaling
- Het aflossingsdeel van die betaling
- De restschuld na elke betaling
- De cumulatieve rente en aflossing
Dit schema geeft je volledig inzicht in de kosten van een lening. Je ziet bijvoorbeeld dat je in het begin vooral rente betaalt en pas later meer aflost. Met Excel maak je zo’n schema in enkele minuten.
Waarom een aflossingsschema maken?
- Transparantie: zie precies waar je geld naartoe gaat
- Vergelijken: beoordeel verschillende leenopties
- Plannen: bepaal het effect van extra aflossingen
- Fiscaal: bereken de jaarlijkse renteaftrek voor je belastingaangifte
De vijf financiele functies voor aflossingsschema’s
Excel heeft vijf functies die je nodig hebt:
| Functie (NL) | Functie (EN) | Berekent |
|---|---|---|
| BET | PMT | De vaste maandelijkse betaling |
| IBET | IPMT | Het rentedeel van een specifieke termijn |
| PBET | PPMT | Het aflossingsdeel van een specifieke termijn |
| CUM.RENTE | CUMIPMT | Cumulatieve rente over meerdere periodes |
| CUM.HOOFDSOM | CUMPRINC | Cumulatieve aflossing over meerdere periodes |
Aflossingsschema maken: stap-voor-stap
Laten we een compleet aflossingsschema bouwen voor een lening van 50.000 euro tegen 5% rente over 5 jaar.
Stap 1: Maak het invoergedeelte
Maak eerst een overzichtelijk invoergedeelte bovenaan je werkblad:
| Cel | Omschrijving | Waarde |
|---|---|---|
| B1 | Leenbedrag | 50000 |
| B2 | Jaarlijkse rente | 5% |
| B3 | Looptijd (jaren) | 5 |
| B4 | Termijnen per jaar | 12 |
In cel B5 bereken je het totaal aantal termijnen:
=B3*B4
Resultaat: 60 termijnen
In cel B6 bereken je de rente per periode:
=B2/B4
Resultaat: 0,417% per maand
Stap 2: Bereken de maandelijkse betaling met BET
De BET-functie (Engels: PMT) berekent de vaste maandelijkse betaling voor je lening.
Syntax:
=BET(rente; aantal_termijnen; hw; [tw]; [type])
In cel B8 typ je:
=BET(B6;B5;B1)
Uitleg:
– B6: maandrente (5%/12 = 0,417%)
– B5: totaal aantal termijnen (60)
– B1: het leenbedrag (50.000)
Resultaat: -943,56 euro
Het minteken betekent dat dit geld is dat je betaalt. Je maandlast is dus 943,56 euro.
Tip: Wil je een positief getal? Gebruik dan:
=-BET(B6;B5;B1)
Stap 3: Bouw de tabel voor het aflossingsschema
Maak nu de kolomkoppen voor je aflossingsschema:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Termijn | Beginsaldo | Betaling | Rente | Aflossing | Eindsaldo |
| 1 | |||||
| 2 | |||||
| … |
Stap 4: Vul de eerste rij in
Cel A11 (Termijn):
1
Cel B11 (Beginsaldo eerste termijn):
=$B$1
Dit verwijst naar het leenbedrag. De dollartekens zorgen ervoor dat deze verwijzing vast blijft. Leer meer over celverwijzingen in onze handleiding over cel vastzetten in Excel.
Cel C11 (Betaling):
=-$B$8
Dit is de vaste maandlast (positief gemaakt met minteken).
Cel D11 (Rente met IBET):
De IBET-functie (Engels: IPMT) berekent het rentedeel van een specifieke termijn.
Syntax:
=IBET(rente; periode; aantal_termijnen; hw; [tw]; [type])
In cel D11 typ je:
=-IBET($B$6;A11;$B$5;$B$1)
Uitleg:
– $B$6: maandrente (vast)
– A11: het huidige termijnnummer (1)
– $B$5: totaal aantal termijnen (vast)
– $B$1: leenbedrag (vast)
Resultaat: 208,33 euro (rente in termijn 1)
Cel E11 (Aflossing met PBET):
De PBET-functie (Engels: PPMT) berekent het aflossingsdeel van een specifieke termijn.
Syntax:
=PBET(rente; periode; aantal_termijnen; hw; [tw]; [type])
In cel E11 typ je:
=-PBET($B$6;A11;$B$5;$B$1)
Resultaat: 735,23 euro (aflossing in termijn 1)
Controle: Rente + Aflossing = Betaling (208,33 + 735,23 = 943,56) – klopt!
Cel F11 (Eindsaldo):
=B11-E11
Resultaat: 49.264,77 euro
Stap 5: Vul de volgende rijen in
Cel A12 (Termijn 2):
=A11+1
Cel B12 (Beginsaldo is vorige eindsaldo):
=F11
Cellen C12, D12 en E12: Kopieer de formules uit rij 11. De verwijzingen met $ blijven vast, de termijnnummers passen zich aan.
Cel F12:
=B12-E12
Stap 6: Kopieer naar alle termijnen
- Selecteer de cellen A12:F12
- Kopieer met Ctrl+C (Windows) of Cmd+C (Mac)
- Selecteer het bereik A13:A70 (tot termijn 60)
- Plak met Ctrl+V of Cmd+V
Je hebt nu een compleet aflossingsschema!
Het volledige schema analyseren
Met je aflossingsschema kun je interessante inzichten krijgen:
Totaal betaald aan rente
Onder je schema bereken je de totale rentekosten:
=SOM(D11:D70)
Resultaat: 6.613,67 euro
Je betaalt dus 6.613,67 euro rente over 5 jaar. Samen met de aflossing van 50.000 euro is je totale betaling 56.613,67 euro.
Rente versus aflossing per jaar
Met de functies CUM.RENTE en CUM.HOOFDSOM (Engels: CUMIPMT en CUMPRINC) bereken je de totalen per jaar:
Cumulatieve rente jaar 1 (termijn 1-12):
=-CUM.RENTE(B6;B5;B1;1;12;0)
Cumulatieve aflossing jaar 1:
=-CUM.HOOFDSOM(B6;B5;B1;1;12;0)
Syntax CUM.RENTE / CUM.HOOFDSOM:
=CUM.RENTE(rente; aantal_termijnen; hw; begin_periode; eind_periode; type)
| Jaar | Rente | Aflossing | Totaal |
|---|---|---|---|
| 1 | 2.287,25 | 9.035,49 | 11.322,74 |
| 2 | 1.816,90 | 9.505,84 | 11.322,74 |
| 3 | 1.329,41 | 9.993,33 | 11.322,74 |
| 4 | 823,85 | 10.498,89 | 11.322,74 |
| 5 | 299,23 | 11.023,51 | 11.322,74 |
Je ziet dat in jaar 1 nog 2.287 euro rente betaalt, maar in jaar 5 slechts 299 euro.
Praktijkvoorbeeld: hypotheek aflossingsschema
Laten we een realistischer voorbeeld bekijken: een hypotheek van 250.000 euro tegen 4% rente over 30 jaar.
Invoergegevens
| Omschrijving | Waarde |
|---|---|
| Hypotheekbedrag | 250.000 |
| Jaarrente | 4% |
| Looptijd | 30 jaar |
| Termijnen | 360 |
Berekeningen
Maandlast:
=BET(4%/12;360;250000)
Resultaat: -1.193,54 euro per maand
Totaal betaald over 30 jaar:
=1193,54*360
Resultaat: 429.674,40 euro
Totaal aan rente:
=429674,40-250000
Resultaat: 179.674,40 euro
Je betaalt bijna 180.000 euro aan rente! Meer dan 70% van je oorspronkelijke hypotheek. Dit illustreert het belang van een goed rentepercentage.
Wat-als: extra aflossen
Stel je lost elk jaar 5.000 euro extra af. Met de wat-als analyse functies kun je berekenen hoeveel sneller je hypotheekvrij bent en hoeveel rente je bespaart.
Geavanceerd: dynamisch aflossingsschema
Voor een flexibel schema dat automatisch aanpast aan je invoer, gebruik je slimme formules:
Termijnnummers automatisch genereren
In plaats van handmatig 1, 2, 3… te typen, gebruik je:
=REEKS(B5)
Deze REEKS-functie genereert automatisch een reeks van 1 tot het aantal termijnen.
Beginsaldo met ALS-functie
=ALS(A11=1;$B$1;F10)
Dit zorgt ervoor dat de eerste termijn het leenbedrag gebruikt en alle volgende termijnen het vorige eindsaldo. Leer meer over voorwaardelijke logica in onze ALS-functie handleiding.
Schema stoppen bij eindsaldo 0
Voeg een controle toe zodat het schema stopt als de lening is afgelost:
=ALS(B11<=0;"";-IBET($B$6;A11;$B$5;$B$1))
Veelgemaakte fouten bij aflossingsschema’s
Fout 1: Jaarrente niet delen door 12
Probleem: Je maandlast is onrealistisch hoog.
Oorzaak: Je gebruikt de jaarrente in plaats van de maandrente.
Oplossing: Deel de jaarrente altijd door 12 voor maandelijkse betalingen:
// Fout:
=BET(5%;60;50000) // Resultaat: -3.344,88
// Correct:
=BET(5%/12;60;50000) // Resultaat: -943,56
Fout 2: Verkeerde periode in IBET/PBET
Probleem: Je rente- en aflossingsbedragen kloppen niet.
Oorzaak: Je gebruikt een vast getal voor de periode in plaats van een celverwijzing.
Oplossing: Verwijs naar de cel met het termijnnummer:
// Fout:
=IBET(B6;1;B5;B1) // Altijd termijn 1
// Correct:
=IBET(B6;A11;B5;B1) // Past zich aan per rij
Fout 3: Dollartekens vergeten bij vaste waarden
Probleem: Bij kopiëren verschuiven je verwijzingen en krijg je fouten.
Oorzaak: Je hebt geen absolute verwijzingen gebruikt voor vaste waarden.
Oplossing: Gebruik $ voor cellen die niet mogen verschuiven:
// Fout:
=IBET(B6;A11;B5;B1)
// Correct:
=IBET($B$6;A11;$B$5;$B$1)
Meer over dit onderwerp vind je in onze handleiding over absolute en relatieve celverwijzingen.
Fout 4: Restschuld wordt negatief
Probleem: Je eindsaldo gaat onder nul.
Oorzaak: Afrondingsverschillen of verkeerde formules.
Oplossing: Voeg een MAX-functie toe:
=MAX(0;B11-E11)
Fout 5: Mintekens inconsistent gebruiken
Probleem: Sommige bedragen zijn positief, andere negatief.
Oorzaak: Excel gebruikt mintekens voor uitgaande geldstromen.
Oplossing: Wees consistent. Zet een minteken voor alle BET, IBET en PBET formules om positieve bedragen te krijgen:
=-BET(...)
=-IBET(...)
=-PBET(...)
Tips voor een professioneel aflossingsschema
1. Gebruik Excel-tabellen
Converteer je schema naar een Excel-tabel met Ctrl+T. Voordelen:
– Automatisch doorrekenen bij nieuwe rijen
– Gestructureerde verwijzingen
– Totaalrij onderaan
2. Voeg voorwaardelijke opmaak toe
Maak je schema visueel met voorwaardelijke opmaak:
– Kleurenschaal voor restschuld (hoog = rood, laag = groen)
– Markeer de maand waarin je halverwege bent
– Highlight termijnen met hoge rentekosten
3. Maak een grafiek
Visualiseer de verhouding rente/aflossing met een gestapeld staafdiagram. Je ziet dan duidelijk hoe de samenstelling van je betaling verandert over tijd.
4. Voeg scenario’s toe
Maak kolommen voor:
– Standaard aflossing
– Extra maandelijkse aflossing
– Eenmalige extra aflossing
Zo vergelijk je eenvoudig de impact van extra aflossen.
Overzicht: formules voor aflossingsschema’s
| Wat je berekent | Formule |
|---|---|
| Maandlast | =BET(rente/12;jaren*12;leenbedrag) |
| Rente termijn X | =IBET(rente/12;X;jaren*12;leenbedrag) |
| Aflossing termijn X | =PBET(rente/12;X;jaren*12;leenbedrag) |
| Totale rente jaar 1 | =CUM.RENTE(rente/12;jaren*12;leenbedrag;1;12;0) |
| Totale aflossing jaar 1 | =CUM.HOOFDSOM(rente/12;jaren*12;leenbedrag;1;12;0) |
| Restschuld na X termijnen | =leenbedrag+CUM.HOOFDSOM(rente/12;jaren*12;leenbedrag;1;X;0) |
FAQ: Veelgestelde vragen over aflossingsschema’s in Excel
Wat is het verschil tussen rente en aflossing?
Rente is de vergoeding die je betaalt voor het lenen van geld. Aflossing is het terugbetalen van de schuld zelf. Bij een annuiteitenlening is je maandbedrag vast, maar de verhouding rente/aflossing verandert: in het begin betaal je vooral rente, later vooral aflossing.
Hoe bereken ik de maandelijkse betaling voor een lening?
Gebruik de BET-functie: =BET(jaarrente/12;looptijd_jaren*12;leenbedrag). Bijvoorbeeld voor 30.000 euro tegen 6% over 4 jaar: =BET(6%/12;48;30000) geeft -704,55 euro per maand.
Wat doet de IBET-functie in Excel?
IBET (Engels: IPMT) berekent het rentedeel van een specifieke termijn. Met =IBET(6%/12;1;48;30000) bereken je hoeveel rente je betaalt in de eerste termijn van een lening. Het resultaat is negatief omdat het een uitgave is.
Wat is het verschil tussen IBET en PBET?
IBET berekent het rentedeel, PBET het aflossingsdeel. Samen zijn ze gelijk aan de totale betaling (BET). In het begin is IBET groter, aan het einde is PBET groter.
Hoe bereken ik de totale rentekosten over de hele looptijd?
Tel alle rentebedragen op met SOM(), of gebruik: =BET(rente/12;termijnen;leenbedrag)*termijnen-leenbedrag. Dit geeft het verschil tussen wat je totaal betaalt en wat je hebt geleend.
Kan ik extra aflossingen verwerken in mijn schema?
Ja, voeg een kolom toe voor extra aflossing en pas de restschuldberekening aan: =Beginsaldo-Aflossing-ExtraAflossing. Je IBET en PBET formules moeten dan wel de nieuwe restschuld gebruiken, wat complexer is.
Wat is het verschil tussen annuiteit en lineaire aflossing?
Bij annuiteit is je maandbedrag vast (BET). Bij lineaire aflossing is je aflossingsdeel vast en daalt je totale betaling over tijd. Excel’s BET, IBET en PBET zijn voor annuiteiten. Voor lineair deel je simpelweg het leenbedrag door het aantal termijnen.
Waarom zijn mijn IBET en PBET resultaten negatief?
Excel gebruikt mintekens voor geldstromen die je betaalt. Zet een minteken voor de formule om positieve bedragen te krijgen: =-IBET(…).
Hulp nodig bij Excel?
Kom je er niet uit met je aflossingsschema of andere financiele berekeningen in Excel? Ik help je graag verder.
Neem contact op:
– WhatsApp: Stuur een bericht
– E-mail: w.bouwmeester@bouwmeesterconsultancy.nl
– Telefoon: +31 6 28963636
Of bekijk mijn Excel hulp diensten voor professionele ondersteuning.
Gerelateerde artikelen
- Financiele functies in Excel: BET, HW, TW uitgelegd
- Procenten berekenen in Excel
- Wat-als analyse in Excel
- Excel formules: compleet overzicht
Laatst bijgewerkt: januari 2026
