Formel för att beräkna en livränta i Excel

Lånebetalningar är enklare och snabbare att beräkna med Microsoft Office Excel. Mycket mer tid läggs på manuell beräkning. Den här artikeln kommer att fokusera på annuitetsbetalningar, funktionerna i deras beräkning, fördelar och nackdelar.

Vad är en livränta

En metod för månatlig återbetalning av ett lån, där det insatta beloppet inte förändras under hela låneperioden. De där. på vissa datum i varje månad sätter en person in en viss summa pengar tills lånet är helt återbetalat.

Dessutom är räntan på lånet redan inkluderad i det totala belopp som betalas till banken.

Klassificering livränta

Livränta kan delas in i följande typer:

1. Fast. Betalningar som inte ändras har en fast ränta oavsett yttre förutsättningar.
2. Valuta. Möjligheten att ändra betalningsbeloppet vid fall eller uppgång i växelkursen.
3. indexeras. Betalningar beroende på nivå, inflationsindikator. Under lånetiden ändras ofta deras storlek.
4. Variabler. Livränta, som kan ändras beroende på tillståndet i det finansiella systemet, instrument.

Var uppmärksam! Fasta betalningar är att föredra för alla låntagare, eftersom de har liten risk.

För- och nackdelar med livränta

För att bättre förstå ämnet är det nödvändigt att studera nyckelfunktionerna i denna typ av lånebetalningar. Det har följande fördelar:

• Fastställande av ett specifikt betalningsbelopp och datum för betalningen.
• Hög tillgänglighet för låntagare. Nästan vem som helst kan ansöka om livränta, oavsett ekonomisk situation.
• Möjligheten att sänka beloppet på den månatliga avbetalningen med en ökning av inflationen.

Inte utan nackdelar:

• Hög hastighet. Låntagaren kommer att betala för mycket en större summa pengar jämfört med skillnadsbetalningen.
• Problem som uppstår från önskan att betala av skulden i förväg.
• Inga omräkningar för förtida betalningar.

Vad är lånebetalningen?

Livränta har följande komponenter:

• Ränta som betalas av en person vid betalning av ett lån.
• En del av kapitalbeloppet.

Som ett resultat överstiger det totala räntebeloppet nästan alltid det belopp som låntagaren bidrar med för att minska skulden.

Grundläggande annuitetsutbetalningsformel i Excel

Som nämnts ovan kan du i Microsoft Office Excel arbeta med olika typer av betalningar för lån och förskott. Livränta är inget undantag. Generellt sett är formeln med vilken du snabbt kan beräkna annuitetsavgifter följande:  

Viktigt! Det är omöjligt att öppna parenteser i nämnaren för detta uttryck för att förenkla det.

Formelns huvudvärden dechiffreras enligt följande:

• AP – livränta (namnet förkortas).
• O – storleken på låntagarens huvudskuld.
• PS – den ränta som läggs fram på månadsbasis av en viss bank.
• C är antalet månader lånet löper.

För att assimilera informationen räcker det med att ge några exempel på användningen av denna formel. De kommer att diskuteras vidare.

Exempel på användning av PMT-funktionen i Excel

Vi ger ett enkelt tillstånd för problemet. Det är nödvändigt att beräkna den månatliga lånebetalningen om banken lägger fram en ränta på 23% och det totala beloppet är 25000 rubel. Utlåningen gäller i 3 år. Problemet löses enligt algoritmen:

1. Gör ett allmänt kalkylblad i Excel baserat på källdata.

2. Aktivera PMT-funktionen och ange argument för den i lämplig ruta.
3. I fältet "Satsning" anger du formeln "B3/B5". Detta blir räntan på lånet.
4. På raden "Nper" skriv värdet i formen "B4*B5". Detta kommer att vara det totala antalet betalningar under hela låneperioden.
5. Fyll i "PS"-fältet. Här måste du ange det initiala beloppet som tagits från banken och skriva värdet "B2".

6. Se till att efter att ha klickat på "OK" i källtabellen beräknades värdet "Månatlig betalning".

Ytterligare information! Ett negativt tal indikerar att låntagaren spenderar pengar.

Ett exempel på att beräkna beloppet för överbetalning på ett lån i Excel

I det här problemet måste du beräkna det belopp som en person som har tagit ett lån på 50000 27 rubel till en ränta på 5% i 12 år kommer att betala för mycket. Totalt gör låntagaren XNUMX betalningar per år. Lösning:

1. Kompilera den ursprungliga datatabellen.

2. Från det totala betalningsbeloppet subtraherar du det ursprungliga beloppet enligt formeln «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Den måste infogas i formelfältet högst upp i programmets huvudmeny.
3. Som ett resultat kommer mängden överbetalningar att visas på den sista raden på den skapade plattan. Låntagaren kommer att betala för mycket 41606 rubel ovanpå.

Formeln för att beräkna den optimala månatliga lånebetalningen i Excel

En uppgift med följande villkor: klienten har registrerat ett bankkonto för 200000 4 rubel med möjlighet till månatlig påfyllning. Det är nödvändigt att beräkna betalningsbeloppet som en person måste göra varje månad, så att han efter 2000000 år har 11 rubel på sitt konto. Satsen är XNUMX %. Lösning:

1. Skapa ett kalkylblad baserat på originaldata.

2. Ange formeln på inmatningsraden i Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» och tryck på "Enter" från tangentbordet. Bokstäverna kommer att skilja sig beroende på i vilka celler tabellen är placerad.
3. Kontrollera att bidragsbeloppet automatiskt beräknas på sista raden i tabellen.

Var uppmärksam! Således, för att kunden ska kunna samla 4 rubel med en hastighet av 2000000% på 11 år, måste han sätta in 28188 rubel varje månad. Minus i beloppet indikerar att kunden ådrar sig förluster genom att ge pengar till banken.

Funktioner för att använda PMT-funktionen i Excel

I allmänhet skrivs denna formel så här: =PMT(hastighet; nper; ps; [bs]; [typ]). Funktionen har följande funktioner:

1. När månatliga avgifter beräknas tas endast årsavgiften i beaktande.
2. När man anger räntan är det viktigt att räkna om utifrån antalet avbetalningar per år.
3. Istället för argumentet "Nper" i formeln anges ett specifikt nummer. Detta är betalningsperioden.

Betalningsberäkning

Generellt sett beräknas livränta i två steg. För att förstå ämnet måste var och en av stegen övervägas separat. Detta kommer att diskuteras vidare.

Steg 1: beräkning av den månatliga avbetalningen

För att i Excel beräkna det belopp du behöver betala varje månad på ett lån med fast ränta måste du:

1. Kompilera källtabellen och välj den cell där du vill visa resultatet och klicka på knappen "Infoga funktion" längst upp.

2. I listan över funktioner, välj "PLT" och klicka på "OK".

3. I nästa fönster ställer du in argumenten för funktionen och anger motsvarande rader i den kompilerade tabellen. I slutet av varje rad måste du klicka på ikonen och sedan välja önskad cell i arrayen.

4. När alla argument är ifyllda, kommer lämplig formel att skrivas i raden för inmatning av värden, och beräkningsresultatet med ett minustecken visas i fältet i tabellen "Månatlig betalning".

Viktigt! Efter att ha beräknat avbetalningen kommer det att vara möjligt att beräkna det belopp som låntagaren kommer att betala för mycket för hela lånetiden.

Steg 2: betalningsinformation

Beloppet för överbetalning kan beräknas månadsvis. Som ett resultat kommer en person att förstå hur mycket pengar han kommer att spendera på ett lån varje månad. Detaljberäkning utförs enligt följande:

1. Skapa ett kalkylblad i 24 månader.

2. Placera markören i den första cellen i tabellen och infoga "OSPLT"-funktionen.

3. Fyll i funktionsargumenten på samma sätt.

4. När du fyller i fältet "Period", måste du hänvisa till den första månaden i tabellen, som anger cell 1.

5. Kontrollera att den första cellen i kolumnen "Betalning av lånet" är ifylld.
6. För att fylla alla rader i den första kolumnen måste du sträcka cellen till slutet av tabellen

7. Välj "PRPLT"-funktionen för att fylla i den andra kolumnen i tabellen.
8. Fyll i alla argument i det öppnade fönstret i enlighet med skärmdumpen nedan.

9. Beräkna den totala månadsbetalningen genom att lägga till värdena i de två föregående kolumnerna.

10. För att beräkna "Saldot" måste du lägga till räntan på betalningen på lånet och sträcka den till slutet av plattan för att fylla alla månader av lånet.

Ytterligare information! Vid beräkning av återstoden måste dollartecken hängas på formeln så att den inte rör sig ut när den sträcks ut.

Beräkning av annuitetsbetalningar på ett lån i Excel

PMT-funktionen ansvarar för att beräkna livräntan i Excel. Principen för beräkning i allmänhet är att utföra följande steg:

1. Kompilera den ursprungliga datatabellen.
2. Skapa ett schema för återbetalning av skulder för varje månad.
3. Välj den första cellen i kolumnen "Betalningar på lånet" och ange beräkningsformeln "PLT ($B3/12;$B$4;$B$2)".
4. Det resulterande värdet sträcks ut för alla kolumner på plattan.

Beräkning i MS Excel återbetalning av skuldens kapitalbelopp

Livränta ska betalas ut månadsvis med fasta belopp. Och räntan förändras inte.

Beräkning av saldot av kapitalbeloppet (med BS=0, typ=0)

Antag att ett lån på 100000 10 rubel tas i 9 år till 1%. Det är nödvändigt att beräkna beloppet av huvudskulden under den första månaden av det tredje året. Lösning:

1. Sammanställ ett datablad och beräkna månadsbetalningen med hjälp av ovanstående PV-formel.
2. Beräkna andelen av betalningen som krävs för att betala av en del av skulden med hjälp av formeln «=-PMT-(PS-PS1)*artikel=-PMT-(PS +PMT+PS*artikel)».
3. Beräkna storleken på huvudskulden under 120 perioder med hjälp av en välkänd formel.
4. Använd HPMT-operatören för att hitta räntan som betalas för den 25:e månaden.
5. Kontrollera resultatet.

Beräknar det kapitalbelopp som betalats mellan två perioder

Denna beräkning görs bäst på ett enkelt sätt. Du måste använda följande formler för att beräkna beloppet i intervallet för två perioder:

• =«-BS(objekt; kon_period; plt; [ps]; [typ]) /(1+typ *objekt)».
• = "+ BS(hastighet; startperiod-1; plt; [ps]; [typ]) /IF(startperiod =1; 1; 1+typ *hastighet)".

Var uppmärksam! Bokstäver inom parentes ersätts med specifika värden.

Förtida återbetalning med förkortad löptid eller betalning

Om du behöver minska lånetiden måste du göra ytterligare beräkningar med hjälp av IF-operatören. Så det kommer att vara möjligt att kontrollera nollsaldot, som inte bör uppnås före slutet av betalningsperioden.

För att minska betalningarna måste du räkna om bidraget för varje föregående månad.

Lånekalkylator med oregelbundna betalningar

Det finns flera annuitetsalternativ där låntagaren kan sätta in rörliga belopp vilken dag i månaden som helst. I en sådan situation beräknas saldot av skulden och räntan för varje dag. Samtidigt i Excel behöver du:

1. Ange de dagar i månaden för vilka betalningar görs och ange deras antal.
2. Kontrollera om det finns negativa och positiva belopp. Negativa är att föredra.
3. Räkna dagarna mellan två datum då pengarna sattes in.

Beräkning av den periodiska betalningen i MS Excel. Terminsdeposition

I Excel kan du snabbt räkna ut mängden vanliga betalningar, förutsatt att ett fast belopp redan har samlats. Denna åtgärd utförs med PMT-funktionen efter att den initiala tabellen har kompilerats.

Slutsats

Således är livränta enklare, snabbare och effektivare att beräkna i Excel. PMT-operatören ansvarar för deras beräkning. Mer detaljerade exempel finns ovan.