Met de financiele functies in Excel bereken je maandlasten, rentes en spaarplannen binnen enkele seconden. In dit artikel leer je stap voor stap hoe je de BET-, HW-, TW-, RENTE- en NPER-functies gebruikt, met praktische voorbeelden voor hypotheekberekeningen en spaardoelen.
Wat zijn financiele functies in Excel?
Excel biedt een krachtige set financiele functies waarmee je leningen, investeringen en spaarplannen doorrekent zonder ingewikkelde formules te onthouden. De vijf belangrijkste functies zijn:
- BET (Engels: PMT) – berekent de periodieke betaling voor een lening
- HW (Engels: PV) – berekent de huidige waarde van toekomstige betalingen
- TW (Engels: FV) – berekent de toekomstige waarde van een investering
- RENTE (Engels: RATE) – berekent het rentepercentage
- NPER – berekent het aantal betalingsperiodes
Deze functies werken nauw samen: ze gebruiken dezelfde variabelen maar berekenen telkens een andere onbekende. Als je vier van de vijf variabelen kent, bereken je met de juiste functie de vijfde.
Belangrijk: positieve en negatieve cashflows begrijpen
Voordat je met financiele functies werkt, moet je het concept van cashflows begrijpen. Excel gebruikt plus en min om geldstromen aan te duiden:
- Negatief (-): geld dat je betaalt of investeert (uitgaande stroom)
- Positief (+): geld dat je ontvangt (inkomende stroom)
Bij een lening:
– De lening die je ontvangt is positief (HW)
– De maandelijkse aflossing is negatief (BET)
– Het eindsaldo is nul (TW)
Bij sparen:
– Je maandelijkse inleg is negatief (je betaalt in)
– Het eindbedrag dat je ontvangt is positief (TW)
Tip: Als je een onverwacht minteken krijgt, draai dan het teken om van je invoerwaarden of zet een minteken voor de formule.
BET functie: periodieke betalingen berekenen
De BET-functie (Payment) berekent de maandelijkse of periodieke betaling voor een lening met vaste rente. Dit is de meest gebruikte financiele functie.
Syntax van BET
=BET(rente; aantal_termijnen; hw; [tw]; [type])
Argumenten:
- rente (verplicht): Het rentepercentage per periode. Bij maandelijkse betalingen deel je de jaarrente door 12.
- aantal_termijnen (verplicht): Het totaal aantal betalingen. Bij maandelijkse betalingen vermenigvuldig je het aantal jaren met 12.
- hw (verplicht): De huidige waarde, oftewel het geleende bedrag.
- tw (optioneel): De toekomstige waarde, oftewel het gewenste eindsaldo. Standaard 0.
- type (optioneel): 0 = betaling aan einde periode, 1 = betaling aan begin periode. Standaard 0.
Stap-voor-stap: maandlasten berekenen met BET
Scenario: Je wilt een autolening van 25.000 euro afsluiten tegen 6% rente per jaar. De looptijd is 5 jaar. Wat zijn je maandlasten?
Stap 1: Zet je gegevens in cellen
| A | B |
|---|---|
| Leenbedrag | 25000 |
| Jaarrente | 6% |
| Looptijd (jaren) | 5 |
Stap 2: Typ de BET-formule
In cel B5 typ je:
=BET(B2/12;B3*12;B1)
Uitleg van de formule:
– B2/12: jaarrente van 6% gedeeld door 12 = 0,5% per maand
– B3*12: 5 jaar keer 12 = 60 maanden
– B1: het leenbedrag van 25.000 euro
Stap 3: Bevestig de formule
– Windows: Druk op Enter
– Mac: Druk op Return
Resultaat: -483,32 euro (negatief omdat het een uitgave is)
Voorbeelden met BET
| Situatie | Formule | Resultaat |
|---|---|---|
| Hypotheek 300.000 euro, 4% rente, 30 jaar | =BET(4%/12;30*12;300000) | -1.432,25 |
| Studielening 20.000 euro, 2% rente, 15 jaar | =BET(2%/12;15*12;20000) | -128,82 |
| Persoonlijke lening 10.000 euro, 8% rente, 3 jaar | =BET(8%/12;3*12;10000) | -313,36 |
HW functie: huidige waarde berekenen
De HW-functie (Present Value) berekent hoeveel een reeks toekomstige betalingen vandaag waard is. Dit is handig om te bepalen hoeveel je kunt lenen bij een bepaalde maandlast.
Syntax van HW
=HW(rente; aantal_termijnen; bet; [tw]; [type])
Argumenten:
- rente (verplicht): Het rentepercentage per periode
- aantal_termijnen (verplicht): Het totaal aantal betalingen
- bet (verplicht): De betaling per periode (negatief voor uitgaven)
- tw (optioneel): De toekomstige waarde. Standaard 0.
- type (optioneel): 0 = einde periode, 1 = begin periode. Standaard 0.
Stap-voor-stap: maximale leencapaciteit berekenen
Scenario: Je kunt maximaal 1.200 euro per maand betalen. De rente is 4,5% per jaar en de looptijd 25 jaar. Hoeveel kun je lenen?
Stap 1: Zet je gegevens in cellen
| A | B |
|---|---|
| Maandlast | -1200 |
| Jaarrente | 4,5% |
| Looptijd (jaren) | 25 |
Stap 2: Typ de HW-formule
=HW(B2/12;B3*12;B1)
Resultaat: 205.724,39 euro
Dit betekent dat je bij een maandlast van 1.200 euro en een rente van 4,5% maximaal ongeveer 205.700 euro kunt lenen.
Voorbeelden met HW
| Situatie | Formule | Resultaat |
|---|---|---|
| Max. lenen bij 800 euro/maand, 5% rente, 30 jaar | =HW(5%/12;30*12;-800) | 149.028,81 |
| Waarde pensioenuitkering 2000/maand, 3%, 20 jaar | =HW(3%/12;20*12;-2000) | 358.682,97 |
TW functie: toekomstige waarde berekenen
De TW-functie (Future Value) berekent hoeveel je spaargeld waard zal zijn na een bepaalde periode met rente. Perfect voor het plannen van spaardoelen.
Syntax van TW
=TW(rente; aantal_termijnen; bet; [hw]; [type])
Argumenten:
- rente (verplicht): Het rentepercentage per periode
- aantal_termijnen (verplicht): Het totaal aantal betalingen
- bet (verplicht): De betaling per periode (negatief voor inleg)
- hw (optioneel): De huidige waarde (startkapitaal). Standaard 0.
- type (optioneel): 0 = einde periode, 1 = begin periode. Standaard 0.
Stap-voor-stap: spaardoel berekenen
Scenario: Je spaart 200 euro per maand tegen 3% rente per jaar. Je begint met 5.000 euro. Hoeveel heb je na 10 jaar?
Stap 1: Zet je gegevens in cellen
| A | B |
|---|---|
| Maandelijkse inleg | -200 |
| Jaarrente | 3% |
| Looptijd (jaren) | 10 |
| Startkapitaal | -5000 |
Stap 2: Typ de TW-formule
=TW(B2/12;B3*12;B1;B4)
Resultaat: 34.685,88 euro
Na 10 jaar heb je bijna 35.000 euro opgebouwd, terwijl je in totaal maar 29.000 euro hebt ingelegd (5.000 + 200 x 120). De rest is rente.
Voorbeelden met TW
| Situatie | Formule | Resultaat |
|---|---|---|
| 100 euro/maand, 4% rente, 20 jaar | =TW(4%/12;20*12;-100) | 36.677,46 |
| 10.000 startkapitaal, 5% rente, 15 jaar, geen inleg | =TW(5%/12;15*12;0;-10000) | 21.137,04 |
| 500 euro/maand, 2,5% rente, 5 jaar | =TW(2,5%/12;5*12;-500) | 31.908,77 |
RENTE functie: rentepercentage berekenen
De RENTE-functie berekent het rentepercentage wanneer je de andere variabelen kent. Handig om aanbiedingen te vergelijken.
Syntax van RENTE
=RENTE(aantal_termijnen; bet; hw; [tw]; [type]; [schatting])
Argumenten:
- aantal_termijnen (verplicht): Het totaal aantal betalingen
- bet (verplicht): De betaling per periode
- hw (verplicht): De huidige waarde (leenbedrag)
- tw (optioneel): De toekomstige waarde. Standaard 0.
- type (optioneel): 0 = einde periode, 1 = begin periode. Standaard 0.
- schatting (optioneel): Beginschatting voor de rente. Standaard 10%.
Stap-voor-stap: werkelijke rente van een lening bepalen
Scenario: Je leent 15.000 euro en betaalt 36 maanden lang 475 euro. Wat is de effectieve maandrente?
Stap 1: Zet je gegevens in cellen
| A | B |
|---|---|
| Leenbedrag | 15000 |
| Maandlast | -475 |
| Aantal maanden | 36 |
Stap 2: Typ de RENTE-formule
=RENTE(B3;B2;B1)
Resultaat: 0,84% per maand, oftewel 10,1% per jaar (0,84% x 12)
Tip: Vermenigvuldig het resultaat met 12 om de jaarrente te krijgen:
=RENTE(B3;B2;B1)*12
Voorbeelden met RENTE
| Situatie | Formule | Jaarrente |
|---|---|---|
| 20.000 lenen, 60 x 400 betalen | =RENTE(60;-400;20000)*12 | 7,42% |
| 5.000 lenen, 24 x 225 betalen | =RENTE(24;-225;5000)*12 | 8,76% |
NPER functie: aantal periodes berekenen
De NPER-functie berekent hoeveel betalingen nodig zijn om een lening af te lossen of een spaardoel te bereiken.
Syntax van NPER
=NPER(rente; bet; hw; [tw]; [type])
Argumenten:
- rente (verplicht): Het rentepercentage per periode
- bet (verplicht): De betaling per periode
- hw (verplicht): De huidige waarde
- tw (optioneel): De toekomstige waarde. Standaard 0.
- type (optioneel): 0 = einde periode, 1 = begin periode. Standaard 0.
Stap-voor-stap: looptijd van een lening berekenen
Scenario: Je hebt een schuld van 8.000 euro met 9% rente per jaar. Je kunt 250 euro per maand aflossen. Hoe lang duurt het om schuldenvrij te zijn?
Stap 1: Zet je gegevens in cellen
| A | B |
|---|---|
| Schuld | 8000 |
| Jaarrente | 9% |
| Maandlast | -250 |
Stap 2: Typ de NPER-formule
=NPER(B2/12;B3;B1)
Resultaat: 36,5 maanden (ongeveer 3 jaar)
Stap-voor-stap: tijd tot spaardoel berekenen
Scenario: Je wilt 50.000 euro sparen voor een auto. Je spaart 600 euro per maand tegen 2% rente. Hoe lang duurt het?
=NPER(2%/12;-600;0;50000)
Resultaat: 78 maanden (6,5 jaar)
Praktijkvoorbeeld 1: complete hypotheekberekening
Laten we een volledige hypotheek doorrekenen met alle functies.
Situatie
Je overweegt een huis te kopen van 350.000 euro. Je hebt 50.000 euro eigen geld. De hypotheekrente is 4,2% per jaar en de looptijd is 30 jaar.
Stap 1: Stel je rekenschema op
| A | B | C |
|---|---|---|
| Koopsom | 350000 | |
| Eigen geld | 50000 | |
| Hypotheekbedrag | =B1-B2 | 300000 |
| Jaarrente | 4,2% | |
| Looptijd (jaren) | 30 | |
| Aantal termijnen | =B5*12 | 360 |
Stap 2: Bereken de maandlasten met BET
=BET(B4/12;B6;B3)
Resultaat: -1.467,09 euro per maand
Stap 3: Bereken totale kosten
| A | B | C |
|---|---|---|
| Totaal te betalen | =ABS(B7)*B6 | 528.152,40 |
| Totaal aan rente | =B8-B3 | 228.152,40 |
Je betaalt in totaal ruim 228.000 euro aan rente over 30 jaar.
Stap 4: Wat-als analyse met verschillende looptijden
Combineer dit met de wat-als analyse functies om verschillende scenario’s te vergelijken:
| Looptijd | Maandlast | Totale rente |
|---|---|---|
| 20 jaar | -1.854,24 | 145.017,60 |
| 25 jaar | -1.621,31 | 186.393,00 |
| 30 jaar | -1.467,09 | 228.152,40 |
Praktijkvoorbeeld 2: spaardoel voor studie
Je wilt over 18 jaar 30.000 euro gespaard hebben voor de studie van je kind.
Scenario A: hoeveel moet je maandelijks sparen?
Gegeven: 30.000 euro doel, 18 jaar, 3% rente per jaar
We gebruiken BET met TW als doel:
=BET(3%/12;18*12;0;30000)
Resultaat: -104,69 euro per maand
Je moet ongeveer 105 euro per maand sparen om over 18 jaar 30.000 euro te hebben.
Scenario B: hoeveel heb je als je 150 euro per maand spaart?
=TW(3%/12;18*12;-150;0)
Resultaat: 42.980,45 euro
Met 150 euro per maand bereik je zelfs meer dan je doel.
Scenario C: hoeveel startkapitaal is nodig als je 100 euro per maand spaart?
Je wilt precies 30.000 euro bereiken met 100 euro per maand. Hoeveel startkapitaal is nodig?
=HW(3%/12;18*12;-100;30000)
Resultaat: -6.785,98 euro (negatief = je moet dit bedrag inleggen)
Met 6.786 euro startkapitaal en 100 euro per maand heb je na 18 jaar precies 30.000 euro.
Overzichtstabel financiele functies
| Functie (NL) | Functie (EN) | Berekent | Typisch gebruik |
|---|---|---|---|
| BET | PMT | Periodieke betaling | Maandlasten lening |
| HW | PV | Huidige waarde | Maximale leencapaciteit |
| TW | FV | Toekomstige waarde | Eindwaarde spaargeld |
| RENTE | RATE | Rentepercentage | Leningen vergelijken |
| NPER | NPER | Aantal periodes | Looptijd berekenen |
Geavanceerde financiele functies
Naast de vijf basisfuncties heeft Excel nog meer financiele functies:
IBET en PBET: rente en aflossing apart berekenen
- IBET (IPMT): berekent het rentedeel van een specifieke termijn
- PBET (PPMT): berekent het aflossingsdeel van een specifieke termijn
=IBET(rente;periode;aantal_termijnen;hw)
=PBET(rente;periode;aantal_termijnen;hw)
Voorbeeld: aflossingsschema
Voor termijn 1 van een lening van 10.000 euro, 5% rente, 60 maanden:
=IBET(5%/12;1;60;10000) // Rente: -41,67
=PBET(5%/12;1;60;10000) // Aflossing: -147,01
De som is gelijk aan BET: -41,67 + -147,01 = -188,68 euro.
CUM.RENTE en CUM.HOOFDSOM: cumulatieve berekeningen
- CUM.RENTE (CUMIPMT): totale rente over een periode
- CUM.HOOFDSOM (CUMPRINC): totale aflossing over een periode
Dit is handig voor fiscale berekeningen waar je de totale rente per jaar nodig hebt.
Veelgemaakte fouten bij financiele functies
Fout 1: jaarrente niet omrekenen naar maandrente
Probleem: De berekende maandlast is veel te hoog.
Oorzaak: Je hebt de jaarrente ingevuld in plaats van de maandrente.
Oplossing: Deel de jaarrente altijd door 12:
// Fout:
=BET(5%;60;100000) // Resultaat: -6.504
// Correct:
=BET(5%/12;60;100000) // Resultaat: -1.887
Fout 2: jaren niet omrekenen naar maanden
Probleem: Onrealistische resultaten.
Oorzaak: Je hebt jaren ingevuld in plaats van het aantal maanden.
Oplossing: Vermenigvuldig het aantal jaren met 12:
// Fout:
=BET(4%/12;5;200000) // 5 periodes
// Correct:
=BET(4%/12;5*12;200000) // 60 periodes
Fout 3: tekens door elkaar halen
Probleem: Onverwachte mintekens of verkeerde resultaten.
Oorzaak: Verkeerde interpretatie van cashflows.
Oplossing: Onthoud de regel:
– Geld dat je betaalt = negatief
– Geld dat je ontvangt = positief
Of zet simpelweg een minteken voor de hele formule als je een positief resultaat wilt:
=-BET(4%/12;60;25000) // Positief resultaat
Fout 4: vergeten dat RENTE een perioderesultaat geeft
Probleem: De berekende rente lijkt veel te laag.
Oorzaak: RENTE geeft de rente per periode, niet per jaar.
Oplossing: Vermenigvuldig met 12 voor de jaarrente:
=RENTE(60;-450;20000)*12 // Jaarrente
Fout 5: HW en TW verwisselen
Probleem: Formule geeft een foutmelding of verkeerd resultaat.
Oorzaak: Verwarring tussen huidige en toekomstige waarde.
Oplossing: Onthoud:
– HW = wat je nu hebt of leent
– TW = wat je aan het einde wilt hebben
Meer over het oplossen van Excel-foutmeldingen lees je in onze handleiding foutmeldingen.
Combineren met andere Excel-functies
Financiele functies werken nog krachtiger in combinatie met andere functies:
Met ALS voor scenario’s
=ALS(B1<100000;BET(4%/12;30*12;B1);BET(4,5%/12;30*12;B1))
Dit past automatisch een hogere rente toe voor hypotheken boven 100.000 euro. Leer meer over de ALS-functie in onze Excel formules handleiding.
Met AFRONDEN voor nette bedragen
=AFRONDEN(-BET(4%/12;30*12;250000);0)
Dit geeft een afgerond maandbedrag.
Met percentageberekeningen
Combineer financiele functies met percentageberekeningen om rendementen te analyseren.
Tips voor efficienter werken met financiele functies
- Maak een sjabloon met invoercellen bovenaan en formules eronder. Zo kun je snel verschillende scenario’s doorrekenen.
- Gebruik benoemde bereiken voor leesbaarheid: =BET(Rente/12;Jaren12;Leenbedrag) is duidelijker dan =BET(B2/12;B312;B1).
- Zet percentages als decimalen of met % in Excel. Beide werken: 0,05 en 5% geven hetzelfde resultaat.
- Controleer je berekening door het omgekeerde te berekenen. Als BET -500 geeft, controleer dan of HW het juiste leenbedrag teruggeeft.
- Gebruik de Solver voor complexere financiele optimalisaties. Meer hierover in onze Solver handleiding.
FAQ: Veelgestelde vragen over financiele functies in Excel
Hoe bereken ik maandlasten voor een hypotheek in Excel?
Gebruik de BET-functie: =BET(jaarrente/12;looptijd_jaren12;hypotheekbedrag). Bijvoorbeeld voor 200.000 euro tegen 4% over 30 jaar: =BET(4%/12;3012;200000) geeft -954,83 euro per maand.
Wat is het verschil tussen HW en TW in Excel?
HW (huidige waarde) berekent wat toekomstige betalingen nu waard zijn. TW (toekomstige waarde) berekent wat je huidige geld later waard zal zijn met rente. Bij leningen is HW het leenbedrag, bij sparen is TW je eindbedrag.
Waarom geeft BET een negatief getal?
Excel gebruikt mintekens voor geldstromen. Een negatief resultaat betekent dat je geld betaalt (uitgave). Als je een positief getal wilt, zet dan een minteken voor de formule: =-BET(…).
Hoe bereken ik hoeveel ik maximaal kan lenen?
Gebruik de HW-functie met je maximale maandlast: =HW(jaarrente/12;looptijd_jaren12;maandlast). De maandlast moet negatief zijn. Bijvoorbeeld: =HW(4%/12;3012;-1000) geeft 209.461 euro.
Kan ik de effectieve rente van een lening berekenen?
Ja, met de RENTE-functie: =RENTE(aantal_termijnen;maandlast;leenbedrag)*12. Het resultaat vermenigvuldig je met 12 voor de jaarrente. Let op: voer de maandlast in als negatief getal.
Hoe bereken ik hoelang het duurt om een schuld af te lossen?
Gebruik NPER: =NPER(jaarrente/12;maandaflossing;schuld). Bijvoorbeeld: =NPER(6%/12;-300;10000) geeft 36,5 maanden. De aflossing moet negatief zijn.
Kan ik een aflossingsschema maken in Excel?
Ja, met IBET en PBET bereken je per termijn het rente- en aflossingsdeel. Combineer dit met een reeks rijnummers voor een compleet schema. Dit werkt goed samen met Excel tabellen.
Hoe vergelijk ik verschillende leenopties in Excel?
Maak een tabel met de BET-functie voor elke optie. Vergelijk maandlasten, totale kosten (maandlast x termijnen) en totale rente (totale kosten – leenbedrag). Gebruik de Solver voor optimalisatie.
Kom je er niet uit? Laat mij even meekijken. Neem gerust contact op, ik denk graag mee.
Hulp nodig met financiele berekeningen in Excel?
Wil je een professioneel financieel model bouwen? Of heb je hulp nodig bij complexe rente- en leningberekeningen? Neem gerust contact op voor persoonlijke ondersteuning.
Neem contact op:
– WhatsApp: Stuur een bericht
– E-mail: w.bouwmeester@bouwmeesterconsultancy.nl
– Telefoon: +31 6 28963636
Of bekijk mijn andere Excel-tutorials:
– Excel formules: compleet overzicht
– Procenten berekenen in Excel
– Wat-als analyse in Excel
– Solver in Excel
