Hem / Pengar och stat/ Användbara Excel-formler för ekonomisk kontroll. Exempel på PMT-funktionen i Excel: beräkna betalningar på ett annuitetslån Prognos med hjälp av What-if-analys

Användbara Excel-formler för att kontrollera ekonomin. Exempel på PMT-funktionen i Excel: beräkna betalningar på ett annuitetslån Prognos med hjälp av What-if-analys

Excel är ett verkligt kraftfullt verktyg på grund av dess unika mångsidighet och förmåga att lösa problem för människor från olika yrkesområden. Excel är oumbärligt för chefer och ekonomer, entreprenörer och finansiärer, revisorer och analytiker, matematiker och ingenjörer. Dess mångsidighet ges av specifika inbyggda funktioner som vissa specialister använder i sina beräkningar.

En av de största och mest populära funktionskategorierna är finansiell. Den senaste versionen av Excel har 55 funktioner som ingår i denna grupp. Många av dem är specifika och snävt fokuserade, men några kan vara användbara för nästan alla. En av dessa grundläggande funktioner är PMT.

Som det officiella intyget säger,PMT-funktionen returnerar det periodiska betalningsbeloppet för en livränta baserat på konstanta betalningsbelopp och en konstant ränta. Om du är förvirrad av den specifika termen "livränta" - bli inte orolig. Med andra ord, med hjälp av PMT-funktionen kan du beräkna det belopp som kommer att behöva betalas varje månad, förutsatt att räntan på lånet inte ändras och betalningar görs regelbundet med lika stora belopp.

Funktionssyntax

Funktionen har följande syntax:

PMT(hastighet; nper; ps; [bs]; [typ])

Låt oss titta på alla argument en efter en:

Bud.Krävs argument. Representerar räntan för perioden. Det viktigaste här är att inte göra ett misstag när du räknar om storleken på insatsen för den period som krävs. Om du planerar att betala tillbaka lånet i månatliga betalningar, och årsräntan, så måste den omvandlas till en månadsränta, dividerad med 12. Om till exempel lånet återbetalas en gång i kvartalet, så måste årsräntan delas upp. med 4 (och därmed få kursen för 1 kvartal ). Kursen kan anges i procent eller hundradelar.
Nper.Nödvändig. Detta argument representerar antalet faktureringsperioder (hur många gånger betalningar kommer att göras för att återbetala lånet). Liksom räntan beror detta argument på vilken avräkningsperiod som används för beräkningarna. Om lånet erhålls på 5 år med betalningar en gång i månaden, dåNper = 5*12 = 60 perioder . Om för 3 år, med betalningar en gång i kvartalet, dåNper = 3*4 = 12 perioder .
Ps. Nödvändig. Lånebeloppet, det vill säga mängden skuld som kommer att behöva betalas tillbaka med framtida betalningar.
[bs].Frivillig. Det skuldbelopp som måste förbli obetalt efter att alla faktureringsperioder har löpt ut. Vanligtvis är detta argument 0 (lånet måste återbetalas i sin helhet). Eftersom argumentet är valfritt kan det utelämnas (i detta fall tas det lika med noll).
[typ].Frivillig. Indikerar betalningstillfället - i början eller slutet av perioden. För det första fallet måste du ange ett, och för det andra noll (eller hoppa över detta argument helt). I de flesta fall används det andra alternativet - betalningar i slutet av perioden, vilket innebär att detta argument oftast kan utelämnas.

En speciell egenskap hos funktionssyntaxen är indikeringen av kassaflödets riktning. Om kassaflödet är inkommande (till exempel beloppet på det mottagna lånet, specificerat i Ps-argumentet), måste det anges som ett positivt tal. Utgående flöden, tvärtom, indikeras som negativa siffror (till exempel, efter beräkning, kommer PMT-funktionen att returnera ett negativt resultat, eftersom beloppet för lånebetalningen är ett utgående kassaflöde).

Exempel på användning

Uppgift 1. Beräkna mängden lånebetalningar

Låt oss anta att en bank fick ett lån till beloppet1 000 000 gnugga. under 17,5% per år under en period 6 år. Lånet kommer att återbetalas i lika stora månatliga betalningar under hela låneperioden. Vid slutet av löptiden kommer hela skulden att betalas. Den första betalningen kommer att göras i slutet av den första perioden. Du måste hitta det månatliga betalningsbeloppet.

Så vi vet den årliga räntan och lånet kommer att återbetalas månadsvis. Detta innebär att vi för att beräkna måste konvertera årskursen till en månadskurs, dividera 17,5 % med 12 månader.I det första argumentet skriver vi 17,5%/12 .

Lånet erhölls för 6 år. Betalas månadsvis. Detta innebär antalet betalningsperioder = 6*12.I det andra argumentet skriver vi 72 .

I det tredje argumentet skriver vi lånebeloppet. Det är lika med 1 000 000 rubel. (för låntagaren är detta ett inkommande kassaflöde, vi anger det som ett positivt tal).

Vi kommer att utelämna det fjärde argumentet, eftersom beloppet kommer att vara fullt återbetalat vid slutet av löptiden. Vi kommer också att utelämna det femte argumentet, eftersom betalningar görs i slutet av perioden.

Formeln kommer att se ut så här:

PLT(17,5%/12;72;1000000)

Resultatet av beräkningen är-22526,05 RUR. Siffran är negativ eftersom lånebetalningen är ett utgående kassaflöde för låntagaren. Detta är det belopp som kommer att behöva betalas varje månad för att återbetala lånet som beskrivs i villkoren.

För att beräkna beloppet för den slutliga överbetalningen måste du multiplicera den månatliga betalningen med antalet perioder (Nper) och subtrahera lånebeloppet (Ps) från resultatet.

Uppgift 2. Beräkning av insättningsbeloppet för att ackumulera en viss mängd medel

Banken har öppnat en påfyllningsbar deposition med en ränta på 9% per år. Du planerar att sätta in samma summa pengar varje kvartal (till exempel en del av den mottagna kvartalsbonusen) med målet att samla exakt 1 000 000 rubel på kontot om 4 år. Fråga: Hur mycket ska jag fylla på mitt konto varje kvartal?

Vi anger det första argumentet som 9%/4 (eftersom årskursen måste omvandlas till en kvartalskurs), det andra argumentet = 4*4 (4 år, 4 kvartal - totalt 16 bidrag). Det tredje argumentet är lånebeloppet. Vi tar det som 0, eftersom vi inte tog någonting. Det fjärde argumentet är framtida värde. Vi anger det belopp vi vill spara (1 000 000 RUB). Vi utelämnar det femte argumentet igen (betalningar i slutet av perioden, detta är den vanligaste situationen).

Vi får formeln:

PMT(9%/4;4*4;0;1000000).

Beräkningsresultat:-52 616,63 gnugga.Detta belopp måste sättas in på den angivna insättningen varje kvartal för att ha en miljon rubel på kontot efter fyra år.

Totalt belopp av insatta medel = 52616,63 * 16 = 841 866,08 rubel. Resten ackumuleras genom ränta.

Funktioner Funktioner

När du använder funktionen, var uppmärksam på följande punkter:

funktionen är endast avsedd för annuitetsbetalningar (det vill säga lika betalningar med jämna mellanrum);
funktionen fungerar enligt den klassiska kreditmodellen, som inte alltid överensstämmer med vad moderna kreditorganisationer erbjuder. I många fall kommer lånevillkoren inte att tillåta dig att framgångsrikt tillämpa PLT-funktionen på dem och du måste skriva en separat modell och leta efter en lösning medVal av parameter eller Att hitta en lösning(skapandet av en liknande modell kan beställas på vår hemsida - tDots.ru);
funktionen tar hänsyn till betalning av kapitalbelopp och upplupen ränta, men tar inte hänsyn till olika tilläggsavgifter, provisioner, skatter och avgifter etc.;
talets tecken (positivt eller negativt) anger riktningen för kassaflödet. Flödet från borgenären till gäldenären (till exempel lånebeloppet) kommer att ha ett tecken, och flödet från gäldenären till borgenären (till exempel det månatliga återbetalningsbeloppet) kommer att ha motsatt tecken (det spelar ingen roll oavsett om det är plus eller minus).

Du kan stödja vårt projekt och dess vidareutveckling .

Du kan ställa dina frågor om artikeln genom vår feedbackbot påTelegram:

PMT-funktionen i Excel ingår i kategorin "Finansiellt". Den returnerar beloppet för den periodiska betalningen för en livränta, med hänsyn till de konstanta betalningsbeloppen och räntan. Låt oss ta en närmare titt.

Syntax och funktioner för PMT-funktionen

Funktionssyntax: hastighet; nper; ps; [bs]; [typ].

Avkoda argumenten:

Räntan är räntan på lånet.
Nper – det totala antalet lånebetalningar.
Ps – nuvärde, lika med ett antal framtida betalningar (lånebelopp).
Fs är det framtida värdet på lånet efter den senaste betalningen (om argumentet utelämnas antas det framtida värdet vara 0).
Typ – ett valfritt argument som anger om betalningen görs i slutet av perioden (värde 0 eller frånvarande) eller i början (värde 1).

Funktioner för PMT-drift:

Endast amorteringar och räntebetalningar ingår i beräkningen av den periodiska betalningen. Inkluderar inte skatter, avgifter, extra avgifter eller reservbetalningar som ibland är förknippade med ett lån.
När du anger argumentet "Rate" är det nödvändigt att ta hänsyn till frekvensen av ränteberäkningen. För ett lån på 6 % är kvartalsräntan 6 %/4; för en månadsränta – 6%/12.
Argumentet "Nper" anger det totala antalet lånebetalningar. Om en person gör månatliga betalningar på ett treårigt lån, används värdet 3*12 för att specificera argumentet.

Exempel på PMT-funktionen i Excel

För att funktionen ska fungera korrekt måste du ange de ursprungliga uppgifterna korrekt:

Lånets storlek anges med ett minustecken, eftersom Kreditinstitutet "ger" och "förlorar" dessa pengar. För att registrera räntevärdet måste du använda procentformatet. Om det skrivs i numerisk form används ett decimaltal (0,08).

Klicka på fx-knappen ("Infoga funktion"). Fönstret Funktionsguiden öppnas. I kategorin "Finansiellt" väljer du PMT-funktionen. Fyll i argumenten:

När markören är i fältet för ett eller annat argument visas ett "tips" nedan: vad som måste anges. Eftersom källdata matades in i en Excel-tabell använde vi cellreferenser med motsvarande värden som argument. Men du kan också ange numeriska värden.

Var uppmärksam! I fältet "Rate" divideras det årliga räntevärdet med 12: lånebetalningar görs månadsvis.

Månatliga lånebetalningar i enlighet med de villkor som anges som argument uppgår till 1 037,03 RUB.

För att hitta det totala beloppet som måste betalas för hela perioden (kapitalbelopp plus ränta), multiplicera den månatliga lånebetalningen med "Nper"-värdet:

Låt oss från beräkningen av månatliga lånebetalningar utesluta betalningen som gjordes i början av perioden:

För att göra detta måste du ange värdet 1 som "Type"-argument.

Vi detaljerar beräkningen med OSPT- och PRPLT-funktionerna. Med den första kommer vi att visa lånekroppen och med den andra visar vi räntan.

För en detaljerad beräkning, låt oss skapa en tabell:

Låt oss beräkna lånekroppen med hjälp av OSPLT-funktionen. Argumenten fylls i analogt med PMT-funktionen:

I fältet "Period" anger du numret på den period för vilken huvudskulden beräknas.

Vi fyller i argumenten för PRPLT-funktionen på samma sätt:

Vi duplicerar formlerna ner till den sista perioden. För att beräkna den totala betalningen summerar vi lånekroppen och räntan.

Vi beräknar saldot av huvudskulden. Vi får ett bord så här:

Den totala lånebetalningen är densamma som den månatliga betalningen beräknad med PMT-funktionen. Detta är ett konstant värde, eftersom Användaren har tagit ett annuitetslån.

Således kan PMT-funktionen användas för att beräkna månatliga inbetalningar eller lånebetalningar, förutsatt att räntan och beloppen är konstanta.

PMT-funktionen returnerar det periodiska betalningsbeloppet för en livränta baserat på konstanta betalningsbelopp och konstanta räntor.

PMT funktionsbeskrivning

Returnerar det periodiska betalningsbeloppet för en livränta baserat på konstanta betalningsbelopp och konstanta räntor.

Syntax

=PLT(hastighet; nper; ps; [bs]; [typ])

Argument

rate nper ps bs typ

Nödvändig. Låneränta.

Nödvändig. Det totala antalet lånebetalningar.

Nödvändig. Nuvärdet, det vill säga det totala beloppet som för närvarande motsvarar en serie framtida betalningar, kallas också för ”kapitalbeloppet”.

Frivillig. Värdet på det framtida värdet, det vill säga det önskade saldot efter den senaste betalningen. Om "bs" utelämnas antas det vara 0 (till exempel är det framtida värdet för ett lån 0).

Frivillig. En siffra 0 eller 1 som anger när betalningen ska göras.

Anteckningar

Betalningarna som returneras av PMT-funktionen inkluderar kapitalbelopp och räntebetalningar, men inkluderar inte skatter, reservavgifter eller avgifter som ibland är förknippade med lånet.
Se till att du är konsekvent i ditt val av enheter för att specificera argumenten hastighet och nper. Om du gör månatliga betalningar på ett fyraårigt lån till 12 procent årlig ränta, använd då 12%/12 för ränteargumentet och 4*12 för nper-argumentet. Om du gör årliga betalningar på samma lån, använd 12 procent för ränta och 4 procent för nper.

Exempel

Exempel1 Exempeluppgift1

Uppgift
En bankklient lånar 5 000 rubel. med 6 % per år i 6 månader. Bestäm kundens månatliga betalningar. Betalningar görs i slutet av månaden.

Lösning
Låt oss ange de första uppgifterna.

Den här artikeln beskriver en Excel-formel som kan användas för att beräkna annuitetsbeloppet i Excel.

För att beräkna beloppet av livräntan behöver du veta:

1. Lånebelopp.

2. Lånetid.

3. Räntebeloppet på lånet.

4. Frekvensen av ränta på lånet (månatlig, veckovis, etc.), eller snarare antalet betalningsperioder för typen av ränta.

Låt oss titta på ett exempel: vi måste beräkna beloppet för den månatliga annuitetsbetalningen på ett lån till ett belopp av 100 000,00 rubel, för en period av 2 år, med en ränta på 18 procent per år. I det här fallet kommer antalet betalningsperioder att vara lika med 12 eftersom det finns 12 månader på ett år (enligt förhållandena för problemet överväger vi den årliga räntan).

Beräkning av beloppet för livränta med hjälp av formeln

För att beräkna livräntan i Excel måste du använda funktionen PLT.

I den engelska versionen av Excel kallas funktionen PMT.

Den har 5 parametrar, av vilka vi är intresserade av de första 3 ( kurs, nper, ps), de återstående parametrarna är valfria, så vi kommer inte att specificera dem.

Beskrivning av parametrar:

bud– ränta normaliserad till en betalningsperiod. För vårt exempel kommer det att vara lika med / =. Observera att räntan måste anges i bråkdelar av 1. Summan är 0,015.

nper– antal lånebetalningar. I vårt exempel görs lånebetalningar månadsvis, så vi anger lånetiden som värdet på denna parameter, det vill säga 24 månader.

ps– lånebelopp.

Som ett resultat får vi följande formel: =PLT(0,015;24;100000).

Värdet som erhålls genom formeln kommer att vara negativt, vi gör en liten korrigering =-PLT(0,015;24;100000).

Som ett resultat erhåller vi det belopp av livräntan som motsvarar 4 992,41 rubel.

Beräkning av annuitetsbeloppet med hjälp av VBA

För att beräkna annuitetsvärdet måste du använda funktionen ArbetsbladFunction.Pmt, parametrarna för denna funktion liknar de som används i funktionen för formler.

Beräkning av livränta på VBA med hjälp av exemplet ovan:

Annuitet = -WorksheetFunction.Pmt(0,015, 24, 100000)

PMT funktion( ) , engelsk version av PMT(), låter dig beräkna det månatliga lånebeloppet vid annuitetsbetalningar (när lånet betalas i lika delar).

Ett block med artiklar ägnat åt teori och beräkningar av annuitetsparametrar. Den här artikeln täcker bara syntaxen och exemplen på hur PMT()-funktionen används.

FunktionssyntaxPMT()

PMT(hastighet; nper; ps; [bs]; [typ])

Bud. Ränta på ett lån (lån).
Nper. Det totala antalet lånebetalningar.
ps. Lånebelopp.
Bs. Valfritt argument. Erforderligt lånesaldo efter den senaste betalningen. Om detta argument utelämnas antas det vara 0 (lånet kommer att återbetalas till fullo).
Typ. Valfritt argument. Tar värdet 0 (noll) eller 1. Om =0 (eller utelämnad), antas det att den vanliga betalningen görs i slutet av perioden, om 1, då i början av perioden (beloppet av regelbunden betalning kommer att vara något mindre).

Betalningarna som returneras av PMT()-funktionen inkluderar kapital- och räntebetalningar, men inkluderar inte skatter, reservbetalningar eller avgifter som ibland är förknippade med ett lån.

Exempel 1

Anta att en person planerar att ta ett lån till ett belopp av 50 000 rubel. (cell B8 ) på banken till 14 % per år ( B6 ) i 24 månader ( B7 ) (se exempelfil).

Beräkning av det månatliga betalningsbeloppet för ett sådant lån med PMT()-funktionen

PLT(B6/12;B7;B8)

RÅD :
Var noga med att vara konsekvent i ditt val av tidsenheter för att ange argumenten hastighet och nper. I vårt fall räknar vi månatlig betalningar på ett tvåårigt lån (24 månader) med 14 procent per år ( 14% / 12 månader).

Beräkning av det månatliga betalningsbeloppet för ett sådant lån med funktionen UTAN PMT()

B8*(B6/12*(1+B6/12)^B7)/((1+B6/12)^B7-1)

För att hitta beloppet för överbetalning, multiplicera värdet som returneras av PLT()-funktionen med "nper" (du får ett tal med ett minustecken) och lägg till lånebeloppet. I vårt fall blir överbetalningen 7 615,46 RUB. (i 2 år).

Exempel 2

Anta att en person planerar att spara pengar varje månad för att spara på 5 år (cell E7 ) 1 miljon rubel ( E8 ). Han planerar att ta pengarna till banken varje månad och fylla på sin insättning. Banken har en ränta på 10 % ( E6 ) och personen tror att den kommer att gälla utan ändringar i 5 år. Hur mycket ska en person betala till banken varje månad för att spara 1 miljon rubel på 5 år? (se exempelfil).