annuïteit berekenen Excel

Annuïteit berekenen in Excel

Om de annuïteit op een (hypothecaire) lening te berekenen kun je gebruik maken van de ingebouwde BET functie in Excel. Dat scheelt weer, aangezien de wiskundige formule een stuk ingewikkelder is.

Excel formule annuïteit

=-BET(rente;aantal-termijnen;hoofdsom)

In feite hoe je dus maar drie waarden op te geven: het rentepercentage, het aantal termijnen en de oorspronkelijk hoofdsom (totale lening). Excel doet de rest!

Voorbeeld annuïteit berekening

=-BET(G4/12;G5;G3)

In bovenstaande voorbeeld hanteren we een hoofdsom van 200.000 euro. Het rentepercentage is 2,25% op jaarbasis. Omdat we uitgaan van een maandelijkse betaling, hebben we deze 2,25% door 12 gedeeld. Dit is ook de reden dat de looptijd van 30 jaar in 360 maanden is weergegeven.

Download voorbeeldberekening in Excel

Je kunt hier gratis het voorbeeldbestand downloaden waarin de annuïteitenhypotheek is uitgewerkt. Deze kun je vervolgens invullen naar jouw situatie.

Meer informatie over annuïteit

Een annuïteit is een vast bedrag per periode (bij hypotheken maandelijks) dat betaald dient te worden. Bij een annuïteitenhypotheek betaal je in het begin relatief veel rente en weinig aflossing. Doordat de lening steeds lager wordt ga je minder rente betalen, waardoor je maandelijkse aflossing groter wordt.

Annuïteitenhypotheek versus lineaire hypotheek

Naast een annuïteitenhypotheek bestaat er ook nog een lineaire hypotheek, waarbij niet het termijnbedrag, maar het aflossingsgedeelte constant is. Hierdoor is een lineaire hypotheek in absolute euro’s over de gehele looptijd voordeliger. Dit komt omdat je bij een lineaire hypotheek in het begin relatief veel aflost in vergelijking met een annuïteitenhypotheek. Hierdoor neemt de openstaande lening sneller af, waardoor je minder rente betaalt. Een lineaire hypotheek is echter niet altijd haalbaar, omdat je dan in het beginperiode behoorlijk hoge lasten hebt.

Bekijk ook het artikel waarin de lineaire hypotheek wordt vergeleken met de annuïteiten hypotheek. Tevens vind je daar ook een mooie rekentool om beide direct in Excel met elkaar te vergelijken.

Eindelijk eens goed worden in Excel? Volg de tweedaagse Excel Basiscursus van SnelExcel.nl in Rotterdam, Utrecht of Amsterdam. Kosteloos via het STAP budget. Lees hier meer.

46 reacties

        1. Hoi Miranda, wat bedoel je precies met “omgekeerde berekening”? Wil je deze berekening doen met een restwaarde/slottermijn?

  1. Hoi Bas,
    Dan heb je de totale maandlasten, maar kun je dat ook opsplitsen in het deel rente (wat weer van belang is voor de aftrek) en het deel aflossing?

    1. Hoi Stan, Er zit een voorbeeldbestand bij, die kan je hierboven downloaden. Daar staat rente en aflossing uitgesplitst. Succes.

  2. Stel het volgende
    Hypotheek 200000, 2,25%, 20 jaar vast. begin looptijd 1 jan 2021
    Looptijd hypotheek 30 jaar dus 360 termijnen.
    Na 20 jaar vindt renteherziening plaats. Rente wordt nu 2,00.
    1. Wat wordt dan de formule voor rente en aflossing in jaar 2021? Je blijft toch uitgaan van hypotheeksom van 200.000 en niet van grootte hypotheek op 1 jan 2021 . Ook al heb je afgelost in de afgelopen 20 jaar.
    Op 1 jan 2051 moet de hypotheek tenslotte in zijn geheel afgelost zijn

    2. Stel tegelijk met de renteherziening op 1 jan 2021, los je extra af 10.000.
    Hoe luiden dan de formules voor rente en aflossing per maand in jaar 2021 en verdere jaren/maanden

    1. Hoi Hans, ik zou bij de renteherziening gewoon een nieuwe berekening maken. Dan voer je de restschuld na 20 jaar in als hoofdsom voor de nieuwe berekening met een looptijd van 10 jaar. Dan krijg je twee bestanden.

  3. Hoi Bas, hoe bereken je de waarde van een annuïtair dalende overlijdensrisicoverzekering?
    Bijvoorbeeld verzekerd kapitaal 100.000 euro. Deze wordt jaarlijks verlaagd met het aflossingsbedrag uit de jaarlijkse annuïteit van 6,00% per jaar en een looptijd van 20 jaar.

    1. Hoi Arjan, ik heb niet echt verstand van overlijdensrisicoverzekeringen, maar ik heb er zojuist twee met elkaar vergeleken (Dela en FBTO). En mijn vermoeden werd bevestigd: ondanks dat je gewoon dezelfde gegevens invoert (20 jaar, 6%, 100.000 euro), is de premie niet hetzelfde. De premie-berekening is dus niet helemaal transparant. Daarnaast heb je te maken met een extra variabele (of je wel of niet rookt), en ik geen idee wat voor formule hierachter zit. Het zal dus heel lastig worden om hiervoor een soortelijk Excel bestand te maken. Maar als je extra informatie hebt, hoor ik het graag 🙂

  4. Oh, heel blij met dit Excel bestand.
    Is het ook mogelijk om tussentijdse extra aflossingen hierin te verwerken?
    Hoe doe ik dat?
    Groetjes, Astrid

    1. Hoi Astrid, sorry voor mijn late reactie. Tussentijdse aflossingen kan je hier niet 123 in verwerken. Je kan wel een kopie van dit bestand (of werkblad) maken en dan het resterende bedrag als hoofdsom invullen met de rest van de looptijd.

  5. Hoi Bas,
    Ik was zelf even bezig maar deze formule lijkt ook voorbij te gaan aan de variabele rente over tijd vanwege de teruggedrongen risicotabel. Heb jij enig idee hoe een bank dat doet ik kom steeds uit op een echt vaste bruto last maar in de opgaves is dat niet hoe ze het daadwerkelijk gebruiken, er lijkt een correctie op bruto te zijn op basis van dit verschil.

    1. Denk dat ik het nu wel snap, de aflossing is op basis van de vaste annuiteits alsof deze niet variabel is, erna is de som van bruto last gelijk aan aflossing + de variabele rente, dan is zowel de aflossingssom correct en behoud je nagenoeg maar niet exacte gelijke bruto lasten.

  6. Beste Bas,

    Wat een super model, bedankt dat jij deze beschikbaar hebt gesteld!
    Is het ook mogelijk om af te lossen niet per termijn maar per datum (in het kader van: durf te vragen)?
    Wanneer je een lening aangaat per de 17e van een maand dat het schema eerst het restant voor die maand uitrekent en vervolgens uit gaat van betalingen op de laatste dag van elke maand.

    1. Hi Wieteke, bedankt voor je bericht. Graag gedaan! In het kader van: “durf nee te zeggen”, moet ik je helaas teleurstellen voor wat betreft jouw vraag over het aflossen van die eerste (halve) maand 🙂 Ik zou niet weten hoe dat moet. Dat wordt echt ontzettend complex ben ik bang. Waar heb je dat voor nodig?

  7. dag Bas bedankt voor uw excelletjes

    Maar wat als ik wil weten hoelang ik moet afbetalen met een maandelijkse annuiteit tegen een vast percentage ?

    bijvoorbeeld:
    bedrag 100.000 euro
    maandelijkse annuiteit: 100euro
    percentage tegen 5%
    hoelang moet ik dan die 100 euro betalen ?

  8. Beste Bas,

    Bedankt voor je berekening. Kan ik dit ook in excel berekenen met een restwaarde? dus bij voorbeeld: wat is de maandelijkse annuïteit voor een hypotheek als ik aan het einde van de looptijd een X bedrag over mag houden? bedankt

    1. Hoi Ton, achteraf (omdat er over de laatste termijn ook nog rente betaald moet worden). Mogelijk dat dit ook nog per hypotheekverstrekker kan verschillen?

  9. Hoi,

    Voor het maandelijkse rentepercentage lijk je te gebruiken: jaarpercentage/12:

    =-PMT(I4/12,I5,I3)

    Dus voor 12% jaarlijkse rente, neem je 1% maandelijkse rente. Dat lijkt me niet te kloppen toch?
    1% rente per maand leidt tot 1.01^12=1.1268-> 12.68% jaarlijkse rente.

    Als ik 12% rente betaal per jaar, dan betaal ik per maand 1.12^(1/12)=1.00949 -> 0.949% aan rente.

    Groet,
    Remco

    1. Hi Albert, ik denk dat dit heel lastig wordt. je zou een tweede berekening kunnen maken, dat zou sowieso wel moeten kunnen. Dan begin je met de tweede berekening met het restbedrag ná je extra aflossing.

    1. Beste Adriaan, misschien dat ik hier nog eens naar ga kijken, om dit in één model te gieten, maar je kan ook een tweede berekening maken na je extra aflossing, zie ook vorige reactie.

  10. Dag Bas,

    Ik zag al iemand die een omgekeerde annuiteit wil berekenen, volgens mij heb ik die ook nodig aangezien onze kinderen een huis kopen met een familiehypotheek van ons maar in hun eerste jaren nog geen aftrek/teruggave rente kunnen claimen door weinig tot geen inkomsten. Dus zou eerst groot deel van het maandbedrag gebruikt moeten worden om af te laten aflossen zodat als ze dadelijk verdienen de rente aftrek mogelijk is op hun IB.

    Ik heb de excell gedownload en ben aan het puzzelen.

    bvd

  11. Hi Bas,

    Hoe heb jij een dynamische tabel gemaakt. Graag zou ik het bereik van de tabel ook koppelen aan een waarde in een gegeven cel.

    1. Hoi Jeroen, dat heb ik gedaan met een voorwaardelijke opmaak. Ik snap nog niet helemaal wat je precies bedoelt. Kan je dat nog eens iets meer uitleggen? Grt Bas

  12. super deze excel!
    nu gaat mijn dochter een nieuwbouwhuis kopen, dus ik ga niet meteen de hele hoofdsom uitlenen. hoe verwerk ik de bouwtermijnen hierin? dus eerst leent ze 20% van de hoofdsom, na zoveel maanden 35% van de hoofdsom etc.

Laat een antwoord achter aan Jeroen Reactie annuleren

Het e-mailadres wordt niet gepubliceerd.