Planificați rambursările împrumutului cu formule Excel
Rambursarea împrumutului este actul de a rambursa banii împrumutați anterior de la un creditor, de obicei printr-o serie de plăți periodice care includ dobândă principală. Știați că puteți utiliza programul software Excel pentru calcularea rambursărilor împrumutului dvs.?
Acest articol este un ghid pas cu pas pentru configurarea calculelor împrumuturilor.
Cheie de luat cu cheie
- Utilizați Excel pentru a vă ocupa de creditul dvs. ipotecar, determinând plata lunară, rata dobânzii și programul de împrumut.
- Puteți arunca o privire mai detaliată la defalcarea unui împrumut cu excel și puteți crea un program de rambursare care funcționează pentru dvs.
- Există calcule disponibile pentru fiecare pas pe care îl puteți regla pentru a răspunde nevoilor dvs. specifice.
- Reducerea și examinarea pas cu pas a împrumutului dvs. poate face ca procesul de rambursare să se simtă mai puțin copleșitor și mai gestionabil.
Înțelegerea creditului dvs. ipotecar
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ă programul î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 se calculează 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 imaginea de mai sus, este scrisă după cum urmează:
= -PMT (rate; lungime; present_value; [valoare_viitoare]; [type])
Semnul minus în 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ă implicită la zero; se achită în avans (pentru unul) sau la sfârșit (pentru zero), este de asemenea opțional.
Formula Excel utilizată pentru calcularea plății lunare 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 rata, utilizăm rata lunară (perioada ratei), apoi calculăm numărul perioadelor (120 timp de 10 ani înmulțit cu 12 luni) și, în final, indicăm principalul împrumutat. Plata noastră lunară va fi de 1.161, 88 USD pe 10 ani.
Calculați rata anuală a dobânzii
Am văzut cum să stabilim calculul unei plăți lunare pentru o ipotecă. Dar este posibil să dorim să stabilim o plată lunară maximă pe care ne putem permite, care afișează și numărul de ani peste care va trebui să rambursăm împrumutul. Din acest motiv, am dori să cunoaștem rata dobânzii anuale corespunzătoare.
După cum se arată în imaginea de mai sus, mai întâi calculăm rata perioadei (lunar, în cazul nostru), apoi rata anuală. Formula folosită va fi RATE, așa cum se arată în imaginea de mai sus. Este scris astfel:
= RATE (Nper; pmt; present_value; [valoare_viitoare]; [type])
Primele trei argumente sunt durata împrumutului (numărul de perioade), plata lunară pentru rambursarea împrumutului și principalul împrumutat. Ultimele trei argumente sunt opționale, iar valoarea reziduală implicită este zero; termenul argument pentru gestionarea scadenței în avans (pentru una) 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 calcularea ratei 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 ratei 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 de 3, 58%. Cu alte cuvinte, pentru a împrumuta 120.000 USD pe 13 ani pentru a plăti 960 USD lunar, ar trebui să negociem un împrumut la o rată maximă anuală de 3, 58%.
Utilizarea Excel este o modalitate excelentă de a urmări ceea ce datorezi și de a oferi un program de rambursare care să reducă la minimum orice taxe pe care le-ai putea datora.
Determinarea lungimii unui împrumut
Vom vedea acum cum să stabilim 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 $ ">
Formula pe care o vom folosi este NPER, așa cum se arată în imaginea de mai sus și este scrisă astfel:
= NPER (rate; pmt; present_value; [valoare_viitoare]; [type])
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ă implicit la zero. Termenul de argument plătit î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)
Notă: 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 dolari lunar, ar trebui să rambursăm scadențele timp de 128 de luni sau 10 ani și 8 luni.
Descompunerea împrumutului
O plată de împrumut este compusă din capital și dobândă. Dobânda este calculată pentru fiecare perioadă - de exemplu, rambursările lunare pe 10 ani ne vor oferi 120 de perioade.
Tabelul de mai sus arată defalcarea unui împrumut (o perioadă totală egală cu 120) folosind formulele PPMT și IPMT. Argumentele celor două formule sunt aceleași și sunt defalcate după cum urmează:
= -PPMT (rate; num_period, lungime, principal; [rezidual]; [termenul])
Argumentele sunt aceleași ca pentru formula PMT deja văzută, cu excepția „num_period”, care este adăugată pentru a arăta perioada în care să se descompună împrumutul dat principalul și dobânda. 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 ecranul de mai sus "Descompunerea împrumutului" în perioada analizată, care este "una;" adică prima perioadă sau prima lună. Plătim 1.161, 88 USD defalcat în 856, 20 USD principal ș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; dată_început; end_date; tip)
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 și aici avem nevoie de argumentele „start_date” și „end_date”. „Start_date” indică începutul perioadei care urmează a fi analizată, iar „final_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 ecranul „Cumul primul an”, astfel încât perioadele analizate variază de la una la 12 din prima perioadă (prima lună) până la a douăsprezecea (a 12-a lună). Peste un an, vom plăti 10.419, 55 USD în capital și dobândă de 3.522, 99 USD.
Amortizarea împrumutului
Formulele anterioare ne permit să ne creăm programul pe perioade, să știm cât vom plăti lunar în principal și dobândă și să știm cât mai rămâne de plătit.
Crearea unui program de împrumut
Pentru a crea un program de împrumuturi, vom folosi diferitele formule discutate mai sus și le vom extinde pe numărul de perioade.
În coloana primei perioade, introduceți „1” ca primă perioadă și apoi trageți celula în jos. În cazul nostru, avem nevoie de 120 de perioade, deoarece o plată de 10 ani înmulțită cu 12 luni este egală cu 120.
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 din prima perioadă:
= -PMT (TP-1; 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 cea de-a 40-a perioadă, vom rambursa 945, 51 USD în principal pe suma totală lunară de 1.161, 88 USD.
Pentru a calcula suma principală răscumpărată, utilizăm 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 folosim formula pentru a calcula principalul rambursat pe suma noastră lunară pentru a descoperi cât de multă dobândă trebuie plătită:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
A cincea coloană conține suma rămasă de plătit. De exemplu, după cea de-a 40-a plată, va trebui să plătim 83.994, 69 USD pe 120 000 USD.
Formula este următoarea:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1, A18; 0)
Formula folosește o combinație de principal într-o perioadă anterioară celulei 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 se rambursează împrumutul nostru.