Exceltip | Annuïteitenschema

Introductie

Een annuïteitenschema wordt gebruikt om de aflossing en rente van een lening of hypotheek te berekenen en overzichtelijk weer te geven. Het schema laat zien hoe de periodieke betalingen (annuïteiten) zijn opgebouwd, waarbij een deel wordt toegewezen aan het aflossen van de lening en een ander deel aan het betalen van de rente.

Het kan gebruikt worden voor de financiële planning om inzicht te krijgen hoeveel je in welke periode moet betalen en hoe de schuld in de loop van de tijd wordt afgebouwd. Het toont ook hoe het aandeel van rente en aflossing verandert naarmate de looptijd vordert, hoeveel je in totaal aan rente hebt betaald en hoever je bent gevorderd met het aflossen in een bepaalde periode.

Voorbeeldgegevens

Als voorbeeld gebruik ik onderstaande data.

• B3: het bedrag van de lening (in dit voorbeeld 350.000).
• B4: het rentepercentage. In dit voorbeeld is dat een jaarrente van 3,5%.
• B5: de looptijd van de lening. In dit voorbeeld 30 jaar.

Eronder komt het schema. Hierin staan de volgende kolommen.

• Periode: hierin staan alle periodes van de looptijd genummerd.
• Restschuld: hierin staat het restant van de schuld in die periode.
• Annuïtaire rente: hierin staat het bedrag dat in die periode aan rente is betaald.
• Aflossing: hierin staat het bedrag dat in die periode is afgelost op de lening.
• Termijnbedrag: het bedrag wat in die periode is betaald aan rente + aflossing.
• Cum. rente: rentebedragen tot en met die periode opgeteld: zoveel rente is er totaal betaald t/m die periode.
• Cum. aflossing: aflossingsbedragen tot en met die periode opgeteld: zoveel is er totaal afgelost t/m die periode.

Belangrijk!

Net zoals je appels niet zomaar kunt vergelijken met peren, moet je bij de functies die gebruikt worden ook werken met dezelfde eenheden. Werk dus met jaarrente als je de looptijd en het termijnbedrag per jaar wilt weten. En als je het termijnbedrag in maanden wilt, dan werk je met een maandrente en een looptijd in maanden.

Rente omrekenen van jaarrente naar maandrente en vice versa

Het omrekenen van jaarrente naar maandrente wordt vaak gedaan door de jaarrente te delen door 12.
Het delen van de jaarrente door 12 lijkt op het eerste gezicht logisch en eenvoudig, maar het is niet accuraat. Het houdt namelijk geen rekening met het effect van samengestelde rente. Rente wordt berekend over de al opgebouwde rente, en dit maakt een groot verschil over meerdere periodes.

Beter is daarom deze formule: (1+jaarrente)^1/12-1
In Excel is dat: =(1+jaarrente)^(1/12)−1

Heb je een maandrente die je moet omrekenen naar jaarrente, dan gebruik je deze formule: (1+maandrente)¹²-1
In Excel is dat: =(1+maandrente)^12-1

In de afbeelding hieronder zie je het verschil tussen de jaarrente naar maandrente waarbij eenvoudig door 12 wordt gedeeld bij ① en wat als je de samengestelde renteberekening gebruikt ②. Dat scheelt nogal wat in de verschillende bedragen!

Gebruikte functies

Voor de verschillende berekeningen worden de volgende functies gebruikt.

• Periode: functie REEKS of een zelf gemaakt formule (Engels: SEQUENCE).
• Restschuld: eigen formule.
• Annuïtaire rente: functie IBET (Engels: IPMT).
• Aflossing: functie PBET (Engels: PPMT)
• Termijnbedrag: BET (Engels: PMT)
• Cum. rente: CUM.RENTE (Engels: CUMIPMT)
• Cum. aflossing: CUM.HOOFDSOM (Engels: CUMPRINC)

Deze functies gebruiken grotendeels dezelfde argumenten.

• Rente: het rentepercentage, hier B4.
• Hw: het bedrag dat geleend wordt (Huidige Waarde): hier B3.
• Aantal-termijnen: de looptijd, hier B5
• Tw: restwaarde van de lening als je die niet helemaal gaat aflossen (Toekomstige Waarde)>
  Als hier niets wordt ingevuld wordt uitgegaan van 0. Dat doe ik hier ook, want het is gebruikelijk dat een hypotheek in zijn geheel wordt afgelost. Bij 1 zou er aan het begin van de periode betaald worden.

Voor de bijzondere argumenten per functie volgt de uitleg hieronder.

Periode

Bij een hypotheek is over het algemeen de looptijd 30 jaar. De betaling is meestal per maand. Dat is ook het uitgangspunt dat ik hier ga gebruiken. In B5 staat dat de looptijd 30 jaar is. Dat is dus 30×12=360 maanden. In de Periode-kolom moeten dus de waarden 1 t/m 360 komen. Als je werkt met de nieuwere versies van Excel kun je hiervoor heel mooi de functie REEKS gebruiken (zie ①). Als argument neem je de looptijd van B5 en vermenigvuldig je die met 12 (zie de beschrijving van de functie in B8). Je krijgt dan een dynamisch bereik van 1 t/m 360. Het dynamische bereik heeft een blauwe rand als je een cel erin hebt geselecteerd.

Heb je een oudere versie van Excel, dan is de reeks-functie niet aanwezig. Je zet dan in A8 een 1.
In A9 zet je de formule: =A8+1. Dat kopieer je zover naar beneden tot er 360 komt te staan (zie ②).
In beide situaties zal het aantal periodes wijzigen als je de looptijd in B5 aanpast. Bij ② moet je het aantal keren door kopiëren dan aanpassen.

Termijnbedrag

Hier gebruik je in de hele kolom de functie BET. De rente is omgerekend naar een maandrente, het aantal termijn is vermenigvuldigd met 12 en het leenbedrag staat erin als een positief getal. Dat betekent dat het termijnbedrag in de cel komt te staan als een negatief bedrag (je moet het immers betalen!)

Bij deze functie is er een argument Type_getal dat je ook kunt invullen. Vul je hier een 0 in dan betaal je aan het einde van de periode/maand. Vul je hier een 1 in dan betaal je aan het begin. Als je dit leeg laat, vult Excel een 0 in voor je.

Annuïtaire rente

Hier gebruik je voor de hele kolom de functie IBET. Vergeleken met het termijnbedrag is hier een extra argument: Termijn. Dat is de periode waarvoor deze rente betaald wordt. Die staat in de A-kolom.
Let op: hier staat ook een Type_getal, maar die zie je niet direct (schuifbalk gebruiken!). Net als bij het termijnbedrag komt hier een negatief bedrag te staan.

Aflossing

Hier gebruik je voor de hele kolom de functie PBET. Net als bij de rente heb je hier ook het extra argument Termijn: de periode waarvoor deze aflossing betaald wordt. Die staat in de A-kolom.
Let op: hier staat ook een Type_getal, maar die zie je niet direct (schuifbalk gebruiken!). Net als bij het termijnbedrag komt hier een negatief bedrag te staan.

Restschuld

In deze kolom heeft de eerste cel een afwijkende formule. In de eerste periode is de restschuld natuurlijk nog het hele leenbedrag: =A3. Vanaf de tweede periode is het de restschuld van de vorige periode en daarvan afgetrokken het afgelost bedrag van de vorige periode. Omdat dat hier een negatieve waarde is, moet de formule zijn: =B8+D8. En die kopieer je door naar de rest van die kolom (zie ook de Bonustip onderaan).

Cumulatieve rente

Om te bekijken hoeveel de totale rente is die je al hebt betaald in een bepaalde periode gebruik je de functie CUM.RENTE. Die is hetzelfde in de hele kolom. In de afbeelding zie je de formule van de eerste cel in deze kolom. Naast Rente, Aantal_termijnen en Hw heb je hier Startperiode en Eindperiode als verplichte argumenten. De Startperiode is de eerste periode (dus A8). Die moet worden vastgezet met $-tekens omdat die voor de hele kolom gelijk is. De Eindperiode is de periode waar deze formule nu staat. Bij de eerste in de kolom is dat A8, bij de tweede A9 enz. Dus ook A8 maar dan zonder de $-tekens.
Ook hier is een Type_getal, maar die is hier verplicht. Zorg dat dit hetzelfde is als bij de andere functies, in mijn voorbeeld 0 (betaling aan het einde van de periode).

Cumulatieve aflossing

Om te bekijken hoeveel je totaal al hebt afgelost in een periode gebruik je de functie CUM.HOOFDSOM. Die berekening is hetzelfde in de hele kolom. In de afbeelding zie je weer de formule van de eerste cel in deze kolom. Deze functie heeft exact dezelfde argumenten als CUM.RENTE.

Bonustip

Zelf vind ik het altijd prettig als in de hele kolom dezelfde berekening staat. Dat is hier overal het geval, behalve bij de kolom met de restschuld. Als je daar onderstaande formule gebruikt, kun je wel in de hele kolom dezelfde berekening gebruiken. In feite is het overal de cel erboven + de aflossing. Maar bij de 1^e periode is de cel erboven een tekst, dus zou dat een foutmelding opleveren. Met de functie ALS.FOUT kun je dat ondervangen. De waarde die je wilt zien is B7+D7, maar als dat een fout oplevert, wil je dat B3 gebruikt wordt. In de afbeelding zie je dat die een #WAARDE! oplevert bij de 1^e periode.
Deze formule kun je wel door kopiëren naar de hele kolom.

Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Wil je bijvoorbeeld meer weten over dynamische berekeningen, volg dan de Online Excel Expert-training voor de accountant: leer nieuwe geheimen van Excel.