Programați rambursările împrumuturilor cu formule Excel
Rambursarea împrumutului este actul de rambursare a banilor împrumutați anterior de la un creditor, de obicei printr-o serie de plăți periodice care includ principalul plus dobânzile. Știați că puteți utiliza programul Excel pentru a calcula rambursările împrumutului?
Acest articol este un ghid pas cu pas pentru stabilirea calculelor împrumuturilor.
Chei de luat masa:
- Utilizați Excel pentru a obține un control asupra ipotecii dvs. determinând plata lunară, rata dobânzii și programul de împrumut.
- Puteți arunca o privire mai detaliată asupra defalcării unui împrumut cu Excel și puteți crea un program de rambursare care să funcționeze pentru dvs.
- Există calcule disponibile pentru fiecare pas pe care le puteți modifica pentru a vă satisface nevoile specifice.
- Descompunerea și examinarea pas cu pas a împrumutului dvs. pot face ca procesul de rambursare să se simtă mai puțin copleșitor și mai ușor de gestionat.
Înțelegerea ipotecii dvs.
Folosind Excel, puteți obține o mai bună înțelegere a ipotecii dvs. în trei pași simpli. Primul pas determină plata lunară. Al doilea pas calculează rata dobânzii, iar al treilea pas determină graficul împrumutului.
Puteți construi un tabel în Excel care vă va indica rata dobânzii, calculul împrumutului pe durata împrumutului, descompunerea împrumutului, amortizarea și plata lunară.
Calculați plata lunară
În primul rând, iată cum să calculați plata lunară pentru o ipotecă. Folosind rata anuală a dobânzii, principalul și durata, putem determina suma care trebuie rambursată lunar.
Formula, așa cum se arată în captura de ecran de mai sus, este scrisă după cum urmează:
= -PMT (rata; lungime; valoare_ prezent; [valoare_ viitor]; [tip])
Semnul minus din fața PMT este necesar, deoarece formula returnează un număr negativ. Primele trei argumente sunt rata împrumutului, durata împrumutului (numărul de perioade) și principalul împrumutat. Ultimele două argumente sunt opționale, valoarea reziduală este implicită zero; plătibil în avans (pentru unul) sau la sfârșit (pentru zero) este, de asemenea, opțional.
Formula Excel utilizată pentru a calcula plata lunară a împrumutului este:
= PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Explicație: Pentru tarif, folosim rata lunară (perioada ratei), apoi calculăm numărul perioadelor (120 timp de 10 ani înmulțit cu 12 luni) și, în cele din urmă, indicăm principalul împrumutat. Plata noastră lunară va fi de 1.161,88 USD pe o perioadă de 10 ani.
Calculați rata anuală a dobânzii
Am văzut cum să setăm calculul unei plăți lunare pentru o ipotecă. Dar s-ar putea să dorim să stabilim o plată lunară maximă pe care ne-o putem permite, care să afișeze, de asemenea, numărul de ani peste care ar trebui să rambursăm împrumutul. Din acest motiv, am dori să cunoaștem rata anuală a dobânzii corespunzătoare.
După cum se arată în captura de ecran de mai sus, calculăm mai întâi rata perioadei (lunar, în cazul nostru), apoi rata anuală. Formula utilizată va fi RATE, așa cum se arată în captura de ecran de mai sus. Este scris astfel:
= RATE (Nper; pmt; present_value; [future_value]; [type])
Primele trei argumente sunt durata împrumutului (numărul perioadelor), plata lunară pentru rambursarea împrumutului și principalul împrumutat. Ultimele trei argumente sunt opționale, iar valoarea reziduală este implicită zero; termenul argument pentru gestionarea scadenței în avans (pentru unul) sau la sfârșit (pentru zero) este, de asemenea, opțional. În cele din urmă, argumentul estimării este opțional, dar poate oferi o estimare inițială a ratei.
Formula Excel utilizată pentru a calcula rata de creditare este:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Notă: datele corespunzătoare din plata lunară trebuie să primească un semn negativ. Acesta este motivul pentru care există un semn minus înainte de formulă. Perioada de rată este de 0,294%.
Folosim formula = (1 + B5) este 12-1 ^ = (1 + 0,294%) ^ 12-1 pentru a obține rata anuală a împrumutului nostru, care este 3,58%. Cu alte cuvinte, pentru a împrumuta 120.000 USD pe o perioadă de 13 ani pentru a plăti 960 USD lunar, ar trebui să negociem un împrumut la o rată anuală maximă de 3,58%.
Utilizarea Excel este o modalitate excelentă de a ține evidența a ceea ce datorați și de a veni cu un program de rambursare care să minimizeze orice taxe pe care ați putea să le datorați.
Determinarea lungimii unui împrumut
Vom vedea acum cum să determinăm durata unui împrumut atunci când cunoașteți rata anuală, principalul împrumutat și plata lunară care urmează să fie rambursată. Cu alte cuvinte, cât timp va trebui să rambursăm o ipotecă de 120.000 USD cu o rată de 3,10% și o plată lunară de 1.100 USD?
Formula pe care o vom folosi este NPER, așa cum se arată în captura de ecran de mai sus și este scrisă după cum urmează:
= NPER (rata; pmt; valoare_ prezent; [valoare_ viitor]; [tip])
Primele trei argumente sunt rata anuală a împrumutului, plata lunară necesară rambursării împrumutului și principalul împrumutat. Ultimele două argumente sunt opționale, valoarea reziduală fiind implicită zero. Termenul argument plătibil în avans (pentru unul) sau la sfârșit (pentru zero) este, de asemenea, opțional.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Semn Minus Înainte de Formula
Datele corespunzătoare din plata lunară trebuie să primească un semn negativ. Acesta este motivul pentru care avem un semn minus înainte de formulă. Durata rambursării este de 127,97 perioade (luni în cazul nostru).
Vom folosi formula = B5 / 12 = 127,97 / 12 pentru numărul de ani pentru a finaliza rambursarea împrumutului. Cu alte cuvinte, pentru a împrumuta 120.000 USD, cu o rată anuală de 3,10% și pentru a plăti 1.100 USD lunar, ar trebui să rambursăm scadențele pentru 128 de luni sau 10 ani și opt luni.
Descompunerea împrumutului
O plată de împrumut este compusă din principal și dobânzi. Dobânda este calculată pentru fiecare perioadă – de exemplu, rambursările lunare de peste 10 ani ne vor oferi 120 de perioade.
Tabelul de mai sus prezintă defalcarea unui împrumut (o perioadă totală egală cu 120) utilizând formulele PPMT și IPMT. Argumentele celor două formule sunt aceleași și sunt defalcate după cum urmează:
= -PPMT (rata; num_period; lungime; principal; [rezidual]; [termen])
Argumentele sunt aceleași ca și pentru formula PMT deja văzută, cu excepția „num_period”, care este adăugată pentru a arăta perioada în care trebuie defalcat împrumutul dat de principal și dobândă. Iată un exemplu:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3,10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Rezultatul este afișat în captura de ecran de mai sus „Descompunerea împrumutului” pe perioada analizată, care este „una;” adică prima perioadă sau prima lună. Plătim 1.161,88 USD defalcat în principal de 856,20 USD și dobândă de 305,68 USD.
Calculul împrumutului în Excel
De asemenea, este posibil să se calculeze rambursarea principalului și a dobânzii pentru mai multe perioade, cum ar fi primele 12 luni sau primele 15 luni.
= -CUMPRINC (rata; lungime; principal; start_date; end_date; type)
Găsim argumentele, rata, lungimea, principalul și termenul (care sunt obligatorii) pe care le-am văzut deja în prima parte cu formula PMT. Dar aici avem nevoie și de argumentele „start_date” și „end_date”. „Start_date” indică începutul perioadei de analizat, iar „end_date” indică sfârșitul perioadei de analizat.
Iată un exemplu:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Rezultatul este afișat în captura de ecran „Cumul primul an”, astfel încât perioadele analizate variază de la unu la 12 din prima perioadă (prima lună) până la a douăsprezecea (a 12-a lună). Peste un an, am plăti 10.419,55 USD în principal și 3.522,99 USD în dobânzi.
Amortizarea împrumutului
Formulele anterioare ne permit să ne creăm programul perioadă cu perioadă, să știm cât vom plăti lunar în principal și dobânzi și să știm cât mai rămâne de plătit.
Crearea unui program de împrumut
Pentru a crea un program de împrumut, vom folosi diferitele formule discutate mai sus și le vom extinde pe parcursul numărului de perioade.
În prima coloană de perioadă, introduceți „1” ca prima perioadă și apoi trageți celula în jos. În cazul nostru, avem nevoie de 120 de perioade, deoarece o plată pe 10 ani a împrumutului înmulțită cu 12 luni este egală cu 120.
Cea de-a doua coloană este suma lunară pe care trebuie să o plătim în fiecare lună – care este constantă pe întregul program de împrumut. Pentru a calcula suma, introduceți următoarea formulă în celula primei noastre perioade:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
A treia coloană este principalul care va fi rambursat lunar. De exemplu, pentru a 40-a perioadă, vom rambursa 945,51 USD în principal din suma noastră lunară totală de 1.161,88 USD.
Pentru a calcula suma răscumpărată, folosim următoarea formulă:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Cea de-a patra coloană este dobânda, pentru care folosim formula pentru a calcula principalul rambursat din suma noastră lunară pentru a descoperi câtă dobândă trebuie plătită:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Cea de-a cincea coloană conține suma rămasă de plătit. De exemplu, după a 40-a plată, va trebui să plătim 83.994,69 USD pentru 120.000 USD.
Formula este următoarea:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
Formula utilizează o combinație de principal sub o perioadă înainte de celula care conține principalul împrumutat. Această perioadă începe să se schimbe atunci când copiem și tragem celula în jos. Tabelul de mai jos arată că la sfârșitul a 120 de perioade, împrumutul nostru este rambursat.