Știați că puteți utiliza Excel pentru a calcula rambursările de împrumut? Acest articol vă va ajuta să faceți pașii necesari pentru a face acest lucru. (Vezi deasemenea: Calculatoare ipotecare: Cum functioneaza )

Folosind Excel, poti obtine o mai buna intelegere a ipotecii in trei pasi simpli. Primul pas este stabilirea plății lunare. Al doilea este de a descoperi rata dobânzii, iar a treia este de a găsi programul de împrumut. Pentru a face acest lucru, puteți crea o tabelă în Excel care vă va spune: Ratele de interes; calcularea împrumutului pe durată; descompunând un împrumut, precum și amortizarea și calcularea chiriei lunare.

Calcularea împrumutului pentru chiria lunară

Mai întâi, să vedem cum să punem în aplicare calculul unei plăți lunare pentru un credit ipotecar. Cu alte cuvinte, utilizând rata anuală de interese, principalul și durata, putem determina suma care trebuie rambursată lunar.

Formula, așa cum se arată în imaginea de mai sus, este scrisă după cum urmează:

- <->

= - PMT (rata; lungimea; valoarea actuala; [future_value]; [type])

Semnul minus in fata PMT este necesar, deoarece formula returneaza un numar 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ă la 0, plătibilă în avans (pentru 1) sau la final (pentru 0), este de asemenea opțională.

Formula Excel utilizată pentru a calcula plata lunară a împrumutului este:

= - PMT ((1 + B2) ^ (1/12) -1; = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Explicație: Pentru rata folosită pentru perioada de rată, numărul de perioade (luni aici 120 pentru 10 ani înmulțit cu 12 luni) și în final indicăm principalul împrumutat. Plata noastră lunară va fi de 1 $, 161.88 de peste 10 ani.

Calculul ipotecilor pentru ratele dobânzilor

Am văzut cum să stabilim calculul unei plăți lunare pentru un credit ipotecar. Dar am putea dori să stabilim o plată lunară maximă pe care o putem permite, care să afișeze și numărul de ani pe care ar trebui să-l rambursăm. Din acest motiv, dorim să cunoaștem rata anuală corespunzătoare a dobânzii.

Calculul ratei dobânzii pentru un împrumut

După cum se arată în captura de ecran de mai sus, calculăm mai întâi rata dobânzii (lunar în cazul nostru) și apoi rata anuală. Formula folosită va fi RATE, așa cum se arată în imaginea de mai sus, este scrisă după cum urmează:

= RATE (NPR; împrumutul (numărul de perioade) și plata lunară pentru rambursarea principalului împrumutat. Ultimele trei argumente sunt opționale, iar valoarea reziduală este defașită la 0, argumentul termenului pentru gestionarea scadenței în avans (pentru 1) sau la final (pentru 0) este, de asemenea, opțional și, în final, argumentul estimării este opțional. oferă o estimare inițială a ratei.

Formula de calcul Excel utilizată pentru a calcula rata de împrumut este:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; 960; 120000)

plata lunară trebuie să aibă un semn negativ. Acesta este motivul pentru care un semn minus înainte de formula. Perioada noastră de rată este 0. 294%.

Folosim formula

= (1 + B5) este 12-1 ^ = (1 + 0,294%) ^ 12-1 %. Cu alte cuvinte, pentru a împrumuta 120.000 $ peste 13 ani pentru a plăti lunar 960 $, ar trebui să negociem un împrumut cu o rată anuală maximă de 58%. Calculul ipotecilor pentru durata împrumutului

Vom vedea acum cum să obțineți durata unui împrumut atunci când cunoașteți rata anuală, principalul împrumutat și plata lunară care urmează a fi rambursată. Cu alte cuvinte, cât timp va trebui să rambursăm o ipotecă de 120.000 dolari, cu o rată de 3, 10% și o plată lunară de 1, 100?

Numărul de rambursări pentru un împrumut

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 (rate; pmt; current_value; [tip])

Primele trei argumente sunt rata anuală a împrumutului, plata lunară necesară pentru rambursarea împrumutului și principalul împrumutat. Ultimele două argumente sunt opționale, valoarea reziduală este defașită la 0, argumentul pe termen lung plătit în avans (pentru 1) sau la final (pentru 0) este de asemenea opțional.

= NPER (1 + B2) ^ (1/12) -1; -B4; B3) = NPER (1 + 3, 10%

Notă: datele corespunzătoare din plata lunară trebuie să aibă un semn negativ. Acesta este motivul pentru care avem un semn minus înainte de formula. Durata de rambursare este de 127. 97 de perioade (luni în cazul nostru).

Vom folosi formula = B5 / 12 = 127. 97/12 pentru numărul de ani pentru finalizarea rambursării împrumutului. Cu alte cuvinte, pentru a împrumuta 120.000 de dolari, cu o rată anuală de 3.10% și pentru a plăti $ 1, 100 lunar, ar trebui să plătim scadențele pentru 128 luni sau 10 ani și 8 luni.

Descompunerea împrumutului

Plata unui împrumut constă din două elemente, principalul și dobânda. Dobânda este calculată pentru fiecare perioadă, de exemplu rambursările lunare de peste 10 ani, ne va oferi 120 de perioade.

Imaginea 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 (rată; num_period; lungime; principal; [rezidual]; [terme])

= - INTPER; lungime principală [rezidual]; [terme])

Argumentele sunt aceleași ca și pentru formula PMT din prima parte, cu excepția num_period care se adaugă pentru a arăta perioada în care să se împartă împrumutul, oferind principalul și interesul pentru acesta. Să luăm un exemplu:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT (1 + 3, 10% (1 + 2) -1 -1; 10 * 12; 120000)

= - INTPER %) ^ (1/12) -1; 1; 10 * 12; 120000)

Rezultatul este cel prezentat în captura de ecran "Descompunere împrumut", pe perioada analizată care este "1" , sau prima lună.Pentru aceasta, plătim 1161 USD. 88, defalcate în 856 $, 20 principal și 305 $. 68 de interese.

Calculul împrumutului de la

Acum este de asemenea 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; lungimea; principalul; start_date; end_date; type)

= CUMIPMT (rata; principiul ș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. Primul indică începutul perioadei de analizat și al doilea sfârșitul. Să luăm un exemplu:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0) (1/12) -1; 10 * 12; 120000; 1; 12; 0); ; 1; 12; 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; unul afișat în captura de ecran "Cumul primul an", astfel încât perioadele analizate variază de la 1 la 12, din prima perioadă (prima lună) până în a douăsprezecea (a 12-a lună). Peste un an, am plăti 10 419, 55 Principale și 3 522. 99 Dobânzi.

Amortizarea împrumutului

Formulele anterioare ne permit să ne creăm perioada de programare în funcție de perioadă, cât de mult vom plăti lunar în principal și în interes și cât va fi plătită.

Creați o schemă de împrumut în Excel

Pentru a crea un program de împrumut, vom folosi diferite formule discutate mai sus și le vom extinde pe numărul de perioade.

În coloana din prima perioadă, pur și simplu introduceți "1" ca prima perioadă, apoi trageți celula în jos. În cazul nostru, avem nevoie de 120 de perioade de când o plată de 10 ani a împrumutului înmulțită cu 12 luni = 120.

A doua coloană este suma lunară pe care trebuie să o plătim în fiecare lună, care este constantă pe întreaga durată a împrumutului. Pentru a calcula, introduceți următoarea formulă în celula din prima noastră perioadă:

= - PMT (TP-1; B4 * 12; B3) = -PMT (1 + 3, 10% ) -1; 10 * 12; 120000)

A treia coloană este principalul care va fi rambursat lunar. De exemplu, pentru perioada 40, vom rambursa 945 USD. 51 în principal pentru suma lunară totală de 1, 161. 88. Pentru a calcula suma principală 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)

A patra coloană este dobânda pentru care calculam rambursarea principalului din suma lunară se va plăti un interes mare, folosind formula:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER (1 + 3, 10% ; 1; 10 * 12; 120000)

Cea de-a cincea coloană conține suma rămasă la plată. De exemplu, după cea de-a 40-a plată, vom plăti $ 83, 994. 69 pe $ 120, 000. Formula este după cum urmează:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; principal în perioada următoare, cu celula care conține principalul împrumutat. Această perioadă începe să se schimbe când copiem și tragem celula în jos.Imaginea de mai jos arată că, la sfârșitul celor 120 de perioade, împrumutul nostru este rambursat.