Plan terugbetalingen van leningen met Excel-formules
Terugbetaling van een lening is het terugbetalen van eerder geleend geld van een geldschieter, meestal via een reeks periodieke betalingen die 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 instellen van leningberekeningen.
Belangrijkste leerpunten
- Gebruik Excel om grip te krijgen op uw hypotheek door uw maandelijkse betaling, uw rentetarief en uw leenschema te bepalen.
- U kunt de uitsplitsing van een lening met Excel nader bekijken 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 aflossingsproces minder overweldigend en 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 leenschema.
U kunt een tabel in Excel maken die u de rente, de berekening van de lening voor de duur van de lening, de ontbinding van de lening, de amortisatie en de maandelijkse betaling laat zien.
Bereken de maandelijkse betaling
Ten eerste, hier is hoe de maandelijkse betaling voor een hypotheek te berekenen. Aan de hand van de jaarlijkse rentevoet, de hoofdsom en de looptijd kunnen we het bedrag bepalen dat maandelijks moet worden terugbetaald.
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, de lengte van de lening (aantal periodes) en de geleende hoofdsom. De laatste twee argumenten zijn optioneel, de restwaarde is standaard nul; vooraf 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 de koers gebruiken we de maandelijkse koers (renteperiode), vervolgens berekenen we het aantal perioden (120 voor 10 jaar vermenigvuldigd met 12 maanden) en tot slot geven we de geleende hoofdsom aan. Onze maandelijkse betaling is $ 1.161, 88 over een periode van 10 jaar.
Bereken de jaarlijkse rentevoet
We hebben gezien hoe we de berekening van een maandelijkse betaling voor een hypotheek kunnen instellen. Maar misschien willen we een maximale maandelijkse betaling instellen die we ons kunnen veroorloven die ook het aantal jaren weergeeft waarover we de lening zouden moeten terugbetalen. Daarom willen wij graag de overeenkomstige jaarlijkse rentevoet weten.
Zoals weergegeven in de bovenstaande schermafbeelding, berekenen we eerst het periodetarief (in ons geval maandelijks) en vervolgens het jaarlijkse tarief. De gebruikte formule is RATE, zoals weergegeven in de bovenstaande schermafbeelding. Het is als volgt geschreven:
= RATE (aantal_termijnen, PMT, huidige_waarde; [toekomstige_waarde]; [Type])
De eerste drie argumenten zijn de lengte van de lening (aantal periodes), 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; het termijnargument voor het vooraf beheren van de looptijd (voor één) of aan het einde (voor nul) is ook optioneel. Ten slotte is het schattingsargument optioneel, maar het kan een eerste schatting van de koers geven.
De Excel-formule die wordt gebruikt om de leentarief te berekenen, is:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Opmerking: de bijbehorende 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 het jaarlijkse rentetarief van onze lening te verkrijgen, namelijk 3, 58%. Met andere woorden, om $ 13.000 te lenen over een periode van 13 jaar om $ 960 per maand te betalen, moeten we een lening afsluiten met een jaarlijks maximum van 3, 58%.
Het gebruik van Excel is een geweldige manier om bij te houden wat u verschuldigd bent en met een terugbetalingsschema te komen dat de kosten die u mogelijk verschuldigd bent, minimaliseert.
De lengte van een lening bepalen
We zullen nu zien hoe we de lengte van een lening kunnen bepalen wanneer u de jaarlijkse rentevoet, de geleende hoofdsom en de terug te betalen maandelijkse betaling kent. Met andere woorden, hoe lang moeten we een hypotheek van $ 120.000 terugbetalen met een tarief van 3, 10% en een maandelijkse betaling van $ 1.100 ">
De formule die we zullen gebruiken is NPER, zoals weergegeven in de bovenstaande screenshot, en het is als volgt geschreven:
= NPER (snelheid, PMT, huidige_waarde; [toekomstige_waarde]; [Type])
De eerste drie argumenten zijn de jaarlijkse rente 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. Het termijnargument 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)
Opmerking: de bijbehorende gegevens in de maandelijkse betaling moeten een negatief teken krijgen. Daarom hebben we een minteken voor de formule. De terugbetalingsduur is 127, 97 periodes (in ons geval maanden).
We gebruiken de formule = B5 / 12 = 127, 97 / 12 voor het aantal jaren om de terugbetaling van de lening te voltooien. Met andere woorden, om $ 120.000 te lenen, met een jaarlijks tarief van 3, 10% en om $ 1.100 per maand te betalen, moeten we de looptijden terugbetalen voor 128 maanden of 10 jaar en 8 maanden.
De lening ontbinden
Een lening bestaat uit hoofdsom en rente. De rente wordt berekend voor elke periode. De maandelijkse aflossingen over een periode van 10 jaar geven ons bijvoorbeeld 120 periodes.
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 zijn als volgt onderverdeeld:
= -PPMT (snelheid, num_period, lengte, OG, [resterende]; [term])
De argumenten zijn dezelfde als voor de PMT-formule die al is gezien, behalve "num_period", die wordt toegevoegd om de periode aan te geven waarover de lening moet worden afgebroken, gegeven de hoofdsom en 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; 120, 000)
Het resultaat wordt weergegeven in de screenshot hierboven "Ontleding van leningen" 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 te berekenen voor verschillende periodes zoals de eerste 12 maanden of de eerste 15 maanden.
= -CUMPRINC (snelheid, lengte, OG, begindatum, einddatum, type)
We vinden de argumenten, snelheid, lengte, hoofdsom en term (die verplicht zijn) die we al zagen in het eerste deel met de formule PMT. Maar hier hebben we ook de argumenten "startdatum" en "einddatum" nodig. De "startdatum" geeft het begin van de te analyseren periode aan en de "einddatum" 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 weergegeven in de schermafbeelding "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 in hoofdsom en $ 3.522, 99 aan rente betalen.
Afschrijving van de lening
De voorgaande formules stellen ons in staat om onze planningsperiode per periode te creëren, om te weten hoeveel we maandelijks in hoofdsom en rente zullen betalen, en om te weten hoeveel er nog te betalen is.
Een leenschema opstellen
Om een leenschema op te stellen, gebruiken we de verschillende formules die hierboven zijn besproken en breiden we ze uit over het aantal perioden.
Voer in de kolom eerste periode "1" in als de eerste periode en sleep de cel naar beneden. In ons geval hebben we 120 periodes nodig, aangezien een leningbetaling van 10 jaar vermenigvuldigd met 12 maanden gelijk is aan 120.
De tweede kolom is het maandelijkse bedrag dat we elke maand moeten betalen - dat constant is gedurende het hele leenschema. Om het bedrag te berekenen, voegt u de volgende formule in de cel van onze eerste periode in:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
De derde kolom is de hoofdsom die maandelijks wordt terugbetaald. Voor de 40e periode zullen we bijvoorbeeld in hoofdsom $ 945, 51 terugbetalen op ons totale maandelijkse 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 terugbetaalde hoofdsom te berekenen op ons maandelijkse bedrag om te ontdekken hoeveel rente 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 te betalen bedrag. Na de 40e betaling moeten we bijvoorbeeld $ 83.994, 69 betalen op $ 120.000.
De formule is als volgt:
= $ B $ 3 + CUM.HOOFDSOM (TP; $ B $ 4 * 12; $ B $ 3, 1, A18; 0)
De formule gebruikt een combinatie van hoofdsom gedurende een periode voorafgaand aan de cel met de geleende hoofdsom. Deze periode begint te veranderen wanneer we de cel kopiëren en naar beneden slepen. De onderstaande tabel laat zien dat aan het einde van 120 periodes onze lening wordt terugbetaald.