Planera återbetalningar av lån med Excel-formler

Lånåterbetalning är handlingen att betala tillbaka pengar som tidigare lånats från en långivare, vanligtvis genom en serie periodiska betalningar som inkluderar kapital plus ränta. Visste du att du kan använda programvaran Excel för att beräkna dina lånåterbetalningar?

Den här artikeln är en steg-för-steg-guide för att ställa in låneberäkningar.

Key Takeaways

• Använd Excel för att få hand om din inteckning genom att bestämma din månatliga betalning, din ränta och ditt låneskema.
• Du kan ta en mer djupgående titt på fördelningen av ett lån med Excel och skapa ett återbetalningsschema som fungerar för dig.
• Det finns beräkningar tillgängliga för varje steg som du kan justera för att möta dina specifika behov.
• Att bryta ner och granska ditt lån steg för steg kan göra att återbetalningsprocessen känns mindre överväldigande och mer hanterbar.

Förstå ditt inteckning

Med hjälp av Excel kan du få en bättre förståelse av 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 en tabell i Excel som visar räntesatsen, låneberäkningen för lånets varaktighet, nedbrytning av lånet, amorteringen och den månatliga betalningen.

Beräkna månadsbetalningen

Först, här beräknar du den månatliga betalningen för en inteckning. Med hjälp av den årliga räntan, kapitalet och varaktigheten kan vi bestämma det belopp som ska återbetalas varje månad.

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

= -PMT (hastighet; längd; present_value; [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 den lånade kapitalen. De två sista argumenten är valfria, restvärdet är 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 lånets månatliga betalning är:

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

Förklaring: För räntan använder vi den månatliga räntan (periodens ränta), sedan beräknar vi antalet perioder (120 i 10 år multiplicerat med 12 månader) och slutligen anger vi den lånade kapitalen. Vår månatliga betalning kommer att vara 1 161, 88 dollar under 10 år.

Beräkna den årliga räntan

Vi har sett hur man ställer in beräkningen av en månatlig betalning för en inteckning. Men vi kanske vill ställa in en högsta månatlig betalning som vi har råd med som också visar antalet år som vi skulle behöva betala tillbaka lånet. Av den anledningen vill vi veta motsvarande årlig ränta.

Som visas på skärmdumpen ovan beräknar vi först periodskursen (månadsvis, i vårt fall) och sedan årskursen. Den använda formeln är RATE, som visas på skärmdumpen ovan. Det är skrivet på följande sätt:

= HASTIGHET (NPER; PMT; present_value; [future_value]; [typ])

De tre första argumenten är lånets längd (antal perioder), den månatliga betalningen för att återbetala lånet och den lånade kapitalen. De tre sista argumenten är valfria och restvärdet är noll; Begreppet argument för att hantera förfallen 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 hastigheten.

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 data i månadsbetalningen måste ges ett negativt tecken. Det är därför det finns ett minustecken före formeln. Kursperioden ä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, som är 3, 58%. Med andra ord, för att låna 120 000 $ över 13 år för att betala 960 $ varje månad, bör vi förhandla om ett lån till en årlig högsta ränta på 3, 58%.

Att använda Excel är ett utmärkt sätt att hålla reda på vad du är skyldig och komma med ett schema för återbetalning som minimerar eventuella avgifter som du kan hamna på grund av.

Bestämma lånets längd

Vi kommer nu att se hur du bestämmer lånets längd när du känner till den årliga räntan, den lånade kapitalen och den månatliga betalningen som ska återbetalas. Med andra ord, hur länge kommer vi att behöva betala tillbaka en 120.000 $ -lån med en ränta på 3.10% och en månatlig betalning på $ 1.100 ">

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

= NPER (hastighet; PMT; present_value; [future_value]; [typ])

De tre första argumenten är lånets årliga ränta, den månatliga betalningen som krävs för att återbetala lånet och den lånade kapitalen. De två sista argumenten är valfria, restvärdet är noll. Begreppet argument som ska betalas i förväg (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)

Obs: motsvarande data 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 kommer att använda formeln = B5 / 12 = 127, 97 / 12 under antalet år för att slutföra lånets återbetalning. 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 USD varje månad, bör vi återbetala löptider i 128 månader eller 10 år och 8 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 månatliga återbetalningar under 10 år att ge oss 120 perioder.

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

= -PPMT (hastighet; num_period; längd; huvudsakliga; [rest]; [term])

Argumenten är desamma som för PMT-formeln som redan har sett, med undantag för "num_period", som läggs till för att visa perioden för hur lånet ska delas upp med tanke på kapital och ränta. 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; 120 tusen)

Resultatet visas i skärmdumpen ovan "Loan Decomposition" under den analyserade perioden, som är "en;" det vill säga den första perioden eller den första månaden. Vi betalar 1 161, 88 dollar uppdelade i $ 856, 20 kapital och 305, 68 $ ränta.

Lånsberäkning i Excel

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

= -CUMPRINC (hastighet; längd; huvudsakliga, startdatum, slutdatum; typ)

Vi hittar de argument, hastighet, längd, huvud och term (som är obligatoriska) som vi redan såg i den första delen med formeln PMT. Men här behöver vi argumenten "start_datum" och "slutdatum" också. "Start_datum" indikerar början på den period som ska analyseras, och "slutdatum" indikerar slutet på 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 av den första perioden (första månaden) till den tolfte (12: e månaden). Under ett år betalade vi 10 419, 55 dollar i huvudstad och 3, 522, 99 dollar i ränta.

Amortering av lånet

De tidigare formlerna tillåter oss att skapa vår schema period för period, att veta hur mycket vi ska betala varje månad i ränta och ränta och att veta hur mycket som finns kvar att betala.

Skapa ett låneskema

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

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

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

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

Den tredje kolumnen är den huvudsakliga som kommer att återbetalas varje månad. Till exempel, för den 40: e perioden, kommer vi att återbetala 945, 51 $ i huvudsak 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 återbetalda kapitalet på vårt månatliga belopp 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 är kvar att betala. Till exempel, efter den 40: e betalningen, måste vi betala $ 83 994, 69 för $ 120 000.

Formeln är som följer:

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

Formeln använder en kombination av huvudstol under en period före den cell som innehåller det lånade huvudet. Denna period börjar förändras när vi kopierar och drar ned cellen. Tabellen nedan visar att efter 120 perioder återbetalas vårt lån.