Excel-samengestelde rente formule: een complete gids

Samengestelde rente begrijpen is cruciaal voor financiële analyse, omdat het een grote rol speelt in persoonlijke financiën, beleggingsstrategieën en zakelijke besluitvorming. Excel maakt deze berekeningen eenvoudiger, zodat je toekomstige besparingen kunt voorspellen, rendementen kunt beoordelen en aflossingen kunt plannen.

Deze gids loodst je door verschillende methoden om samengestelde rente in Excel te berekenen, van basisformules tot geavanceerde technieken. Onze Financial Modeling in Excel-cursus bouwt verder op de technieken en strategieën die in deze tutorial worden besproken. Als je deze concepten onder de knie krijgt, kun je je vaardigheden in financiële analyse flink verbeteren.

Wat is samengestelde rente?

Samengestelde rente staat voor de exponentiële groei van geld in de tijd. In tegenstelling tot enkelvoudige rente, die lineair groeit, versnelt samengestelde rente doordat de rente in elke periode onderdeel wordt van de hoofdsom voor de volgende periode.

De kracht van samengestelde rente wordt duidelijk als je langetermijnbeleggingen visualiseert. Stel dat een klein verschil in rente (zoals 6% versus 8%) over decennia tot sterk verschillende uitkomsten kan leiden. Dat kan het verschil betekenen tussen een comfortabele oude dag en financiële stress.

De formule voor samengestelde rente

Samengestelde rente wordt berekend met de formule:

Formule voor samengestelde rente. Afbeelding door de auteur.

Waarbij:

• A = Eindbedrag na rente
• P = Hoofdsom (initiële inleg)
• r = Jaarlijkse rentevoet (in decimale vorm)
• n = Aantal keren per jaar dat de rente wordt gekapitaliseerd
• t = Aantal jaren

Nu we het concept en de wiskundige formule begrijpen, gaan we het in Excel toepassen.

Samengestelde rente berekenen in Excel

Om deze formule in Excel te gebruiken, maak je een eenvoudige spreadsheetstructuur met duidelijk gelabelde cellen voor elke variabele.

Orden je spreadsheet eerst met de volgende labels:

• Cel A1: "Hoofdsom"
• Cel A2: "Jaarlijkse rentevoet"
• Cel A3: "Kapitalisatiemomenten per jaar"
• Cel A4: "Tijd (jaren)"
• Cel A5: "Eindbedrag"

Voer vervolgens je waarden in kolom B in:

• Cel B1: Voer je hoofdsom in (bijv. 1000)

• Cel B2: Voer je rentevoet in als decimaal (bijv. 0.05 voor 5%)

• Cel B3: Voer het aantal kapitalisatiemomenten per jaar in (bijv. 12 voor maandelijks)

• Cel B4: Voer de tijd in jaren in (bijv. 5)

Na het invoeren van de gegevens, ziet je Excel-blad er zo uit:

Het instellen van de Excel-tabel. Afbeelding door de auteur.

Voer ten slotte in cel B5 de formule voor samengestelde rente in:

=B1*(1+B2/B3)^(B3*B4)

Formule voor samengestelde rente in Excel. Afbeelding door de auteur.

Deze formule past de bovenstaande wiskundige vergelijking direct toe en berekent het eindbedrag na samengestelde rente.

Samengestelde rente berekenen. Afbeelding door de auteur.

De functie FV() gebruiken

Hoewel de handmatige formule prima werkt, biedt Excel ingebouwde financiële functies die berekeningen voor samengestelde rente vereenvoudigen. De functie FV() (toekomstige waarde) is vooral handig wanneer je samengestelde rente wilt berekenen met regelmatige stortingen of investeringen.

De syntaxis voor de functie FV() is:

=FV(rate, nper, pmt, [pv], [type])

Waarbij:

• rate = Rente per periode

• nper = Totaal aantal perioden

• pmt = Betaling per periode (voer 0 in als er geen is)

• pv = Contante waarde (initiële hoofdsom), moet als negatief getal worden ingevoerd

• type = Wanneer betalingen verschuldigd zijn (0 voor einde van de periode, 1 voor begin)

Laten we onze spreadsheet aanpassen om de functie FV() te gebruiken:

• Cel A6: "De functie FV() gebruiken"

Typ het volgende in cel B6:

=FV(B2/B3, B3*B4, 0, -B1)

Samengestelde rente berekenen met FV(). Afbeelding door de auteur.

Let op dat we de jaarlijkse rente delen door het aantal kapitalisatiemomenten om de rente per periode te krijgen, en dat we de hoofdsom als een negatief getal invoeren, zoals de functie vereist.

Je zou de berekende samengestelde rente als volgt moeten zien:

Samengestelde rente met FV(). Afbeelding door de auteur.

De functie PMT() gebruiken

De functie PMT() berekent de betaling voor een lening op basis van constante betalingen en een constante rente. Hoewel deze vooral bedoeld is voor leningbetalingen, kun je hem ook gebruiken in scenario’s met samengestelde rente waarin je wilt bepalen welk vast bedrag je regelmatig moet inleggen om een doelsom te bereiken.

De syntaxis voor de functie PMT() is:

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

Waarbij:

• rate = Rente per periode

• nper = Totaal aantal perioden

• pv = Contante waarde (initiële hoofdsom)

• fv = Toekomstige waarde (het doelbedrag dat je wilt bereiken)

• type = Wanneer betalingen verschuldigd zijn (0 voor einde van de periode, 1 voor begin)

Laten we dit toevoegen aan onze spreadsheet:

• Cel A7: "Doelbedrag"

• Cel B7: Voer je doelbedrag in (bijv. 5000)

• Cel A8: "Benodigde maandelijkse inleg"

Typ het volgende in cel B8:

=PMT(B2/12, B4*12, -B1, B7)

Samengestelde rente berekenen met PMT(). Afbeelding door de auteur.

Deze formule berekent hoeveel je maandelijks moet inleggen om je doelbedrag te halen, gegeven je initiële hoofdsom en rentevoet.

De berekende maandelijkse betaling zie je als volgt:

Samengestelde rente met PMT(). Afbeelding door de auteur.

De functie PMT() in Excel geeft standaard een negatieve waarde terug omdat deze is opgezet vanuit een kasstroomperspectief:

• Negatieve waarden staan voor geld dat je uitgeeft (cash-outflow)
• Positieve waarden staan voor geld dat je ontvangt (cash-inflow)

Samengestelde rente in Excel in de praktijk

De formule voor samengestelde rente in Excel heeft talloze toepassingen in persoonlijke financiën en zakelijke contexten. Laten we enkele van deze praktische scenario’s verkennen.

Sparen en beleggen

Een van de meest voorkomende toepassingen van berekeningen met samengestelde rente is het prognosticeren van de groei van spaar- of beleggingsrekeningen. Begrijpen hoe geld in de tijd groeit, helpt ons om weloverwogen beslissingen te nemen.

Laten we een model voor pensioenopbouw maken in een nieuw gedeelte van onze spreadsheet. Nadat je "Pensioenopbouwprognose" in cel D1 hebt ingevoerd, kun je je parameters instellen in de cellen D2 tot en met D6:

• Cel D2: "Initiële inleg" met E2: 10000

• Cel D3: "Maandelijkse storting" met E3: 500

• Cel D4: "Jaarlijkse rentevoet" met E4: 0.07

• Cel D5: "Tijdshorizon (jaren)" met E5: 30

• Cel D6: "Kapitalisatiefrequentie" met E6: "Maandelijks"

Na het invoeren van "Verwachte toekomstige waarde" in cel D7, typ je de volgende formule in E7:

=FV(E4/12, E5*12, -E3, -E2)

Verwachte toekomstige waarde berekenen. Afbeelding door de auteur.

Deze formule voorspelt je pensioenvermogen, rekening houdend met zowel je initiële inleg als je regelmatige stortingen. De toekomstige waarde zie je als volgt:

Verwachte toekomstige waarde met FV(). Afbeelding door de auteur.

We kunnen dit model uitbreiden met een jaarkalender van de groei:

• Voer in cel D9 "Jaar-op-jaarprognose" in

• Voer in cel D10 "Jaar" in en in cel E10 "Saldo"

• Voer in cel D11 0 in (startjaar)

• Voer in cel E11 je initiële inleg in: =E2

• Voer in cel D12 1 in

• Bereken in cel E12 het saldo na jaar 1:

=FV($E$4/12, 1*12, -$E$3, -E11)

Een jaar-op-jaarprognosetabel maken. Afbeelding door de auteur.

• Selecteer de cellen D11 en D12 en sleep vervolgens de vulgreep (het kleine vierkantje rechtsonder) naar rij 41 om de jaren 0 t/m 30 te maken
• Selecteer cel E12 en sleep de vulgreep naar rij 41

Excel past de celverwijzingen in elke rij automatisch aan en maakt zo een reeks berekeningen waarbij elk jaar voortbouwt op het saldo van het voorgaande jaar.

Jaar-op-jaartabel met prognoses. Afbeelding door de auteur.

Deze grafiek helpt je te begrijpen hoe je vermogen in de tijd groeit en kan motiveren om consistent te blijven beleggen, doordat het het versneld groeipatroon van samengestelde rente laat zien.

Leningen berekenen

Samengestelde rente geldt ook voor leningen, waarbij de rente wordt gekapitaliseerd over het resterende saldo. Met Excel-functies kun je verschillende aspecten van leningen doorrekenen, van maandlasten tot totaal betaalde rente.

Maak voor een aflossingsschema je leningparameters als volgt aan:

De maandelijkse betaling van de lening berekenen. Afbeelding door de auteur.

Typ in cel H3 het volgende om de maandlast te berekenen:

=PMT(H3/12, H4*12, H2)

Maandlast van lening berekenen met PMT(). Afbeelding door de auteur.

Je ziet de maandlast als:

Maandlast lening. Afbeelding door de auteur.

Maak vervolgens de kop van de aflossingstabel als volgt aan:

Aflossingstabel maken. Afbeelding door de auteur.

Voer de beginwaarden in voor de eerste betaling (rij 8):

• Cel G8: 1 (eerste betaling)

• Cel H8: =H2 (oorspronkelijk leenbedrag)

• Cel I8: =ABS(H5) (betaalbedrag, met ABS() om het negatieve PMT()-resultaat positief te maken)

• Cel J8: =H8*($H$3/12) (rentedeel: beginsaldo × maandrente)

• Cel K8: =I8-J8 (aflossingsdeel: betaalbedrag − rentedeel)

• Cel L8: =H8-K8 (eindsaldo: beginsaldo − aflossingsdeel)

Berekeningen voor de aflossingstabel. Afbeelding door de auteur.

Maak voor de tweede betaling (rij 9) de volgende formules:

• Cel G9: 2 (tweede betaling)

• Cel H9: =L8 (beginsaldo is het eindsaldo van de vorige betaling)

• Cel I9: =I8 (betaalbedrag blijft gelijk)

• Cel J9: =H9*($H$3/12) (rentedeel gebaseerd op nieuw beginsaldo)

• Cel K9: =I9-J9 (aflossingsdeel)

• Cel L9: =H9-K9 (eindsaldo)

Berekeningen voor de aflossingstabel. Afbeelding door de auteur.

Selecteer daarna de cellen H9 t/m L9 en sleep omlaag tot rij 367 (voor alle 360 betalingen).

In cel H6 kunnen we de totaal betaalde rente berekenen: 

=SUM(J8:J367)

Het uiteindelijke schema ziet er zo uit:

Aflossingstabel. Afbeelding door de auteur.

Dit aflossingsschema laat zien hoe elke betaling je leningsaldo beïnvloedt en hoeveel rente je in totaal betaalt over de looptijd. Vroege betalingen bestaan vooral uit rente, terwijl latere betalingen vooral de hoofdsom verlagen—een belangrijk inzicht bij het begrijpen van hypotheken.

Geavanceerde technieken voor samengestelde rente

Laten we scenario’s verkennen waarin geavanceerde technieken nodig zijn, zoals omgaan met onregelmatige kapitalisatieperiodes en variabele rentevoeten.

Variabele rentevoeten

In de praktijk komen variabele rentevoeten vaak voor, zoals bij hypotheken met variabele rente of aan de markt gekoppelde beleggingen. Met wat extra opzet kan Excel deze complexere scenario’s aan.

We kunnen een model maken met verschillende rentevoeten voor verschillende perioden. Laten we een tabel met variabele rente opzetten waarbij de rente elke 5 jaar met 0,5% stijgt:

Een tabel opzetten voor variabele rentevoeten. Afbeelding door de auteur.

Om het eindsaldo van jaar 1 en het beginsaldo van jaar 2 te berekenen, typ je het volgende:

• Cel Q4: =P4*(1+O4)

• Cel P5: =Q4

Berekeningen met variabele rentevoeten. Afbeelding door de auteur.

We kunnen nu de rijen voor jaren 1–2 selecteren en omlaag slepen om de tabel uit te breiden tot het gewenste aantal jaren, waarbij je de rentevoeten naar behoefte aanpast, zoals in de onderstaande afbeelding:

Rekenmodel voor variabele rentevoeten. Afbeelding door de auteur.

Met dit model kun je visualiseren hoe veranderende rentevoeten je investering of lening in de tijd beïnvloeden. 

Kapitalisatiefrequentie

Financiële producten gebruiken vaak niet-standaard kapitalisatieperioden. Sommige beleggingen kapitaliseren per kwartaal, andere maandelijks en sommige zelfs dagelijks. Deze verschillen in frequentie kunnen je rendement aanzienlijk beïnvloeden.

Om hiermee in Excel rekening te houden, kun je de formule voor samengestelde rente dienovereenkomstig aanpassen. Stel dat je een investering van $10.000 tegen 6% jaarlijkse rente met dagelijkse kapitalisatie gedurende 5 jaar wilt berekenen; dan gebruik je deze formule:

=10000*(1+0.06/365)^(365*5)

Een voorbeeldcalculator voor onregelmatige kapitalisatie ziet er zo uit:

Calculator voor onregelmatige kapitalisatie. Afbeelding door de auteur.

Deze vergelijking laat duidelijk zien hoe de kapitalisatiefrequentie je beleggingsrendement beïnvloedt.

Zoals je ziet levert vaker kapitaliseren hogere rendementen op, al neemt het extra voordeel af naarmate de frequentie toeneemt. Dit inzicht is vooral waardevol bij het beoordelen van financiële producten die verschillende kapitalisatiemethoden hanteren.

Conclusie

In deze gids hebben we diverse praktische toepassingen van Excel-formules voor samengestelde rente verkend. Van het begrijpen van de onderliggende wiskundige formule tot het toepassen van Excel-functies zoals FV() en PMT(): je hebt nu de essentiële tools om zinvolle financiële berekeningen uit te voeren.

Wil je je Excel-vaardigheden verder verdiepen buiten deze tutorial? Schrijf je dan in voor onze Financial Modeling in Excel-cursus. Deze cursus bouwt voort op de basisconcepten uit de tutorial met geavanceerde technieken voor financieel modelleren.