Innehållsförteckning
- Förstå ditt inteckning
- Beräkna månadsbetalningen
- Beräkna den årliga räntan
- Bestämma lånets längd
- Nedbrytning av lånet
- Lånsberäkning i Excel
- Amortering av lånet
- Skapa ett låneskema
Lånåterbetalning är att återbetala 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 finjustera för att tillgodose dina specifika behov. Om du bryter ned och undersöker ditt lån steg för steg kan återbetalningsprocessen kännas 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 berättar 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 på följande sätt:
= -PMT (hastighet; längd; present_value;;)
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 lånet. 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 måste återbetala lånet över. Av den anledningen vill vi veta motsvarande årlig ränta.
Som visas i skärmdumpen ovan beräknar vi först periodskursen (varje månad, i vårt fall) och sedan årskursen. Den använda formeln är RATE, som visas på skärmdumpen ovan. Det är skrivet enligt följande:
= HASTIGHET (NPER; PMT; present_value;;)
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 den månatliga betalningen 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 fram ett lån till en årlig maximal ränta på 3, 58%.
Att använda Excel är ett bra 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 återbetala en inteckning på 120 000 USD 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:
= Perioder (hastighet; PMT; present_value;;)
De första tre 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 som standard 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 den månatliga betalningen 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 USD, 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;;)
Argumenten är desamma som för PMT-formeln som redan 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 på 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 utvidga 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. För den 40: e perioden kommer vi till exempel att återbetala $ 945, 51 i huvudsak på vårt totala månadsbelopp 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 den ränta, 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.
