×

Yoors


exit_to_app Inloggen

camera_alt
Afbeelding toevoegen
60
Excel & schulden - hoe kun je uitrekenen hoe lang het duurt voor een schuld afgelost is

Excel & schulden - hoe kun je uitrekenen hoe lang het duurt voor een schuld afgelost is


We gaan verder met het document dat we in de vorige tutorial zijn begonnen.

We gaan eerst uitrekenen hoe lang het duurt voor de schulden afbetaald zijn als je de bedragen betaalt zoals ze in het overzicht staan.

De formule is als volgt:

Het schuldbedrag delen door de afbetaling

 In Excel doe je dat zo:

  • Ga in de lege cel naast de eerste afbetaling staan
  • Type: = (is het begin van de formule)
  • Klik met de muis in de cel met het schuldbedrag op dezelfde rij (er komt nu een gekleurd stippellijntje om de cel)
  • Type: / (het teken voor delen)
  • Klik met de muis in de cel met het afbetalingsbedrag (krijgt een andere kleur stippellijntje)
  • Druk op enter – de formule is klaar

Kopieer de formule naar de cellen eronder

In het plaatje hieronder heb ik de formules in rood ernaast gezet

Oef, dat is best even schrikken! Zo lang nog bezig om af te betalen, kan dat niet sneller?

Ja dat kan, en wel op twee manieren:

  1. De afbetalingsbedragen verhogen – maar dat moet je wel kunnen (besparen op andere zaken dus).
  2. Gebruik maken van het sneeuwbaleffect. Dat ga ik later uitleggen.

 Eerst kom ik even terug op de looptijd van de lening in het overzicht. Die klopt niet. Ik heb namelijk de schuld gedeeld door de aflossing, maar je moet ook nog rente betalen, dus je bent waarschijnlijk nog langer aan het afbetalen. 

We gaan eerst uitwerken hoe lang het aflossen van de lening werkelijk zou duren als we de rente meerekenen.

Noot: de renteberekening in dit document is niet 100% nauwkeurig maar komt wel redelijk in de buurt. Ik heb geen financiële achtergrond en ik kan dus geen berekening maken zoals een bank doet, maar om inzicht te krijgen is het goed genoeg.

 Begin een nieuwe tabel onder de vorige, type de tekst over zoals op onderstaand plaatje (kleurtjes zijn optioneel).

Nu gaan we de tabel vullen met formules. We beginnen met de datum. We willen dat de datum steeds 1 maand opschuift. Als we nu de maand in cel A12 + 1 zouden doen, wordt de uitkomst 2-7-2017. Een dag later dus.

We moeten een formule gaan gebruiken. Ik zal deze stap voor stap opsommen.

Ik heb de Engelse versie van Excel, het kan zijn dat in de Nederlandse versie Nederlandse woorden ingevuld moeten worden.

  • Klik in de cel A13 (de cel onder de datum)
  • Type: = (is het begin van de formule)
  • Type: date(year(    (Met de haakjes en zonder spaties! Terwijl je dit typt laat Excel waarschijnlijk al een paar formule-opties zien, je kunt zo zien welk woord en in welke taal je moet gebruiken)
  • Klik met de muis in de cel met de datum erboven (A12, er komt nu een gekleurd stippellijntje om de cel).
  • Type: );month(
  • Klik met de muis weer in cel A12
  • Type: )+
  • Klik met de muis in de cel met het cijfer 1 (A11)
  • Druk of de F4 knop bovenaan je toetsenbord (bij sommige computers moet je nog een extra knop ingedrukt houden (bv. fn-knop). Er komen nu dollartekens voor en na de A in de formule te staan. Die dollartekens zorgen ervoor dat de koppeling niet wijzigt als je gaat kopiëren.
  • Type: ;day(
  • Klik met de muis weer in cel A12
  • Type: ))
  • Druk op enter – de formule is klaar

 

Als het goed is staat nu 1-8-2017 in A13.

Excel zet een datum altijd tegen de rechterkant in een cel, in mijn lijst vind ik het prettiger als ze aan de linkerkant staan dus dat heb ik met een simpele druk op de knopjes in de menubalk boven het werkblad aangepast.

In het plaatje hieronder zie je het resultaat. De formule staat in het rood eronder.

We gaan nu het aantal maanden laten optellen, dat is redelijk eenvoudig: de vorige maand +1.

  •  Klik in de cel B1 (de cel onder het cijfer nul in de kolom maand)
  • Type: = (is het begin van de formule)
  • Klik met de muis in de cel met het cijfer nul erboven (A11, er komt nu een gekleurd stippellijntje om de cel).
  • Type: +1
  • Druk op enter – de formule is klaar

Kopieer de formule naar de cel eronder, als je het goed gedaan hebt telt Excel vanzelf door.
Ik heb gekozen om de getallen in deze kolom in het midden te zetten.

In het oranje vakje onder betaalde rente vul je het rentepercentage wat je per jaar moet betalen over de lening in; en onder restschuld vul je nu het beginbedrag van de schuld in.

In het plaatje hieronder staat de formule weer in rood ernaast.

Vul nu onder de kolom betaling handmatig 3 x onder elkaar het aflossingsbedrag in.

Ik heb er in deze tabel voor gekozen om het niet te koppelen aan de bedragen in de tabel erboven. Als je namelijk op een gegeven moment het aflosbedrag wilt wijzigen wil je dat vanaf een bepaalde datum doen en niet vanaf het begin, anders kloppen de berekeningen niet meer.

Kopieer ook de formules uit de eerste 2 cellen naar beneden zodat je nu in totaal 3 rijen met gegevens hebt.

 We gaan verder met het berekenen van de betaalde rente. De formule daarvoor is:

De restschuld van de vorige maand x de jaarrente, en dat delen door 12 maanden.

Als je weet wat de rente per maand is hoef je het bedrag niet door 12 te delen.

  • Klik in de cel D12 (de cel onder het rentepercentage)
  • Type: =(
  • Klik met de muis in de cel met het bedrag van de restschuld in de regel erboven (F11)
  • Type: * (het teken voor vermenigvuldigen)
  • Klik met de muis in de cel met het rentepercentage (D11)
  • Druk of de F4 knop bovenaan je toetsenbord (bij sommige computers moet je nog een extra knop ingedrukt houden (bv. fn-knop). Er komen nu dollartekens voor en na de A in de formule te staan)
  • Type: )/12  (/ is het teken voor delen door)
  • Druk op enter – de formule is klaar

 

Als er een getal weergegeven wordt in plaats van een bedrag kun je dat aanpassen door op de knop met het geld icoontje boven in de werkbalk te klikken.

Nu we weten hoeveel rente er berekend is kunnen we uitrekenen hoeveel we afgelost hebben.

De formule is: het maandbedrag min de betaalde rente.

En dan kunnen we ook het restbedrag uitrekenen, dat is:
De restschuld van de vorige maand min de betaalde aflossing.

Die beide formules gaan we invoeren.

Hoeveel is afgelost:

  •  Klik in cel E12 onder aflossing
  • Type: =
  • Klik met de muis in de cel met het bedrag van de maandelijkse betaling (C12)
  • Type: - (het minteken)
  • Klik met de muis in de cel met de betaalde rente (D12)
  • Druk op enter – de formule is klaar

De restschuld berekenen: 

  • Klik in cel F12 onder restschuld
  • Type: =
  • Klik met de muis in de cel met het bedrag van de restschuld in de regel erboven (F11)
  • Type: - (het minteken)
  • Klik met de muis in de cel met het aflossingsbedrag (E12)
  • Druk op enter – de formule is klaar

 De formules staan weer in het rood aangegeven in het plaatje hieronder.

Selecteer nu de 3 cellen met de formules in D, E en F en kopieer ze naar beneden zodat de eerste 3 regels van de tabel gevuld zijn. Dat doe je zo: ga met de muis op het kleine vierkantje rechts onderaan de selectie staan, je ziet nu een plusje. Sleep dat plusje naar beneden.

Selecteer nu de onderste twee rijen van de tabel en kopieer ze naar beneden, net zover tot je uitkomt bij een restschuld van € 0 of minder.

Door twee rijen tegelijk te selecteren voorkom je dat Excel zelf automatisch steeds 1 optelt bij het maandbedrag.

Als je het goed hebt gedaan zie je dat je ongeveer 105 maanden nodig hebt om deze lening af te lossen als je steeds hetzelfde maandbedrag betaalt (ik heb voor het gemak de tussenliggende rijen -grijs- verborgen).

Dat aantal kun je nu invullen in de bovenste tabel.

We gaan voor alle schulden in de bovenste tabel aparte tabellen maken waarbij je kunt zien hoeveel restschuld per maand over blijft.

Voor leningen waarover rente betaald moet worden volg je de stappen hierboven, voor de leningen zonder rente staan de stappen hieronder.

Noot: dit bestand is niet bedoeld om te printen, je zou dan heel veel papier nodig hebben.

Vul de gegevens van de 3 leningen in zoals het plaatje hieronder.

Je hoeft niet bij elke lening de datum en het aantal maanden in te vullen, het is voldoende als dat aan het begin van de regel staat.

Vul nu onder de kolom betaling bij de huurachterstand handmatig 3 x onder elkaar het aflossingsbedrag in.

Daarna gaan we verder met he berekenen van de restschuld.

De formule daarvoor is simpel: de restschuld van de vorige maand min de aflossing.

  • Klik in cel I12 onder restschuld
  • Type: =
  • Klik met de muis in de cel met het bedrag van de restschuld in de regel erboven (I11)
  • Type: - (het minteken)
  • Klik met de muis in de cel met het aflossingsbedrag (H12)
  • Druk op enter – de formule is klaar

Kopieer de formule naar beneden zodat de eerste 3 regels van de tabel gevuld zijn. In het plaatje hieronder staan de formules er in het rood naast.

En nu komt het mooie van Excel: je hoeft niet bij elke schuld de formule helemaal opnieuw in te vullen.

Vul bij de twee andere schulden ook 3 x onder elkaar handmatig de aflossingsbedragen in.

Daarna selecteer je de 3 cellen met de formules uit de tabel van de huurachterstand en die kopieer je naar de beide andere tabellen. Excel zorgt ervoor dat de formules automatisch aangepast worden zodat de getallen uit de andere 2 tabellen gebruikt worden.

Vertrouw je het toch niet helemaal of wil je nog een keertje extra oefenen kun je natuurlijk ook de stappen van de eerste tabel (huurachterstand) volgen.

Ook nu selecteer je de onderste twee rijen van de tabellen en kopieer ze naar beneden, net zover tot je uitkomt bij een restschuld van € 0 of minder.

In het plaatje hieronder heb ik de rijen die grijs zijn even verborgen, je ziet dat de nummering van de maanden een stukje overslaat.

Je hebt nu een overzicht van hoe het schema is als je doorgaat met aflossen zoals je nu bezig bent.

Laat je weten of het tot nu toe duidelijk is?

In het volgende deel van de tutorial laat ik zien hoe je sommige schulden sneller af kunt lossen door gebruik te maken van het sneeuwbaleffect.

Excel tutorials - inhoudsopgave

De tutorials in deze serie zijn bedoeld voor Excel beginners en zijn gebaseerd op mijn eigen (veelal financiële) bestanden.

Doe je mee? Word lid van Yoors en meld je aan!


Inhoudsopgave uitleg Yoors

Wil je eerst weten wat Yoors is en hoe het werkt? Kijk dan hier voor een uitgebreid overzicht.




_PetitCorbeau_
Weer heel handig
07-08-2017 13:08
07-08-2017 13:08 • 1 reactie • Reageer
Yvonne35
Dank je :-)
07-08-2017 17:44
07-08-2017 17:44 • Reageer
HelpendeHandjes
Wij snappen er niet veel van maar het ziet er wel duidelijk uit!
04-08-2017 17:28
04-08-2017 17:28 • 1 reactie • Reageer
Yvonne35
Gewoon proberen, als je er niet uitkomt laat het maar weten. Dan kan ik gericht antwoord geven en eventueel de tutorial aanpassen.
07-08-2017 17:43
07-08-2017 17:43 • 1 reactie • Reageer