Plan terugbetalingen van leningen met Excel-formules
Aflossing van een lening is het terugbetalen van geld dat eerder van een geldschieter is geleend, meestal via een reeks periodieke betalingen die de hoofdsom plus rente omvatten. Wist u dat u het softwareprogramma Excel kunt gebruiken om uw aflossingen te berekenen?
Dit artikel is een stapsgewijze handleiding voor het opzetten van leningberekeningen.
Belangrijkste leerpunten:
- Gebruik Excel om grip te krijgen op uw hypotheek door uw maandelijkse betaling, uw rentetarief en uw leningsschema te bepalen.
- Met Excel kunt u dieper ingaan op de uitsplitsing van een lening en een aflossingsschema opstellen dat voor u werkt.
- Er zijn berekeningen beschikbaar voor elke stap die u kunt aanpassen om aan uw specifieke behoeften te voldoen.
- Door uw lening stap voor stap af te breken en te onderzoeken, kan het terugbetalingsproces minder overweldigend en beter beheersbaar aanvoelen.
Inzicht in uw hypotheek
Met Excel krijgt u in drie eenvoudige stappen een beter inzicht in uw hypotheek. De eerste stap bepaalt de maandelijkse betaling. De tweede stap berekent de rentevoet en de derde stap bepaalt het uitleenschema.
U kunt in Excel een tabel maken die u de rentevoet, de leningberekening voor de looptijd van de lening, de ontbinding van de lening, de aflossing en de maandelijkse betaling vertelt.
Bereken de maandelijkse betaling
Ten eerste, hier is hoe u de maandelijkse betaling voor een hypotheek berekent. Aan de hand van de jaarlijkse rente, de hoofdsom en de looptijd kunnen we het maandelijks af te lossen bedrag bepalen.
De formule, zoals weergegeven in de bovenstaande schermafbeelding, is als volgt geschreven:
= -PMT (snelheid; lengte; huidige_waarde; [toekomstige_waarde]; [type])
Het minteken voor PMT is nodig omdat de formule een negatief getal retourneert. De eerste drie argumenten zijn de rentevoet van de lening, de duur van de lening (aantal perioden) en de geleende hoofdsom. De laatste twee argumenten zijn optioneel, de restwaarde is standaard nul; vooruit te betalen (voor één) of aan het einde (voor nul) is ook optioneel.
De Excel-formule die wordt gebruikt om de maandelijkse betaling van de lening te berekenen, is:
= PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Toelichting: Voor het tarief gebruiken we het maandtarief (renteperiode), daarna berekenen we het aantal periodes (120 voor 10 jaar vermenigvuldigd met 12 maanden) en tenslotte geven we de geleende hoofdsom aan. Onze maandelijkse betaling zal over 10 jaar $ 1.161,88 bedragen.
Bereken de jaarlijkse rentevoet
We hebben gezien hoe je de berekening van een maandbedrag voor een hypotheek opzet. Maar misschien willen we een maximale maandelijkse betaling instellen die we ons kunnen veroorloven, waarin ook het aantal jaren wordt weergegeven waarover we de lening zouden moeten terugbetalen. Om die reden willen wij graag weten wat het bijbehorende jaarlijkse rentepercentage is.
Zoals te zien is in de bovenstaande schermafbeelding, berekenen we eerst het periodetarief (in ons geval maandelijks) en vervolgens het jaartarief. De gebruikte formule is RATE, zoals weergegeven in de bovenstaande schermafbeelding. Het is als volgt geschreven:
= RATE (Aantal; pmt; huidige_waarde; [toekomstige_waarde]; [type])
De eerste drie argumenten zijn de lengte van de lening (aantal perioden), de maandelijkse betaling om de lening terug te betalen en de geleende hoofdsom. De laatste drie argumenten zijn optioneel en de restwaarde is standaard nul; de term argument voor het managen van de looptijd vooraf (voor één) of aan het einde (voor nul) is ook optioneel. Ten slotte is het argument voor schatting optioneel, maar het kan een eerste schatting van het tarief geven.
De Excel-formule die wordt gebruikt om de uitleenrente te berekenen, is:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Let op: de corresponderende gegevens in de maandelijkse betaling moeten een negatief teken krijgen. Daarom staat er een minteken voor de formule. De renteperiode is 0,294%.
We gebruiken de formule = (1 + B5) is 12-1 ^ = (1 + 0,294%) ^ 12-1 om de jaarlijkse rente van onze lening te verkrijgen, die 3,58% is. Met andere woorden, om $ 120.000 over 13 jaar te lenen om $ 960 maandelijks te betalen, moeten we onderhandelen over een lening tegen een jaarlijks maximumtarief van 3,58%.
Het gebruik van Excel is een geweldige manier om bij te houden wat u verschuldigd bent en om een aflossingsschema op te stellen waarmee u eventuele kosten tot een minimum kunt beperken.
De lengte van een lening bepalen
We zullen nu zien hoe u de duur van een lening kunt bepalen als u de jaarlijkse rente, de geleende hoofdsom en de maandelijkse aflossing kent die moet worden terugbetaald. Met andere woorden, hoe lang hebben we nodig om een hypotheek van $ 120.000 met een tarief van 3,10% en een maandelijkse betaling van $ 1.100 terug te betalen?
De formule die we zullen gebruiken is NPER, zoals weergegeven in de bovenstaande schermafbeelding, en deze is als volgt geschreven:
= NPER (rente; pmt; huidige_waarde; [toekomstige_waarde]; [type])
De eerste drie argumenten zijn de jaarlijkse rentevoet van de lening, de maandelijkse betaling die nodig is om de lening terug te betalen en de geleende hoofdsom. De laatste twee argumenten zijn optioneel, de restwaarde is standaard nul. De term argument dat vooraf (voor één) of aan het einde (voor nul) moet worden betaald, is ook optioneel.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Minteken voor de formule
De bijbehorende gegevens in de maandelijkse betaling moeten een negatief teken krijgen. Daarom hebben we een minteken voor de formule. De duur van de vergoeding is 127,97 periodes (in ons geval maanden).
We gebruiken de formule = B5 / 12 = 127,97 / 12 voor het aantal jaren om de lening af te lossen. Met andere woorden, om $ 120.000 te lenen, met een jaarlijks tarief van 3,10% en om $ 1.100 maandelijks te betalen, moeten we looptijden van 128 maanden of 10 jaar en acht maanden terugbetalen.
Ontbinding van de lening
Een leningbetaling is samengesteld uit hoofdsom en rente. De rente wordt voor elke periode berekend – de maandelijkse aflossingen over 10 jaar leveren ons bijvoorbeeld 120 perioden op.
De bovenstaande tabel toont de uitsplitsing van een lening (een totale periode gelijk aan 120) met behulp van de PPMT- en IPMT-formules. De argumenten van de twee formules zijn hetzelfde en worden als volgt opgesplitst:
= -PPMT (rente; aantal_periode; lengte; hoofdsom; [rest]; [term])
De argumenten zijn dezelfde als voor de PMT-formule die we al hebben gezien, behalve voor “aantal_periode”, die wordt toegevoegd om de periode te tonen waarover de lening moet worden opgesplitst, gegeven de hoofdsom en de rente. Hier is een voorbeeld:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3,10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Het resultaat wordt getoond in de schermafbeelding boven “Decompositie van de lening” over de geanalyseerde periode, die “één” is; dat wil zeggen, de eerste periode of de eerste maand. We betalen $ 1.161,88 uitgesplitst in $ 856,20 hoofdsom en $ 305,68 rente.
Leningberekening in Excel
Het is ook mogelijk om de aflossing van de hoofdsom en de rente voor meerdere perioden te berekenen, zoals de eerste 12 maanden of de eerste 15 maanden.
= -CUMPRINC (tarief; lengte; opdrachtgever; startdatum; einddatum; type)
We vinden de argumenten, snelheid, lengte, hoofdsom en term (die verplicht zijn) die we al in het eerste deel zagen met de formule PMT. Maar hier hebben we ook de argumenten “start_date” en “end_date” nodig. De “start_date” geeft het begin van de te analyseren periode aan en de “end_date” geeft het einde van de te analyseren periode aan.
Hier is een voorbeeld:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Het resultaat wordt getoond in het screenshot “Cumul 1e jaar”, dus de geanalyseerde perioden variëren van één tot 12 van de eerste periode (eerste maand) tot de twaalfde (12e maand). Over een jaar zouden we $ 10.419,55 aan hoofdsom en $ 3.522,99 aan rente betalen.
Afschrijving van de lening
De eerdere formules stellen ons in staat om per periode ons schema te creëren, om te weten hoeveel we maandelijks aan hoofdsom en rente zullen betalen, en om te weten hoeveel er nog moet worden betaald.
Een uitleenschema opstellen
Om een uitleenschema te maken, gebruiken we de verschillende hierboven besproken formules en breiden we deze uit over het aantal perioden.
Voer in de eerste puntkolom “1” in als de eerste punt en sleep de cel naar beneden. In ons geval hebben we 120 perioden nodig, aangezien een aflossing van een lening van 10 jaar vermenigvuldigd met 12 maanden gelijk is aan 120.
De tweede kolom is het maandelijkse bedrag dat we elke maand moeten betalen – dit is constant over het hele uitleenschema. Om het bedrag te berekenen, voegt u de volgende formule in de cel van onze eerste periode in:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
De derde kolom is de hoofdsom die maandelijks wordt afgelost. Voor de 40e periode betalen we bijvoorbeeld $ 945,51 in hoofdsom terug op ons maandelijkse totale bedrag van $ 1.161,88.
Om de afgeloste hoofdsom te berekenen, gebruiken we de volgende formule:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
De vierde kolom is de rente, waarvoor we de formule gebruiken om de afgeloste hoofdsom over ons maandbedrag te berekenen om te ontdekken hoeveel rente er moet worden betaald:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
De vijfde kolom bevat het bedrag dat nog moet worden betaald. Na de 40e betaling moeten we bijvoorbeeld $ 83.994,69 betalen over $ 120.000.
De formule is als volgt:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
De formule gebruikt een combinatie van de hoofdsom onder een periode vóór de cel met de geleende hoofdsom. Deze periode begint te veranderen wanneer we de cel kopiëren en naar beneden slepen. Onderstaande tabel laat zien dat aan het einde van 120 periodes onze lening wordt afgelost.