Planlæg lånebetalinger med Excel-formler

Tilbagebetaling af lån er handlingen med at tilbagebetale penge, der tidligere er lånt fra en långiver, typisk gennem en række periodiske betalinger, der inkluderer hovedstol plus renter. Vidste du, at du kan bruge softwareprogrammet Excel til at beregne dine afdrag på lån?

Denne artikel er en trinvis vejledning til opsætning af låneberegninger.

Key takeaways

• Brug Excel til at få et håndtag på dit prioritetslån ved at bestemme din månedlige betaling, din rente og din låneplan.
• Du kan tage et mere dybtgående kig på fordelingen af ​​et lån med Excel og oprette en tilbagebetalingsplan, der fungerer for dig.
• Der er tilgængelige beregninger for hvert trin, som du kan finjustere for at imødekomme dine specifikke behov.
• Nedbrydning og undersøgelse af dit lån trin for trin kan gøre tilbagebetalingsprocessen føles mindre overvældende og mere håndterbar.

Forstå dit pant

Ved hjælp af Excel kan du få en bedre forståelse af dit prioritetslån i tre enkle trin. Det første trin bestemmer den månedlige betaling. Det andet trin beregner renten, og det tredje trin bestemmer låneplanen.

Du kan opbygge en tabel i Excel, der fortæller dig renten, låneberegningen for lånets varighed, lånets nedbrydning, amortisering og den månedlige betaling.

Beregn den månedlige betaling

Først her, hvordan man beregner den månedlige betaling for et prioritetslån. Ved hjælp af den årlige rente, hovedstolen og varigheden kan vi bestemme det beløb, der skal tilbagebetales månedligt.

Formlen, som vist på skærmbillede ovenfor, er skrevet som følger:

= -PMT (hastighed, længde present_value [fremtidig_værdi] [type])

Minustegnet foran PMT er nødvendigt, da formlen returnerer et negativt tal. De første tre argumenter er lånets rente, lånets længde (antal perioder) og den lånte hovedstol. De sidste to argumenter er valgfri, den resterende værdi er som standard nul; betales på forhånd (for en) eller i slutningen (for nul), er også valgfri.

Excel-formlen, der bruges til at beregne den månedlige betaling af lånet, er:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Forklaring: For satsen bruger vi den månedlige rente (renteperiode), derefter beregner vi antallet af perioder (120 i 10 år ganget med 12 måneder) og til sidst angiver vi den lånte hovedstol. Vores månedlige betaling vil være $ 1.161, 88 over 10 år.

Beregn den årlige rentesats

Vi har set, hvordan vi indstiller beregningen af ​​en månedlig betaling for et prioritetslån. Men vi ønsker måske at indstille en maksimal månedlig betaling, som vi har råd til, og som også viser antallet af år, som vi bliver nødt til at tilbagebetale lånet over. Derfor vil vi gerne vide den tilsvarende årlige rente.

Som vist på skærmbillede ovenfor beregner vi først periodens sats (månedligt i vores tilfælde) og derefter den årlige sats. Den anvendte formel vil være RATE, som vist på skærmbilledet ovenfor. Det skrives som følger:

= RATE (nper, PMT, present_value [fremtidig_værdi] [type])

De første tre argumenter er lånets længde (antal perioder), den månedlige betaling for at tilbagebetale lånet og den lånte hovedstol. De sidste tre argumenter er valgfri, og den resterende værdi er som standard nul; udtrykket argument for styring af forfalden på forhånd (for en) eller i slutningen (for nul) er også valgfrit. Endelig er estimeringsargumentet valgfrit, men kan give et indledende estimat af satsen.

Excel-formlen, der bruges til at beregne udlånsrenten, er:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Bemærk: De tilsvarende data i den månedlige betaling skal have et negativt tegn. Derfor er der et minustegn før formlen. Satsperioden er 0, 294%.

Vi bruger formlen = (1 + B5) er 12-1 ^ = (1 + 0, 294%) ^ 12-1 for at opnå den årlige sats på vores lån, som er 3, 58%. Med andre ord, for at låne $ 120.000 over 13 år for at betale $ 960 månedligt, bør vi forhandle om et lån til en årlig maksimumssats på 3, 58%.

Brug af Excel er en fantastisk måde at holde styr på, hvad du skylder, og komme med en tidsplan for tilbagebetaling, der minimerer de gebyrer, du muligvis ender med.

Bestemmelse af lånets længde

Vi vil nu se, hvordan du bestemmer lånets længde, når du kender den årlige rente, den lånte hovedstol og den månedlige betaling, der skal tilbagebetales. Med andre ord, hvor længe har vi brug for at tilbagebetale et lån på $ 120.000 til en rente på 3, 10% og en månedlig betaling på $ 1.100 ">

Den formel, vi vil bruge, er NPER, som vist på skærmbilledet ovenfor, og den er skrevet som følger:

= NPER (hastighed, PMT, present_value [fremtidig_værdi] [type])

De første tre argumenter er den årlige rente for lånet, den månedlige betaling, der er nødvendig for at tilbagebetale lånet, og den lånte hovedstol. De sidste to argumenter er valgfri, den resterende værdi er som standard nul. Udtrykket argument, der skal betales på forhånd (for en) eller i slutningen (for nul), er også valgfrit.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Bemærk: De tilsvarende data i den månedlige betaling skal have et negativt tegn. Derfor har vi et minustegn før formlen. Refusionens længde er 127, 97 perioder (måneder i vores tilfælde).

Vi vil bruge formlen = B5 / 12 = 127, 97 / 12 i antallet af år til at gennemføre tilbagebetaling af lån. Med andre ord, for at låne $ 120.000, med en årlig rente på 3, 10% og til at betale $ 1.100 hver måned, skal vi tilbagebetale løbetider i 128 måneder eller 10 år og 8 måneder.

Nedbrydning af lånet

En lånebetaling er sammensat af hovedstol og renter. Renterne beregnes for hver periode - for eksempel giver de månedlige tilbagebetalinger over 10 år 120 perioder.

Tabellen ovenfor viser fordelingen af ​​et lån (en samlet periode svarende til 120) ved hjælp af PPMT- og IPMT-formlerne. Argumenterne for de to formler er de samme og opdeles som følger:

= -PPMT (hastighed, num_period, længde ordregiver [rest] [term])

Argumenterne er de samme som for PMT-formlen, der allerede er set, bortset fra "num_period", som tilføjes for at vise den periode, hvor lånet skal fordeles i betragtning af hovedstol og renter. Her er et eksempel:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

Resultatet vises på skærmbilledet ovenfor "Nedbrydning af lån" i den analyserede periode, der er "en;" det vil sige den første periode eller den første måned. Vi betaler $ 1.161, 88 fordelt på $ 856, 20 hovedstol og $ 305, 68 renter.

Låneberegning i Excel

Det er også muligt at beregne hovedstol og renteregulering i flere perioder, såsom de første 12 måneder eller de første 15 måneder.

= -CUMPRINC (hastighed, længde ordregiver startdato, slutdato; type)

Vi finder de argumenter, hastighed, længde, hovedstol og udtryk (som er obligatoriske), som vi allerede har set i den første del med formlen PMT. Men her har vi brug for argumenterne "start_dato" og "slut_dato". "Startdato" angiver begyndelsen på den periode, der skal analyseres, og "slutdato" angiver slutningen på den periode, der skal analyseres.

Her er et eksempel:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

Resultatet vises i skærmbilledet "Cumul 1. år", så de analyserede perioder spænder fra en til 12 i den første periode (første måned) til den tolvte (12. måned). Over et år betalte vi $ 10.419, 55 i hovedstol og $ 3.522.99 i renter.

Amortisering af lånet

De forudgående formler giver os mulighed for at oprette vores tidsplan periode for periode, at vide, hvor meget vi skal betale månedligt i hovedstol og renter, og at vide, hvor meget der er tilbage at betale.

Oprettelse af en låneplan

For at oprette en låneplan, bruger vi de forskellige formler diskuteret ovenfor og udvider dem over antallet af perioder.

I den første periode-kolonne skal du indtaste "1" som den første periode og derefter trække cellen ned. I vores tilfælde har vi brug for 120 perioder, da en 10-årig lånebetaling ganget med 12 måneder svarer til 120.

Den anden kolonne er det månedlige beløb, vi skal betale hver måned — hvilket er konstant over hele låneplanen. For at beregne mængden skal du indsætte følgende formel i cellen i vores første periode:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Den tredje kolonne er den hovedstol, der tilbagebetales månedligt. For eksempel vil vi i den 40. periode tilbagebetale $ 945, 51 i hovedstol på vores månedlige samlede beløb på $ 1.161, 88.

For at beregne det indløste hovedbeløb bruger vi følgende formel:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Den fjerde kolonne er renterne, som vi bruger formlen til at beregne den hovedstol, der er tilbagebetalt på vores månedlige beløb for at finde ud af, hvor meget rente der skal betales:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Den femte kolonne indeholder det beløb, der er tilbage at betale. For eksempel, efter den 40. betaling, bliver vi nødt til at betale $ 83.994, 69 på $ 120.000.

Formlen er som følger:

= $ B $ 3 + AKKUM.HOVEDSTOL (TP; $ B $ 4 * 12; $ B $ 3; 1, A18; 0)

Formlen bruger en kombination af hovedstol under en periode forud for cellen, der indeholder den lånte hovedstol. Denne periode begynder at ændre sig, når vi kopierer og trækker cellen ned. Tabellen nedenfor viser, at vores lån tilbagebetales ved udgangen af ​​120 perioder.