Annuïteitenhypotheek zelf berekenen

Begin december schreef ik over het narekenen van onze annuïteiten hypotheek. Daar bood ik aan  om een tutorial te maken hoe je zelf kunt uitrekenen wat je maandlasten worden als je bijvoorbeeld een nieuwe hypotheek afsluit of een hypotheekdeel om wilt zetten naar een annuïteiten hypotheek. Ik zou ook wel een excel bestand willen klaarzetten die je zou kunnen downloaden, maar ik heb nog niet uitgevonden hoe ik dat doe in blogger, zonder mijn privédropbox open te zetten. Tips zijn daarvoor meer dan welkom.

Waarschuwing: door de vele screenshots is dit best een lange blog geworden.

Maar terug naar het onderwerp van deze blogpost, we gaan in excel een annuïtaire hypotheek bouwen. 

Eerst maar eens de uitgangspunten van deze fictieve hypotheek:

• Startlening: €200.000
• Hypotheekrente: 2%
• Looptijd 30 jaar

De basis

We beginnen met deze gegevens in te vullen.

Zoals je misschien ziet heb ik de looptijd van de hypotheek in maanden gezet in plaats van in jaren. Dit komt verderop van nog van pas.

Vervolgens maak je, bijvoorbeeld onder dit kader een aantal kolommen aan. Dit zijn de kolommen resterende termijnen, openstaande hypotheek, annuïteit, rente, aflossing en hypotheek na aflossing. Hieronder zie je hoe ik dat gedaan heb.

Je ziet dat ik ook al wat getallen heb laten invullen of berekenen door excel. In cel A7 heb ik het aantal termijnen laten invullen. Di t heb ik gedaan door die cel gelijk te stellen aan cel C3. De "formule" in cel A7 is dan "=C3". Op dezelfde manier heb ik cel B7 laten verwijzen naar cel B1.

In cel D7 heb ik de verschuldigde rente in de eerste maand alvast berekend. Dit wordt berekend door de jaarrente door 12 te delen en te vermenigvuldigen met de openstaande hypotheek in cel B2. De formule die daarbij hoort is: "=B7*$B$2/12". 

Het dollarteken is van belang voor als we straks deze formule willen kopiëren voor de gehele looptijd van de hypotheek. Het zorgt ervoor dat er altijd naar cel B2 wordt verwezen als de formule wordt gekopieerd.

Dan komt nu de belangrijkste formule van deze berekening. De berekening van de annuïteit.

De formule die daarvoor gebruikt word is BET. Daarvoor heb je een aantal gegevens nodig die je invult:

• De rente: in dit geval de rente per maand dus 4%/12
• Het aantal termijnen waarin je de hypotheek terugbetaalt: in dit geval 360 maanden
• De huidige hypotheek: in dit geval €200.000
• De eindwaarde van de lening: Dat is 0, we willen de hypotheek helemaal afbetalen

Hoe de formule er in excel komt uit te zien kun je in de formulebalk zien in de volgende afbeelding.

Als je niet van plan bent om extra af te lossen op je hypotheek gedurende de gehele looptijd ben je nu eigenlijk klaar. Je zal dan namelijk 30 jaar lang dit elke maand dit bedrag aan de bank betalen. Over hoe je extra aflossingen opneemt in de berekeningen kom ik morgen in een nieuw blog op terug.

Met de gegevens die we nu berekend hebben kunnen we de aflossing bepalen en dus ook de hypotheek nadat je de eerste betaling aan de bank hebt gedaan. De aflossing bereken je door de rente van de annuïteit af te trekken. Daarna kun je de aflossing van de openstaande hypotheek aftrekken. 

Je kunt zien dat de aflossing deze eerste maand al ruim €288 is en de hypotheek aan het eind van de eerste maand €199.711.

We zijn nu bijna zover om de berekening te gaan kopiëren voor de gehele looptijd van de hypotheek. Maar eerst moeten we nog een tweede regel van deze berekeningen maken. Na de eerste maand zijn er nog 359 maanden over. Dat moet in cel A8 komen. Dat doen ik door de formule "=A7-1" te gebruiken.

De openstaande hypotheek van de tweede maand is de hypotheek na aflossing van de eerste maand. Voor de cel B8 gebruiken we daarom de formule: "=F7".

Voor de cellen C8, D8, E8 en F8 kun je de formules van de rij erboven kopiëren met copy en paste. Dat ziet er dan als volgt uit:

Je ziet dat je in de tweede maand al bijna een euro minder rente hoeft te betalen dan de eerste maand en dat je dus bijna een euro meer aflost die maand. Ook zie je dat de annuïteit gelijk blijft. De formule doet dus wat hij moet doen.

Nu kunnen we de regels kopiëren totdat het aantal resterende maanden op 1 komt. Als controle dat de berekening helemaal goed is opgezet kun je controleren dat de hypotheek na aflossing un de laatste regel op 0 euro uitkomt. Als je het bestand precies hebt opgemaakt als ik hier heb voorgedaan moet in cel F366 dus een bedrag van 0 euro staan. Bij mij klopt dat:

Op deze manier kun je het verloop van je hypotheek helemaal volgen vanaf de eerste betaling tot de laatste aflossingen. Je zou daar ook nog een mooi grafiekje van kunnen maken.

Morgen ga ik in een nieuwe post laten zien hoe je extra aflossingen zou kunnen verwerken in dit bestand. Ook zal ik dan een voorbeeld geven hoe je een verandering van rente na bijvoorbeeld het einde van de rentevaste periode kunt verwerken in deze berekeningen.

Mocht je nog andere vragen hebben rondom deze berekeningen, laat het maar weten in de reacties. Mogelijk kan ik het meenemen vanavond als ik de blogpost van morgen schrijf.

Wat ik trouwens wel kan doen is natuurlijk op verzoek het bestand dat ik nu gemaakt heb mailen naar geïnteresseerden. Wil je de berekening hebben stuur dan een mailtje naar hypotheekweg@gmail.com

onder vermelding van annuïteitenhypotheek.