Cum se utilizează funcția plt în excel. Exemple de funcție PMT în Excel: calcularea plăților pentru un împrumut de anuitate

11.12.2023

Funcția PMT returnează suma plății periodice pentru o anuitate pe baza consecvenței sumelor de plată și a persistenței dobândă. Funcția are următoarea sintaxă:

PLT (rata; nper; ps; bs; tip).

Argumente ale funcției PMT

Argument necesar. Rata dobânzii pentru o perioadă.

Notă: dacă este exprimată ca procent pe an, atunci această valoare trebuie împărțită la numărul de perioade

Argument necesar (valoare constantă). Numărul de perioade de plăți efectuate.

Notă: această valoare nu se poate modifica pe toată perioada de plată. Această valoare include de obicei plățile principalului și ale dobânzilor, dar nu impozitele sau comisioanele.

Argument necesar. Valoarea actuală (actuală), adică suma totală care este în prezent echivalentă cu un număr de plăți viitoare.

Notă: Reprezentat ca număr negativ în cazul plății numerarși pozitiv - dacă este primit. Dacă argumentul este omis, atunci este setat la 0. În acest caz, trebuie specificată valoarea argumentului ps.

Argument opțional. Valoarea valorii viitoare, adică soldul de fonduri dorit după ultima plată.

Notă: Dacă argumentul „bs” este omis, se presupune că este 0 (de exemplu, valoarea viitoare a unui împrumut este 0).

Argument opțional. Indică când trebuie efectuată plata.

Notă: argumentul este zero dacă plata se face la sfârșitul perioadei și unul dacă la început. „Tip” este implicit la zero.

Funcția PMT din Excel este inclusă în categoria „Financial”. Returnează suma plății periodice pentru o anuitate, ținând cont de sumele de plată constante și rata dobânzii. Să aruncăm o privire mai atentă.

Sintaxa și caracteristicile funcției PMT

Sintaxa funcției: rate; nper; ps; [bs]; [tip].

Decodificarea argumentelor:

Rata este dobânda la credit.
Nper – numărul total de plăți ale împrumutului.
Ps – valoarea actuală, egală cu un număr de plăți viitoare (suma împrumutului).
Fs este valoarea viitoare a împrumutului după ultima plată (dacă argumentul este omis, se presupune că valoarea viitoare este 0).
Tip – un argument opțional care specifică dacă plata se face la sfârșitul perioadei (valoare 0 sau absent) sau la început (valoare 1).

Caracteristici ale funcționării PMT:

Doar plățile de principal și plățile de dobândă sunt incluse în calculul plății periodice. Impozitele, comisioanele, contribuțiile suplimentare și plățile de rezervă asociate uneori cu un împrumut nu sunt luate în considerare.
La specificarea argumentului „Rata”, este necesar să se țină cont de frecvența calculării dobânzii. Pentru un credit de 6%, rata trimestrială este de 6%/4; pentru o rată lunară – 6%/12.
Argumentul „Nper” indică numărul total de plăți ale împrumutului. Dacă o persoană face plăți lunare pentru un împrumut pe trei ani, atunci valoarea 3*12 este folosită pentru a stabili argumentul.

Exemple de funcție PMT în Excel

Pentru ca funcția să funcționeze corect, trebuie să introduceți corect datele inițiale:

Mărimea împrumutului este indicată cu semnul minus, deoarece Instituția de credit „dă” și „pierde” acești bani. Pentru a înregistra valoarea ratei dobânzii, trebuie să utilizați formatul procentual. Dacă este scris sub formă numerică, se folosește un număr zecimal (0,08).

Faceți clic pe butonul fx („Insert function”). Se deschide fereastra Function Wizard. În categoria „Financial”, selectați funcția PMT. Completați argumentele:

Când cursorul se află în câmpul unuia sau altui argument, mai jos este afișat un „hint”: ce trebuie introdus. Deoarece datele sursă au fost introduse într-un tabel Excel, am folosit referințe de celule cu valorile corespunzătoare ca argumente. Dar puteți introduce și valori numerice.

Fiţi atenți! În câmpul „Rate”, valoarea dobânzii anuale este împărțită la 12: plățile împrumutului se fac lunar.

Plățile lunare ale împrumutului în conformitate cu condițiile specificate ca argumente se ridică la 1.037,03 RUB.

Pentru a găsi valoare totală care trebuie plătit pentru întreaga perioadă (principal plus dobândă), înmulțiți plata lunară a împrumutului cu valoarea „Nper”:

PMT(Rata;Număr_per;Ps;[Bs];[Tip])

PS

Formula PS utilizat pentru a calcula valoarea actuală a unei investiții. Această funcție este inversul operatorului PLT. Are exact aceleași argumente, dar în loc de argumentul valorii prezente ( "PS"), care se calculează efectiv, se indică valoarea plății periodice ( "Plt"). Sintaxa este în consecință:

PS(Rata;Număr_per;Plt;[Bs];[Tip])

VPN

Următoarea declarație este utilizată pentru a calcula valoarea actualizată netă sau valoarea actualizată. Această funcție are două argumente: rata de actualizare și valoarea plăților sau a încasărilor. Adevărat, al doilea dintre ele poate avea până la 254 de opțiuni reprezentând fluxuri de numerar. Sintaxa acestei formule este:

NPV(Rata;Valoare1;Valoare2;…)

BID

Funcţie BID calculează rata dobânzii la o anuitate. Argumentele acestui operator sunt numărul de perioade ( „Kol_per”), suma plății regulate ( "Plt") și suma plății ( "Ps"). În plus, există argumente opționale suplimentare: valoare viitoare ( "Bs") și o indicație la începutul sau la sfârșitul perioadei că plata va fi efectuată ( "Tip"). Sintaxa arată astfel:

BET(Număr_per;Plt;Ps[Bs];[Tip])

EFECT

Operator EFECT Calculează rata reală (sau efectivă) a dobânzii. Această funcție are doar două argumente: numărul de perioade din anul pentru care se calculează dobânda, precum și rata nominală. Sintaxa sa arată astfel:

EFECT(Rata_număr, Număr_per)

Am luat în considerare doar cele mai populare funcții financiare. În general, numărul operatorilor din acest grup este de câteva ori mai mare. Dar chiar și aceste exemple demonstrează clar eficacitatea și ușurința de utilizare a acestor instrumente, care facilitează foarte mult calculele pentru utilizatori.

Articolul discută funcțiile financiare PLT(), OSPLT(), PRPLT(), NPER(), RATE(), PS(), BS(), precum și GENERAL INOME() și GENERAL PAYMENT(), care sunt utilizate pentru a calcula parametrii schemei de anuitate .

Acest articol face parte dintr-o serie de articole despre calcularea parametrilor anuității. Lista tuturor articolelor de pe site-ul nostru despre anuitate.

Acest articol conține o scurtă secțiune despre teoria anuității, o scurtă descriere a funcțiilor anuității și argumentele acestora și link-uri către articole cu exemple de utilizare a acestor funcții.

Puțină teorie

O anuitate (uneori sunt folosiți termenii „chirie”, „rentă financiară”) este unidirecțional fluxul de numerar, ale cărui elemente sunt la fel în mărimeși sunt produse prin perioade egale de timp(de exemplu, când plățile se fac anual în sume egale).

Sintaxă PRPLT (rata; perioada; nper; ps; bs, tip). (2.13)

Argumentele funcției înseamnă: licitare

perioadă- precizează perioada pentru care trebuie găsite plățile de dobândă, valoarea trebuie să fie în intervalul de la 1 la „nper”;

nper- numărul total de perioade de plată pentru anuitate;

ps- valoarea actuală sau suma totală care este în prezent echivalentă cu o serie de plăți viitoare, numită și suma principală;

bs- valoarea cerută a valorii viitoare sau a soldului de fonduri după ultima plată;

tip- un număr 0 sau 1 care indică momentul în care trebuie efectuată plata. Dacă acest argument este omis, acesta este setat la 0.

Dacă această funcție nu este disponibilă sau returnează eroarea # NUME?, atunci instalați și încărcați suplimentul „Pachet de analiză” Pentru a face acest lucru, în meniu Serviciu Selectați echipa Suplimente Excel. Din lista de suplimente, selectați Pachet de analizeși apăsați butonul BINE. Urmați instrucțiunile de instalare dacă este necesar.

Soluţie: PRPLT (10% / 12, 1, 12 * 3; 800) = - 6,667 mii UAH.

Exemplul 2.28. Datorită contribuțiilor anuale de peste 6 ani, s-a format un fond de 500 mii UAH. Este necesar să se calculeze câte venituri au adus investițiile proprietarului în ultimul an, dacă rata anuală a fost de 17,5%.

Soluţie: Venitul per anul trecut(6 perioade) s-au ridicat la:

PRPLT (17,5%; 6; 6;; 500) = 66,48110268 mii UAH.

Anual PMT era datorat (17,5%; 6;; 500) = - 53,627 mii UAH.

Suma plății principalului împrumutului (plata datoriilor), care este rambursată în plăți egale la sfârșitul sau la începutul fiecărei perioade de facturare, pentru perioada specificată este calculată utilizând funcția Excel OSPLT:

Sintaxă OSPLT (rata; perioadă; nper; ps; bs; tip) (2.14)

sau se regăsește ca diferență între plata periodică fixă și dobânda la partea restantă a împrumutului. Argumentele funcției înseamnă: licitare- rata dobânzii pentru perioada;

perioadă- setează perioada, valoarea trebuie să fie în intervalul de la 1 la „nper”;

nper- numărul total de perioade de plăți anuale de anuitate;

ps- valoarea actualizată, adică suma totală care este echivalentă cu un număr de plăți viitoare;

tip- un număr 0 sau 1 care indică momentul în care trebuie efectuată plata.

Exemplul 2.29. Determinați suma plății principalului pentru un împrumut pe doi ani de 2000 UAH. pentru prima lună la cota de 10% pe an. Dobânda se calculează lunar.

Soluţie: Plata principalului împrumutului pentru prima lună:

OSPLT (10% / 12, 1, 2 * 12; 2000) = - 75,62 UAH.

Venitul acumulat din împrumut (suma plăților dobânzilor), care se rambursează în plăți egale la sfârșitul sau la începutul fiecărei perioade de facturare, se calculează în Excel de către funcția PLATĂ GENERALĂ între două perioade de plată.

Sintaxă PLATA GENERALA (rata; nper; ps;

perioada de început; perioada; tip). (2.15)

Argumentele funcției înseamnă: licitare- dobândă; nper ps start_period -

sfârșit_perioada -

tip- aceasta este alegerea timpului de plată.

Exemplul 2.30. A emis un împrumut pentru achiziționarea de bunuri imobiliare în valoare de 125 mii UAH. pe o perioadă de 30 de ani la 9% pe an, dobânda se acumulează lunar. Determinați cuantumul plăților de dobândă a) pentru al doilea an, b) pentru prima lună.

Soluţie: Plata cumulativă a dobânzii pentru al doilea an (din perioada a 13-a până în a 24-a) va fi:

PLATA GENERALA (9% / 12; 30 * 12; 125.000; 13; 24; 0) = - 11135,23 UAH. O plată pentru prima lună va fi:

PLATA GENERALA (9% / 12; 30 * 12; 125.000, 1, 1, 0) = - 937,50 UAH. Aceeași valoare va fi obținută la calcularea utilizând formula:

PRPLT (9% / 12, 1, 30 * 12; 125.000) = - 937,50 UAH. În Excel, funcția VENIT TOTAL calculează suma cumulativă (cumulativă) plătită pentru rambursarea sumei principalului unui împrumut între două perioade:

Sintaxă VENIT TOTAL (rata; nper;

ps; perioada de început; perioada; tip). (2.16)

Argumentele funcției înseamnă:

licitare- dobândă;

nper- este numărul total de perioade de plată;

ps- este costul investitiei in momentul curent;

start_period - acesta este numărul primei perioade incluse în calcul. Perioadele de plată sunt numerotate începând de la 1;

sfârșit_perioada - acesta este numărul ultimei perioade incluse în calcul;

tip- aceasta este alegerea timpului de plată.

Exemplul 2.31. A emis un împrumut în valoare de 125 mii UAH. pe o perioadă de 30 de ani la 9% pe an, dobânda se acumulează lunar. Stabiliți cuantumul plăților de bază: a) pentru prima lună; b) al doilea an (plăți din perioada a 13-a până în a 24-a).

soluţie:

a) VENIT TOTAL (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68,27827118 UAH;

b) dacă împrumutul este rambursat în plăți egale la sfârșitul fiecărei perioade de facturare, atunci valoarea plății datoriilor pentru al doilea an va fi:

VENIT TOTAL (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934,1071234 UAH. Perioadele de la 13 la 24 constituie al doilea an.

Lucrare de laborator nr 2

Lucrul cu funcțiile financiare.

Analiza ce ar fi

Scopul lucrării: Învață să lucrezi cu funcțiile financiare Excel

și efectuați o analiză ce ar fi cazul

1 Funcţiile financiare în calculele economice

2 Prognoza folosind analiza What-if

Funcţiile financiare în calculele economice

Funcția PMT. Calculul sumei lunare a plății creditului

Funcția PMT determină suma plății periodice pentru o anuitate pe baza sumelor de plată constante și a ratelor constante ale dobânzii.

Exemplul 1 Determinați plata lunară dacă banca oferă un împrumut de 140.000 de ruble. cu rate de 5 ani la 8,5% pe an cu plăți lunare. Ultima plată ar trebui să fie de 10.000 de ruble.

Să introducem datele în tabelul Excel conform Fig. 1)

1 Selectați celula B6și faceți clic pe butonul funcție Inserare (semn f x în stânga barei de formule). Va apărea fereastra Function Wizard, selectați categoria Financiară.

2 Faceți clic pe funcția PMT, trageți fereastra PMT într-un spațiu gol de pe ecran , să elibereze masa și

Figura 1 Calcularea anuității completați câmpurile:

▪ Câmp Licitați- acesta este procentul pe lună,

introduceți 0,085,

▪ Nper– numărul de perioade de plată, de ex. 5 ani*12 luni, introduceți 5*12

▪ NZ– suma totală a tuturor plăților din momentul curent, introduceți 140000,

▪ Bs– valoare viitoare, introduceți 130000 cu semnul „-”, deoarece Noi plătim, nu banca,

§ Tip– plata la sfârșitul lunii, deci introduceți 0 sau nimic.

3 Apăsați Bine.

Rezultat: aproximativ 2738 rub. Trebuie să plătiți lunar pentru a plăti 130.000 de ruble. timp de 5 ani (la sfârșitul termenului, ultima plată este încă 10.000 de ruble)

2 Prognoza folosind analiza ce se întâmplă dacă

Analiza „ce ar fi dacă” vă permite să preziceți valoarea oricărei funcții (matematice, financiare, statistice etc.) atunci când argumentele acesteia se schimbă. Există trei moduri de a prezice valori: folosind tabele de căutare a datelor, folosind scripturi și folosind selecția parametrilor și găsirea unei soluții.

1 cale. Tabel de căutare a datelor - Acesta este un interval de celule care arată modul în care modificarea valorilor de căutare afectează rezultatul returnat de formulă. Dacă o celulă conține o formulă care conține elemente din alte celule, atunci modificarea valorii într-una sau mai multe celule va schimba rezultatul în celula care conține formula.

Exemplul 2 Compania a făcut un împrumut de 80.000 de ruble. pe o perioadă de 3 ani. Defini:

Plăți lunare la rate ale dobânzii de 7%, 8% și 9% pe an,

Plăți lunare la o rată a dobânzii de 5%, un termen de împrumut de 5 ani și o sumă a împrumutului de 100.000 RUR.

1 Să introducem un tabel de substituție sub forma (Fig. 2):

Figura 2 Tabel de înlocuire

2 Introduceți formula de plată PMT (B3/12;B4*12;B5) în celula D2 manual sau prin fereastra PMT din Function Wizard (vezi exemplul 1), valoarea calculată a funcției -2470,17 ruble va apărea în D2.

3 Să modificăm valoarea celulei B3 cu 8%, iar în D2 obținem suma de plată -2506,91 ruble.

4 Să modificăm valoarea celulei B3 cu 9%, primim în D2 suma de plată -2543,98 ruble.

5 Să modificăm simultan valorile celulelor: B3 cu 5%, B4 cu 5 și B5 cu 100.000, primim în D2 suma de plată -1887,12 ruble.

Tabelul de substituție trebuie să conțină într-una dintre celule formula.

Metoda 2. Scenariu - este un set de valori wildcard utilizate pentru a prezice comportamentul modelului. Puteți crea și salva mai multe scenarii diferite pe o singură foaie Excel și puteți comuta la oricare dintre ele pentru a vedea rezultatele și a alege pe cel mai bun.

Exemplul 3 Să scriem opțiunile de înlocuire a datelor de la punctele 2 și 3 din exemplul 2 sub formă de scenarii.

Pentru a crea un script, trebuie să faceți următoarele:

1 Din meniul Instrumente, selectați comanda Scripturi.

2 În fereastra Script Manager care se deschide, faceți clic pe butonul Adăugare.

3 Introduceți un nume pentru scenariu, de exemplu „Rate 7%”.

4 În câmpul Celule modificabile, specificați acele celule (separate prin două puncte) pe care urmează să le modificați, în acest caz, celula B3.

5 Apăsați butonul BINE.

6 În caseta de dialog Valori scenarii care se deschide, pentru fiecare celulă care este schimbată, introduceți o nouă valoare sau formulă, în acest caz, introduceți numărul 0,07 în B3; Faceți clic pe butonul Bine. Este recomandabil să salvați modelul inițial what-if ca script, numindu-l, de exemplu, „Valori de pornire”. În caz contrar, atunci când definiți noi celule modificabile, datele originale se vor pierde.

Pentru a vizualiza scriptul trebuie să folosiți butonul Retrage în fereastra Script Manager. Făcând clic pe butonul Rezumat din caseta de dialog Scenario Manager, puteți obține un raport rezumat într-o foaie de lucru separată numită Structura scenariului, care arată impactul diferitelor scenarii asupra uneia sau mai multor celule de rezultat. Semnele „+”(“-”) din stânga și de sus vă permit să extindeți (restrângeți) secțiuni individuale ale raportului. Câmpurile modificabile sunt evidențiate cu gri.

3 căi. Selectarea parametrului. La selectarea unui parametru, valoarea celulei de influență (parametrul) se modifică până când formula care depinde de această celulă returnează valoarea specificată.

Exemplul 4 Condiția exemplului 1. Compania poate plăti lunar nu mai mult de 2500 de ruble. Stabiliți care ar trebui să fie ultima plată pentru aceasta.

1.Selectați celula B6:

2. În meniul Instrumente, selectați comanda Parameter Selection.

În fereastra de selecție a parametrilor:

În câmpul Set in cell – este introdus B6,

În câmpul Valoare - introduceți -2500

În câmpul Modificare valoarea celulei, introduceți B3 (ultima celulă de plată),

Clic BINE.

Rezultat: ultima plată = -27716 rub.

La selectarea unui parametru, una dintre celule trebuie să conțină și formula , deoarece tabelul este un tabel de căutare.

Comanda Căutare soluție din meniul Instrumente este folosită pentru a selecta simultan mai mulți parametri pentru a maximiza sau a minimiza conținutul celulei țintă și este discutată în detaliu în lucrarea de laborator nr. 7 (Excel-7).

Întrebări de securitate

1 Cum să afișați aplicația Function Wizard?

2 Ce operație efectuează funcția PMT, ce este introdus în câmpurile sale Norm, Nper, Nz, Bs, Type?

3 Scopul și metodele analizei „Dar dacă”?

4 Ce este un „Tabel de substituții”, care este compoziția celulelor sale?

5 Ce este un script, cum să îl creați, să îl vizualizați și să obțineți raportul final pe o foaie separată?

6 Esența operației Selectarea unui parametru, cum se realizează?

Misiuni

1 Finalizați sarcina din exemplul 1, modificând suma împrumutului la 140000· n, Unde n- numărul elevului în jurnalul profesorului. Procedați la fel pentru noua sumă a împrumutului, modificând dobânda anuală de la 8,5% la 5% și durata împrumutului de la 5 la 10 ani.

2 Efectuați o analiză „Dacă ar fi” pe baza tabelului de înlocuire din Exemplul 2, modificând suma împrumutului la 80000·n, unde n este numărul elevului din jurnalul profesorului.

3 Formulați sub formă de scenarii toate operațiunile de la paragraful 1 (două scenarii) și paragraful 2 (patru scenarii) din această sarcină pentru lucrări de laborator.

4 Finalizați sarcina din exemplul 4, schimbând suma plății lunare în n·100.

1 Titlul, scopul, conținutul lucrării

2 Răspunsuri scrise la întrebările de securitate

Înainte de a contracta un împrumut, ar fi o idee bună să calculați toate plățile aferente acestuia. Acest lucru îl va salva pe împrumutat în viitor de diverse probleme și dezamăgiri neașteptate atunci când se dovedește că supraplata este prea mare. Instrumentele pot ajuta la acest calcul programe Excel. Să aflăm cum să calculăm plățile împrumutului de anuitate în acest program.

În primul rând, trebuie spus că există două tipuri de plăți ale împrumutului:

diferențiat;
Anuitate.

Cu o schemă diferențiată, clientul plătește băncii o cotă egală lunară din plățile asupra organismului de credit plus plățile dobânzilor. Suma plăților dobânzilor scade în fiecare lună, pe măsură ce corpul creditului din care sunt calculate scade. Astfel, scade și plata totală lunară.

Schema de anuitate folosește o abordare ușor diferită. Clientul efectuează o plată lunară de aceeași sumă, care constă în plăți asupra organismului de credit și plăți de dobândă. Inițial, plățile de dobândă sunt calculate pe întreaga sumă a împrumutului, dar pe măsură ce suma împrumutului scade, la fel și dobânda acumulată. Dar suma totală a plății rămâne neschimbată din cauza creșterii lunare a sumei plăților la organismul de credit. Astfel, în timp, ponderea dobânzii în plata totală lunară scade, iar ponderea plății pe organism crește. În același timp, plata lunară totală în sine nu se modifică pe toată durata împrumutului.

Ne vom concentra pe calcularea plății anuității. În plus, acest lucru este relevant, deoarece în prezent majoritatea băncilor folosesc această schemă specială. Este convenabil și pentru clienți, deoarece în acest caz suma totală de plată nu se modifică, rămânând fixă. Clienții știu întotdeauna cât să plătească.

Etapa 1: Calculul ratei lunare

Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială - PLT. Face parte din categoria operatorilor financiari. Formula pentru această funcție este următoarea:

PLT(rata;nper;ps;bs;tip)

După cum puteți vedea, această funcție are destul un număr mare argumente. Adevărat, ultimele două dintre ele nu sunt obligatorii.

Argument "Ofertă" indică rata dobânzii pentru o anumită perioadă. Dacă, de exemplu, se utilizează o rată anuală, dar plata împrumutului se face lunar, atunci rata anuală trebuie împărțită la 12 și folosiți rezultatul ca argument. Dacă se utilizează un tip de plată trimestrial, atunci rata anuală trebuie împărțită la 4 etc.

"Nper" indică numărul total de perioade de plată a creditului. Adică dacă împrumutul este contractat pe un an cu plăți lunare, atunci se ia în considerare numărul de perioade 12 , dacă pentru doi ani, atunci numărul de perioade este 24 . Dacă împrumutul este contractat pe doi ani cu plată trimestrială, atunci numărul perioadelor este egal cu 8 .

"Ps" indică valoarea actuală la momentul actual. Vorbitor în cuvinte simple, aceasta este suma totală a împrumutului la începutul împrumutului, adică suma pe care o împrumutați, excluzând dobânda și alte plăți suplimentare.

"Bs" este valoarea viitoare. Aceasta este suma care va fi corpul împrumutului la momentul finalizării contractului de împrumut. În cele mai multe cazuri, acest argument este egal cu «0» , întrucât împrumutatul trebuie să plătească împrumutătorului integral la sfârșitul termenului împrumutului. Argumentul specificat este opțional. Prin urmare, dacă este omis, este considerat egal cu zero.

Argument "Tip" determină timpul de calcul: la sfârşitul sau la începutul perioadei. În primul caz ia valoarea «0» , iar în al doilea - "1". Majoritatea instituțiilor bancare folosesc opțiunea de plată la sfârșitul perioadei. Acest argument este, de asemenea, opțional și, dacă este omis, este considerat a fi zero.

Acum este timpul să trecem la exemplu concret calcularea ratei lunare folosind funcția PMT. Pentru a face calculul, folosim un tabel cu datele inițiale, care arată rata dobânzii la împrumut ( 12% ), suma împrumutului ( 500.000 de ruble) și termenul împrumutului ( 24 de luni). Plata se face lunar la sfarsitul fiecarei perioade.

Selectați elementul de pe foaia în care va fi afișat rezultatul calculului și faceți clic pe pictogramă „Inserare funcție”, situat lângă bara de formule.

Fereastra se deschide Vrăjitorii de funcții. În categorie "Financiar" evidențiați numele "PLT"și apăsați butonul "BINE".

După aceasta, se deschide fereastra de argumente operator PLT.
În câmp "Ofertă" Trebuie să introduceți rata dobânzii pentru perioada respectivă. Acest lucru se poate face manual prin simpla setare a unui procent, dar în cazul nostru este indicat într-o celulă separată de pe foaie, așa că vom da un link către acesta. Plasați cursorul în câmp, apoi faceți clic pe celula corespunzătoare. Dar, după cum ne amintim, rata anuală a dobânzii este stabilită în tabelul nostru, iar perioada de plată este egală cu o lună. Prin urmare, împărțim rata anuală, sau mai bine zis legătura cu celula în care este conținută, la număr 12 , corespunzător numărului de luni dintr-un an. Efectuăm împărțirea direct în câmpul ferestrei de argumente.

În câmp "Nper" Termenul împrumutului este stabilit. El este egal cu noi 24 luni. Puteți introduce un număr în câmp 24 manual, dar noi, ca și în cazul precedent, indicăm o legătură către locația acestui indicator în tabelul sursă.

În câmp "Ps" este indicată suma inițială a împrumutului. Este egal 500.000 de ruble. Ca și în cazurile anterioare, indicăm un link către elementul foaie care conține acest indicator.

În câmp "Bs" Valoarea împrumutului este indicată după plata integrală. După cum ne amintim, această valoare este aproape întotdeauna egală cu zero. Setați un număr în acest câmp «0» . Deși acest argument poate fi omis cu totul.

În câmp "Tip" indicați la începutul sau sfârșitul lunii în care se face plata. În cazul nostru, ca în majoritatea cazurilor, se face la sfârșitul lunii. Prin urmare, setăm numărul «0» . Ca și în cazul argumentului anterior, nu puteți introduce nimic în acest câmp, atunci programul va presupune implicit că conține o valoare egală cu zero.

După ce au fost introduse toate datele, faceți clic pe butonul "BINE".

După aceasta, rezultatul calculului este afișat în celula pe care am evidențiat-o în primul paragraf al acestui ghid. După cum puteți vedea, plata totală lunară a împrumutului este 23536,74 ruble. Nu vă lăsați confundați de semnul „-” din fața acestei sume. Deci Excel indică faptul că aceasta este o cheltuială a fondurilor, adică o pierdere.

Pentru a calcula suma totală a plății pentru întregul termen al împrumutului, ținând cont de rambursarea organismului de credit și de dobânda lunară, este suficient să înmulțim suma de plată lunară ( 23536,74 ruble) după numărul de luni ( 24 de luni). După cum puteți vedea, suma totală a plăților pentru întreaga perioadă de împrumut în cazul nostru a fost 564881,67 ruble.

Acum puteți calcula suma plății în exces a împrumutului. Pentru a face acest lucru, trebuie să scădeți suma inițială împrumutată din suma totală a plăților împrumutului, inclusiv dobânda și organismul de împrumut. Dar ne amintim că prima dintre aceste valori este deja semnată «-» . Prin urmare, în cazul nostru particular, se dovedește că trebuie adăugate. După cum puteți vedea, suma totală a plății în exces a împrumutului pentru întregul termen a fost 64881,67 ruble.

Etapa 2: detalii de plată

Și acum, cu ajutorul altor operatori Excel, vom face detalii lunare ale plăților pentru a vedea cât de mult plătim într-o anumită lună pentru organismul de credit și cât este suma dobânzii. În aceste scopuri, desenăm un tabel în Excel pe care îl vom completa cu date. Rândurile acestui tabel vor corespunde perioadei corespunzătoare, adică lunii. Având în vedere că perioada noastră de creditare este 24 lună, atunci și numărul de linii va fi adecvat. Coloanele indică rambursarea organismului de credit, plata dobânzii, plata totală lunară, care este suma celor două coloane anterioare, și suma rămasă de plătit.

Pentru a determina valoarea plății pentru organismul de împrumut, folosim funcția OSPLT, care este tocmai destinat acestor scopuri. Plasați cursorul într-o celulă care se află în linie "1" iar în coloană „Plata pe corpul împrumutului”. Faceți clic pe butonul „Inserare funcție”.

Să mergem la Expertul de funcții. În categorie "Financiar" marcați numele "OSPLT"și apăsați butonul "BINE".

Se deschide fereastra pentru argumentele operatorului OSPT. Are următoarea sintaxă:
OSPLT(Rata;Perioada;Nper;Ps;Bs)

După cum puteți vedea, argumentele acestei funcții coincid aproape complet cu argumentele operatorului PLT, doar în loc de un argument opțional "Tip" argument necesar adăugat "Perioadă". Indică numărul perioadei de plată și, în cazul nostru particular, numărul lunii.

Completarea câmpurilor din fereastra de argumente ale funcției care ne sunt deja familiare OSPLT aceleași date care au fost utilizate pentru funcție PLT. Luând în considerare faptul că în viitor formula va fi copiată folosind un marcator de umplere, trebuie să faceți absolute toate referințele din câmpuri, astfel încât să nu se schimbe. Acest lucru necesită plasarea unui semn dolar în fața fiecărei valori de coordonate verticale și orizontale. Dar este mai ușor să faci asta prin simpla evidențiere a coordonatelor și apăsând tasta funcțională F4. Semnul dolarului va fi plasat automat în locurile potrivite. De asemenea, nu uitați că rata anuală trebuie împărțită la 12 .

Dar mai avem încă un argument nou pe care funcția nu l-a avut PLT. Acest argument "Perioadă". În câmpul corespunzător, setați o legătură către prima celulă a coloanei "Perioadă". Acest element de foaie conține un număr "1", care indică numărul primei luni de împrumut. Dar spre deosebire de câmpurile anterioare, în câmpul specificat lăsăm legătura relativă și nu o facem absolută.
După ce toate datele despre care am vorbit mai sus au fost introduse, faceți clic pe butonul "BINE".

După aceasta, celula pe care am evidențiat-o anterior va afișa suma plății creditului pentru prima lună. Se va ridica la 18536,74 ruble.

Apoi, după cum sa menționat mai sus, ar trebui să copiem această formulă în celulele rămase ale coloanei folosind mânerul de umplere. Pentru a face acest lucru, plasați cursorul în colțul din dreapta jos al celulei care conține formula. Cursorul este convertit într-o cruce, care se numește marcator de umplere. Țineți apăsat butonul stâng al mouse-ului și trageți-l în jos până la sfârșitul tabelului.

Ca rezultat, toate celulele coloanei sunt umplute. Acum avem un program lunar de rambursare a creditului. După cum sa menționat mai sus, valoarea plății conform acestui articol crește cu fiecare nouă perioadă.

Acum trebuie să facem un calcul lunar al plăților dobânzilor. În aceste scopuri vom folosi operatorul PRPLT. Selectați prima celulă goală din coloană „Plata dobânzii”. Faceți clic pe butonul „Inserare funcție”.

În fereastra deschisă Vrăjitorii de funcțiiîn categorie "Financiar" scoatem in evidenta numele PRPLT. Faceți clic pe butonul "BINE".

Se deschide fereastra cu argumente ale funcției PRPLT. Sintaxa sa arată astfel:
PRPLT(Rata;Perioada;Nper;Ps;Bs)

După cum puteți vedea, argumentele acestei funcții sunt absolut identice cu elemente similare ale operatorului OSPLT. Prin urmare, pur și simplu introducem în fereastră aceleași date pe care le-am introdus în fereastra de argumente anterioară. Nu uitați că linkul din câmp "Perioadă" trebuie să fie relative, iar în toate celelalte câmpuri coordonatele trebuie convertite în formă absolută. După aceasta, faceți clic pe butonul "BINE".

Apoi rezultatul calculării sumei plății dobânzii pentru împrumutul pentru prima lună este afișat în celula corespunzătoare.

Folosind marcatorul de umplere, copiem formula in elementele ramase ale coloanei, obtinand astfel un grafic lunar de plati a dobanzii pentru credit. După cum vedem, după cum sa spus mai devreme, valoarea acestui tip de plată scade de la lună la lună.

Acum trebuie să calculăm plata totală lunară. Pentru acest calcul, nu trebuie să apelați la niciun operator, deoarece puteți utiliza o formulă aritmetică simplă. Adăugați conținutul celulelor din prima lună a coloanelor „Plata pe corpul împrumutului”Şi „Plata dobânzii”. Pentru a face acest lucru, instalăm un semn «=» la prima celulă goală a coloanei „Plata lunară totală”. Apoi faceți clic pe cele două elemente de mai sus, plasând un semn între ele «+» . Apăsați butonul Intră.

Apoi, folosind marcatorul de umplere, ca și în cazurile anterioare, umplem coloana cu date. După cum puteți vedea, pe toată durata contractului, suma totală a plății lunare, inclusiv plata organismului de credit și plata dobânzii, va fi 23536,74 ruble. De fapt, am calculat deja acest indicator mai devreme folosind PLT. Dar în acest caz este prezentat mai clar, și anume ca sumă de plată pentru organismul de împrumut și dobândă.

Acum trebuie să adăugați date în coloana în care va fi afișat lunar soldul sumei împrumutului care mai trebuie plătită. În prima celulă a coloanei „Sold de plătit” calculul va fi cel mai simplu. Trebuie să scădem plata împrumutului pentru prima lună din tabelul de calcul din suma inițială a împrumutului, care este indicată în tabelul cu date primare. Dar, dat fiind faptul că unul dintre numerele noastre are deja un semn «-» , atunci nu trebuie scăzute, ci adăugate. Facem asta și apăsăm butonul Intră.

Dar calcularea soldului care urmează să fie plătit după luna a doua și următoarea va fi ceva mai complicată. Pentru a face acest lucru, trebuie să scădem suma totală a plăților la organismul de împrumut pentru perioada anterioară din organismul de împrumut la începutul împrumutului. Instalarea unui semn «=» în a doua celulă a coloanei „Sold de plătit”. În continuare, indicăm un link către celula care conține suma inițială a împrumutului. Fă-l absolut selectându-l și apăsând tasta F4. Apoi punem un semn «+» , deoarece a doua noastră valoare va fi deja negativă. După aceasta, faceți clic pe butonul „Inserare funcție”.

Începe Expertul de funcții, în care trebuie să treci la categorie "Matematic". Acolo scoatem in evidenta inscriptia "SUMĂ"și apăsați butonul "BINE".

Se deschide fereastra cu argumente ale funcției SUMĂ. Operatorul specificat este folosit pentru a rezuma datele din celule, ceea ce trebuie să facem în coloană „Plata pe corpul împrumutului”. Are următoarea sintaxă:
SUM(număr1;număr2;…)

Argumentele sunt referințe la celulele care conțin numere. Plasam cursorul in camp „Numărul 1”. Apoi țineți apăsat butonul stâng al mouse-ului și selectați primele două celule ale coloanei de pe foaie „Plata pe corpul împrumutului”. După cum puteți vedea, câmpul afișează un link către interval. Este format din două părți, separate prin două puncte: o referință la prima celulă din interval și o referire la ultima. Pentru a putea copia formula specificată folosind un marcator de umplere în viitor, facem ca prima parte a intervalului de referință să fie absolută. Selectați-l și apăsați tasta funcțională F4. Lăsăm a doua parte a linkului relativ. Acum, când utilizați mânerul de umplere, prima celulă din interval va fi înghețată, iar ultima celulă se va întinde pe măsură ce vă deplasați în jos. De asta avem nevoie pentru a ne atinge obiectivele. Apoi, faceți clic pe butonul "BINE".

Deci, rezultatul soldului datoriei creditare după a doua lună este afișat în celulă. Acum, pornind de la această celulă, copiem formula în elementele goale ale coloanei folosind marcatorul de umplere.

Calculul lunar al soldurilor creditelor se face pe întreaga perioadă a creditului. După cum era de așteptat, la sfârșitul termenului această sumă este zero.

Astfel, nu am calculat doar plata creditului, ci am organizat un fel de calculator de credit. Care va funcționa conform schemei de anuitate. Dacă, de exemplu, modificăm valoarea împrumutului și rata dobânzii anuale în tabelul inițial, atunci datele vor fi recalculate automat în tabelul final. Prin urmare, poate fi folosit nu doar o singură dată pentru un anumit caz, ci poate fi folosit în diverse situații pentru a calcula opțiunile de credit în cadrul schemei de anuitate.

După cum puteți vedea, folosind programul Excel acasă, puteți calcula cu ușurință plata totală lunară a împrumutului în cadrul unei scheme de anuitate, folosind operatorul în aceste scopuri PLT. În plus, folosind funcțiile OSPLTŞi PRPLT Puteți calcula suma plăților asupra organismului de împrumut și dobânda pentru o perioadă specificată. Folosind împreună toată această gamă de funcții, este posibil să creați un calculator puternic de împrumut care poate fi folosit de mai multe ori pentru a calcula plata anuității.

Sintaxa și caracteristicile funcției PMT

Sintaxa funcției: rate; nper; ps; [bs]; [tip].

Decodificarea argumentelor:

Rata este dobânda la credit.
Nper – numărul total de plăți ale împrumutului.
Ps – valoarea actuală, egală cu un număr de plăți viitoare (suma împrumutului).
Fs este valoarea viitoare a împrumutului după ultima plată (dacă argumentul este omis, se presupune că valoarea viitoare este 0).
Tip – un argument opțional care specifică dacă plata se face la sfârșitul perioadei (valoare 0 sau absent) sau la început (valoare 1).

Caracteristici ale funcționării PMT:

Doar plățile de principal și plățile de dobândă sunt incluse în calculul plății periodice. Nu include taxe, taxe, taxe suplimentare sau plăți de rezervă asociate uneori cu un împrumut.
La specificarea argumentului „Rata”, este necesar să se țină cont de frecvența calculării dobânzii. Pentru un credit de 6%, rata trimestrială este de 6%/4; pentru o rată lunară – 6%/12.
Argumentul „Nper” indică numărul total de plăți ale împrumutului. Dacă o persoană face plăți lunare pentru un împrumut pe trei ani, atunci valoarea 3*12 este folosită pentru a stabili argumentul.

Exemple de funcție PMT în Excel

Pentru ca funcția să funcționeze corect, trebuie să introduceți corect datele inițiale:

Faceți clic pe butonul fx („Insert function”). Se deschide fereastra Function Wizard. În categoria „Financial”, selectați funcția PMT. Completați argumentele:

Fiţi atenți! În câmpul „Rate”, valoarea dobânzii anuale este împărțită la 12: plățile împrumutului se fac lunar.

Plățile lunare ale împrumutului în conformitate cu condițiile specificate ca argumente se ridică la 1.037,03 RUB.

Pentru a afla suma totală care trebuie plătită pentru întreaga perioadă (principal plus dobândă), înmulțiți plata lunară a împrumutului cu valoarea „Nper”:

Să excludem din calculul plăților lunare ale creditului plata efectuată la începutul perioadei:

Pentru a face acest lucru, trebuie să specificați valoarea 1 ca argument „Tip”.

Detaliem calculul folosind funcțiile OSPT și PRPLT. Folosind primul vom arăta corpul împrumutului, iar folosind al doilea vom arăta dobânda.

Pentru un calcul detaliat, să creăm un tabel:

Să calculăm corpul de împrumut folosind funcția OSPLT. Argumentele sunt completate prin analogie cu funcția PMT:

În câmpul „Perioada”, indicați numărul perioadei pentru care se calculează datoria principală.

Completam argumentele funcției PRPLT în același mod:

Duplicăm formulele până la ultima perioadă. Pentru a calcula plata totală, însumăm corpul creditului și dobânda.

Calculăm soldul datoriei principale. Obținem un tabel ca acesta:

Plata totală a împrumutului este aceeași cu plata lunară calculată folosind funcția PMT. Aceasta este o valoare constantă, deoarece Utilizatorul a contractat un împrumut anual.

Astfel, funcția PMT poate fi folosită pentru a calcula plățile lunare ale depozitelor sau plățile la credite, cu condiția ca rata dobânzii și sumele să fie constante.