Planlegg tilbakebetaling av lån med Excel-formler

Tilbakebetaling av lån er handlingen med å betale tilbake penger som tidligere er lånt fra en utlåner, vanligvis gjennom en rekke periodiske betalinger som inkluderer hovedstol pluss renter. Visste du at du kan bruke programvaren Excel til å beregne lånets nedbetalinger?

Denne artikkelen er en trinnvis veiledning for å sette opp låneberegninger.

Viktige takeaways

• Bruk Excel til å få tak i pantelånet ditt ved å bestemme den månedlige betalingen, renten og låneplanen din.
• Du kan ta en mer dyptgående titt på fordelingen av et lån med Excel og lage en nedbetalingsplan som fungerer for deg.
• Det er beregninger tilgjengelig for hvert trinn som du kan finpusse for å møte dine spesifikke behov.
• Å bryte ned og undersøke lånet ditt trinnvis kan gjøre at tilbakebetalingsprosessen føles mindre overveldende og mer håndterbar.

Forstå pantelånet ditt

Ved hjelp av Excel kan du få en bedre forståelse av pantelånet ditt i tre enkle trinn. Det første trinnet bestemmer den månedlige betalingen. Det andre trinnet beregner renten, og det tredje trinnet bestemmer låneplanen.

Du kan bygge en tabell i Excel som forteller deg renten, låneberegningen for lånets varighet, nedbrytningen av lånet, amortiseringen og den månedlige betalingen.

Beregn den månedlige betalingen

Først, her er hvordan du beregner den månedlige betalingen for et pantelån. Ved å bruke den årlige renten, hovedstolen og varigheten, kan vi bestemme beløpet som skal tilbakebetales månedlig.

Formelen, som vist på skjermdumpen over, er skrevet på følgende måte:

= -PMT (hastighet, lengde; present_value; [fremtidig_verdi]; [type])

Minustegnet foran PMT er nødvendig ettersom formelen returnerer et negativt tall. De tre første argumentene er lånets rente, lånets lengde (antall perioder) og lånets hovedstol. De to siste argumentene er valgfrie, restverdien er null; betales på forhånd (for en) eller på slutten (for null), er også valgfritt.

Excel-formelen som brukes til å beregne den månedlige betalingen av 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 bruker vi den månedlige satsen (rate of rate), deretter beregner vi antall perioder (120 i 10 år multiplisert med 12 måneder), og til slutt angir vi hovedstolen som er lånt. Vår månedlige betaling vil være $ 1.161, 88 over 10 år.

Beregn den årlige rentesatsen

Vi har sett hvordan vi kan sette opp beregningen av en månedlig betaling for et pantelån. Men det kan være lurt å sette en maksimal månedlig betaling som vi har råd til, som også viser antall år som vi måtte betale tilbake lånet over. Av den grunn vil vi gjerne vite den tilsvarende årlige renten.

Som vist på skjermdumpen over, beregner vi først periodesatsen (månedlig, i vårt tilfelle), og deretter den årlige satsen. Formelen som brukes vil være RATE, som vist på skjermdumpen over. Det skrives som følger:

= RATE (Antall_innbet; betalings; present_value; [fremtidig_verdi]; [type])

De tre første argumentene er lengden på lånet (antall perioder), den månedlige utbetalingen for å tilbakebetale lånet og lånets hovedstol. De tre siste argumentene er valgfrie, og restverdien er null; begrepet argument for å håndtere forfall på forhånd (for en) eller på slutten (for null) er også valgfritt. Endelig er estimeringsargumentet valgfritt, men kan gi et innledende estimat av hastigheten.

Excel-formelen som brukes til å beregne utlånsrenten er:

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

Merk: de tilsvarende dataene i den månedlige betalingen må gis et negativt tegn. Dette er grunnen til at det er et minustegn før formelen. Satsperioden er 0, 294%.

Vi bruker formelen = (1 + B5) er 12-1 ^ = (1 + 0, 294%) ^ 12-1 for å oppnå den årlige renten på lånet vårt, som er 3, 58%. Med andre ord, for å låne $ 120 000 over 13 år for å betale $ 960 månedlig, bør vi forhandle om et lån til en årlig maksimumssats på 3, 58%.

Å bruke Excel er en flott måte å følge med på hva du skylder og komme med en plan for tilbakebetaling som minimerer eventuelle gebyrer du måtte ende opp med.

Bestemme lengden på et lån

Vi vil nå se hvordan du kan bestemme lengden på et lån når du vet årsrenten, lånet og den månedlige utbetalingen som skal tilbakebetales. Med andre ord, hvor lenge vil vi trenge å tilbakebetale et lån på $ 120.000 med en rente på 3, 10% og en månedlig betaling på $ 1100 ">

Formelen vi vil bruke er NPER, som vist på skjermdumpen over, og den er skrevet på følgende måte:

= NPER (hastighet; betalings; present_value; [fremtidig_verdi]; [type])

De tre første argumentene er den årlige renten for lånet, den månedlige utbetalingen som trengs for å tilbakebetale lånet, og hovedstolen som er lånt. De to siste argumentene er valgfrie, restverdien er null. Begrepet argument som skal betales på forhånd (for en) eller på slutten (for null) er også valgfritt.

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

Merk: de tilsvarende dataene i den månedlige betalingen må gis et negativt tegn. Dette er grunnen til at vi har et minustegn før formelen. Refusjonslengden er 127, 97 perioder (måneder i vårt tilfelle).

Vi vil bruke formelen = B5 / 12 = 127, 97 / 12 i antall år for å fullføre tilbakebetaling av lån. Med andre ord, for å låne $ 120 000, med en årlig rente på 3, 10% og for å betale $ 1100 månedlig, bør vi betale tilbake forfall i 128 måneder eller 10 år og 8 måneder.

Nedbryting av lånet

En lånebetaling er sammensatt av hovedstol og renter. Renten beregnes for hver periode - for eksempel vil månedlige tilbakebetalinger over 10 år gi oss 120 perioder.

Tabellen over viser fordelingen av et lån (en total periode lik 120) ved å bruke PPMT- og IPMT-formlene. Argumentene for de to formlene er de samme og er oppdelt på følgende måte:

= -PPMT (rate; num_period, lengde; stolen; [rest]; [sikt])

Argumentene er de samme som for PMT-formelen som allerede er sett, bortsett fra "num_period", som er lagt til for å vise perioden for å fordele lånet gitt 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; 120, 000)

Resultatet vises på skjermdumpen over "Loan Decomposition" over den analyserte perioden, som er "en;" det vil si den første perioden eller den første måneden. Vi betaler $ 1.161, 88 fordelt på $ 856, 20 hovedstol og $ 305, 68 renter.

Låneberegning i Excel

Det er også mulig å beregne hovedstol- og rentetilskudd i flere perioder, for eksempel de første 12 månedene eller de første 15 månedene.

= -CUMPRINC (hastighet, lengde; rektor, startdato, sluttdato; type)

Vi finner de argumenter, hastighet, lengde, hovedstol og begrep (som er obligatoriske) som vi allerede så i den første delen med formelen PMT. Men her trenger vi argumentene "start_dato" og "sluttdato" også. "Startdatoen" indikerer begynnelsen på perioden som skal analyseres, og "sluttdatoen" indikerer slutten på perioden som skal analyseres.

Her er et eksempel:

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

Resultatet vises på skjermdumpen "Cumul 1st year", slik at de analyserte periodene varierer fra en til 12 av den første perioden (første måned) til den tolvte (12. måneden). I løpet av et år ville vi betale $ 10.419, 55 i hovedstol og $ 3.522.99 i renter.

Amortisering av lånet

De tidligere formlene tillater oss å lage tidsplanen vår for periode, å vite hvor mye vi skal betale månedlig i hovedstol og renter, og å vite hvor mye som er igjen å betale.

Opprette en låneplan

For å lage en låneplan, bruker vi de forskjellige formlene diskutert over og utvider dem over antall perioder.

I den første periodekolonnen skriver du inn "1" som den første perioden og drar deretter cellen ned. I vårt tilfelle trenger vi 120 perioder siden en 10-årig lånebetaling multiplisert med 12 måneder tilsvarer 120.

Den andre kolonnen er det månedlige beløpet vi trenger å betale hver måned - som er konstant over hele låneplanen. For å beregne mengden, sett inn følgende formel i cellen i vår første periode:

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

Den tredje kolonnen er hovedstol som vil bli tilbakebetalt månedlig. For eksempel betaler vi for den 40. perioden 945, 51 dollar i hovedstol på det totale månedlige beløpet på 1 161, 88 dollar.

For å beregne hovedbeløpet innløst bruker vi følgende formel:

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

Den fjerde kolonnen er renten, som vi bruker formelen for å beregne hovedstolen som er tilbakebetalt på vårt månedlige beløp for å finne ut hvor mye renter som skal betales:

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

Den femte kolonnen inneholder beløpet som er igjen å betale. Etter den 40. betalingen må vi for eksempel betale $ 83.994, 69 for $ 120 000.

Formelen er som følger:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

Formelen bruker en kombinasjon av hovedstol under en periode foran cellen som inneholder hovedstolen lånt. Denne perioden begynner å endre seg når vi kopierer og drar cellen ned. Tabellen nedenfor viser at ved utløpet av 120 perioder er lånet vårt nedbetalt.