PMT() in Excel: bereken aflossingen en spaarbijdragen

De functie PMT() in Excel berekent de periodieke betaling die nodig is om een lening of investering met constante betalingen en een vaste rente af te lossen. Hoewel veelzijdig voor verschillende financiële berekeningen, wordt PMT() het meest gebruikt om aflossingen te bepalen, met name bij hypotheken. De functie wordt ook ingezet in financiële modellering en budgettering om te bepalen hoeveel je per periode voor een lening betaalt of hoeveel je moet inleggen om een spaardoel te bereiken.

Als je bezig bent met zaken als het berekenen van aflossingen en rentetarieven, is het wellicht ook interessant om meer te leren over kasstroomanalyse en investeringsselectie. In dat geval is onze cursus Financial Modeling in Excel de moeite waard. Aanrader ook als je wilt leren hoe je winst-en-verliesrekeningen ontleedt en de tijdswaarde van geld begrijpt.

Kort Antwoord

De functie PMT() in Excel berekent vaste betalingen voor leningen of investeringen met constante betalingen en een vaste rente. Voor een typische hypotheek van $300.000 over 30 jaar met 4,5% jaarlijkse rente bedraagt de maandelijkse betaling circa $1.520,06. De formule is:

=PMT(4.5%/12, 30*12, 300000)

PMT()-syntaxis en parameters

De syntaxis van de functie PMT() is:

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

Laten we elke parameter toelichten:

• rate: Rente per periode. Als je lening een jaarlijkse rente van 6% heeft maar je maandelijks betaalt, gebruik je 6%/12 oftewel 0,5% per maand.

• nper: Totaal aantal betaalperiodes. Voor een lening van 5 jaar met maandelijkse betalingen gebruik je 5*12 oftewel 60 periodes.

• pv: Contante waarde (leningbedrag of hoofdsom). Dit is het initiële bedrag dat je leent of investeert.

• fv (optioneel): Toekomstige waarde na de laatste betaling. Standaard 0 als je dit weglaat, wat betekent dat de lening volledig is afgelost.

• type (optioneel): Moment van betaling. Gebruik 0 voor betalingen aan het einde van de periode (standaard) of 1 voor betalingen aan het begin.

Het is belangrijk om de kasstroomconventie in Excel te begrijpen: betalingen (geld dat uitstroomt) worden als negatieve getallen weergegeven, terwijl instromen (geld dat binnenkomt) positief zijn. Daarom verschijnen resultaten van de functie PMT() doorgaans als negatieve waarden—ze staan voor uitgaande betalingen.

Inzicht in aanpassingen voor betaalsnelheid

Wanneer je de functie PMT() gebruikt, moet je de rente en het aantal periodes aanpassen op basis van de samenstellingsfrequentie van de lening. Veel leningen vermelden een jaarlijkse rente, maar betalingen vinden vaak vaker plaats.

Zo pas je de jaarlijkse rente en het aantal periodes aan bij verschillende betaalfrequenties:

• Maandelijks: jaarlijkse rente ÷ 12, aantal jaren × 12
• Per kwartaal: jaarlijkse rente ÷ 4, aantal jaren × 4
• Halfjaarlijks: jaarlijkse rente ÷ 2, aantal jaren × 2

Stel, je hebt een lening van 5 jaar met 6% jaarlijkse rente en maandelijkse betalingen. Dan gebruik je:

• Rente: 6%/12 = 0,5% per maand
• Aantal periodes: 5×12 = 60 maanden

Deze tabel vat de aanpassingen samen voor een lening van 5 jaar met 6% jaarlijkse rente:

Door deze aanpassingen zorg je ervoor dat je berekeningen de samenstellingsfrequentie van je lening of investering correct weerspiegelen.

Veelvoorkomende toepassingen van de Excel-functie PMT()

Laten we kijken naar de twee meest voorkomende toepassingen. 

De functie PMT() voor hypotheken

Laten we een praktijkvoorbeeld doorlopen om maandelijkse hypotheekbetalingen te berekenen:

• Leningbedrag: $400.000
• Rente: 6% per jaar
• Looptijd: 20 jaar
• Betaalfrequentie: Maandelijks

Eerst passen we de rente en periodes aan:

• Aangepaste rente: 6%/12 = 0,5% per maand
• Aangepaste periodes: 20×12 = 240 maanden

De formule is dan:

PMT()-functie voor hypotheeklening. Afbeelding door auteur.

=PMT(6%/12, 20*12, 400000)

Dit levert -$2.865,72 op. Het minteken geeft aan dat dit een betaling is (uitstroom van geld). Je maandelijkse hypotheekbetaling bedraagt dus $2.865,72.

De functie PMT() voor sparen en beleggen

De functie PMT() kan ook helpen berekenen hoeveel je regelmatig moet sparen om een toekomstig financieel doel te halen.

Stel dat je over 18 jaar $50.000 wilt sparen met 6% jaarlijks rendement. Hoeveel moet je maandelijks inleggen?

In dit geval:

• Toekomstige waarde (fv): $50.000 (je doel)
• Contante waarde (pv): 0 (ervan uitgaande dat je vanaf nul begint)
• Rente: 6%/12 = 0,5% per maand
• Periodes: 18×12 = 216 maanden

De formule is dan:

PMT()-functie voor sparen en beleggen. Afbeelding door auteur.

=PMT(6%/12, 18*12, 0, 50000)

Dit geeft ongeveer $129,08, wat betekent dat je maandelijks circa $130 moet storten om je doel van $50.000 in 18 jaar te halen, uitgaande van 6% jaarlijks rendement.

In dit geval gebruiken we een positieve waarde voor de toekomstige waarde (50000) omdat dit geld vertegenwoordigt dat je in de toekomst ontvangt. Bij het berekenen hoeveel je moet sparen om een doel te bereiken, zal het PMT()-resultaat negatief zijn (wat aangeeft dat je dat bedrag moet inleggen/betalen), en de toekomstige waarde positief (geld dat je aan het einde hebt).

Veelvoorkomende fouten en probleemoplossing

Bij het werken met de functie PMT() kun je foutmeldingen of onverwachte resultaten tegenkomen. Hier zijn enkele veelvoorkomende problemen en hoe je ze oplost:

#NUM!- en #VALUE!-fouten

De functie PMT() kan deze fouten om verschillende redenen teruggeven:

• #NUM! verschijnt doorgaans wanneer Excel met de opgegeven invoer geen geldig resultaat kan berekenen. Dit kan gebeuren als je rente extreem hoog is of je looptijd uitzonderlijk lang.

• #VALUE! duidt meestal op een probleem met een van je invoerwaarden, zoals tekst waar een getal wordt verwacht.

Veelgemaakte fouten om te vermijden

Vergeten om rente en nper aan te passen voor maandelijkse/kwartaalbetalingen

Een van de meest voorkomende fouten is de jaarlijkse rente direct gebruiken in plaats van te delen door het aantal betalingen per jaar. Bijvoorbeeld 5% gebruiken in plaats van 5%/12 voor maandelijkse betalingen.

Incorrect: =PMT(5%, 30*12, 250000)
Correct: =PMT(5%/12, 30*12, 250000)

Niet-numerieke waarden invoeren

Zorg dat alle parameters numerieke waarden of celverwijzingen naar numerieke waarden bevatten. Per ongeluk tekst of lege cellen opnemen veroorzaakt fouten.

Onjuiste tekens gebruiken (positief in plaats van negatief)

Onthoud dat bij financiële functies geld dat je uitgeeft negatief moet zijn en geld dat je ontvangt positief. Als je leningbedrag (pv) als negatief wordt ingevoerd, zal je betaling positief lijken (en omgekeerd). Voor leningen:

If PV is positive: PMT will be negative (you're paying out)
If PV is negative: PMT will be positive (appears as money coming in)

Het aantal periodes door elkaar halen

Zorg dat je nper (aantal periodes) overeenkomt met je renteperiode. Als je je rente naar een maandcijfer hebt aangepast, moeten je periodes ook in maanden zijn.

Door deze veelvoorkomende fouten te voorkomen, krijg je nauwkeurige resultaten met de functie PMT() voor financiële berekeningen.

Geavanceerde use-cases en gerelateerde functies

De functie PMT() werkt goed samen met andere financiële functies in Excel om uitgebreide leenanalyses en financiële modellen te maken. Zo integreert PMT() met andere financiële functies:

IPMT (rentedeel van de betaling)

De functie IPMT() berekent het rentedeel van een leningbetaling voor een specifieke periode. In combinatie met PMT() zie je hoeveel van elke betaling naar rente gaat.

=IPMT(rate, per, nper, pv, [fv], [type])

Terugkomend op ons eerdere hypotheekvoorbeeld van $300.000 kunnen we het rentedeel van de 12e betaling vinden (30 jaar hypotheek tegen 4,5% jaarlijkse rente):

IPMT()-functie voor hypotheeklening. Afbeelding door auteur.

=IPMT(4.5%/12, 12, 30*12, 300000)

Dit geeft -$1.108,39, wat betekent dat $1.108,39 van deze betaling naar rente gaat.

PPMT (aflossingsdeel van de betaling)

De functie PPMT() berekent het aflossingsdeel van een leningbetaling voor een specifieke periode. Dit laat zien hoeveel van je betaling de eigenlijke leensaldo verlaagt.

=PPMT(rate, per, nper, pv, [fv], [type])	

Met hetzelfde hypotheekvoorbeeld, om het aflossingsdeel van de 12e betaling te vinden:

PPMT-formule voor hypotheeklening. Afbeelding door auteur.

=PPMT(4.5%/12, 12, 30*12, 300000)

Dit levert -$411,66 op, wat laat zien dat $411,66 naar het verlagen van het leensaldo gaat.

Merk op dat de som van het rente- en aflossingsdeel (-$1.108,39 - $411,66 = -$1.520,05) ongeveer gelijk is aan ons totale maandbedrag. Deze relatie geldt altijd: PMT = IPMT + PPMT voor elke periode.

NPER (aantal periodes nodig voor een lening)

Als je weet hoeveel je maandelijks kunt betalen, helpt NPER() te bepalen hoe lang het duurt om een lening af te lossen:

=NPER(rate, pmt, pv, [fv], [type])

Bijvoorbeeld: als je $2.000 per maand kunt betalen op een lening van $300.000 met 4,5% jaarlijkse rente:

NPER-formule voor hypotheeklening. Afbeelding door auteur.

=NPER(4.5%/12, -2000, 300000)

Dit geeft 220,86 terug, wat betekent dat het ongeveer 221 maanden (circa 18,4 jaar) duurt om de lening af te lossen. Dit is aanzienlijk sneller dan de standaardlooptijd van 30 jaar omdat je meer dan het minimumbedrag betaalt.

RATE (het rentetarief berekenen)

Als je het leningbedrag, de betaling en de looptijd kent, kan RATE() de rente vinden:

=RATE(nper, pmt, pv, [fv], [type], [guess])	

Voor een lening van $300.000 met maandlasten van $1.520,06 over 30 jaar:

RATE-formule voor hypotheeklening. Afbeelding door auteur.

=RATE(30*12, -1520.06, 300000)*12

Dit geeft 4,5% terug en bevestigt dat onze maandlast van $1.520,06 overeenkomt met 4,5% jaarlijkse rente. We vermenigvuldigen de uitkomst met 12 om de maandrente naar een jaarrente om te rekenen.

Doelzoeken-technieken

Met Excel’s Doelzoeken-functie kun je vragen beantwoorden zoals:

• Hoeveel kan ik lenen als ik een specifiek maandbedrag kan dragen?
• Welke rente moet ik onderhandelen om de betalingen onder een bepaald bedrag te houden?

Als we bijvoorbeeld onze maandlasten op $2.000 willen houden voor de hypotheek van $400.000 uit ons eerdere voorbeeld (6% rente, 20 jaar looptijd), kunnen we met Doelzoeken bepalen hoeveel aanbetaling we moeten doen. Voor een uitgebreide handleiding hierover, bekijk onze gids How to Use Goal Seek in Excel.

Deze geïntegreerde aanpakken maken meer geavanceerde financiële planning en besluitvorming mogelijk dan louter basisberekeningen van betalingen. Door PMT() te combineren met deze gerelateerde functies kun je volledige aflossingsschema’s opstellen en uiteenlopende financiële scenario’s analyseren.

Belangrijkste punten

De functie PMT() correct gebruiken in Excel vergt nauwkeurigheid, vooral bij het aanpassen van rente en periodes. Dit zijn de belangrijkste aandachtspunten:

• Pas je rente en aantal periodes altijd aan op basis van de betaalsnelheid. Voor maandelijkse betalingen op een jaarrente deel je de rente door 12 en vermenigvuldig je de jaren met 12.

• De functie PMT() volgt een specifieke kasstroomconventie: betalingen die je doet verschijnen als negatieve getallen, terwijl geld dat je ontvangt positief is.

• PMT() maakt deel uit van een familie financiële functies (IPMT(), PPMT(), NPER(), RATE()) die samen uitgebreide analyses van leningen en beleggingen mogelijk maken.

• Veelvoorkomende fouten kun je voorkomen door je invoer te dubbelchecken, goed te corrigeren voor betaalsnelheid en de juiste tekens voor kasstromen te gebruiken.

• De functie PMT() is niet alleen voor leningen—ze is even waardevol voor spaar- en beleggingsplanning wanneer je regelmatige inlegbedragen wilt bepalen.

Excel-sjablonen met de functie PMT() kunnen tijd besparen en fouten verminderen bij herhaalde financiële berekeningen. Maak eens een herbruikbare sjabloon voor leningen vergelijken of spaarplannen om je financiële analyse te stroomlijnen.

Conclusie

Het toepassen van de functie PMT() in Excel biedt een solide basis voor weloverwogen financiële beslissingen. Of je nu hypotheekbetalingen berekent, spaarinleg plant of beleggingskansen beoordeelt, deze veelzijdige functie helpt je de financiële implicaties van verschillende scenario’s te begrijpen.

Door de functie PMT() te combineren met gerelateerde functies zoals IPMT(), PPMT(), NPER() en RATE() kun je uitgebreide financiële modellen maken die complexe vragen over leningen en beleggingen beantwoorden. Met Doelzoeken en scenarioanalyse vergroot je bovendien je vermogen om verschillende financiële mogelijkheden te verkennen.

Wil je je Excel-vaardigheden verder verdiepen? We bieden uitstekende bronnen om je leertraject voort te zetten:

• Ons vaardigheidstraject Excel Fundamentals is perfect voor iedereen die een stevige basis in Excel wil opbouwen, van datapreparatie en visualisatie tot analysetechnieken. Dit traject vereist geen voorkennis en begeleidt je via praktijkoefeningen met realistische scenario’s.
• Voor wie specifiek geïnteresseerd is in financiële toepassingen leert onze cursus Financial Modeling in Excel hoe je met kasstromen, scenarioanalyse, tijdswaarde van geld en investeringsselectie werkt. Deze cursus op gemiddeld niveau wordt gegeven door professionals uit het vak en bevat hands-on oefeningen met financiële data.

Met deze vaardigheden en bronnen ben je uitstekend uitgerust om de functie PMT() en aanverwante Excel-tools toe te passen in je financiële besluitvormingsprocessen, aflossingsschema’s te maken en geavanceerde beleggingsmodellen te bouwen.