Excel & hypotheek 5 - wat bespaar je bij extra aflossen op de hypotheek - annuïteit [2] - verkorte looptijd

Excel & hypotheek 5 - wat bespaar je bij extra aflossen op de hypotheek - annuïteit [2] - verkorte looptijd


settings
  • Instellingen
  • Positie

    Automatische scrolling

Opmerking vooraf:ik ga bij deze tutorial uit van het feit dat het maandbedrag gedurende de hele periode gelijk blijft en dat de looptijd verkort wordt.
De tutorial waarbij de looptijd hetzelfde blijft maar het maandbedrag verlaagd wordt, volgt later.

In de eerste tutorial uit deze serie leg ik uit hoe je een bestand begint door de basisgegevens en de eerste formules in te voeren. Daarna ben ik verder gegaan met het opzetten van de berekening van het aflossen op een aflossingsvrije hypotheek.

In deel 4ben ik verder gegaan met het opzetten van de berekening van het aflossen op een annuïtaire hypotheek. Dat bestand gaan we verder afmaken. 

Het bestand ziet eruit als in het plaatje hieronder.

Om de tabel verder goed in te kunnen vullen is het nodig om vanaf dit punt jouw eigen hypotheekgegevens bij de basisgegevens bovenaan het werkblad in te vullen.

Vooral de begindatum is van belang omdat we berekeningen per jaar gaan invoegen.

Als de startdatum van jouw hypotheek op een andere dag dan de eerste van de maand gestart is kun je ervoor kiezen om die datum te gebruiken, maar je kunt ook kiezen om de eerste dag van die beginmaand te nemen. Het is maar net wat je zelf het meest handig/duidelijk vindt.

Noot: dit bestand is bedoeld om een globaal inzicht te krijgen, niet om exacte berekeningen uit te voeren zoals een hypotheekvertrekker dat doet want ik heb geen enkele financiële achtergrond.
Aan deze berekeningen kunnen dan ook geen rechten ontleend worden.

Opmerking: de gegevens in dit overzicht zijn verzonnen; in werkelijkheid zou de hypotheek moeten doorlopen tot het hele bedrag is afgelost. Als je jouw eigen gegevens invult zou de hele lening aan het eind van de looptijd afgelost moeten zijn.

 

Zoals gezegd, we gaan verder.

Als jij de gegevens in andere cellen hebt gezet dan het voorbeeldbestand, zult bij het opzetten van de formules moeten opletten met de verwijzing naar de juiste kolommen, maar dat wijst zich vanzelf als je de omschrijvingen van de formules goed leest.

Vul in cel A19 (geel) de gewenste begindatum (van jouw hypotheek) in.

Kopieer nu bij alle drie tabellen zoveel rijen naar beneden dat je in de drie tabellen minimaal het eerste (misschien niet volledige) jaar en het tweede (volledige) jaar ziet.

Zet vervolgens bij alle drie tabellen een streep tussen december en januari, om duidelijk te maken waar een jaar ophoudt en het volgende jaar begint.

Die streep mag je zo mooi maken als je zelf wilt: dik dun, kleurtje of niet :-)

We gaan nu in kolom T per jaar het totaal van de extra aflossingen berekenen.

  • Klik in kolom T in de cel boven de streep van het jaareinde (bij het voorbeeld is dat T26)
  • Klik op hetΣ teken in de werkbalk (Excel zet al een gekleurde stippellijn om het totale blok van cellen waarvan het programma denkt dat je die wilt optellen)
  • selecteer in kolom R (extra aflossing) alle cellen van dat jaar (bij het voorbeeld is dat R19 t/m R26)
  • druk op enter – de formule is klaar.

De formule in T26: =SUM(R19:R26)

Volg dezelfde stappen bij het tweede (volledige) jaar (cellen R27 t/m R38 in het voorbeeld)

De formule in T38: =SUM(R27:R38)

Op dezelfde manier gaan we in kolom W de besparing per jaar uitrekenen.

In het voorbeeld tellen we in cel W26 de bedragen in V19 t/m V26 en vervolgens in cel W38 de bedragen in V27 t/m V38 op.
De formule in W26: =SUM(V19:V26)
De formule in W38: =SUM(V27:V38)

Het bestand ziet er nu zo uit (de formules staan in het rood eronder):

We gaan nu zorgen dat de tabellen doorlopen tot aan het einde van de hypotheek.

  • Selecteer de 12 rijen cellen van het (tweede) complete jaar [cellen N27 t/m AB38 in het voorbeeld).
  • Druk op ctrl + c (sneltoets voor kopiëren) of klik op de rechtermuisknop, en kies kopieer uit het menu. Excel zet een gekleurd stippellijntje om de selectie.
  • Ga in de eerste lege cel onder de laatste datum in kolom N staan (N39 in het voorbeeld) en druk op ctrl + v (sneltoets voor plakken) of klik op enter.

De rijen die net gekopieerd zijn, zijn nog geselecteerd.

Herhaal nu de bovenstaande stappen net zo vaak tot je een jaar voorbij de rentevast periode komt (31-1-2023). In de tutorial van de aflossingsvrije hypotheek hadden we hier al een extra rij ingevoegd, en die hadden we een afwijkend kleurtje gegeven.

Zoals je nu kunt zien zorgt dat ervoor dat de datum niet klopt, en dat de optellijn in het jaar na de einde rentevast periode ook verschoven is. Dat gaan we eerst oplossen.

Verwijder de datum in de rij van de rentevast periode (cel N137 in het voorbeeld).

Ga in de cel rechts daarnaast staan (cel O137 in het voorbeeld) en selecteer vervolgens de cellen over de breedte van de drie kolommen t/m het einde van de gegevens die je ingevoerd hebt (O137 t/m AB158 in het voorbeeld).

Druk op Ctrl + de “x” tegelijk (is sneltoets voor knippen), druk 1 keer op de pijltjestoets naar beneden, je staat nu in cel O138, en druk op enter. De hele selectie verhuist nu 1 rij naar beneden.

Excel past zelf de optelformule aan; ondanks die ene lege rij in het midden worden de bedragen van het hele jaar nog steeds opgeteld. Ook staat de lijn aan het eind van het jaar weer goed, behalve in de kolom met de datum. Dat kun je nu vast wel zelf aanpassen ;-)

Als je de lege cellen van de rentevast periode een kleurtje geeft zie je in een oogopslag dit belangrijke punt in de looptijd van de hypotheek.

We gaan nu weer verder met het kopiëren van de gegevens voor de rest van de looptijd van de hypotheek.

Ga naar een willekeurig compleet jaar (dus januari t/m december) boven het jaar met de einde rentevast periode, en kopieer die gegevens naar de jaren onder het jaar met de rentevast periode, net zolang tot je aan het eind van de looptijd komt (1-3-2028 in het voorbeeld). We kunnen nu zien dat de totale looptijd van deze hypotheek 180 maanden bedraagt. Dit aantal kun je invullen bij de basisgegevens bovenin het bestand.

We hebben nu teveel maanden ingevoerd, dat moet aangepast worden.

Omdat we in een van de vorige delen van de tutorial al de bovenste rijen geblokkeerd hadden, blijven de basisgegevens, de kopteksten van de tabel en een paar rijen daaronder altijd in beeld.

Nu scrollen we helemaal naar beneden.  Je ziet dat de bovenste rijen in beeld blijven en dat je nu een deel van de onderste rijen ziet.

In ons voorbeeld is 1-3-2028 de einddatum, dus de rijen in de drie kolommen na 1-3-2028 kunnen verwijderd worden.

  • Selecteer de betreffende rijen en klik binnen de selectie op de rechtermuisknop.
  • Kies verwijderen uit het menu.

Een andere manier is om onder de drie tabellen een rij lege cellen te selecteren, en deze naar boven te kopiëren tot je op het punt bent waar de hypotheek afloopt.

Het laatste lening-jaar bestaat nu uit nog maar 3 maanden.

Om alle totalen uiteindelijk goed op te kunnen tellen gaan we in de laatste rij ook het van de extra aflossing en het totaal van de besparing over dat laatste gedeeltelijke jaar invullen.

  • Klik in kolom F in de laatste cel van het jaareinde (bij het voorbeeld is dat T198)
  • Klik op het Σ teken in de werkbalk en selecteer in kolom R (extra aflossing) alle cellen van dat jaar (bij het voorbeeld is dat R196 t/m R198).
  • druk op enter – de formule is klaar.

Datzelfde doe je ook in de laatste cel van kolom W.
Nu nog een mooi lijntje om de kolom af te sluiten en dit deel is klaar.

We gaan nu in de kopteksten formules invoeren waardoor je allerlei totalen ziet en waardoor je in één oogopslag kun zien wat je bespaart gedurende de hele looptijd.

Ga naar boven in het bestand (sneltoets ctrl + home)

We willen nu bepaalde getallen over het hele jaar gaan optellen maar het is een beetje lastig om steeds heen en weer te scrollen. We gaan daarom tijdelijk een heleboel tussenliggende rijen verbergen. Zodra alle formules ingevoerd zijn maken we ze weer zichtbaar.

Selecteer twee of drie rijen onder de lijn van de geblokkeerde titels (tussen rij 20 en 21 in het voorbeeld) een hele rij door op het cijfer links in de kantlijn te klikken.

Scrol met de muis helemaal naar beneden (of gebruik de schuifbalk rechts van het werkblad) totdat je de laatste rijen ziet. Zorg wel dat je tussendoor niet per ongeluk in het bestand klikt, want dan moet je die rij bovenin weer opnieuw selecteren.

Druk op de shift toets en selecteer nu de zesde rij van onderen. Bij jouw eigen gegevens zou je ervoor kunnen zorgen dat de laatste 2 jaartellingen nog zichtbaar blijven.

Alle rijen tussen de bovenste en onderste geselecteerde rijen zijn nu geselecteerd (grijs).

Klik binnen het geselecteerde gebied op de rechtermuisknop en kies verbergen (hide in het Engels).

Je ziet nu niets bijzonders aan het bestand, behalve dat de cijfers in de kantlijn niet doorlopen.

In de koptekst boven de tabel met de extra aflossingen gaan we optellen hoeveel rente er over de hele wordt betaald.

  • Klik in cel P16.
  • Klik op het Σ teken in de werkbalk en selecteer in kolom P (alle betaalde rente) alle cellen van het begin tot het eind – alle verborgen rijen worden ook meegeteld.
  • Druk op enter – de formule is klaar.

In de koptekst boven de tabel met de extra aflossingen gaan we optellen hoeveel er over de hele looptijd extra is afgelost. Ik heb als voorbeeld een paar willekeurige bedragen in de kolom van de extra aflossing gezet.

  • Klik in cel T15
  • Klik op het Σ teken in de werkbalk en selecteer in kolom T (jaartotalen bij de extra aflossing) alle cellen van het begin van de tabel tot het eind – alle verborgen rijen worden ook meegeteld.
  • Druk op enter – de formule is klaar.

In de koptekst boven de tabel met de besparingen gaan we optellen hoeveel er over de hele looptijd extra is afgelost. In het voorbeeld stond deze berekening er al in maar ik ga uitleggen hoe jij hem erin kunt zetten.

  • Klik in cel V16
  • Klik op het Σ teken in de werkbalk en selecteer in kolom V (alle bespaarde bedragen) alle cellen van het begin tot het eind – alle verborgen rijen worden ook meegeteld.
  • Druk op enter – de formule is klaar.

In de koptekst boven de tabel met de onafgeloste hypotheek gaan we optellen hoeveel rente er over de hele looptijd moet worden betaald.

  • Klik in cel Z16
  • Klik op het Σ teken in de werkbalk en selecteer in kolom Z (alle betaalde rente) alle cellen van het begin tot het eind – alle verborgen rijen worden ook meegeteld.
  • Druk op enter – de formule is klaar.

Als je alles goed gedaan hebt is het verschil tussen de beide sommen van betaalde rente uit de beide tabellen (links en rechts) hetzelfde als de som van de besparing in de middelste tabel.

Dus met de getallen uit het voorbeeld:

€ 76.881,06 – € 76.053,42 = € 827,65

Alle berekeningen zijn nu klaar.

Je kunt de verborgen rijen nu weer zichtbaar maken en de voorbeeldgetallen bij de extra aflossingen verwijderen.

Belangrijk om rekening mee te houden:

Ik ben bij deze berekening uitgegaan van het feit dat de looptijd van de annuïteiten hypotheek bij elke extra aflossing de looptijd iets korter wordt. Het zou ook kunnen zijn dat het maandbedrag verlaagd wordt (volgende tutorial). Dit is afhankelijk van wat de afspraken met de hypotheekverstrekker zijn.

Bij elke extra aflossing krijg je bericht van de hypotheekvertrekker met de bijgewerkte gegevens van de uitstaande hypotheekschuld. Op het moment dat ik de brief krijg controleer ik of mijn berekening klopt en vul ik in de kolom van de restschuld (kolom S in het voorbeeld) bij de betreffende maand in, en maak ik die cel groen. Op die manier kan ik zien dat op dat moment het bedrag van de hypotheek klopt met de werkelijkheid.

In mijn geval kloppen de berekeningen goed, slechts af en toe zit er een of meer centen (maar ook niet meer dan dat) verschil tussen mijn berekening en het werkelijke bedrag van de hypotheekverstrekker.

Een paar tips

Zoals je ziet in het voorbeeld begint de looptijd van deze hypotheek in het verleden. Als je eenvoudig wilt nagaan hoeveel maanden je nog moet betalen voor het einde van jouw hypotheek kun je de huidige maand als beginpunt gebruiken. Dat kun je doen door het de huidige maand het maandcijfer 0 te geven.

De formule telt daaronder gewoon verder.
Wil je dit weer ongedaan maken, kopieer je de formule van de rij erboven gewoon weer naar beneden.

Stel dat je in het verleden al hebt afgelost op de hypotheek maar je weet niet meer hoeveel of wanneer, dan kun je dat bedrag bij de basisgegevens invullen.

Je kunt nu jouw doelen berekenen. Bijvoorbeeld:

Hoeveel kan ik besparen als ik € 1000 per jaar aflos?

Ik wil de lening voor de einde rentevaste periode (of binnen xx jaar) afbetaald hebben. Hoeveel zou ik dan per maand moeten sparen/tussentijds aflossen? [even geen rekening houdend met de normale aflossing - deel de restschuld door het aantal maanden]

Tip: als je de huidige maand op nul zet en je scrolt door naar die groene balk van de rentevaste periode, zie je meteen hoeveel maanden dat is.

De tutorial voor de berekening van de annuïteiten hypotheek met verlaagde maandlasten kun je in de toekomst verwachten.

Ik hoop dat je met deze tutorial uit de voeten kunt en ik zou het op prijs stellen om feedback te krijgen over jouw ervaringen met dit bestand. Wat vind je ervan, wat heb je ervan geleerd en, heel belangrijk, word je [net als ik] door de berekeningen gemotiveerd om te proberen zoveel mogelijk versneld af telossen?

 

Een overzicht van al mijn Excel tutorials vind jet hier.

Wil je meer weten over Yoors? Hier vind je een compleet overzicht.
Wil je kunnen reageren op berichten? Meld je aan en wordt gratis lid. 

 

 
 

© 2018 Yvonne35 - De informatie in dit bericht is uitsluitend bedoeld voor persoonlijk gebruik.
De link naar dit bericht mag vrij gedeeld worden.

Het is niet toegestaan om [delen van] de tekst of afbeeldingen te kopiëren en te gebruiken zonder bronvermelding, of te gebruiken voor commerciële doeleinden.



Beoordeel


auto leasen
auto leasen
private lease goedkoop
private lease goedkoop
Muziek, Kunst & Cultuur
Aanbevolen Muziek
Aanbevolen Muziek
 
×

Yoors


exit_to_app Inloggen