Reactie op een wat ouder bericht, maar meerdere mensen zaten zich dit af te vragen, o.a. ook @SmoothTweaker
Als je een
annuitaire hypotheek hebt, is een Excelletje hiervoor zo gemaakt. Je hebt maar een paar cellen nodig met echte formules. Er staan zonder twijfel al voorbeelden op internet, maar voor wie wil snappen hoe het werkt, onderstaande als je er zelf een wilt klussen:
Inputs zijn: Het volledige, originele hypotheekbedrag (bv. 300k), jaarrente nominaal (=de rente die in je offerte staat), en aantal termijnen (vrijwel altijd 30 jaar, dus 360 maandtermijnen). Stel dat de nominale rente 2% is, dan moet NominaleRente hieronder 0.02 zijn (dit gaat vanzelf als je het veld als percentage formatteert, je kunt dan dus gewoon 2 invullen).
De effectieve jaarrente is dan iets hoger:
EffectieveRente = ((1+( NominaleRente / 12)) ^ 12) - 1 - bij genoemde 2% komt dat op 2.018%
Waarom is dit: de nominale rente is op jaarbasis, maar je betaalt per maand. Dus ook je schuld gaat elke maand iets omlaag. Om dan te zorgen dat het rentebedrag over het hele jaar klopt ten opzichte van de schuld aan het begin van dat jaar, moet je per maand iets meer betalen over het dan nog openstaande bedrag. Het is natuurlijk geen toeval dat het nominale percentage op jaarbasis gequote wordt, want dat is altijd lager.
Met de effectieve rente is de maandrente uit te rekenen:
MaandRente = (1+EffectieveRente) ^ (1/12) - 1, in het rekenvoorbeeld komt dat op 0.167%. Elke maand betaal je dus 0.167% van het dan nog openstaande bedrag aan rente voor die maand. Omdat je aflost gaat dit bedrag dus elke maand omlaag (jay!) De rest van het maandbedrag is per definitie aflossing, en omdat het maandbedrag vast is, wordt dit deel dus steeds groter.
Het termijnbedrag is waar de meeste trickyness in zit:
TermijnBedrag = VolledigHypotheekBedrag * (MaandRente / (1-((1+Maandrente) ^ -AantalTermijnen))) - in het rekenvoorbeeld met 300k komt dat op 1108.86.
In mijn geval klopt dit tot op de cent (met andere bedragen en percentages, voor de duidelijkheid) met wat Merius voorrekent - ik kreeg zo'n voorbeeldtabel bij de offerte, mijn Excel matcht die 100%.
Nu heb je alle informatie om een tabel te maken, bv.:
Kolom A = datum, B = nog openstaand bedrag, C = rentebedrag voor deze termijn, D = aflossing voor deze termijn, E = tot nu toe afgelost bedrag
Met als eerste rij:
A2 = Datum ingang hypotheek
B2 = Origineel hypotheekbedrag
C2 = MaandRente * B2
D2 = TermijnBedrag - C2
E2 = B$2-B2 (in de eerste maand heb je nog niks afgelost, het bedrag dat je betaalt gaat de volgende maand van de openstaande som af)
De volgende rijen zijn nu puur formules:
A3 = A2 + 1 maand (hint: EDATE(A2,1))
B3 = B2 - D2 (namelijk: het openstaande bedrag van de maand ervoor minus de aflossing in die maand)
C3 = MaandRente * B3 (dus nog openstaand bedrag maal maandrente)
D3 = Termijnbedrag - C3 (logisch, alles wat geen rente is gaat naar aflossing, het termijnbedrag is vast)
E3 = B$2 - B3
Mocht je op een bepaald moment extra aflossen, dan kun je vanaf de maand erna rekenen met een termijnbedrag gebaseerd op de hoofdsom minus de *extra* aflossingen tot aan die maand (dus, alsof je originele hypotheek voor een lager bedrag was, maar pas vanaf dat moment). Het termijnbedrag gaat daardoor dus omlaag vanaf dat moment. Ook bij 'nog openstaande schuld' moet je natuurlijk het extra afgeloste bedrag in die maand aftrekken.
Mocht de rente op enig punt veranderen dan vanaf dat punt de nominale rente aanpassen, de rest hangt daar van af, dus het termijnbedrag verandert daardoor ook.
En ja, het is best een voordeel als je hypotheekverstrekker dit ergens voor je op een website heeft. Maar zijn we nou tweakers of niet. Succes met knutselen