Planlæg tilbagebetalinger af lån med Excel -formler

Tilbagebetaling af lån er handlingen med at betale tilbage 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 lånebetalinger?

Denne artikel er en trin-for-trin guide til opsætning af låneberegninger.

Forstå dit realkreditlån

Ved hjælp af Excel kan du få en bedre forståelse af din pant 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 bygge et bord i Excel det fortæller dig renten, låneberegningen for lånets løbetid, nedbrydningen af ​​lånet, amortiseringen og den månedlige betaling.

Beregn den månedlige betaling

Først, her er hvordan beregne den månedlige betaling for et realkreditlå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ærmbilledet ovenfor, er skrevet som følger:

= -PMT (sats; længde; nuværende værdi; [fremtidig værdi]; [type])

Minustegnet foran PMT er nødvendigt, da formlen returnerer et negativt tal. De tre første argumenter er lånets rente, lånets længde (antal perioder) og den lånte hovedstol. De to sidste argumenter er valgfri, restværdien er som standard nul; betales forud (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 sats (renteperiode), derefter beregner vi antallet af perioder (120 i 10 år ganget med 12 måneder), og endelig angiver vi rektor Lånte. Vores månedlige betaling vil være $ 1.161,88 over 10 år.

Beregn den årlige rente

Vi har set, hvordan man opretter beregningen af ​​en månedlig betaling for et realkreditlån. Men vi vil muligvis angive en maksimal månedlig betaling, som vi har råd til, og som også viser det antal år, vi skulle betale lånet tilbage over. Af den grund vil vi gerne vide den tilsvarende årlige rente.

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

= RATE (Nper; pmt; nuværende værdi; [fremtidig værdi]; [type])

De tre første argumenter er længden af lån (antal perioder), den månedlige betaling for at tilbagebetale lånet og den lånte hovedstol. De sidste tre argumenter er valgfri, og restværdien er som standard nul; betegnelsen argument for at styre løbetiden på forhånd (for en) eller i slutningen (for nul) er også valgfri. Endelig er estimatargumentet 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 at opnå den årlige rente 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 maksimalrente på 3,58%.

Bestemmelse af lånets længde

Vi vil nu se, hvordan vi bestemmer længden af ​​et lån, når du kender den årlige rente, den lånte hovedstol og den månedlige betaling, der skal tilbagebetales. Med andre ord, hvor lang tid har vi brug for at tilbagebetale et pant på $ 120.000 med en rente på 3,10% og en månedlig betaling på $ 1.100?

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

= NPER (sats; pmt; nuværende værdi; [fremtidig værdi]; [type])

De tre første argumenter er lånets årlige rente, den månedlige betaling, der er nødvendig for at tilbagebetale lånet, og den lånte hovedstol. De to sidste argumenter er valgfrie, restvæ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)

Vi vil bruge formlen = B5 / 12 = 127,97 / 12 i antallet af år for at fuldføre tilbagebetalingen af ​​lånet. Med andre ord, for at låne $ 120.000 med en årlig rente på 3,10% og betale $ 1.100 månedligt, bør vi tilbagebetale løbetider i 128 måneder eller 10 år og otte måneder.

Nedbrydning af lånet

En lånebetaling består af hovedstol og renter. Renterne beregnes for hver periode - for eksempel vil de månedlige tilbagebetalinger over 10 år give os 120 perioder.

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

= -PPMT (sats; num_periode; længde; hovedstol; [residual]; [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 nedbrydes i betragtning af hovedstolen 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 er vist på skærmbilledet ovenfor "Lånedbrydning" i løbet af den analyserede periode, hvilket er "et;" 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 hovedstolen og tilbagebetaling af renter i flere perioder, f.eks. De første 12 måneder eller de første 15 måneder.

= -CUMPRINC (sats; længde; rektor; start dato; slutdato; type)

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

Her er et eksempel:

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

Resultatet vises på 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 ville vi betale $ 10.419,55 i hovedstol og $ 3.522,99 i renter.

Amortisering af lånet

De tidligere formler giver os mulighed for at oprette vores tidsplan periode for periode, at vide, hvor meget vi vil 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, der er diskuteret ovenfor, og udvider dem over antallet af perioder.

I den første periodekolonne 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, siden en 10-årig lånebetaling ganget med 12 måneder er lig med 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; B4*12; B3) = -PMT ((1+3,10%)^(1/12) -1; 10*12; 120000)

Den tredje kolonne er hovedstolen, der vil blive tilbagebetalt 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 hovedstolen tilbagebetalt på vores månedlige beløb for at opdage, hvor mange renter 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+CUMPRINC (TP; $ B $ 4*12; $ B $ 3; 1; A18; 0)

Formlen bruger en kombination af hovedstol i 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. Nedenstående tabel viser, at ved udgangen af ​​120 perioder tilbagebetales vores lån.