Rente-op-rente berekenen in Excel: formule en controle

Voor rente-op-rente op één beginsaldo gebruik je in Excel `=A1*(1+A2)^A3`. Zet het beginsaldo in A1, de rente per periode in A2 en het aantal perioden in A3. De renteperiode en de getelde perioden moeten overeenkomen, bijvoorbeeld allebei jaren of allebei maanden.

Deze berekening is bedoeld voor een vast beginsaldo zonder extra stortingen of opnames. Een periodetabel maakt zichtbaar hoe elke rentebijschrijving het saldo voor de volgende periode verhoogt.

Wat je nodig hebt voor de berekening

Noteer vooraf het beginsaldo, de rente per periode en het totale aantal perioden. Een rente per jaar hoort bij een aantal jaren; een maandrente hoort bij een aantal maanden. Als de opgegeven rente jaarlijks is, moet je ook weten of die nominaal of effectief is voordat je haar omzet naar een perioderente.

Rente-op-rente berekenen in Excel

1. Vul het beginsaldo, de perioderente en het aantal perioden in

Zet het beginsaldo in A1, de rente per periode in A2 en het aantal perioden in A3. Voor een beginsaldo van € 1.000, een jaarlijkse rente van 5% en twee jaren vul je `1000`, `5%` en `2` in. Zo gebruikt de formule één rentepercentage per jaarlijkse periode.

2. Bereken de eindwaarde met de formule

Voer in een lege cel `=A1*(1+A2)^A3` in. Bij € 1.000, 5% per jaar en twee jaarlijkse perioden is de uitkomst € 1.102,50. De formule rekent met een constante rente per periode en bevat geen extra stortingen of opnames.

3. Zet de jaarrente goed om voor een maandberekening

Bij een nominale jaarrente van 5% die maandelijks wordt gekapitaliseerd, gebruik je `5%/12` als rente per maand. Voor twee jaar vul je `24` perioden in. Met hetzelfde beginsaldo van € 1.000 geeft `=1000*(1+5%/12)^24` ongeveer € 1.104,94. Dit is een berekening op basis van die aannames, geen opbrengst van een financieel product.Is de jaarrente effectief, dan levert delen door 12 niet in het algemeen de gelijkwaardige maandrente op. Om dezelfde effectieve jaarrente te behouden, bereken je de maandrente als `(1+jaarrente)^(1/12)-1`.

Het verschil tussen de jaarlijkse uitkomst van € 1.102,50 en de maandelijkse uitkomst van ongeveer € 1.104,94 komt door de gekozen kapitalisatieperiode bij dezelfde nominale jaarrente. Het is dus belangrijk dat de rente die je invoert past bij de manier waarop perioden worden geteld.

Controleer de uitkomst met een periodetabel

Maak voor elke periode een rij met de beginstand, de rente over die periode en de eindstand. Bereken de rente door de beginstand met de perioderente te vermenigvuldigen. Tel die rente bij de beginstand op; gebruik de eindstand vervolgens als beginstand van de volgende rij.

Bij twee jaarlijkse perioden tegen 5% begint jaar 2 met € 1.050,00. De rente in dat jaar is daardoor € 52,50, berekend over de nieuwe beginstand. De laatste eindstand moet gelijk zijn aan de uitkomst van de directe formule.

Wat als je ook stortingen of opnames hebt?

De machtsformule berekent alleen de groei van één beginsaldo. Excel heeft ook een functie voor toekomstige waarde, FV, die een beginsaldo en een vaste betaling per periode kan meenemen. De functie rekent met betalingen aan het begin of einde van de periode; onder de documenteerde tekenconventie voer je een storting als uitgaande betaling als negatief bedrag in.

Wisselende of onregelmatige stortingen en opnames passen niet bij één vaste betaling per periode. Houd dan per periode het beginsaldo, de rente en elke daadwerkelijke storting of opname bij. Zo wordt duidelijk welk saldo de rente in die periode draagt.

Veelgemaakte fouten bij de Excel-formule

• Een jaarrente combineren met een aantal maanden: zet de rente om naar een maandrente of tel perioden in jaren.
• Een percentage als `5` invoeren in plaats van `5%`: zo verandert de ingevoerde rente van 5% in 500%.
• Stortingen of opnames vergeten: die zitten niet in de formule voor één beginsaldo.
• Een berekende uitkomst behandelen als een contractuele rente of gegarandeerde opbrengst: de spreadsheet rekent alleen met de ingevoerde aannames.

Zo weet je of de berekening klopt

De controle slaagt wanneer de laatste eindstand in je periodetabel gelijk is aan de uitkomst van de formule. Controleer bij een verschil of beide berekeningen dezelfde rente per periode, hetzelfde aantal perioden en dezelfde stortingen of opnames gebruiken. De formule bepaalt op zichzelf niet welke rente, kosten, belastingen of voorwaarden bij een bankproduct of lening gelden.

Kort naslagoverzicht

• Eén beginsaldo, geen extra geldstromen: `=A1*(1+A2)^A3`.
• Vaste rente en perioden: gebruik dezelfde tijdseenheid voor beide.
• Vaste stortingen: gebruik een berekening voor toekomstige waarde met de juiste betaalmomenten.
• Wisselende stortingen of opnames: werk met een afzonderlijke rij voor iedere periode.