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.
Excel hulp nodig?
Heb je een Excel vraagstuk? Neem dan contact met mij (Bas) op. Ik denk graag met je mee! Je kunt dan uitleggen waar je hulp bij kunt gebruiken. Vervolgens maak ik een inschatting of ik de aangewezen persoon ben om je hierbij te helpen en doe ik een voorstel.
Het eerste gesprek is altijd volledig kosteloos én vrijblijvend. Mail of bel me gerust!
Is het ook mogelijk om met een slot termijn te rekenen van bijv. € 2.000?
Zoals, bij financieringen.
Hoi Jan, ik stuur je even een email.
Ik heb dezelfde vraag over de omgekeerde berekening. Kan ik dat aanpassen?
Hoi Miranda, wat bedoel je precies met “omgekeerde berekening”? Wil je deze berekening doen met een restwaarde/slottermijn?
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?
Hoi Stan, Er zit een voorbeeldbestand bij, die kan je hierboven downloaden. Daar staat rente en aflossing uitgesplitst. Succes.
Dank je wel voor deze geweldige berekening, het werkt super
Hoi Marion, graag gedaan! En een mooie dag verder.
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
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.
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.
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 🙂
Ha Bas, heel erg bedankt voor jouw rekenmoel in excel.
Hier heb ik wat aan.
Gr. Fred
Graag gedaan, Fred! Bedankt voor je reactie!
Oh, heel blij met dit Excel bestand.
Is het ook mogelijk om tussentijdse extra aflossingen hierin te verwerken?
Hoe doe ik dat?
Groetjes, Astrid
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.
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.
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.
dank voor het model ! Super !
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.
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?
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 ?
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
Hi Hubertien, je kan deze gebruiken: https://snelexcel.nl/financial-lease-berekenen-in-excel/
Die heeft een restschuld ingebouwd. Suc6, Bas
Hi Bas , mooie sheets !
Is het uitgangspunt maandbetaling vooraf of achteraf ?
met hartelijke groet, Ton. Ton
Hoi Ton, achteraf (omdat er over de laatste termijn ook nog rente betaald moet worden). Mogelijk dat dit ook nog per hypotheekverstrekker kan verschillen?
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
Hoi Remco, bedankt voor je reactie. Ik snap wat je bedoelt, maar het klopt echt. Lees het maar na op de website van Microsoft: https://support.microsoft.com/nl-nl/office/bet-functie-0214da64-9a63-4996-bc20-214433fa6441. Ik heb het ook gecheckt met de online rekentool van de Rabobank. Grt., Bas
Beste Bas,
kan jij ook een berekening maken met een aflossingsvrij gedeelte?
MvG Wim van Dijk
Beste Wim, zou wel moeten kunnen, misschien dat ik er binnenkort nog eens wat uitbreidingen aan toevoeg.
ik zoek een annuiteitsberekening waarbij jaarlijks een vast bedrag extra wordt afgelost
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.
Ik zat met hetzelfde probleem, maar heb het kunnen oplossen:
maak van kolom G een nieuwe kolom met als titel: Extra aflossing.
Wijzig in cel E3 (restschuld) de formule =ALS(A3″”;B3-D3;””) in =ALS(A3″”;B3-D3-G3;””).
Wijzig in cel F3 (totale maandbedrag) de formule =ALS(A3″”;$I$6 in =ALS(A3″”;-BET(I$4/12;I$5-A2;E2). Je berekent dus elke maand opnieuw de annuïteit.
Trek de formule in cel E3 door tot en met E361, en de formule in cel F3 tot en met F361
In de maand na de extra aflossing wordt het maandbedrag in kolom F lager.
Je kunt de werking controleren door naar cel E361 te kijken. Deze moet dan 0 zijn.
Vul in cel G1 de titel : Extra aflossing.
Verander cel E2 van =ALS(A2″”;B2-D2;””) naar =ALS(A2″”;B2-D2-G2;””)
In de huidige kolom F (maandbedrag), verander je de formule in cel F3 (periode 2) van =ALS(A3″”;$I$6;””) naar =ALS(A3″”;=-BET($I$4/12;$I$5-A2;B3);””).
Trek de formule van cel E2 door t/m E361
Trek de formules van cel F3 door t/m F361
Als je nu in een willekeurige maand in kolom G een extra aflosbedrag invult, dan wordt het maandbedrag in kolom F de volgende maand lager. Als controle kun je kijken of cel E361 nog steeds de waarde 0 heeft.
ik zoek een annuïteiten excel berekening (de formules) waarbij het mogelijk is om tussentijds een extra aflossing te doen.
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.
Beste Bas, wij willen het rente percentage uit een amortisatie schema uit rekenen. Ik kan je het schema mailen.
Hoi Frank, ik stuur je even een mailtje.
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
Beste Ton, ik heb u even gemaild. Grt., Bas
Dag Bas, hoe zorg ik ervoor dat ik aan het einde van de periode een som functie kan maken (voor bijv. de totale rente) die vast blijft wanneer ik de termijn aanpas?
Hi Oguzhan, wat bedoel je precies? Kan je wat meer toelichting geven? Grt., Bas
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.
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
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.
Hi Nancy, ik zou voor elke bouwtermijn een nieuwe excel maken als ik u was. Grt Bas
Super, dit Excel voorbeeld. Bedankt.
Graag gedaan, Dirk. En jij bedankt voor je reactie! Grt Bas
Ik ben op zoek naar een berekening, als dit mogelijk is, waarbij ik in Excel kan zien hoeveel er na een X aantal maanden (variabele) had moeten worden afgelost van de hoofdsom. Daarbij beschikbaar de [initiële hoofdsom] en het [rentepercentage] en de [annuïtaire looptijd] als variabelen. Stel ik heb een annuïtaire lening van € 100.000,- en deze loopt 48 maanden van de initieel overeengekomen 80 maanden wat had er dan afgelost moeten zijn? Lineair is natuurlijk makkelijk maar de annuïtaire vorm maakt het lastig. Zou u aub met mij willen meedenken? Dank u wel.
Grt, Peter
Werkt ook perfect in open/libreoffice.
Bedankt voor je mooie aanvulling, Ronald! grt Bas
Goedemorgen Bas,
Wat super dat je dit met ons deelt, bedankt daarvoor. Nou ben ik benieuwd naar een formule die mij kan laten zien welke rente er berekend is. Ik weet de hoofdsom, looptijd en de annuiteit. Weet jij een formule die op basis van die gegevens het rentepercentage kan terugrekenen?
Gr. Bob
Hi Bas, ik heb deze sheet gebruikt en aangepast met een kolom voor extra aflossing.
Maar in alle sheets die ik tot nu toe heb gezien zie ik een vaste maandtermijn.
Men gebruikt de vast starttermijn (uit de kop van de sheet) minus rente.
Wat overblijft is het aflossingscomponent.
Maar als ik extra aflos dan zakt de maandtermijn. Hoe die nieuwe maandtermijn wordt berekend begrijp ik niet. Als ik dat weet dan kan ik denk ik zelf wel verwerken in de sheet.
Weet jij dit Bas, of iemand die meeleest?
Hoi Bas,
Lukt het je deze ook in jaren te maken.
Groetjes!
Hi Andrea, waarom wil je dit? Waar heb je dit voor nodig? Voor mij is dat namelijk ook handig om te weten. Misschien kan ik er een apart artikel voor maken. Hoor het graag. grt Bas
oh dat is omdat ik les geef (bedrijfseconomie) en de kids rekenen dan eerder met jaren ipv met maanden. ze moeten de rente en aflossing bij lineaire hypotheek en de annuïteitenhypotheek uitrekenen. Dat gaat dan even voor het gemak op jaarbasis ipv op maandbasis.
Bijvoorbeeld: http://ecobeco.nl/hbeco/hbe21iopg4.pdf
Ah, kijk eens aan! Voor het goede doel! Ik mail hem even, want ik denk dat in de praktijk de meeste mensen toch met maanden werken, en anders maak ik die invulvelden onnodig uitgebreid. grt Bas
Hallo Bas. Mooi model, dank je. Is het wellicht eenvoudig uit te breiden met een extra voorwaarde: namelijk maximale jaarlijkse totale bruto lasten? Bijvoorbeeld 6035,- euro.
Dat ivm het maximaal te schenken bedrag aan kinderen.
Dat voorkomt mij wat invuloefeningen en puzzelen, icm een som per 12 cellen.
Beste Jan-Hein, welk inzicht probeer je hiermee te krijgen? Want ik snap nog niet helemaal wat het doel is. grt Bas
Her maximaal te lenen bedrag, bij een bepaalde rente en een grens aan jaarlasten. Dat laatste gebaseerd op de maximale schenkingsvrijstelling bij de belastingdienst.
Wij lenen een bedrag aan ons kind via een familie hypotheek, schenken jaarlijks het maximale bedrag terug.
Zodat een dergelijke lening als 0 lasten worden worden beoordeeld door een hypotheekverstrekker.
Alternatief is dat ik wat “speel” met deze excel en kijk waar ik op uit kom (ongeveer 100 K)