Schemalägg återbetalning av lån med Excel -formler

Låneåterbetalning är att betala tillbaka pengar som tidigare lånats av en långivare, vanligtvis genom en serie periodiska betalningar som inkluderar huvudstol plus ränta. Visste du att du kan använda programmet Excel för att beräkna dina låneavbetalningar?

Den här artikeln är en steg-för-steg-guide för hur du ställer upp låneberäkningar.

Förstå din inteckning

Med Excel kan du få en bättre förståelse för din inteckning i tre enkla steg. Det första steget bestämmer den månatliga betalningen. Det andra steget beräknar räntan och det tredje steget bestämmer låneskemat.

Du kan bygga ett bord i Excel det kommer att berätta räntan, låneberäkningen för lånets löptid, nedbrytningen av lånet, amorteringen och den månatliga betalningen.

Beräkna den månatliga betalningen

Först, så här gör du beräkna månadsbetalningen för en inteckning. Med hjälp av den årliga räntan, kapitalet och löptiden kan vi bestämma beloppet som ska återbetalas varje månad.

Formeln, som visas på skärmdumpen ovan, är skriven enligt följande:

= -PMT (hastighet; längd; presentvärde; [framtidsvärde]; [typ])

Minustecknet framför PMT är nödvändigt eftersom formeln returnerar ett negativt tal. De tre första argumenten är låneränta, lånets längd (antal perioder) och det lånade kapitalet. De två sista argumenten är valfria, restvärdet är som standard noll; betalas i förskott (för en) eller i slutet (för noll) är också valfritt.

Excel -formeln 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 kursen använder vi den månatliga räntan (ränteperiod), sedan beräknar vi antalet perioder (120 i 10 år multiplicerat med 12 månader) och slutligen anger vi rektor lånad. Vår månatliga betalning kommer att vara 1 161,88 dollar över 10 år.

Beräkna den årliga räntan

Vi har sett hur man ställer upp beräkningen av en månadsbetalning för en inteckning. Men vi kanske vill ange en maximal månatlig betalning som vi har råd med som också visar hur många år vi skulle behöva betala tillbaka lånet. Av den anledningen vill vi veta motsvarande årsränta.

Som visas på skärmdumpen ovan beräknar vi först periodesatsen (månadsvis, i vårt fall), och sedan den årliga räntan. Formeln som används är RATE, som visas på skärmdumpen ovan. Det är skrivet så här:

= RATE (Nper; pmt; presentvärde; [framtidsvärde]; [typ])

De tre första argumenten är längden på lån (antal perioder), den månatliga betalningen för att återbetala lånet och det lånade kapitalet. De tre sista argumenten är valfria och restvärdet är som standard noll; termen argument för att hantera löptiden i förväg (för en) eller i slutet (för noll) är också valfritt. Slutligen är uppskattningsargumentet valfritt men kan ge en initial uppskattning av kursen.

Excel -formeln som används för att beräkna utlåningsräntan är:

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

Obs! Motsvarande uppgifter i den månatliga betalningen måste ges ett negativt tecken. Det är därför det finns ett minustecken före formeln. Ränteperioden är 0,294%.

Vi använder formeln = (1 + B5) är 12-1 ^ = (1 + 0,294 %) ^ 12-1 för att få den årliga räntan på vårt lån, vilket är 3,58%. Med andra ord, för att låna 120 000 dollar under 13 år för att betala 960 dollar per månad, bör vi förhandla om ett lån till en årlig högsta ränta på 3,58%.

Fastställande av lånets längd

Vi kommer nu att se hur man bestämmer lånets längd när du känner till den årliga räntan, lånebeloppet och den månatliga betalningen som ska återbetalas. Med andra ord, hur länge kommer vi att behöva betala tillbaka en inteckning på $ 120 000 med en ränta på 3,10% och en månatlig betalning på $ 10000?

Formeln vi kommer att använda är NPER, som visas på skärmdumpen ovan, och den är skriven enligt följande:

= NPER (hastighet; pmt; presentvärde; [framtidsvärde]; [typ])

De tre första argumenten är lånets årliga ränta, den månatliga betalning som behövs för att betala tillbaka lånet och den lånade huvudmannen. De två sista argumenten är valfria, restvärde som standard till noll. Termen argument betalas i förskott (för en) eller i slutet (för noll) är också valfritt.

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

Vi kommer att använda formeln = B5 / 12 = 127,97 / 12 för antalet år för att slutföra återbetalningen av lånet. Med andra ord, för att låna 120 000 dollar, med en årlig ränta på 3,10% och för att betala 1100 dollar per månad, bör vi återbetala löptider i 128 månader eller 10 år och åtta månader.

Nedbrytning av lånet

En lånebetalning består av kapital och ränta. Räntan beräknas för varje period - till exempel kommer de månatliga återbetalningarna över 10 år att ge oss 120 perioder.

Tabellen ovan visar fördelningen av ett lån (en total period lika med 120) med hjälp av PPMT- och IPMT -formlerna. Argumenten för de två formlerna är desamma och är uppdelade enligt följande:

= -PPMT (hastighet; num_period; längd; huvudman; [kvarvarande]; [term])

Argumenten är desamma som för PMT -formeln som redan setts, förutom "num_period", som läggs till för att visa den period under vilken lånet ska brytas med tanke på huvudstolen och räntan. Här är ett exempel:

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

Resultatet visas i skärmdumpen ovan "Lånets sönderdelning" under den analyserade perioden, vilket är "ett;" det vill säga den första perioden eller den första månaden. Vi betalar 1 161,88 dollar fördelat på 856,20 dollar huvudstol och 305,68 dollar ränta.

Lånberäkning i Excel

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

= -CUMPRINC (hastighet; längd; rektor; start datum; slutdatum; typ)

Vi hittar de argument, ränta, längd, princip och term (som är obligatoriska) som vi redan såg i den första delen med formeln PMT. Men här behöver vi också argumenten "startdatum" och "slutdatum". "Startdatum" anger början av perioden som ska analyseras och "slutdatum" anger slutet av perioden som ska analyseras.

Här är ett exempel:

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

Resultatet visas i skärmdumpen "Cumul 1st year", så de analyserade perioderna sträcker sig från en till 12 i den första perioden (första månaden) till den tolfte (12: e månaden). Över ett år skulle vi betala 10 419,55 dollar i kapital och 3522,99 dollar i ränta.

Amortering av lånet

De tidigare formlerna tillåter oss att skapa vårt schema period för period, att veta hur mycket vi kommer att betala månadsvis i ränta och ränta, och att veta hur mycket som återstår att betala.

Skapa ett låneschema

För att skapa ett lånschema kommer vi att använda de olika formlerna som diskuterats ovan och utöka dem över antalet perioder.

I den första periodkolumnen anger du "1" som den första perioden och drar sedan ner cellen. I vårt fall behöver vi 120 perioder sedan en 10-årig lånebetalning multiplicerad med 12 månader är lika med 120.

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

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

Den tredje kolumnen är den huvudstol som kommer att återbetalas varje månad. Till exempel, för den 40: e perioden kommer vi att återbetala 945,51 dollar i kapital på vårt månatliga totala belopp på 1 161,88 dollar.

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 räntan, för vilken vi använder formeln för att beräkna det återbetalade kapitalet på vårt månadsbelopp för att upptäcka hur mycket ränta som ska betalas:

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

Den femte kolumnen innehåller det belopp som återstår att betala. Till exempel, efter den 40: e betalningen, kommer vi att behöva betala $ 83,994.69 på $ 120.000.

Formeln är följande:

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

Formeln använder en kombination av huvudstol under en period före cellen som innehåller lånad huvudstol. Denna period börjar förändras när vi kopierar och drar ner cellen. Tabellen nedan visar att i slutet av 120 perioder återbetalas vårt lån.