Exemple de funcție PMT în Excel: calcularea plăților pentru un împrumut de anuitate. Aplicarea funcțiilor plt (fostul pmt) și protsplat (fostul plprots) în procesorul de foi de calcul ms Excel Funcția financiară pmt

Acasă

Î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 Excel pot ajuta la acest calcul. 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 dobânzilor 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 un număr destul de mare de argumente. Adevărat, ultimele două dintre ele nu sunt obligatorii. Argument 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. Cu 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.

După cum puteți vedea, această funcție are un număr destul de mare de argumente. Adevărat, ultimele două dintre ele nu sunt obligatorii. "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 un exemplu specific de calcul al 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.

1. 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.



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



3. După aceasta, se deschide fereastra de argumente operator Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -.

   În câmp Argument 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".



4. 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.



5. 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.



6. 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 specific, 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 împrumut 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.

1. 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”.



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



3. 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 Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -, 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 Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -. 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ă faceți acest lucru 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 .



4. Dar mai avem încă un argument nou pe care funcția nu l-a avut Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -. Acest argument "Perioadă". În câmpul corespunzător, setați un link 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".



5. 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.



6. Apoi, după cum am 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 un 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.



7. 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ă.



8. 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”.



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



10. 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".



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



12. Folosind marcatorul de umplere, copiem formula în elementele rămase ale coloanei, obținând astfel un program lunar de plăți a dobânzii pentru credit. După cum vedem, după cum sa spus mai devreme, valoarea acestui tip de plată scade de la lună la lună.



13. 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ă.



14. 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 Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -. Dar în acest caz este prezentat mai clar, și anume ca sumă de plată pentru organismul de împrumut și dobândă.



15. 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 acestea nu trebuie scăzute, ci adăugate. Facem asta și apăsăm butonul Intră.



16. 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”.



17. Î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".



18. 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".



19. 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.



20. 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 Pentru a calcula contribuția lunară atunci când utilizați o schemă de anuitate în Excel, există o funcție specială -. Î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.

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 Calculul 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 What-if vă permite să preziceți valoarea unei funcții (matematică, financiară, statistică 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 RUB.

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 vom obține 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, NS, 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%, iar durata împrumutului de la 5 la 10 ani.

2 Efectuați o analiză „Ce ar fi dacă” folosind tabelul 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

Excel este un instrument cu adevărat puternic datorită versatilității și capacității sale unice de a rezolva probleme pentru oameni din diferite domenii profesionale. Excel este indispensabil pentru manageri și economiști, antreprenori și finanțatori, contabili și analiști, matematicieni și ingineri. Versatilitatea sa este dată de funcții specifice încorporate pe care anumiți specialiști le folosesc în calculele lor.

Una dintre cele mai mari și mai populare categorii de caracteristici este cea financiară. Cea mai recentă versiune de Excel are 55 de funcții care se încadrează în acest grup. Multe dintre ele sunt specifice și limitate, dar unele pot fi utile aproape tuturor. Una dintre aceste funcții de bază este PMT.

După cum spune certificatul oficial,Funcția PMT returnează suma plății periodice pentru o anuitate bazată pe sume de plată constante și o rată constantă a dobânzii. Dacă sunteți confuz de termenul specific „rentă” - nu vă alarmați. Cu alte cuvinte, folosind funcția PMT, puteți calcula suma care va trebui plătită în fiecare lună, cu condiția ca dobânda la credit să nu se modifice și plățile să fie efectuate regulat în sume egale.

Sintaxa funcției

Funcția are următoarea sintaxă:

PMT(rata; nper; ps; [bs]; [tip])

Să ne uităm la toate argumentele unul câte unul:

• Licitați.Argument necesar. Reprezintă rata dobânzii pentru perioada respectivă. Cel mai important lucru aici este să nu faceți o greșeală în recalcularea mărimii pariului pentru perioada necesară. Dacă intenționați să rambursați împrumutul în plăți lunare și rata anuală, atunci aceasta trebuie convertită într-o rată lunară, împărțind la 12. Dacă, de exemplu, împrumutul este rambursat o dată pe trimestru, atunci rata anuală trebuie împărțită. cu 4 (și astfel obțineți rata pentru 1 trimestru). Rata poate fi specificată ca procent sau sutimi.
• Nper.Necesar. Acest argument reprezintă numărul de perioade de facturare (de câte ori vor fi efectuate plăți pentru rambursarea împrumutului). La fel ca și rata, acest argument depinde de perioada de decontare adoptată pentru calcule. Dacă împrumutul este primit pe 5 ani cu plăți o dată pe lună, atunciNper = 5*12 = 60 de perioade . Dacă pentru 3 ani, cu plăți o dată pe trimestru, atunciNper = 3*4 = 12 perioade .
• Ps. Necesar. Suma împrumutului, adică suma datoriei care va trebui rambursată cu plăți viitoare.
• [bs].Opțional. Suma datoriei care trebuie să rămână neachitată după expirarea tuturor perioadelor de facturare. De obicei, acest argument este 0 (împrumutul trebuie rambursat integral). Deoarece argumentul este opțional, acesta poate fi omis (în acest caz va fi luat egal cu zero).
• [tip].Opțional. Indică momentul plății - la începutul sau la sfârșitul perioadei. Pentru primul caz, trebuie să specificați unul, iar pentru al doilea, zero (sau să omiteți cu totul acest argument). În cele mai multe cazuri, se folosește a doua opțiune - plăți la sfârșitul perioadei, ceea ce înseamnă că cel mai adesea acest argument poate fi omis.

O caracteristică specială a sintaxei funcției este indicarea direcției fluxului de numerar. Dacă fluxul de numerar este încasat (de exemplu, suma împrumutului primit, specificată în argumentul Ps), atunci acesta trebuie indicat ca număr pozitiv. Fluxurile de ieșire, dimpotrivă, sunt indicate ca numere negative (de exemplu, după calcul, funcția PMT va returna un rezultat negativ, deoarece suma plății creditului este un flux de numerar de ieșire).

Exemple de utilizare

Sarcina 1. Calcularea sumei plăților împrumutului

Să presupunem că o bancă a primit un împrumut în valoare1 000 000 freca. sub 17,5% pe an pentru o perioadă 6 ani. Împrumutul va fi rambursat în plăți lunare egale pe toată durata împrumutului. Până la sfârșitul termenului, se va plăti întreaga sumă a datoriei. Prima plată va fi efectuată la sfârșitul primei perioade. Trebuie să găsiți suma plății lunare.

Deci, cunoaștem rata anuală, iar împrumutul va fi rambursat lunar. Aceasta înseamnă că pentru a calcula, va trebui să convertim rata anuală într-o rată lunară, împărțind 17,5% la 12 luni.În primul argument scriem 17,5%/12 .

Împrumutul a fost primit pe 6 ani. Plătit lunar. Aceasta înseamnă că numărul de perioade de plată = 6*12.În al doilea argument scriem 72 .

În al treilea argument scriem suma împrumutului. Este egal cu 1.000.000 de ruble. (pentru debitor acesta este un flux de numerar primit, îl indicăm ca un număr pozitiv).

Vom omite al patrulea argument, deoarece suma va fi rambursată integral până la sfârșitul termenului. Vom omite și al cincilea argument, deoarece plățile se fac la sfârșitul perioadei.

Formula va arăta astfel:

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

Rezultatul calculului este-22526,05 RUR. Numărul este negativ, deoarece plata împrumutului este un flux de numerar de ieșire pentru împrumutat. Aceasta este suma care va trebui plătită în fiecare lună pentru a rambursa împrumutul descris în condiții.

Pentru a calcula suma plății în plus finale, trebuie să înmulțiți plata lunară cu numărul de perioade (Nper) și să scădeți suma împrumutului (Ps) din rezultat.

Sarcina 2. Calcularea sumei de reîncărcare a depozitului pentru a acumula o anumită sumă de fonduri

Banca a deschis un depozit rambursabil cu o rată de 9% pe an. Intenționați să depuneți aceeași sumă de bani în fiecare trimestru (de exemplu, o parte din bonusul trimestrial primit) cu scopul de a acumula exact 1.000.000 de ruble în cont în 4 ani. Întrebare: Cât de mult ar trebui să-mi încarc contul în fiecare trimestru?

Indicăm primul argument ca fiind 9%/4 (deoarece rata anuală trebuie convertită într-o rată trimestrială), al doilea argument = 4*4 (4 ani, 4 trimestre - în total 16 contribuții). Al treilea argument este valoarea creditului. O luăm ca 0, deoarece nu am luat nimic. Al patrulea argument este valoarea viitoare. Indicăm suma pe care dorim să o economisim (1.000.000 RUB). Omitem din nou cel de-al cincilea argument (plăți la sfârșitul perioadei, aceasta este situația cea mai frecventă).

Obtinem formula:

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

Rezultatul calculului:-52.616,63 rub.Această sumă trebuie depusă în depozitul specificat în fiecare trimestru pentru a avea un milion de ruble în cont după patru ani.

Suma totală a fondurilor depuse = 52616,63 * 16 = 841,866,08 ruble. Restul se acumulează prin dobândă.

Caracteristici Caracteristici

Când utilizați funcția, acordați atenție următoarelor puncte:

• funcția este destinată numai plăților de anuitate (adică plăți egale la intervale regulate);
• funcția funcționează după modelul clasic de credit, care nu coincide întotdeauna cu ceea ce oferă organizațiile moderne de credit. În multe cazuri, condițiile de împrumut nu vă vor permite să le aplicați cu succes funcția PLT și va trebui să scrieți un model separat și să căutați o soluție folosindSelectarea parametrilor sau Găsirea unei soluții(crearea unui model similar poate fi comandată pe site-ul nostru - tDots.ru);
• funcția ține cont de plata principalului și a dobânzilor acumulate, dar nu ia în considerare diverse taxe suplimentare, comisioane, impozite și taxe etc.;
• semnul numărului (pozitiv sau negativ) specifică direcția fluxului de numerar. Fluxul de la creditor la debitor (de exemplu, suma împrumutului) va avea un semn, iar fluxul de la debitor la creditor (de exemplu, suma de rambursare lunară) va avea semnul opus (nu contează). fie că este plus sau minus).

Puteți susține proiectul nostru și dezvoltarea lui ulterioară .

Puteți adresa întrebări despre articol prin intermediul botului nostru de feedback laTelegramă:

Funcția PMT ( ) , versiunea în limba engleză a PMT(), vă permite să calculați valoarea lunară a plății creditului în cazul plăților de anuitate (când împrumutul este plătit în rate egale).

Un bloc de articole dedicat teoriei și calculelor parametrilor anuității. Acest articol acoperă doar sintaxa și exemplele de utilizare a funcției PMT().

Sintaxa funcțieiPMT()

PMT(rata; nper; ps; [bs]; [tip])

• Licitați. Rata dobânzii la un împrumut (împrumut).
• Nper. Numărul total de plăți ale împrumutului.
• ps. Suma împrumutului.
• Bs. Argument opțional. Soldul împrumutului necesar după ultima plată. Dacă acest argument este omis, se presupune că este 0 (împrumutul va fi rambursat integral).
• Tip. Argument opțional. Ia valoarea 0 (zero) sau 1. Dacă =0 (sau omis), atunci se presupune că plata obișnuită se face la sfârșitul perioadei, dacă 1, atunci la începutul perioadei (suma plata obișnuită va fi puțin mai mică).

Plățile returnate de funcția PMT() includ plăți de principal și dobânzi, dar nu includ impozite, plăți de rezervă sau comisioane asociate uneori cu un împrumut.

Exemplul 1

Să presupunem că o persoană intenționează să contracteze un împrumut în valoare de 50.000 de ruble. (celula B8 ) în bancă la 14% pe an ( B6 ) timp de 24 de luni ( B7 ) (vezi exemplu de fișier).

Calculul sumei de plată lunară pentru un astfel de împrumut utilizând funcția PMT().

PLT(B6/12;B7;B8)

SFATURI :
Asigurați-vă că sunteți consecvent în alegerea dvs. de unități de timp pentru a specifica argumentele ratei și nper. În cazul nostru calculăm lunar plăți pentru un împrumut pe doi ani (24 luni) la rata de 14 la sută pe an ( 14% / 12 luni).

Calcularea sumei de plată lunară pentru un astfel de împrumut utilizând funcția FĂRĂ PMT()

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

Pentru a afla suma plătită în exces, înmulțiți valoarea returnată de funcția PLT() cu „nper” (veți obține un număr cu semnul minus) și adăugați suma împrumutului. În cazul nostru, supraplata va fi de 7.615,46 ruble. (timp de 2 ani).

Exemplul 2

Să presupunem că o persoană plănuiește să economisească bani în fiecare lună pentru a economisi în 5 ani (celula E7 ) 1 milion de ruble ( E8 ). El plănuiește să ducă banii la bancă în fiecare lună și să-și completeze depozitul. Banca are o rată a dobânzii de 10% ( E6 ) iar persoana consideră că va rămâne în vigoare fără modificări timp de 5 ani. Cât de mult ar trebui să plătească o persoană băncii în fiecare lună pentru a economisi 1 milion de ruble în 5 ani? (vezi exemplu de fișier).

Funcția PMT returnează suma plății periodice pentru o anuitate bazată pe sume de plată constante și rate constante ale dobânzii. 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ă: Este reprezentat ca un număr negativ în cazul plății de fonduri și un număr pozitiv în cazul primirii de fonduri. 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.