Visste du at du kan bruke Excel til å beregne dine tilbakebetalinger? Denne artikkelen vil gå deg gjennom alle trinnene som trengs for å gjøre det. (Se også: Lånekalkulatorer: Hvordan de jobber .)

Ved hjelp av Excel kan du få bedre forståelse av boliglånet ditt i tre enkle trinn. Det første trinnet er å bestemme den månedlige betalingen. Den andre er å oppdage renten, og den tredje er å finne låneplanen. For å gjøre det kan du bygge et bord i Excel som vil fortelle deg: De lengste prisene; låneberegningen for varigheten; dekomponere et lån, samt amortisering og beregning for månedlig leie.

Lånsberegning for månedlig leie

Først, la oss se hvordan du skal beregne en månedlig betaling for et boliglån. Med andre ord, ved å bruke den årlige renteintervallet, hovedstol og varighet, kan vi bestemme beløpet som skal tilbakebetales månedlig.

Formelen, som vist på skjermbildet ovenfor, er skrevet som følger:

= - PMT (rate; lengde; present_value; [future_value]; [type])

Minustegnet foran PMT er nødvendig, da formelen returnerer et negativt tall. De tre første argumentene er lånets rente, lengden på lånet (antall perioder) og oppdragsgiver lånt. De to siste argumentene er valgfrie, gjenværende verdi på 0, betales på forhånd (for 1) eller på slutten (for 0), er også valgfri.

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 frekvensen bruker vi tidsrenten, som er månedsfrekvensen, så beregner vi antall perioder (måneder her 120 i 10 år multiplisert med 12 måneder) og til slutt viser vi hovedstol lånt. Vår månedlige betaling vil bli $ 1, 161. 88 over 10 år.

Lånekomputasjon for rentesatser

Vi har sett hvordan du oppretter beregningen av en månedlig betaling for et boliglån. Men vi vil kanskje sette en maksimal månedlig betaling som vi har råd til, og viser også antall år over hvilke vi måtte betale det. Av den grunn vil vi gjerne vite den tilsvarende årlige renten.

Beregning av rentenivået for et lån

Som vist på skjermbildet ovenfor, beregner vi først perioderenten (månedlig i vårt tilfelle) og deretter årsrenten. Formelen som brukes vil være RATE, som vist på skjermbilde over, er det skrevet som følger:

= RATE (Nper; pmt; present_value; [future_value]; [type])

De tre første argumentene er lengden på lånet (antall perioder), og den månedlige betalingen for å tilbakebetale hovedstol lånt. De tre siste argumentene er valgfrie, og restverdien er standard 0, betegnelsen argument for å administrere forfall på forhånd (for 1) eller på slutten (for 0) er også valgfritt, og til slutt er estimeringsargumentet valgfritt, men kan gi et innledende estimat av prisen.

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

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

Merk: Den månedlige betalingen må gis et negativt tegn. Det er derfor et minustegn før formelen. Vår renteperiode er 0. 294%.

Vi bruker formelen

= (1 + B5) er 12-1 ^ = (1 + 0,294%) ^ 12-1 for å få den årlige prisen på lånet vårt til å være 3. 58 %. Med andre ord, for å låne 120 000 $ over 13 år for å betale månedlig 960 $, bør vi forhandle et lån med en årlig 3. 58% maksimal rente. Mortgage Computation for lengden på et lån

Vi vil nå se hvordan du får lengden på et lån når du vet årsrenten, hovedstol lånt og den månedlige betalingen som skal tilbakebetales. Med andre ord, hvor lenge skal vi betale tilbake en $ 120 000 boliglån med en rate på 3. 10% og månedlig betaling på $ 1, 100?

Antall tilbakebetalinger for et lån

Formelen vi skal bruke er NPER, som vist på skjermbildet ovenfor, og det er skrevet som følger:

= NPER (rate; pmt; present_value; [future_value]; [type])

De tre første argumentene er årlig rente på lånet, den månedlige betalingen som trengs for å tilbakebetale lånet, og hovedstol lånt. De to siste argumentene er valgfrie, restverdien er standard 0, termen argumenter som skal betales på forhånd (for 1) eller på slutten (for 0) er også valgfritt.

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

Merk: Tilsvarende data i den månedlige betalingen må gis et negativt tegn. Det er derfor 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 for antall år for å fullføre lånets tilbakebetaling. Med andre ord, for å låne $ 120 000, med en årlig rate på 3,10% og å betale $ 1, 100 månedlig, bør vi betale tilbake forfall i 128 måneder eller 10 år og 8 måneder.

Dekomponering av lånet

En lånebetaling består av to ting, hovedstol og renter. Renten beregnes for hver periode, for eksempel de månedlige avdragene over 10 år, vil gi oss 120 perioder.

Skjermbildet ovenfor viser nedbryting av et lån (en total periode som er 120) ved bruk av PPMT og IPMT formlene. Argumentene til de to formlene er de samme og fordeles som følger:

= - PPMT (rate; num_period; lengde; rektor; [rest]; [terme])

= - INTPER (rate; num_period; lengden, hovedpersonen, [residual], [terme])

Argumentene er de samme som for PMT-formelen sett i første del, med unntak av num_period som er lagt til for å vise perioden over hvilken å bryte ned lånet rektor og interesse for det. La oss ta et eksempel:

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

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

Resultatet er det som vises på skjermbildet "Lånedbrytning" i perioden som er analysert som er "1", så den første perioden , eller den første måneden.For dette betaler vi $ 1161. 88, fordelt på $ 856, 20 hovedstol og $ 305. 68 interesse.

Excel Loan Computation

Nå er det også mulig å beregne hovedstol og renteavdrag i flere perioder, for eksempel de første 12 månedene eller de første 15 månedene.

= - CUMPRMT (rate; lengde; rektor; start_date; end_date; type)

Vi finner argumenter, hastighet, lengde, hovedstol og term (som er obligatorisk) som vi allerede så i første del med formelen PMT. Men her trenger vi også start_date og end_date argumenter. Den første indikerer begynnelsen av perioden som skal analyseres og den andre enden. La oss ta et eksempel:

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

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

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

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

Resultatet er en som vises i skjermbildet "Cumul 1ste år", så de analyserte perioder varierer fra 1 til 12, av den første perioden (første måned) til tolvte (12. måned). Over et år betalte vi $ 10 419, 55 hovedstol og $ 3 522. 99 Renter.

Amortisering av lånet> De tidligere formlene gir oss mulighet til å lage vår tidsperiode etter periode, hvor mye vi skal betale månedlig i hovedstol og interesse, og hvor mye som gjenstår å betale.

Opprett et låneskema i Excel

For å lage en låneplan, vil vi bruke forskjellige formler diskutert ovenfor og utvide dem over antall perioder.

I den første perioden kolonnen, skriv inn "1" som første periode, og dra deretter cellen ned. I vårt tilfelle trenger vi 120 perioder siden en 10-årig lånebetaling multiplisert med 12 måneder = 120.

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

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

Den tredje kolonnen er rektor som vil bli tilbakebetalt månedlig. For eksempel i 40-tallet vil vi betale tilbake $ 945. 51 som hovedstol på vårt månedlige totalsum på $ 1, 161. 88. 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 interessen, som vi beregner hovedstolen tilbakebetalt på vårt månedlige beløp for å oppdage hvordan mye interesse skal betales ved hjelp av formelen:

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

Den femte kolonnen inneholder beløpet igjen å betale. For eksempel, etter den 40. betalingen må vi betale $ 83, 994. 69 på $ 120, 000. Formelen er som følger:

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

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

Formelen bruker en kombinasjon av rektor i en periode fremover med cellen som inneholder hovedstol lånt. Denne perioden begynner å endres når vi kopierer og drar cellen ned.Skjermbildet under viser at ved utgangen av 120 perioder er lånet tilbakebetalt.