Calculul plății anuității în Excel

Înainte de a lua împrumutul, ar fi plăcut să calculați toate plățile pe acesta. Acesta va salva debitorul în viitor de la diferite probleme și dezamăgiri neașteptate atunci când se dovedește că suprapunerea este prea mare. Ajutorul la acest calcul poate instrumentele programului Excel. Să aflăm cum să calculam plățile anuitate asupra împrumutului în acest program.

Calcularea plății

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

• Diferențiate;
• Anuitate.

Cu o schemă diferențiată, clientul aduce o pondere egală lunară a plăților pe corpul unui împrumut, plus plățile la dobândă. Mărimea plăților de dobânzi în fiecare lună scade, deoarece organismul împrumutului este redus de la care se calculează. Astfel, plata lunară globală este, de asemenea, redusă.

ANN AUNT Schema utilizează o abordare ușor diferită. Clientul face o singură sumă a plății totale lunare, care constă în plăți pe corpul unui împrumut și plata dobânzilor. Inițial, contribuțiile la dobânzi sunt numerotate pe întreaga sumă a împrumutului, dar pe măsură ce organismul scade, interesul este redus și acumulator de dobândă. Dar suma totală a plății rămâne neschimbată din cauza unei creșteri lunare a valorii plăților de către organismul împrumutului. Astfel, în timp, proporția de interes în totalul plății lunare, iar greutatea proporției corpului crește. În același timp, plata lunară generală nu se schimbă pe parcursul termenului de credit.

Doar la calcularea plății anuitate, ne vom opri. Mai ales, acest lucru este relevant, deoarece majoritatea băncilor folosesc această schemă particulară. Este convenabil pentru clienți, deoarece, în acest caz, suma totală de plată nu se modifică, rămânând fixată. Clienții știu întotdeauna cât de mult trebuie să plătiți.

Pasul 1: calculul lunar al contribuției

Pentru a calcula o contribuție lunară la utilizarea unui circuit de anuitate în Excelență, există o funcție specială - PPT. Se referă la categoria operatorilor financiari. Formula acestei caracteristici este după cum urmează:

= PPT (rata; kper; ps; bs; tip)

După cum vedem, funcția specificată are un număr destul de mare de argumente. Adevărat, ultimele două dintre ele nu sunt obligatorii.

Argumentul "rata" indică o rată procentuală 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ă în 12 și rezultatul este utilizat ca argument. Dacă se aplică un tip trimestrial de plată, atunci în acest caz pariul anual trebuie împărțit în 4, etc.

"CPR" înseamnă numărul total de perioade de plăți de împrumut. Adică dacă împrumutul este luat timp de un an cu plata lunară, atunci numărul de perioade este considerat 12, dacă doi ani, atunci numărul de perioade - 24. Dacă împrumutul este luat timp de doi ani cu plată trimestrială, atunci Numărul de perioade este de 8.

"PS" indică valoarea actuală în acest moment. Vorbind cu cuvinte simple, aceasta este suma totală a împrumutului la începutul creditării, adică suma pe care o împrumutat, cu excepția dobânzilor și a altor plăți suplimentare.

"BS" este un cost viitor. Această valoare va fi un organism de împrumut în momentul finalizării contractului de împrumut. În majoritatea cazurilor, acest argument este "0", deoarece Împrumutatul de la sfârșitul perioadei de credit ar trebui să se stabilească pe deplin cu creditorul. Argumentul specificat nu este obligatoriu. Prin urmare, dacă este descendent, este considerat zero.

Argumentul "tip" determină timpul de calcul: la sfârșit sau la începutul perioadei. În primul caz, este nevoie de valoarea "0", iar în al doilea - "1". Majoritatea instituțiilor bancare folosesc exact opțiunea cu plata la sfârșitul perioadei. Acest argument este, de asemenea, opțional, iar dacă este omis, se crede că este zero.

Acum este timpul să treceți la un exemplu specific de calcul al unei contribuții lunare utilizând funcția PL. Pentru a calcula, folosim o masă cu datele sursă, unde este indicată rata dobânzii la împrumut (12%), valoarea creditului (500.000 de ruble) și perioada de împrumut (24 de luni). În același timp, plata este făcută lunar la sfârșitul fiecărei perioade.

1. Selectați elementul de pe foaie în care va fi afișat rezultatul rezultat și faceți clic pe pictograma "Inserare Funcție", amplasată în apropierea rândului de formula.



2. Fereastra Winder Wizard este lansată. În categoria "Financial" alocați numele "Plt" și faceți clic pe butonul "OK".



3. După aceea, deschide fereastra argumentelor operatorului PL.

   În câmpul "ritm", trebuie să introduceți valoarea procentuală pentru perioada respectivă. Acest lucru se poate face manual, punând doar un procent, dar este indicat într-o celulă separată pe foaie, așa că vom da o legătură cu ea. Instalați cursorul în câmp, apoi faceți clic pe celula corespunzătoare. Dar, după cum ne amintim, avem rata anuală a dobânzii în tabelul nostru, iar perioada de plată este egală cu luna. Prin urmare, împărțim pariul anual și mai degrabă o legătură cu celula în care este conținută de numărul 12, care corespunde numărului de luni în anul respectiv. Divizia rulează direct în câmpul ferestrei argumentare.

   În câmpul CPR, creditarea este stabilită. El este egal cu 24 de luni. Puteți aplica în câmpul manual numărul 24, dar, ca în cazul precedent, specificăm un link către locația acestui indicator în tabelul sursă.

   În câmpul "PS" indică valoarea inițială a împrumutului. Este egal cu 500.000 de ruble. Ca și în cazurile anterioare, specificăm o legătură cu elementul de frunze, care conține acest indicator.

   În câmpul "BS" indică amploarea împrumutului, după plata completă. După cum vă amintiți, această valoare este aproape întotdeauna zero. Instalați în acest câmp numărul "0". Deși acest argument poate fi, în general, omis.

   În câmpul "Tip", specificăm la început sau la sfârșitul lunii se efectuează o plată. Noi, ca în majoritatea cazurilor, este produs la sfârșitul lunii. Prin urmare, am stabilit numărul "0". Ca și în cazul argumentului anterior, este posibil să introduceți nimic în acest domeniu, atunci programul implicit va presupune că este zero egal cu acesta.

   După introducerea tuturor datelor, apăsați butonul "OK".



4. După aceea, în celula pe care am alocat-o în primul paragraf din acest manual, este afișat rezultatul calculului. După cum puteți vedea, amploarea plății generale lunare pe împrumut este de 23536,74 ruble. Să nu confundați semnul "-" înainte de această sumă. Deci, exilul indică faptul că acesta este fluxul de bani, adică o pierdere.



5. Pentru a calcula valoarea totală a plății pentru întreaga perioadă de împrumut, ținând seama de rambursarea corpului unui împrumut și un interes lunar, mai degrabă multiplicarea valorii plății lunare (23536,74 ruble) pentru 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 de 564881.67 ruble.



6. Acum puteți calcula valoarea plății de plată a împrumutului. Pentru a face acest lucru, este necesar să se elimine de la valoarea totală a plăților la împrumut, inclusiv interesele și corpul de împrumut, suma inițială revendicată. Dar ne amintim că prima dintre aceste valori deja cu semnul "-". Prin urmare, în mod specific, cazul nostru se dovedește că trebuie să fie pliate. După cum vedem, plățile totale de plată a împrumutului pe întreaga perioadă a fost de 64881,67 ruble.

Lecţie: Maestru de funcții în Excel

Etapa 2: Detalii de plată

Și acum, cu ajutorul altor operatori Excel, facem un detaliu lunar de plăți pentru a vedea cât de mult într-o anumită lună plătim prin corpul împrumutului și cât de mult este cantitatea de interes. În aceste scopuri, fierar în tabelul exil, pe care vom completa datele. Liniile acestui tabel vor fi responsabile de perioada corespunzătoare, adică luna. Având în vedere că perioada de creditare este de 24 de luni, numărul de rânduri va fi, de asemenea, adecvat. Coloanele au indicat un organism de împrumut, plăți de dobânzi, o plată lunară totală, care este suma celor două coloane anterioare, precum și suma rămasă de plata.

1. Pentru a determina valoarea plății de către organismul împrumutului, utilizați funcția OSP, care este destinată doar în aceste scopuri. Stabiliți cursorul în celulă, care este situat în linia "1" și în coloana "Plata de către organismul împrumutului". Faceți clic pe butonul "Funcție Paste".



2. Du-te la Master of Funcții. În categoria "Financial", observăm numele "osplt" și faceți clic pe butonul "OK".



3. Argumentele argumentelor operatorului OSP încep. Are următoarea sintaxă:

   = Osult (rata, perioada; kper; ps; bs)

   După cum vedem, argumentele acestei caracteristici coincid aproape complet cu argumentele operatorului PLT, numai în loc de argumentul opțional "tip" a adăugat o "perioadă" obligatorie. Aceasta indică numărul perioadei de plată și în cazul nostru particular cu privire la numărul lunii.

   Completați argumentele argumentelor funcției OSR deja familiarizate cu aceleași date, care au fost utilizate pentru funcția PL. Doar având în vedere faptul că în viitor, copierea unei formule va fi folosită printr-un marker de umplere, trebuie să faceți toate legăturile din câmpuri absolute, astfel încât acestea să nu se schimbe. Acest lucru necesită punerea unui semn de dolar înainte de fiecare valoare a coordonatelor verticale și orizontale. Dar este mai ușor să o faceți, pur și simplu selectați coordonatele și făcând clic pe tasta funcțională F4. Semnul dolar va fi plasat automat în locurile potrivite. De asemenea, nu uităm că pariul anual trebuie împărțit în 12.



4. Dar avem un alt argument nou, care nu a fost din funcția PL. Această argument "perioadă". În câmpul corespunzător, setați o referire la prima celulă a coloanei "Perioadă". Acest element al foii conține numărul "1", care denotă numărul primei luni de împrumut. Dar, spre deosebire de câmpurile anterioare, lăsăm legătura relativă în câmpul specificat și nu o facem absolut de la ea.

   După toate datele despre care am vorbit mai sus sunt introduse, apăsați butonul "OK".



5. După aceea, în celula, pe care am alocat anterior, va apărea suma plății de către organismul împrumutului pentru prima lună. Va fi 18536.74 ruble.



6. Apoi, după cum am menționat mai sus, ar trebui să copiem această formulă la celulele coloanei rămase utilizând un marker de umplere. Pentru a face acest lucru, setați cursorul în colțul din dreapta jos al celulei, care conține formula. Cursorul este transformat într-o cruce, numită un marker de umplere. Faceți clic pe butonul stâng al mouse-ului și trageți-l până la capătul tabelului.



7. Ca rezultat, toate coloanele celulare sunt umplute. Acum avem o diagramă de plată lunar. După cum sa menționat mai sus, valoarea plății privind acest articol crește cu fiecare nouă perioadă.



8. Acum trebuie să facem un calcul lunar de plată cu dobândă. În aceste scopuri, vom folosi operatorul PRT. Noi alocăm prima celulă goală în coloana "Procentajul de plăți". Faceți clic pe butonul "Funcție Paste".



9. În funcțiile Maestrului de funcții din categoria "Financiară", producem numele NAMP. Efectuați un clic pe butonul "OK".



10. Fereastra argumentelor funcției TRP începe. Sintaxa sa arată așa:

    = PRT (rata, perioada, CPU, PS; BS)

    După cum putem vedea, argumentele acestei funcții sunt absolut identice cu elementele similare ale operatorului OSP. Prin urmare, introduceți aceleași date în fereastra pe care am introdus-o în fereastra anterioară a argumentelor. Nu uităm că referința din domeniul "perioadei" ar trebui să fie relativă, iar în toate celelalte domenii coordonatele ar trebui aduse la forma absolută. După aceea, faceți clic pe butonul "OK".



11. Apoi rezultatul calculării valorii plății pentru dobândă pentru un împrumut pentru prima lună este afișat în celula corespunzătoare.



12. Aplicarea markerului de umplere, efectuați copierea formulei în elementele rămase ale coloanei, primind în acest mod un program lunar pentru procente pentru împrumut. După cum putem vedea, așa cum sa spus mai devreme, de la o lună la alta, valoarea acestui tip de plată scade.



13. Acum trebuie să calculăm plata lunară globală. Pentru acest calcul, nu trebuie să recurgeți la niciun operator, deoarece puteți utiliza formula aritmetică simplă. Folosim conținutul celulelor din prima lună a coloanelor "Plata de către organismul împrumutului" și "interesul complet". Pentru a face acest lucru, setați semnul "=" în prima celulă goală a coloanei "Plata lunară totală". Apoi faceți clic pe cele două elemente de mai sus prin setarea semnului "+" între ele. Faceți clic pe tasta Enter.



14. Apoi, folosind marcatorul de umplere, ca în cazurile anterioare, completați coloana de date. După cum putem vedea, pe parcursul întregii acțiuni a contractului, valoarea plății lunare totale, care include plata de către organismul împrumutului și plata dobânzilor, va fi de 23536,74 ruble. De fapt, am calculat deja acest indicator înainte de a utiliza PPT. Dar, în acest caz, este prezentată mai clar, tocmai ca valoare plății de către organismul împrumutului și a interesului.



15. Acum trebuie să adăugați date în coloană, unde este afișat lunar echilibrul creditului, ceea ce este încă necesar să plătiți. În prima celulă a coloanei "echilibru pentru a plăti", calculul va fi cel mai ușor. Trebuie să fim luați de la magnitudinea inițială a împrumutului, care este specificată în tabel cu date primare, plata de către organismul împrumutului pentru prima lună din tabelul calculat. Dar, având în vedere faptul că unul dintre numerele pe care le mergem deja cu semnul "-", atunci nu ar trebui să fie luate, ci să se îndoaie. Realizăm și faceți clic pe butonul ENTER.



16. Dar calculul echilibrului de plată după lunile a doua și ulterioare va fi oarecum mai complicat. Pentru a face acest lucru, trebuie să luăm de la corpul împrumutului la începutul împrumuturilor cuantumul total al plăților de către organismul împrumutului pentru perioada anterioară. Instalați semnul "=" în cea de-a doua celulă a coloanei "Palatul să plătească". Apoi, specificați un link către celulă, care conține suma inițială a împrumutului. Facem absolut, evidențiind și apăsând cheia F4. Apoi am pus semnul "+", deoarece avem al doilea înțeles și atât de negativ. După aceea, faceți clic pe butonul "Inserare Funcție".



17. Maestrul funcțiilor este lansat, în care trebuie să vă deplasați în categoria "Matematică". Acolo vom aloca inscripția "Sums" și apăsați butonul "OK".



18. Fereastra argumentelor începe argumentele funcției. Operatorul specificat servește la rezumarea datelor din celulele pe care trebuie să le îndeplinim în "Plata prin intermediul corpului de împrumut". Are următoarea sintaxă:

    = Sumele (numărul 1, numărul2; ...)

    Ca argumente, referințele la celulele în care sunt conținute numerele. Am pus cursorul în câmpul "număr1". Apoi fixați butonul stâng al mouse-ului și selectați primele două celule ale coloanei corpului de credit de pe foaie. În domeniu, după cum vedem, a apărut o legătură cu gama. Se compune din două părți separate de colon: referințele la prima gamă a gamei și la ultima. Pentru a putea copia formula specificată în viitor printr-un marker de umplere, facem prima legătură cu gama absolută. Am evidențiat-o și faceți clic pe tasta funcțională F4. A doua parte a referinței și a părăsi ruda. Acum, atunci când utilizați un marker de umplere, prima gamă a gamei va fi fixată, iar acesta din urmă se va întinde pe măsură ce se mișcă în jos. Acest lucru este necesar pentru noi să îndeplinim obiectivele. Apoi, faceți clic pe butonul "OK".



19. Deci, rezultatul bilanțului datoriei de credit după a doua lună este evacuată în celulă. Acum, începând cu această celulă, facem copierea formulei în elemente coloane goale folosind un marker de umplere.



20. Calculul lunar al resturilor de a plăti împrumutului se face pentru întreaga perioadă de credit. Așa cum ar trebui să fie, la sfârșitul termenului limită, această sumă este zero.

Astfel, nu am calculat pur și simplu plata împrumutului, dar a organizat un fel de calculator de credit. Care va acționa pe o schemă de anuitate. Dacă în tabelul sursă am modificat, de exemplu, valoarea împrumutului și rata anuală a dobânzii, atunci în tabelul final vor fi recalcularea automată a datelor. Prin urmare, acesta poate fi utilizat nu numai o singură dată pentru un anumit caz, ci să se aplice în diferite situații pentru a calcula opțiunile de credit pe o schemă de anuitate.

Lecţie: Funcții financiare în Excel

După cum puteți vedea, utilizând programul Excel la domiciliu, puteți calcula cu ușurință plata globală a împrumutului pe schema de anuitate, utilizând operatorul PL în aceste scopuri. În plus, cu ajutorul funcțiilor OSR și a PRT, este posibilă calcularea cuantumului plăților de către organismul împrumutului și procentelor pentru perioada specificată. Aplicând împreună toate aceste funcții de bagaje, este posibilă crearea unui calculator de credit puternic care poate fi utilizat de mai multe ori pentru a calcula plata anuității.