Vidste du, at du kan bruge Excel til at beregne dine tilbagebetalinger? Denne artikel vil gå dig igennem alle de trin, der er nødvendige for at gøre det. (Se også: Lånekalkulatorer: Hvordan de virker .)

Ved hjælp af Excel kan du få en bedre forståelse af dit pant i tre enkle trin. Det første skridt er at bestemme den månedlige betaling. Den anden er at opdage renten, og den tredje er at finde låneskemaet. For at gøre det, kan du opbygge et bord i Excel, der fortæller dig: De mindste priser; låneberegningen for varigheden nedlæggelse af et lån samt afskrivninger og beregning for månedlig leje.

Lånberegning for månedlig leje

Lad os først se beregningen af ​​en månedlig betaling for et pant. Med andre ord, ved hjælp af den årlige renteinteresse, hovedstol og varighed, 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_value; [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 hovedstol lånet. De sidste to argumenter er valgfrie, restværdien er standard til 0, der skal betales på forhånd (for 1) eller i slutningen (for 0), også valgfri.

Excel-formlen til beregning af den månedlige udbetaling 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 frekvensen bruger vi perioder, som er den månedlige sats, så beregner vi Antallet af perioder (måneder her 120 i 10 år multipliceret med 12 måneder) og endelig angiver vi hovedstol lånet. Vores månedlige betaling vil være $ 1, 161. 88 over 10 år.

Realkreditberegning for rentesatser

Vi har set hvordan man opretter beregningen af ​​en månedlig betaling for et pant. Men vi ønsker måske at fastsætte en maksimal månedlig betaling, som vi har råd til, og viser også det antal år, over hvilke vi skulle tilbagebetale det. Derfor vil vi gerne vide den tilsvarende årlige rente.

Beregning af rentesatsen for et lån

Som vist på skærmbilledet ovenfor beregner vi først den første satsen (månedligt i vores tilfælde) og derefter årlig satsen. Den anvendte formel vil være RATE, som vist på skærmbillede ovenfor, er den skrevet som følger:

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

De første tre argumenter er længden af lånet (antal perioder), og den månedlige betaling for at tilbagebetale hovedstol lånet. De sidste tre argumenter er valgfrie, og restværdien er som standard 0, betegnelsen argument for styring af forfaldstidspunktet (for 1) eller i slutningen (for 0) er også valgfrit, og endelig er estimeringsargumentet valgfrit, men kan give et indledende skøn over satsen.

Excel-formlen til beregning af 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. Det er derfor et minustegn før formlen. Vores renteperiode er 0. 294%.

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

Mortgage Computation for Lånets Længde

Vi vil nu se, hvordan du får længden af ​​et lån, når du kender årsrenten, hovedstol lånet og den månedlige betaling, der skal tilbagebetales. Med andre ord, hvor længe skal vi tilbagebetale en $ 120.000 pant med en sats på 3. 10% og månedlig betaling på $ 1, 100?

Antal tilbagebetalinger til et lån

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

= NPER (rate; pmt; nut_value; [future_value]; [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 hovedstol lånet. De sidste to argumenter er valgfrie, restværdien er standard til 0, idet betegnelsen forudbetalt (for 1) eller i slutningen (for 0) også er 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. Refusionslængden er 127. 97 perioder (måneder i vores tilfælde).

Vi vil bruge formlen = B5 / 12 = 127. 97/12 for antallet af år for at afslutte lånets tilbagebetaling. Med andre ord at låne $ 120.000 med en årlig rente på 3,10% og at betale $ 1, 100 månedligt, skal vi tilbagebetale løbetider i 128 måneder eller 10 år og 8 måneder.

Dekomponering af lånet

En lånegodtgørelse består af to ting, hovedstol og renter. Renterne beregnes for hver periode, for eksempel de månedlige tilbagebetalinger over 10 år, vil give os 120 perioder.

Skærmbilledet ovenfor viser opdeling 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 er opdelt som følger:

= - PPMT (sats; num_period; længde; hovedstol; [rest]; [terme])

= - INTPER (rate; num_period; længden; hovedprincippet [residual]; [terme])

Argumenterne er de samme som for PMT-formlen set i første del bortset fra num_period, der er tilføjet for at vise den periode, over hvilken lånet skal nedbrydes hovedstol og interesse for det. Lad os tage 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, der vises på skærmbilledet "Lånedbrydning" i den analyserede periode, som er "1", så den første periode , eller den første måned.For dette betaler vi 1161 USD. 88, fordelt på $ 856, 20 principal og $ 305. 68 interesse.

Excel Loan Computation

Nu er det også muligt at beregne hovedstol og renteafdrag i flere perioder, som de første 12 måneder eller de første 15 måneder.

= - CUMPRMT (sats; længde; principal; start_date; end_date; type)

Vi finder argumenterne, sats, længde, hovedstol og term (som er obligatoriske), som vi allerede har set i første del med formlen PMT. Men her har vi også start-date og end-date argumenter. Den første indikerer begyndelsen af ​​den periode, der skal analyseres, og den anden ende. Lad os tage et eksempel:

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

= - CUMPRINC 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 et vist i skærmbilledet "Cumul 1: a år", så de analyserede perioder spænder fra 1 til 12, af den første periode (første måned) til den tolvte (12. måned). I løbet af et år ville vi betale $ 10 419, 55 Principal og $ 3 522. 99 Renter.

Amortisering af lånet

De forudgående formler tillader os at oprette vores tidsplan efter periode, hvor meget vi betaler månedligt i hovedstol og renter, og hvor meget der skal betales.

Opret et låneskema i Excel

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

I den første periode kolonne skal du blot indtaste "1" som den første periode, og træk derefter cellen ned. I vores tilfælde har vi brug for 120 perioder siden en 10-årig lånebetaling multipliceret med 12 måneder = 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 det, indsæt følgende formel i cellen i vores første periode:

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ ) -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 hovedsagen på vores månedlige samlede beløb på $ 1, 161. 88. For at beregne det indbetalte 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 den interesse, som vi beregner hovedstolen tilbagebetalt på vores månedlige beløb for at opdage, hvordan meget interesse skal udbetales ved hjælp af formlen:

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

Den femte kolonne indeholder mængden tilbage til at betale. For eksempel efter 40. betalingen skal vi betale $ 83, 994. 69 på $ 120.000. Formlen 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)

Formlen bruger en kombination af hovedstol i en periode foran med cellen indeholdende hovedstol lånet. Denne periode begynder at ændre sig, når vi kopierer og trækker cellen ned.Skærmbilledet nedenfor viser, at i slutningen af ​​120 perioder er vores lån tilbagebetalt.