Crearea unui program de amortizare a împrumutului în Excel poate fi o abilitate valoroasă, permițându-vă să vizualizați și să gestionați eficient rambursările împrumutului. Un grafic de amortizare este un tabel care detaliază fiecare plată periodică a unui împrumut cu amortizare (de obicei, un credit ipotecar sau auto). Acesta descompune fiecare plată în dobândă și componente principale, arătând soldul rămas după fiecare plată. Să ne aprofundăm într-un ghid pas cu pas pentru a crea un astfel de program în Excel.

Ce este un program de amortizare?

Creați un program de amortizare în Excel

Creați un program de amortizare pentru un număr variabil de perioade

Creați un program de amortizare cu plăți suplimentare

Creați un program de amortizare (cu plăți suplimentare) utilizând șablonul Excel

Ce este un program de amortizare?

Un program de amortizare este un tabel detaliat utilizat în calculele împrumutului care afișează procesul de achitare a unui împrumut în timp. Programele de amortizare sunt utilizate în mod obișnuit pentru împrumuturile cu rată fixă, cum ar fi creditele ipotecare, împrumuturile auto și împrumuturile personale, în care valoarea plății rămâne constantă pe toată durata împrumutului, dar proporția plății față de dobândă față de principal se modifică în timp.

Pentru a crea un program de amortizare a împrumutului în Excel, într-adevăr, funcțiile încorporate PMT, PPMT și IPMT sunt cruciale. Să înțelegem ce face fiecare funcție:

• Funcția PMT: Această funcție este utilizată pentru a calcula plata totală pe perioadă pentru un împrumut pe baza plăților constante și a unei rate constante a dobânzii.
• Funcția IPMT: Această funcție calculează partea de dobândă a unei plăți pentru o anumită perioadă.
• Funcția PPMT: Această funcție este utilizată pentru a calcula partea principală a unei plăți pentru o anumită perioadă.

Folosind aceste funcții în Excel, puteți crea un program detaliat de amortizare care arată dobânda și componentele principale ale fiecărei plăți, împreună cu soldul rămas al împrumutului după fiecare plată.

Creați un program de amortizare în Excel

În această secțiune, vom introduce două metode distincte pentru a crea un program de amortizare în Excel. Aceste metode se adresează diferitelor preferințe și niveluri de calificare ale utilizatorilor, asigurându-se că oricine, indiferent de competența lor cu Excel, poate construi cu succes un program de amortizare detaliat și precis pentru împrumutul său.

Formulele oferă o înțelegere mai profundă a calculelor de bază și oferă flexibilitate pentru a personaliza programul conform cerințelor specifice. Această abordare este ideală pentru cei care doresc să aibă o experiență practică și o perspectivă clară asupra modului în care fiecare plată este împărțită în componente principal și dobândă. Acum, să detaliem procesul de creare a unui program de amortizare în Excel pas cu pas:

⭐️ Pasul 1: Configurați informațiile despre împrumut și tabelul de amortizare

1. Introduceți informațiile relative ale împrumutului, cum ar fi rata anuală a dobânzii, durata împrumutului în ani, numărul de plăți pe an și suma împrumutului în celule, după cum se arată în următoarea captură de ecran:
   
2. Apoi, creați un tabel de amortizare în Excel cu etichetele specificate, cum ar fi Perioadă, Plată, Dobândă, Principal, Sold rămas în celulele A7:E7.
3. În coloana Perioada, introduceți numerele perioadei. Pentru acest exemplu, numărul total de plăți este de 24 de luni (2 ani), așa că veți introduce numerele de la 1 la 24 în coloana Perioadă. Vedeți captura de ecran:
   
4. După ce ați configurat tabelul cu etichetele și numerele perioadei, puteți continua să introduceți formule și valori pentru coloanele Plată, Dobândă, Principal și Sold pe baza specificului împrumutului dvs.

⭐️ Pasul 2: Calculați suma totală de plată utilizând funcția PMT

Sintaxa PMT este:

Introduceți următoarea formulă în celula B7, apoi trageți mânerul de umplere în jos pentru a completa această formulă în alte celule și veți vedea o sumă de plată constantă pentru toate perioadele. Vedeți captura de ecran:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Pasul 3: Calculați dobânda utilizând funcția IPMT

În acest pas, veți calcula dobânda pentru fiecare perioadă de plată folosind funcția IPMT din Excel.

Introduceți următoarea formulă în celula C7, apoi trageți mânerul de umplere în jos pe coloană pentru a completa această formulă și a obține dobânda pentru fiecare perioadă.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Pasul 4: Calculați principalul utilizând funcția PPMT

După calcularea dobânzii pentru fiecare perioadă, următorul pas în crearea unui program de amortizare este de a calcula partea principală a fiecărei plăți. Acest lucru se face folosind funcția PPMT, care este concepută pentru a determina partea principală a unei plăți pentru o anumită perioadă, pe baza plăților constante și a unei rate constante a dobânzii.

Sintaxa IPMT este:

Sintaxa și parametrii pentru formula PPMT sunt identice cu cele utilizate în formula IPMT discutată anterior.

Introduceți următoarea formulă în celula D7, apoi trageți mânerul de umplere în jos pe coloană pentru a completa principalul pentru fiecare perioadă. Vedeți captura de ecran:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Pasul 5: Calculați soldul rămas

După ce ați calculat atât dobânda, cât și principalul fiecărei plăți, următorul pas din programul dvs. de amortizare este să calculați soldul rămas al împrumutului după fiecare plată. Aceasta este o parte crucială a programului, deoarece arată cum scade soldul împrumutului în timp.

1. În prima celulă a coloanei soldului dvs. - E7, introduceți următoarea formulă, ceea ce înseamnă că soldul rămas va fi suma inițială a împrumutului minus partea principală a primei plăți:

   =B4-D7

   
2. Pentru a doua și pentru toate perioadele ulterioare, calculați soldul rămas scăzând plata principalului din perioada curentă din soldul perioadei precedente. Vă rugăm să aplicați următoarea formulă în celula E8:

   =E7-D8

    notițe: Referința la celula de echilibru ar trebui să fie relativă, astfel încât se actualizează pe măsură ce trageți formula în jos.
   
3. Și apoi trageți mânerul de umplere în jos pe coloană. După cum puteți vedea, fiecare celulă se va ajusta automat pentru a calcula soldul rămas pe baza plăților actualizate ale principalului.
   

⭐️ Pasul 6: Faceți un rezumat al împrumutului

După configurarea programului de amortizare detaliat, crearea unui rezumat al împrumutului poate oferi o imagine de ansamblu rapidă a aspectelor cheie ale împrumutului dumneavoastră. Acest rezumat va include de obicei costul total al împrumutului, dobânda totală plătită.

● Pentru a calcula plățile totale:

=SUM(B7:B30)

● Pentru a calcula dobânda totală:

=SUM(C7:C30)

⭐️ Rezultat:

Acum, un program simplu, dar cuprinzător de amortizare a împrumutului a fost creat cu succes. vezi captura de ecran:

Creați un program de amortizare pentru un număr variabil de perioade

În exemplul anterior, creăm un program de rambursare a creditului pentru un număr fix de plăți. Această abordare este perfectă pentru gestionarea unui anumit împrumut sau ipotecă în care termenii nu se modifică.

Dar, dacă doriți să creați un program flexibil de amortizare care să poată fi utilizat în mod repetat pentru împrumuturi cu perioade diferite, permițându-vă să modificați numărul de plăți, după cum este necesar pentru diferite scenarii de împrumut, va trebui să urmați o metodă mai detaliată.

⭐️ Pasul 1: Configurați informațiile despre împrumut și tabelul de amortizare

1. Introduceți informațiile relative ale împrumutului, cum ar fi rata anuală a dobânzii, durata împrumutului în ani, numărul de plăți pe an și suma împrumutului în celule, după cum se arată în următoarea captură de ecran:
   
2. Apoi, creați un tabel de amortizare în Excel cu etichetele specificate, cum ar fi Perioadă, Plată, Dobândă, Principal, Sold rămas în celulele A7:E7.
3. În coloana Perioada, introduceți cel mai mare număr de plăți pe care le puteți lua în considerare pentru orice împrumut, de exemplu, completați numerele cuprinse între 1 și 360. Acest lucru poate acoperi un împrumut standard de 30 de ani dacă efectuați plăți lunare.
   

⭐️ Pasul 2: Modificați formulele de plată, dobândă și principiu cu funcția IF

Introduceți următoarele formule în celulele corespunzătoare, apoi trageți mânerul de umplere pentru a extinde aceste formule până la numărul maxim de perioade de plată pe care l-ați setat.

● Formula de plata:

În mod normal, utilizați funcția PMT pentru a calcula plata. Pentru a încorpora o instrucțiune IF, formula de sintaxă este:

Deci formula este aceasta:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Formula dobânzii:

Formula de sintaxă este:

Deci formula este aceasta:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Formula de principiu:

Formula de sintaxă este:

Deci formula este aceasta:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Pasul 3: Ajustați formula soldului rămas

Pentru soldul rămas, de obicei scădeți principalul din soldul anterior. Cu o instrucțiune IF, modificați-o ca:

● Prima celulă de echilibru:(E7)

=B4-D7

● A doua celulă de echilibru: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Pasul 4: Faceți un rezumat al împrumutului

După ce ați configurat programul de amortizare cu formulele modificate, următorul pas este să creați un rezumat al împrumutului.

● Pentru a calcula plățile totale:

=SUM(B7:B366)

● Pentru a calcula dobânda totală:

=SUM(C7:C366)

⭐️ Rezultat:

Acum, aveți un program de amortizare cuprinzător și dinamic în Excel, completat cu un rezumat detaliat al împrumutului. Ori de câte ori ajustați termenul perioadei de plată, întregul program de amortizare se va actualiza automat pentru a reflecta aceste modificări. Vezi demo-ul de mai jos:

Creați un program de amortizare cu plăți suplimentare

Efectuând plăți suplimentare dincolo de cele programate, un împrumut poate fi achitat mai rapid. Un program de amortizare care încorporează plăți suplimentare, atunci când este creat în Excel, demonstrează modul în care aceste plăți suplimentare pot grăbi rambursarea împrumutului și pot scădea dobânda totală plătită. Iată cum îl puteți configura:

⭐️ Pasul 1: Configurați informațiile despre împrumut și tabelul de amortizare

1. Introduceți informațiile relative ale împrumutului, cum ar fi rata anuală a dobânzii, durata împrumutului în ani, numărul de plăți pe an, suma împrumutului și plata suplimentară în celule, după cum se arată în următoarea captură de ecran:
   
2. Apoi, calculați plata programată.
   În plus față de celulele de intrare, este necesară o altă celulă predefinită pentru calculele noastre ulterioare - suma de plată programată. Aceasta este suma obișnuită de plată a unui împrumut, presupunând că nu se fac plăți suplimentare. Vă rugăm să aplicați următoarea formulă în celula B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Apoi, creați un tabel de amortizare în Excel:
   • Setați etichetele specificate, cum ar fi Perioada, Planificarea plății, Plata suplimentară, Plata totală, Dobânda, Principalul, Soldul rămas în celulele A8:G8;
   • În coloana Perioadă, introduceți cel mai mare număr de plăți pe care le puteți lua în considerare pentru orice împrumut. De exemplu, completați numerele de la 0 la 360. Acest lucru poate acoperi un împrumut standard de 30 de ani dacă efectuați plăți lunare;
   • Pentru Perioada 0 (rândul 9 în cazul nostru), preluați valoarea Soldului cu această formulă = B4, care corespunde sumei inițiale a împrumutului. Toate celelalte celule din acest rând trebuie lăsate necompletate.
   • 

⭐️ Pasul 2: Creați formulele pentru graficul de amortizare cu plăți suplimentare

Introduceți următoarele formule în celulele corespunzătoare una câte una. Pentru a îmbunătăți gestionarea erorilor, includem aceasta și toate formulele viitoare în funcția IFERROR. Această abordare ajută la evitarea erorilor potențiale multiple care ar putea apărea dacă oricare dintre celulele de intrare este lăsată goală sau păstrează valori incorecte.

● Calculați plata programată:

Introduceți următoarea formulă în celula B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Calculați plata suplimentară:

Introduceți următoarea formulă în celula C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Calculați plata totală:

Introduceți următoarea formulă în celula D10:

=IFERROR(B10+C10, "")

● Calculați principalul:

Introduceți următoarea formulă în celula E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Calculați dobânda:

Introduceți următoarea formulă în celula F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Calculați soldul rămas

Introduceți următoarea formulă în celula G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

După ce ați completat fiecare dintre formule, selectați intervalul de celule B10:G10 și utilizați mânerul de umplere pentru a trage și extinde aceste formule în jos prin întregul set de perioade de plată. Pentru perioadele neutilizate, celulele vor afișa 0s. Vedeți captura de ecran:

⭐️ Pasul 3: Faceți un rezumat al împrumutului

● Obțineți numărul programat de plăți:

=B2:B3

● Obțineți numărul real de plăți:

=COUNTIF(D10:D369,">"&0)

● Obțineți totalul plăților suplimentare:

=SUM(C10:C369)

● Obțineți dobândă totală:

=SUM(F10:F369)

⭐️ Rezultat:

Urmând acești pași, creați un program dinamic de amortizare în Excel care ține cont de plățile suplimentare.

Creați un program de amortizare folosind șablonul Excel

Crearea unui program de amortizare în Excel folosind un șablon este o metodă simplă și eficientă în timp. Excel oferă șabloane încorporate care calculează automat dobânda, principalul și soldul rămas pentru fiecare plată. Iată cum puteți crea un program de amortizare folosind un șablon Excel:

1. Clic Fișier > Nou, în caseta de căutare, tastați graficul de amortizare, și apăsați Intrați cheie. Apoi, selectați șablonul care se potrivește cel mai bine nevoilor dvs. făcând clic pe el. De exemplu, aici, voi selecta șablon Calculator simplu de împrumut. Vedeți captura de ecran:
   
2. După ce ați selectat un șablon, faceți clic pe Crea butonul pentru a-l deschide ca registru de lucru nou.
3. Apoi, introduceți propriile detalii despre împrumut, șablonul ar trebui să calculeze și să completeze automat programul pe baza intrărilor dvs.
4. În sfârșit, salvați noul registru de lucru cu programul de amortizare.