Visste du att du kan använda Excel för att beräkna dina återbetalningar? Denna artikel kommer att gå igenom alla steg som behövs för att göra det. (Se även: Lånräknare: Hur de arbetar .)

Med Excel kan du få en bättre förståelse för ditt lån i tre enkla steg. Det första steget är att bestämma månadsbetalningen. Den andra är att upptäcka räntan, och den tredje är att hitta lånet schemat. För att göra det kan du bygga ett bord i Excel som kommer att berätta: De lägsta priserna; låneberäkningen för varaktigheten upplösning av ett lån samt avskrivningar och beräkning för månadshyra.

Lånberäkning för månadshyra

Först ska vi se hur man genomför beräkningen av en månadsbetalning för en inteckning. Med andra ord, med hjälp av den årliga interna räntan, huvudmannen och varaktigheten, kan vi bestämma det belopp som ska återbetalas varje månad.

Formeln, som visas på skärmbilden ovan, är skrivet enligt följande:

= - PMT (hastighet, längd, nutidvärde; [future_value]; [typ])

Minustecknet framför PMT är nödvändigt, eftersom formeln returnerar ett negativt tal. De tre första argumenten är lånets ränta, lånets längd (antal perioder) och huvudansvarig lånad. De två sista argumenten är valfria, restvärdet är 0, betalas i förskott (för 1) eller i slutet (för 0), det är också valfritt.

Excel-formel som används för att beräkna den månatliga betalningen av lånet är:

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

Förklaring: För frekvensen använder vi tidsperioden, vilken är månadsräntan, då beräknar vi Antalet perioder (månader här 120 i 10 år multiplicerat med 12 månader) och slutligen anger vi låntagaren. Vår månadsbetalning blir $ 1, 161. 88 över 10 år.

Hypoteksberäkning för räntekostnader

Vi har sett hur man ställer in beräkningen av en månadsbetalning för en inteckning. Men vi kanske vill ställa in en maximal månadsbetalning som vi har råd att också visar hur många år vi skulle behöva betala tillbaka. Därför vill vi veta den motsvarande årliga räntan.

Beräkning av räntesatsen för ett lån

Som visas i skärmbilden ovan beräknar vi först perioder (månatligen i vårt fall) och därefter årsräntan. Den använda formeln kommer att vara RATE, som visas i skärmbilden ovan, skrivs den enligt följande:

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

De första tre argumenten är längden på lånet (antal perioder) och den månatliga betalningen för att återbetala huvudlånen. De tre sista argumenten är valfria, och restvärdet är 0, termen argument för förvaltning av förfallodagen i förväg (för 1) eller i slutet (för 0) är också valfritt, och slutligen är estimeringsargumentet valfritt, men kan ge en initial uppskattning av kursen.

Excel-formel som används för att beräkna utlåningsfrekvensen är:

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

Obs: Den månatliga betalningen måste ges ett negativt tecken. Det är därför ett minustecken före formeln. Vår ränteperiod är 0. 294%.

Vi använder formeln = (1 + B5) är 12-1 ^ = (1 + 0,294%) ^ 12-1 för att få den årliga kursen på vårt lån att vara 3. 58 %. Med andra ord, för att låna 120 000 $ över 13 år för att betala månatligen 960 $, borde vi förhandla om ett lån med en årlig maximal skattesats på 3. 58%.

Hypoteksberäkning för längden på ett lån

Vi kommer nu att se hur man får lånets längd när man vet årsräntan, låntagaren och den månatliga betalningen som ska återbetalas. Med andra ord, hur länge kommer vi att behöva betala tillbaka en $ 120 000 hypotekslån med en hastighet på 3. 10% och månadsbetalning på $ 1, 100?

Antal återbetalningar för ett lån

Formeln vi ska använda är NPER, som visas i skärmbilden ovan, och den är skriven enligt följande:

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

De tre första argumenten är lånets årliga ränta, den månatliga betalningen som behövs för att återbetala lånet och den upplånade låntagaren. De två sista argumenten är valfria, restvärdet är 0, termen betala på förskott (för 1) eller i slutet (för 0) är också valfritt.

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

Obs: Motsvarande uppgifter i månadsbetalningen måste ges ett negativt tecken. Det är därför vi har ett minustecken före formeln. Återbetalningslängden är 127. 97 perioder (månader i vårt fall).

Vi använder formeln = B5 / 12 = 127. 97/12 för antal år för att slutföra lånets återbetalning. Med andra ord, för att låna $ 120 000 med en årlig skattesats på 3,10% och att betala $ 1, 100 månad, borde vi betala löptider i 128 månader eller 10 år och 8 månader.

Dekomponering av lånet

En lånebetalning består av två saker, huvudmannen och räntan. Räntan beräknas för varje period, till exempel de månatliga återbetalningarna över 10 år, ger oss 120 perioder.

Skärmbilden ovan visar uppdelningen av ett lån (en total period som motsvarar 120), med hjälp av PPMT och IPMT-formlerna. Argumenten för de två formlerna är desamma och är uppdelade enligt följande:

= - PPMT (ränta; num_period; längd; rektor; [rest]; [terme])

= - INTPER (hastighet; num_period; längden, huvudmannen, [kvarvarande]; [terme])

Argumenten är desamma som för PMT-formeln som ses i första delen, förutom num_period som läggs till för att visa den period över vilken man ska bryta ner lånet huvudmannen och intresset för det. Låt oss ta ett exempel:

= - 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 är det som visas i skärmbilden "Låneupplösning" under den analyserade perioden som är "1" så den första perioden , eller den första månaden.För detta betalar vi 1161 USD. 88, uppdelad i $ 856, 20 principal och $ 305. 68 intresse.

Excel Loan Computation

Nu är det också möjligt att beräkna huvud- och räntebetalning under flera perioder, till exempel de första 12 månaderna eller de första 15 månaderna.

= - CUMPRMT (betyg; längd; huvudman; startdatum; slutdatum; typ)

Vi finner argument, betyg, längd, Princip och term (som är obligatoriska) som vi redan såg i första delen med formeln PMT. Men här behöver vi också begin_date och end_date-argumenten. Den första indikerar början på den period som ska analyseras och den andra änden. Låt oss ta ett exempel:

= - 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 är en som visas i skärmbilden "Cumul 1: a år", så att de analyserade perioderna sträcker sig från 1 till 12, av den första perioden (första månaden) till den tolfte (12: e månaden). Över ett år skulle vi betala $ 10 419, 55 Principal och $ 3 522. 99 Ränta.

Avskrivning av lånet

Med de tidigare formulären kan vi skapa vår schemalagd period efter period, hur mycket vi betalar månadsvis i huvudstol och ränta och hur mycket kvarstår att betala.

Skapa ett låneskema i Excel

För att skapa ett lånschema använder vi olika formuleringar som diskuterats ovan och utökar dem över antal perioder.

I den första perioden kolumnen, skriv helt enkelt "1" som första perioden och dra sedan ner cellen. I vårt fall behöver vi 120 perioder sedan en 10-årig lånebetalning multiplicerad med 12 månader = 120.

Den andra kolumnen är det månatliga beloppet vi behöver betala varje månad, vilket är konstant över hela låneplanen. För att beräkna det, sätt i följande formel i cellen i vår första period:

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

Den tredje kolumnen är den huvudstol som återbetalas varje månad. Till exempel, för den 40: e perioden kommer vi att betala tillbaka 945 dollar. 51 i princip på vårt månatliga sammanlagda belopp på $ 1, 161. 88. För att beräkna det inlösta huvudbeloppet använder vi följande formel:

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

Den fjärde kolumnen är den ränta, för vilken vi beräknar den huvudstol som återbetalas på vårt månadsbelopp för att upptäcka hur mycket intresse måste betalas med hjälp av formeln:

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

Den femte kolumnen innehåller beloppet kvar att betala. Efter 40: e betalningen måste vi betala $ 83, 994. 69 på $ 120, 000. Formeln är följande:

= $ 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)

Formeln använder en kombination av huvudmannen under en period framåt med cellen som innehas av den upplånade huvudmannen. Den här perioden börjar förändras när vi kopierar och dra ner cellen.Skärmbilden nedan visar att i slutet av 120 perioder återbetalas vårt lån.