Formula pentru calcularea unei rente în Excel

Plățile împrumutului sunt mai ușor și mai rapid de calculat cu Microsoft Office Excel. Se petrece mult mai mult timp pentru calculul manual. Acest articol se va concentra pe plățile de anuitate, caracteristicile calculului acestora, avantajele și dezavantajele.

Ce este o rentă

O metodă de rambursare lunară a unui împrumut, în care suma depusă nu se modifică pe toată perioada împrumutului. Acestea. la anumite date ale fiecărei luni, o persoană depune o anumită sumă de bani până când împrumutul este rambursat integral.

Mai mult, dobânda la credit este deja inclusă în suma totală plătită băncii.

Renta de clasificare

Plățile de anuitate pot fi împărțite în următoarele tipuri:

1. Fix. Plățile care nu se modifică au o rată fixă ​​indiferent de condițiile externe.
2. Valută. Capacitatea de a modifica valoarea plății în cazul unei scăderi sau creșteri a cursului de schimb.
3. indexate. Plăți în funcție de nivel, indicator de inflație. În timpul perioadei de împrumut, dimensiunea acestora se schimbă adesea.
4. Variabile. Renta, care se poate modifica în funcție de starea sistemului financiar, instrumente.

Fii atent! Plățile fixe sunt de preferat pentru toți debitorii, deoarece au riscuri reduse.

Avantajele și dezavantajele plăților de anuitate

Pentru a înțelege mai bine subiectul, este necesar să se studieze caracteristicile cheie ale acestui tip de plăți de împrumut. Are următoarele avantaje:

• Stabilirea unei anumite sume de plată și a datei plății acesteia.
• Disponibilitate ridicată pentru debitori. Aproape oricine poate solicita o rentă, indiferent de situația sa financiară.
• Posibilitatea de a reduce cuantumul ratei lunare cu o creștere a inflației.

Nu fără dezavantaje:

• Rata ridicată. Împrumutatul va plăti în exces o sumă mai mare de bani în comparație cu plata diferențială.
• Probleme care decurg din dorința de a plăti datoria înainte de termen.
• Fără recalculări pentru plăți anticipate.

Care este plata împrumutului?

Plata rentei are următoarele componente:

• Dobânda plătită de o persoană la achitarea unui împrumut.
• O parte din suma principală.

Ca urmare, suma totală a dobânzii depășește aproape întotdeauna suma contribuită de către împrumutat pentru a reduce datoria.

Formula de bază de plată a anuității în Excel

După cum am menționat mai sus, în Microsoft Office Excel puteți lucra cu diferite tipuri de plăți pentru împrumuturi și avansuri. Renta nu face excepție. În general, formula cu care puteți calcula rapid contribuțiile la rentă este următoarea:  

Important! Este imposibil să deschideți paranteze în numitorul acestei expresii pentru a o simplifica.

Principalele valori ale formulei sunt descifrate după cum urmează:

• AP – plata anuitatii (denumirea este prescurtata).
• O – mărimea datoriei principale a împrumutatului.
• PS – rata dobânzii propusă lunar de o anumită bancă.
• C este numărul de luni în care durează împrumutul.

Pentru a asimila informațiile, este suficient să dam câteva exemple de utilizare a acestei formule. Acestea vor fi discutate în continuare.

Exemple de utilizare a funcției PMT în Excel

Oferim o condiție simplă a problemei. Este necesar să se calculeze plata lunară a împrumutului dacă banca propune o dobândă de 23%, iar suma totală este de 25000 de ruble. Împrumutul va dura 3 ani. Problema este rezolvată conform algoritmului:

1. Realizați o foaie de calcul generală în Excel pe baza datelor sursă.

2. Activați funcția PMT și introduceți argumente pentru aceasta în caseta corespunzătoare.
3. În câmpul „Pari”, introduceți formula „B3/B5”. Aceasta va fi rata dobânzii la împrumut.
4. În rândul „Nper” scrieți valoarea sub forma „B4*B5”. Acesta va fi numărul total de plăți pentru întreaga durată a împrumutului.
5. Completați câmpul „PS”. Aici trebuie să indicați suma inițială luată de la bancă, scriind valoarea „B2”.

6. Asigurați-vă că, după ce faceți clic pe „OK” în tabelul sursă, a fost calculată valoarea „Plată lunară”.

Informații suplimentare! Un număr negativ indică faptul că împrumutatul cheltuiește bani.

Un exemplu de calcul al sumei de plată în exces la un împrumut în Excel

În această problemă, trebuie să calculați suma pe care o persoană care a luat un împrumut de 50000 de ruble la o rată a dobânzii de 27% timp de 5 ani o va plăti în exces. În total, împrumutatul efectuează 12 plăți pe an. Soluţie:

1. Compilați tabelul de date original.

2. Din suma totală a plăților, scădeți suma inițială conform formulei «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Acesta trebuie inserat în bara de formule din partea de sus a meniului principal al programului.
3. Ca urmare, suma plăților în plus va apărea în ultima linie a plăcii create. Împrumutatul va plăti în plus 41606 ruble în plus.

Formula de calcul a plății lunare optime a creditului în Excel

O sarcină cu următoarea condiție: clientul a înregistrat un cont bancar pentru 200000 de ruble cu posibilitatea de reîncărcare lunară. Este necesar să se calculeze suma de plată pe care o persoană trebuie să o facă în fiecare lună, astfel încât după 4 ani să aibă 2000000 de ruble în cont. Rata este de 11%. Soluţie:

1. Creați o foaie de calcul bazată pe datele originale.

2. Introduceți formula în linia de introducere Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» și apăsați „Enter” de la tastatură. Literele vor diferi în funcție de celulele în care este plasat tabelul.
3. Verificați dacă suma contribuției este calculată automat în ultimul rând al tabelului.

Fii atent! Astfel, pentru ca clientul să acumuleze 4 ruble la o rată de 2000000% în 11 ani, trebuie să depună 28188 ruble în fiecare lună. Minusul din sumă indică faptul că clientul suferă pierderi dând bani băncii.

Caracteristici de utilizare a funcției PMT în Excel

În general, această formulă este scrisă după cum urmează: =PMT(rata; nper; ps; [bs]; [tip]). Funcția are următoarele caracteristici:

1. Atunci când se calculează contribuțiile lunare, se ia în considerare doar cota anuală.
2. La specificarea ratei dobânzii este important să se recalculeze în funcție de numărul de rate pe an.
3. În loc de argumentul „Nper” din formulă, este indicat un anumit număr. Aceasta este perioada de plată.

Calculul plății

În general, plata anuității se calculează în două etape. Pentru a înțelege subiectul, fiecare dintre etape trebuie luată în considerare separat. Acest lucru va fi discutat în continuare.

Etapa 1: calculul ratei lunare

Pentru a calcula în Excel suma pe care trebuie să o plătiți în fiecare lună pentru un împrumut cu rată fixă, trebuie să:

1. Compilați tabelul sursă și selectați celula în care doriți să afișați rezultatul și faceți clic pe butonul „Inserare funcție” de sus.

2. În lista de funcții, selectați „PLT” și faceți clic pe „OK”.

3. În fereastra următoare, setați argumentele funcției, indicând liniile corespunzătoare din tabelul compilat. La sfârșitul fiecărei linii, trebuie să faceți clic pe pictogramă și apoi să selectați celula dorită din matrice.

4. Când toate argumentele sunt completate, în linia de introducere a valorilor se va scrie formula corespunzătoare, iar rezultatul calculului cu semnul minus va apărea în câmpul tabelului „Plată lunară”.

Important! După calcularea ratei, se va putea calcula suma pe care împrumutatul o va plăti în exces pe toată perioada împrumutului.

Etapa 2: detalii de plată

Suma plății în plus poate fi calculată lunar. Drept urmare, o persoană va înțelege câți bani va cheltui pe un împrumut în fiecare lună. Calculul detaliat se efectuează după cum urmează:

1. Creați o foaie de calcul pentru 24 de luni.

2. Plasați cursorul în prima celulă a tabelului și introduceți funcția „OSPLT”.

3. Completați argumentele funcției în același mod.

4. Când completați câmpul „Perioadă”, trebuie să vă referiți la prima lună din tabel, indicând celula 1.

5. Verificați dacă prima celulă din coloana „Plata de către corpul împrumutului” este completată.
6. Pentru a umple toate rândurile primei coloane, trebuie să întindeți celula până la sfârșitul tabelului

7. Selectați funcția „PRPLT” pentru a completa a doua coloană a tabelului.
8. Completați toate argumentele în fereastra deschisă în conformitate cu captura de ecran de mai jos.

9. Calculați plata totală lunară adunând valorile din cele două coloane anterioare.

10. Pentru a calcula „Soldul de plătit”, trebuie să adăugați rata dobânzii la plata pe corpul împrumutului și să o întindeți până la capătul plăcii pentru a umple toate lunile împrumutului.

Informații suplimentare! Când se calculează restul, semnele dolarului trebuie să fie agățate pe formulă, astfel încât să nu se miște atunci când sunt întinse.

Calculul plăților de anuitate la un împrumut în Excel

Funcția PMT este responsabilă pentru calcularea anuității în Excel. Principiul de calcul în general este de a efectua următorii pași:

1. Compilați tabelul de date original.
2. Creați un program de rambursare a datoriilor pentru fiecare lună.
3. Selectați prima celulă din coloana „Plăți la împrumut” și introduceți formula de calcul „PLT ($B3/12;$B$4;$B$2)”.
4. Valoarea rezultată este întinsă pentru toate coloanele plăcii.

Calculul în MS Excel rambursarea sumei principale a datoriei

Plățile anuale trebuie efectuate lunar în sume fixe. Și rata dobânzii nu se modifică.

Calculul soldului sumei principale (cu BS=0, tip=0)

Să presupunem că un împrumut de 100000 de ruble este luat pentru 10 ani la 9%. Este necesar să se calculeze cuantumul datoriei principale în prima lună a celui de-al treilea an. Soluţie:

1. Compilați o fișă de date și calculați plata lunară utilizând formula PV de mai sus.
2. Calculați cota de plată necesară pentru achitarea unei părți din datorie folosind formula «=-PMT-(PS-PS1)*articol=-PMT-(PS +PMT+PS*articol)”.
3. Calculați valoarea datoriei principale pentru 120 de perioade folosind o formulă binecunoscută.
4. Folosind operatorul HPMT găsiți suma dobânzii plătite pentru luna a 25-a.
5. Verificați rezultatul.

Calcularea sumei de principal care a fost plătită între două perioade

Acest calcul se face cel mai bine într-un mod simplu. Trebuie să utilizați următoarele formule pentru a calcula suma în intervalul pentru două perioade:

• =«-BS(element; con_period; plt; [ps]; [tip]) /(1+tip *articol)».
• = „+ BS(rata; start_period-1; plt; [ps]; [tip]) /IF(start_period =1; 1; 1+type *rate)”.

Fii atent! Literele din paranteze sunt înlocuite cu valori specifice.

Rambursare anticipată cu termen redus sau plată

Dacă trebuie să reduceți termenul împrumutului, va trebui să efectuați calcule suplimentare folosind operatorul IF. Deci va fi posibil să se controleze soldul zero, care nu trebuie atins înainte de sfârșitul perioadei de plată.

Pentru a reduce plățile, trebuie să recalculați contribuția pentru fiecare lună anterioară.

Calculator de împrumut cu plăți neregulate

Există mai multe opțiuni de anuitate în care împrumutatul poate depune sume variabile în orice zi a lunii. Într-o astfel de situație, soldul datoriei și dobânzile se calculează pentru fiecare zi. În același timp, în Excel aveți nevoie de:

1. Introduceți zilele lunii pentru care se fac plățile și indicați numărul acestora.
2. Verificați dacă există sume negative și pozitive. Se preferă cele negative.
3. Numărați zilele dintre două date la care au fost depuși banii.

Calculul plății periodice în MS Excel. Depozit la termen

În Excel, puteți calcula rapid suma plăților regulate, cu condiția ca o sumă fixă ​​să fi fost deja acumulată. Această acțiune este efectuată folosind funcția PMT după ce tabelul inițial a fost compilat.

Concluzie

Astfel, plățile anuității sunt mai ușor, mai rapid și mai eficient de calculat în Excel. Operatorul PMT este responsabil pentru calculul acestora. Exemple mai detaliate pot fi găsite mai sus.