Cum de a face un împrumut amortizare program în Excel (cu plăți suplimentare)

• C2 – rata anuală a dobânzii
• C3 – Termenul împrumutului în ani
• C4 – numărul de plăți pe an
• C5 – suma împrumutului

următorul lucru pe care îl faceți este să creați un tabel de amortizare cu etichetele (perioada, plată, dobândă, principal, sold) în A7:E7., În Perioada coloană, introduceți o serie de cifre egal cu numărul total de plăți (1 – 24 în acest exemplu):

Cu toate componentele cunoscute în loc, să trecem la partea cea mai interesanta – amortizarea împrumutului formule.

calculați suma totală de plată(formula PMT)

suma de plată este calculată cu funcția PMT (rate, nper, pv,,).pentru a gestiona corect diferite frecvențe de plată (cum ar fi săptămânal, lunar, trimestrial etc.).,), ar trebui să fie în concordanță cu valorile furnizate pentru rata și argumentele nper:

• rata – împărțiți rata anuală a dobânzii la numărul de perioade de plată pe an ($C$2/$C$4).
• Nper-înmulțiți numărul de ani cu numărul de perioade de plată pe an ($C$3*$C$4).
• pentru argumentul pv, introduceți suma împrumutului ($C$5).
• argumentele fv și type pot fi omise, deoarece valorile lor implicite funcționează foarte bine pentru noi (soldul după ultima plată ar trebui să fie 0; plățile se fac la sfârșitul fiecărei perioade).,punând împreună argumentele de mai sus, obținem următoarea formulă:

  =PMT($C$2/$C$4, $C$3*$C$4, $C$5)

  vă rugăm să fiți atenți, că folosim referințe de celule absolute, deoarece această formulă ar trebui să copieze celulele de mai jos fără modificări.,

  Introduceți PMT formula în B8, glisați-l în jos pe coloană, și veți vedea o constantă suma de plată pentru toate perioadele:
  

  Calculeze dobânda (IPMT formula)

  Pentru a găsi interes parte din fiecare plată periodică, utilizați IPMT(rate, per, nper, pv, , funcția):

  =IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

  Toate argumentele sunt aceleași ca și în PMT formula, cu excepția per argument care specifică perioada de plată., Acest argument este furnizat ca o referință relativă a celulei (A8), deoarece se presupune că se schimbă pe baza poziției relative a unui rând la care este copiată formula.,

  Această formulă se duce la C8, și apoi copiați-l în jos pentru a cât mai multe celule după cum este necesar:
  

  Găsi principală (PPMT formula)

  Pentru a calcula partea principală din fiecare plată periodică, utilizați această PPMT formula:

  =PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

  sintaxa și argumentele sunt exact la fel ca în IPMT formula discutat mai sus:

  Această formulă se duce la coloana D, începând din D8:
  

  Sfat. Pentru a verifica dacă calculele dvs. sunt corecte în acest moment, adăugați numerele din coloanele principale și de interes., Suma trebuie să fie egală cu valoarea din coloana de plată din același rând.

  Obțineți soldul rămas

  pentru a calcula soldul rămas pentru fiecare perioadă, vom folosi două formule diferite.

  Pentru a găsi echilibrul după prima plată în E8, adăuga până la suma creditului (C5) și directorul de prima perioadă (D8):

  =C5+D8

  Pentru suma creditului este un număr pozitiv, iar directorul este un număr negativ, acesta din urmă este, de fapt scade din prima.,

  pentru a doua și toate perioadele următoare, adăugați soldul anterior și principalul acestei perioade:

  =E8+D9

  formula de mai sus merge la E9, apoi o copiați în jos pe coloană. Datorită utilizării referințelor relative ale celulelor, formula se ajustează corect pentru fiecare rând.

  asta este! Noastre lunare de amortizare a împrumutului, programul este făcut:
  

  Sfat: Întoarce plăți ca numerele pozitive

  Pentru un împrumut este plătită din contul bancar, Excel funcții reveni la plată, dobânda și principalul ca numere negative., În mod implicit, aceste valori sunt evidențiate în roșu și închise între paranteze, după cum puteți vedea în imaginea de mai sus.dacă preferați să aveți toate rezultatele ca numere pozitive, puneți un semn minus înaintea funcțiilor PMT, IPMT și PPMT.

  Pentru Echilibrul formule, utilizați scădere în loc de plus, așa cum se arată în captura de ecran de mai jos:
  

  program de Amortizare pentru un număr variabil de perioade

  În exemplul de mai sus, am construit un împrumut program de amortizare pentru un număr predefinit de perioade de plată., Această soluție rapidă unică funcționează bine pentru un anumit împrumut sau ipotecă.dacă doriți să creați un program de amortizare reutilizabil cu un număr variabil de perioade, va trebui să adoptați o abordare mai cuprinzătoare descrisă mai jos.

  introduceți numărul maxim de perioade

  în coloana perioadă, introduceți numărul maxim de plăți pe care le veți permite pentru orice împrumut, să zicem, de la 1 la 360. Puteți utiliza funcția de completare automată Excel pentru a introduce mai rapid o serie de numere.,deoarece acum aveți multe numere de perioade excesive, trebuie să limitați cumva calculele la numărul real de plăți pentru un anumit împrumut. Acest lucru se poate face prin înfășurarea fiecărei formule într-o declarație IF. Testul logic al declarației IF verifică dacă numărul perioadei din rândul curent este mai mic sau egal cu numărul total de plăți. Dacă testul logic este adevărat, se calculează funcția corespunzătoare; dacă este fals, se returnează un șir gol.,

  presupunând că perioada 1 este în rândul 8, introduceți următoarele formule în celulele corespunzătoare, apoi copiați-le pe întregul tabel.,

  Plata (B8):

  =IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

  Interes (C8):

  =IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

  Directorul (D8):

  =IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

  Echilibru:

  Pentru o Perioada de 1 (E8), formula este aceeași ca și în exemplul anterior:

  =C5+D8

  Pentru Perioada 2 (E9) și toate perioadele ulterioare, formula are forma asta:

  =IF(A9<=$C$3*$C$4, E8+D9, "")

  Ca urmare, ai calculat corect program de amortizare și o grămadă de rânduri goale cu perioada de numere după împrumutul este plătit.,
  

  ascundeți numerele perioadelor suplimentare

  dacă puteți trăi cu o grămadă de numere de perioade inutile afișate după ultima plată, puteți lua în considerare munca depusă și săriți acest pas. Dacă vă străduiți pentru perfecțiune, ascundeți toate perioadele neutilizate făcând o regulă de formatare condiționată care stabilește culoarea fontului la alb pentru orice rânduri după efectuarea ultimei plăți.,

  Pentru acest lucru, selectați toate rândurile de date dacă tabel de amortizare (A8:E367 în cazul nostru) și faceți clic pe fila Acasă > formatarea Condițională > Noua Regula… > Utilizați o formulă pentru a determina care celule să formatați.

  În caseta corespunzătoare, introduceți mai jos formula care verifică dacă perioada număr din coloana a este mai mare decât numărul total de plata:

  =$A8>$C$3*$C$4

  notă Importantă!, Pentru ca formula de formatare condiționată să funcționeze corect, asigurați-vă că utilizați referințe absolute cell pentru termenul de împrumut și plățile pe an celule pe care le multiplicați ($C$3*$C$4). Produsul este comparat cu celula perioadei 1, pentru care utilizați o referință de celule mixte – coloană absolută și rând relativ ($A8).

  

  după aceea, faceți clic pe butonul Format… și alegeți culoarea fontului alb. Gata!,
  

  faceți un rezumat al împrumutului

  pentru a vedea dintr-o privire informațiile sumare despre împrumutul dvs., adăugați încă câteva formule în partea de sus a programului de amortizare.

  Total plăți (F2):

  =-SUM(B8:B367)

  dobânda Totală (F3):

  =-SUM(C8:C367)

  Dacă aveți plăți numere pozitive, elimina semnul minus din formulele de mai sus.

  asta este! Programul nostru de amortizare a împrumutului este finalizat și gata de plecare!,
  

  Descărcare de amortizare a împrumutului, programul Excel

  Cum de a face un împrumut program de amortizare cu plăți suplimentare în Excel

  amortizarea programele discutate în exemplele anterioare sunt ușor de a crea și urmări (sper :). Cu toate acestea, acestea lasă o caracteristică utilă de care sunt interesați mulți plătitori de împrumuturi – plăți suplimentare pentru a plăti mai repede un împrumut. În acest exemplu, vom analiza modul de creare a unui program de amortizare a împrumutului cu plăți suplimentare.

  definiți celulele de intrare

  ca de obicei, începeți cu configurarea celulelor de intrare., În acest caz, să numească aceste celule scrise mai jos pentru a face formulele noastre mai ușor de citit:

  • InterestRate – C2 (rata anuală a dobânzii)
  • LoanTerm – C3 (pe termen de împrumut în ani)
  • PaymentsPerYear – C4 (numărul de plăți pe an)
  • LoanAmount – C5 (suma totală a împrumutului)
  • ExtraPayment – C6 (cu plată suplimentară pentru fiecare perioadă)

  

  Calcula o plată programată

  în Afară de celulele de intrare, unul mai predefinite mobil este necesar pentru calculele ulterioare – programate suma de plată, și anume, suma care trebuie plătită pentru un împrumut dacă nu se efectuează plăți suplimentare. Această sumă este calculată cu următoarea formulă:

  =IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")

  vă rugăm să acordați atenție că punem un semn minus înainte ca funcția PMT să aibă rezultatul ca număr pozitiv. Pentru a preveni erorile în cazul în care unele dintre celulele de intrare sunt goale, anexăm formula PMT în funcția IFERROR.

  introduceți această formulă într-o celulă (G2 în cazul nostru) și denumiți acea celulă Programatăplata.,
  

  configurați tabelul de amortizare

  creați un tabel de amortizare a împrumutului cu anteturile prezentate în imaginea de mai jos. În coloana perioadă introduceți o serie de numere care încep cu zero (puteți ascunde rândul perioadă 0 mai târziu, dacă este necesar).dacă doriți să creați un program de amortizare reutilizabil, introduceți numărul maxim posibil de perioade de plată (de la 0 la 360 în acest exemplu).pentru perioada 0 (rândul 9 în cazul nostru), trageți valoarea soldului, care este egală cu suma inițială a împrumutului., Toate celelalte celule din acest rând va rămâne gol:

  Formula în G9:

  =LoanAmount

  

  4. Construiți formule pentru programul de amortizare cu plăți suplimentare

  aceasta este o parte esențială a activității noastre. Deoarece funcțiile încorporate ale Excel nu prevăd plăți suplimentare, va trebui să facem toată matematica pe cont propriu.

  notă. În acest exemplu, perioada 0 este în rândul 9 și perioada 1 este în rândul 10. Dacă tabelul dvs. de amortizare începe într-un rând diferit, asigurați-vă că ajustați referințele celulelor în consecință.,

  introduceți următoarele formule în rândul 10 (perioada 1), apoi copiați-le în jos pentru toate perioadele rămase.plata programată (B10):

  dacă suma de plată programată (numită celulă G2) este mai mică sau egală cu soldul rămas (G9), utilizați plata programată. În caz contrar, adăugați soldul rămas și dobânda pentru luna precedentă.

  =IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")

  ca o precauție suplimentară, înfășurăm aceasta și toate formulele ulterioare în funcția IFERROR., Acest lucru va preveni o grămadă de erori diferite dacă unele dintre celulele de intrare sunt goale sau conțin valori nevalide.

  plată suplimentară (C10):

  utilizați o formulă IF cu următoarea logică:

  dacă suma de plată suplimentară (numită celulă C6) este mai mică decât diferența dintre soldul rămas și principalul acestei perioade (G9-E10), returnați plata suplimentară; în caz contrar, utilizați diferența.

  =IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")

  sfat. Dacă aveți plăți suplimentare variabile, trebuie doar să tastați sumele individuale direct în coloana de plată suplimentară.,pur și simplu, adăugați plata programată (B10) și plata suplimentară (C10) pentru perioada curentă:

  =IFERROR(B10+C10, "")

  Principal (E10)

  dacă plata programată pentru o anumită perioadă este mai mare decât zero, returnați o valoare mai mică dintre cele două valori: plata programată minus dobânda (B10-F10) sau soldul rămas (G9); altfel returnați zero.

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

  vă rugăm să rețineți că principalul include doar partea plății programate (nu plata suplimentară!) care merge spre principalul împrumutului.,dacă plata programului pentru o anumită perioadă este mai mare decât zero, împărțiți rata anuală a dobânzii (celula C2 numită) la numărul de plăți pe an (celula C4 numită) și înmulțiți rezultatul cu soldul rămas după perioada anterioară; în caz contrar, returnați 0.

  =IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")

  Echilibru (G10)

  Dacă soldul (G9) este mai mare decât zero, scade porțiunea principală de plată (E10) și de plată suplimentare (C10) din soldul rămas după perioada precedentă (G9); altfel întoarce 0.,

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

  notă. Deoarece unele dintre formule se referă reciproc (nu referință circulară!), acestea pot afișa rezultate greșite în acest proces. Deci, vă rugăm să nu începeți depanarea până când nu introduceți ultima formulă în tabelul de amortizare.

  Dacă totul se face corect, împrumut program de amortizare la acest punct ar trebui să arate ceva de genul asta:
  

  Ascunde suplimentar perioade

  a înființat un formatarea condițională regulă pentru a ascunde valorile neutilizate în perioadele de cum se explică în acest sfat.,plăți pe an:

  =LoanTerm*PaymentsPerYear

  numărul Real de plăți:

  Număra celulele din Totalul de Plată coloană care sunt mai mari decât zero, începând cu Perioada 1:

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

  Total plăți suplimentare:

  se Adaugă celule în Plată Suplimentară coloană, începând cu Perioada 1:

  =SUM(C10:C369)

  dobânda Totală:

  se Adaugă celule în Interesul coloană, începând cu Perioada 1:

  =SUM(F10:F369)

  Opțional, ascunde 0 Perioada rând, și dumneavoastră de împrumut program de amortizare cu plățile suplimentare se face!, Imaginea de mai jos arata rezultatul final:
  

  Descărcare de amortizare a împrumutului, programul cu plăți suplimentare

  program de Amortizare șablon Excel

  Pentru a face un top-notch de împrumut program de amortizare în timp, face uz de Excel încorporat șabloane. Doar du-te la File > Noi, de tip „graficul de rambursare” în caseta de căutare și alegeți șablonul pe care doriți, de exemplu, asta cu plăți suplimentare:
  

  acesta este modul în care creați un credit ipotecar sau program de amortizare în Excel., Vă mulțumesc că ați citit și sper să vă vedem pe blogul nostru săptămâna viitoare!

  Disponibile descărcări

  Amortizare Programa Probă de Registru de lucru

  • Cum de a calcula dobânda compusă în Excel
  • Cum de a găsi CAGR (rata de creștere anuală compusă) în Excel
  • Calcularea procentului în Excel cu formula exemple