zondag 8 januari 2017

Annuïteitenhypotheek zelf berekenen: extra aflossingen en rentewijziging

Gisteren schreef ik al hoe je zelf met excel kunt je annuïteitenhypotheek kan berekenen. Daarin schreef ik ook dat ik vandaag nog verder zou gaan met het toevoegen van extra aflossingen en laten zien hoe je een renteverandering kan toevoegen.

Voordat ik begin heb ik uitgevonden hoe ik het bestand beschikbaar kan maken voor iedereen. Via deze link moet het bestand te downloaden zijn.

Extra aflossingen
Waar waren we gebleven? We hadden voor 360 maanden berekend wat de rente en de aflossing was en kwamen ook mooi op 0 euro uit aan het eind van de looptijd. Ons maandbedrag was €954,83.

De eerste regels van de berekening zagen er als volgt uit.

Om nu de extra aflossingen mogelijk te maken gaan we eerst een kolom voegen tussen de kolom aflossing en de kolom hpotheek na aflossing. Dat doe je door bovenaan op de "F" te klikken met je linker muisknop en daarna met je rechter muisknop. Uit het menuutje dat dan tevoorschijn komt kies je invoegen.
In cel F6 (kolom F is nu de lege kolom en de kolom hypotheek na aflossen staat nu in kolom G) vul je bijvoorbeeld in: Extra aflossing. Je kunt kolom F wat breder maken zodat de hele tekst er netjes in past. De spreadsheet ziet er dan als volgt uit.

Nu moeten we de extra aflossingen nog laten meetellen in de berekening voor hypotheek na aflossing. Daarvoor selecteer je cel G7. Die cel had al de formule: =B7-E7. Die formule verander je naar =B7-E7-F7. Vervolgens kopieer je deze formule weer helemaal door naar beneden.



Je ziet dan nog geen vernaderingen in de getallen staan. Dat klopt, want we hebben nog geen extra aflossing ingevuld. Maar stel dat je nu gelijk in de eerste maand een extra aflossing van €500 wilt doen. Die vul je dan in in cel F7. Wat zie je dan allemaal gebeuren?

  • De hypotheek na aflossing van die maand is €500 lager; €199.211,84 ipv €199.711,84
  • De annuïteit van de tweede maand (en daarna) is verlaagd naar €952,44, een daling van €2.39.
Dat laatste is dus de besparing in je maandlasten die je bereikt hebt voor de rest van je looptijd. In het plaatje hieronder zie je hoe het eruitziet.

Renteverandering
Stel je hebt een rentevaste periode genomen bij het afsluiten van de hypotheek van 10 jaar. Na 10 jaar krijg je dan een nieuwe rente. Stel die is 2%, Hoe verwerk je die dan in dit excelbestand?

Ik gebruik nu hetzelfde bestand als we net gebruikt hebben, ik heb alleen de extra aflossing weer verwijderd.
Eerst vul ik in cel A4 "renterherziening" in. In B4 vul ik 2% in.

Dan ga ik op zoek naar de regel waar ik de nieuwe rente moet laten ingaan. Als er 10 jaar verstreken zijn, ijn er nog 240 maanden over van de looptijd van de hypotheek, Bij mij is dat regel 127. Je hoeft maar in twee cellen te verwijzen naar de nieuwe rente. Dat zijn cel C127 en cel D127. In beide cellen moet je de verwijzing naar cel B2 vervangen door een verwijzing naar cel B4. Danarna moet je de nieuwe formules even naar bendeden kopiëren.

De waarden van beide cellen veranderen. De annuïteit verandert van €954,83 in €797,11, dat worden je nieuwe maandlasten. Dat is duidelijk geen halvering van je maandlasten bij een halvering van de rente. De rente in cel D127 halveert wel. Daardoor wordt de aflossing in cel E127 ook groter dan eerst. Door de renteverlaging ga je dus sneller aflossen. Ik heb rij 127 even geel gemaakt zodat je goed kunt zien waar de verandering optreedt.

Zo kun je dus ook het effect van een andere rente zelf berekenen.


Voor de volledigheid hier nogmaals de link om het bestand dat ik gemaakt heb te downloaden.

Ik hoop dat dit voor jullie nuttig was, en als er nog meer verzoeken zijn om zaken in excel uit te rekenen hoor ik dat graag. Dan kan ik eens kijken of me dat ook lukt...




21 opmerkingen:

  1. Deze reactie is verwijderd door de auteur.

    BeantwoordenVerwijderen
  2. Ok, Ik heb net al mijn waardes ingevoerd, dit is fantastic. Ik kan zien wat er gebeurt met mijn maandbedrag na elke extra aflossing en ook dat in mijn huidige patroon, ik in januari 2020 hypotheek vrij ben, na een laatste 5% extra aflossing. Ook het verlagen van mijn maandbedrag (dramatische verandering in 2019) is super om te zien. Dank je wel voor het delen.

    BeantwoordenVerwijderen
    Reacties
    1. Geen dank.. En dankje voor het delen, nu weet ik zeker dat het downloaden werkt.

      Verwijderen
  3. Ik heb trouwens nog een Excel vraag, kan jij ook een spreadsheet bedenken voor een huishoedboekje soort idee, ook via google drive? Ik heb er een gevonden via excel maar die is te moeilijk. Ik bedoel met inkomen en uitgaven en vaste lasten enzo.

    BeantwoordenVerwijderen
    Reacties
    1. Ik heb zelf nog nooit een huishoudboekje in excel gemaakt, maar is dus zeker een idee. Ik zal er eens over nadenken hoe ik dat zou aanpakken..

      Verwijderen
    2. Ik hoop dat het werkt (naar voorbeeld van Hypotheekweg) een link naar een blanco versie van mijn huishoudboekje op de drive:
      https://docs.google.com/spreadsheets/d/1PjJKPcPGMwaorbmHnrUztxQqwDxaliTFxbVxmfTtLx4/pubhtml

      Verwijderen
    3. Ik heb de mijne 2 jaar vooruit ingevuld. Onder het kopje kun je dan de budgetten invullen.
      In de loop van de periode vul ik helemaal onderaan de werkelijk uitgaven in (gewoon plussen). Doormiddel van de tussenstand+niet afgeschreven vaste lasten te selecteren kan ik af en toe de aansluiting checken.

      Aan het eind van de periode kopieer ik werkelijk naar de budgetregels en kan ik aan de volgende maand beginnen. (en verberg ik de maand ervoor, zo blijft het overzichtelijk, maar kan ik altijd terugkijken)

      Verwijderen
    4. Zelf heb ik er overigen nog allerlei andere sheets bij gemaakt met verbruik en aflossingsplan e.d. En vandaag heb ik nog een sheet aangemaakt om te berekenen wat we nou waard zijn per 1 januari. Leuk om de komende jaren te vergelijken.

      Verwijderen
    5. even getest. Bij deze link download hij een excel ipv een webpagina :P
      https://docs.google.com/spreadsheets/d/1PjJKPcPGMwaorbmHnrUztxQqwDxaliTFxbVxmfTtLx4/pub?output=xlsx

      Verwijderen
    6. P.s. Hoe heb jij hem online gezet Hypotheekweg?

      Verwijderen
    7. Ik heb hem in google drive opgeslagen. En als je dan een link maakt is ie openbaar voor iedereen met die link

      Verwijderen
  4. Het is dat wij geen annuïteitenhypotheek hebben, anders had ik hem zeker gebruikt ;-)

    BeantwoordenVerwijderen
  5. Ha grappig, ik heb een vergelijkbaar document om mijn annuïteitenhypotheek bij te houden.
    Ik heb er zelfs nog rijen aan toegevoegd waarin ik mijn spaargeld en beleggingen aan toevoeg zodat hij mijn vermogen berekend.
    Altijd leuk om mee te rekenen en te kijken vat de invloed is van bepaalde veranderingen!

    BeantwoordenVerwijderen
    Reacties
    1. Leuk. Ik heb die dingen wel, maar vaak in aparte bestanden, het wordt zo snel zo groot vind ik...

      Verwijderen
  6. Vind het super hoe je dat maakt. Vooral de opbreking in maandtermijnen is een toevoeging. Tot nog toe gebruikte ik altijd een website die de berekening alleen in jaren doet en daar dan de maandbedragen van afleid. Uiteaard kun je dara zelf ook een heel eind mee komen, maar dit is natuurlijk eenvoudiger.

    BeantwoordenVerwijderen
    Reacties
    1. Hoi Karin -ik kan die idd openen maar hoe krijg ik het dan in Google sheets? Want dat lukt me niet

      Verwijderen
    2. desnoods downloaden en dan weer uploaden in de drive.

      Verwijderen
  7. Bedankt voor je spreadsheet heb er vandaag wat mee zitten spelen en ondertussen wat zaken aan zitten passen.
    - Ik heb de rente aanpassing geautomatiseerd voor max 4 rentevast perioden
    - Ik heb in ieder regel de datum opgenomen waarop de aflossing van toepassing is
    - ik heb bovenin aangegeven van wanneer tot wanneer de rentevast perioden lopen
    - ik heb wat velden opgemaakt zodat automatisch alle regels aan het begin van ieder jaar afwijkend van kleur zijn en dat iedere regel waarop de nieuwe rentevast periode in werking treed in kleur wordt weergegeven.

    Als er interesse is wil ik hem wel delen (weet zo snel even niet hoe ik dat kan doen...)

    BeantwoordenVerwijderen
    Reacties
    1. Ja hoor, graag. Ik kan hem ook online voor je zetten als je wilt..

      Verwijderen
  8. Bedankt voor het online zetten van het bestand. Mijn vriend en ik hebben net ons eerste huis gekocht en zullen het gaan toepassen.

    Ik heb het bestand nog iets gefinetuned door de titel (categorieën van de verschillende bedragen) vast te zetten, zodat je in het midden van het blad nog weet welke kolom het was.

    Daarnaast heb ik het mogelijk gemaakt om de maanden in en uit te klappen tot een jaaroverzicht. Zo kan je het inklappen tot 30 regels i.p.v. 360 regels.

    Nogmaals dank!

    BeantwoordenVerwijderen