Zakažite otplatu zajma s Excel formulama

Otplata zajma je čin povrata novca koji je prethodno bio posuđen od zajmodavca, obično kroz niz periodičnih plaćanja koja uključuju glavnicu plus kamate. Jeste li znali da za izračun otplate kredita možete koristiti softverski program Excel?

Ovaj je članak korak po korak vodič za postavljanje izračuna zajma.

Ključni odvodi

• Koristite Excel kako biste dobili odgovor na hipoteku kroz određivanje mjesečne uplate, kamatne stope i rasporeda zajma.
• Možete detaljnije pogledati raspodjelu zajma excelom i stvoriti raspored otplate koji vam odgovara.
• Na raspolaganju su izračuni za svaki korak koji možete prilagoditi kako bi zadovoljili vaše specifične potrebe.
• Raščlanjivanje i ispitivanje zajma korak po korak može učiniti da postupak otplate postane manje neodoljiv i upravljiviji.

Razumijevanje vaše hipoteke

Pomoću Excela možete bolje razumjeti hipoteku u tri jednostavna koraka. Prvi korak određuje mjesečno plaćanje. Drugi korak izračunava kamatnu stopu, a treći korak određuje raspored kredita.

Možete izraditi tablicu u Excelu koja će vam pokazati kamatnu stopu, izračun zajam za vrijeme trajanja zajma, dekompoziciju zajma, amortizaciju i mjesečno plaćanje.

Izračunajte mjesečnu uplatu

Prvo, evo kako izračunati mjesečnu uplatu za hipoteku. Pomoću godišnje kamatne stope, glavnice i trajanja možemo odrediti iznos koji se vraća mjesečno.

Formula, kao što je prikazano na gornjoj snimci zaslona, ​​piše ovako:

= -PMT (brzina, duljina, sadaĹĄnja_vrijednost [buduća_vrijednost] [tipa])

Znak minus ispred PMT-a je potreban jer formula vraća negativni broj. Prva tri argumenta su stopa zajma, duljina zajma (broj razdoblja) i glavnica koju su posudili. Zadnja dva argumenta su neobavezna, preostala vrijednost je zadana na nulu; plaća se unaprijed (za jedan) ili na kraju (za nulu), također nije obvezna.

Excel formula koja se koristi za izračunavanje mjesečne isplate zajma je:

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

Objašnjenje: Za stopu koristimo mjesečnu stopu (razdoblje kamatne stope), zatim izračunavamo broj razdoblja (120 za 10 godina pomnoženo s 12 mjeseci) i, na kraju, označavamo glavnicu koja je posuđena. Naša mjesečna uplata iznosit će 1, 161, 88 USD tijekom 10 godina.

Izračunajte godišnju kamatnu stopu

Vidjeli smo kako postaviti račun za mjesečno plaćanje hipoteke. Ali možda ćemo htjeti odrediti maksimalno mjesečno plaćanje koje možemo sebi priuštiti, a to također prikazuje broj godina tijekom kojih bismo morali vratiti kredit. Iz tog razloga želimo znati odgovarajuću godišnju kamatnu stopu.

Kao što je prikazano na gornjoj snimci zaslona, ​​prvo izračunavamo stopu razdoblja (u našem slučaju mjesečno), a zatim godišnju stopu. Formula koja se koristi bit će RATE, kao što je prikazano na gornjoj snimci zaslona. Piše na sljedeći način:

= Stopa (Nper, PMT-sadaĹĄnja_vrijednost [buduća_vrijednost] [tipa])

Prva tri argumenta su duljina zajma (broj razdoblja), mjesečna uplata za vraćanje zajma i glavnica koju su posudili. Zadnja tri argumenta su neobavezna, a zaostala vrijednost zadana na nulu; termin termin za upravljanje dospijećem unaprijed (za jedan) ili na kraju (za nulu) je također izborno. Konačno, argument procjene nije obvezan, ali može dati početnu procjenu stope.

Excel formula koja se koristi za izračunavanje kamatne stope je:

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

Napomena: odgovarajući podaci mjesečne uplate moraju biti negativni. Zbog toga pred formulom postoji znak minus. Razdoblje stope je 0, 294%.

Koristimo formulu = (1 + B5) je 12-1 ^ = (1 + 0, 294%) ^ 12-1 za dobivanje godišnje stope našeg zajma, koja je 3, 58%. Drugim riječima, da bismo posudili 120.000 USD tijekom 13 godina za plaćanje 960 USD mjesečno, trebali bismo pregovarati o zajmu po godišnjoj maksimalnoj stopi od 3, 58%.

Korištenje programa Excel odličan je način praćenja onoga što dugujete i kreiranja rasporeda otplate koji minimaliziraju sve naknade koje biste mogli dugovati.

Određivanje dužine zajma

Sada ćemo vidjeti kako odrediti duljinu zajma kada znate godišnju stopu, glavnicu koju posudite i mjesečnu uplatu koja se treba otplatiti. Drugim riječima, koliko dugo će nam trebati da vratimo hipoteku od 120 000 američkih dolara sa stopom od 3, 10% i mjesečnim plaćanjem od 1100 USD ">

Formula koju ćemo koristiti je NPER, kao što je prikazano na gornjoj snimci zaslona, ​​a napisana je kako slijedi:

= NPER (brzina, PMT-sadaĹĄnja_vrijednost [buduća_vrijednost] [tipa])

Prva tri argumenta su godišnja stopa zajma, mjesečna uplata potrebna za vraćanje zajma i glavnica koja je posuđena. Zadnja dva argumenta su neobavezna, preostala vrijednost je zadana na nulu. Termin argument koji se plaća unaprijed (za jedan) ili na kraju (za nulu) također nije obvezan.

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

Napomena: odgovarajući podaci mjesečne uplate moraju biti negativni. Zbog toga imamo znak minus prije formule. Dužina povrata je 127, 97 razdoblja (u našem slučaju mjeseci).

Pomoću formule = B5 / 12 = 127, 97 / 12 koristit ćemo se godinama kako bismo dovršili otplatu kredita. Drugim riječima, da bismo posudili 120.000 USD, s godišnjom stopom od 3, 10% i da bismo plaćali 1.100 USD mjesečno, trebali bismo otplaćivati ​​dospijeće za 128 mjeseci ili 10 godina i 8 mjeseci.

Prestanak zajma

Plaćanje zajma sastoji se od glavnice i kamate. Kamata se izračunava za svako razdoblje - na primjer, mjesečna otplata tijekom 10 godina dat će nam 120 razdoblja.

Tablica iznad prikazuje raspodjelu zajma (ukupno razdoblje jednako 120) pomoću PPMT i IPMT formula. Argumenti dviju formula su isti i raščlanjeni su kako slijedi:

= -PPMT (brzina, num_period, duljina, glavni [preostala] [Pojam])

Argumenti su isti kao i kod PMT-ove formule koja je već viđena, osim "num_period", koji se dodaje kako bi se pokazalo razdoblje tijekom kojeg se zajam zajam uzima s obzirom na glavnicu i kamate. Evo primjera:

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

Rezultat je prikazan na snimci zaslona iznad "Raspad zajma" tijekom analiziranog razdoblja, a to je "jedan;" odnosno prvo razdoblje ili prvi mjesec. Plaćamo 1.161, 88 USD, raščlanjeno na glavnicu od 856.20, i kamate od 305.68 USD.

Račun zajma u Excelu

Također je moguće izračunati otplatu glavnice i kamata za nekoliko razdoblja poput prvih 12 mjeseci ili prvih 15 mjeseci.

= -CUMPRINC (brzina, duljina, glavni, start_date, završni_datum tipa)

Pronalazimo argumente, stopu, dužinu, glavnicu i pojam (koji su obavezni) koje smo već vidjeli u prvom dijelu s formulom PMT. Ali ovdje su nam potrebni i argumenti "start_date" i "end_date". "Početni datum" označava početak razdoblja koje se analizira, a "krajnji datum" označava kraj razdoblja koje se analizira.

Evo primjera:

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

Rezultat je prikazan na snimci zaslona "Cumul 1. godina", pa se analizirana razdoblja kreću od jednog do 12. prvog razdoblja (prvog mjeseca) do dvanaestog (12. mjeseca). Tijekom godinu dana platili bismo 10.419, 55 USD glavnice i 3.522, 99 USD kamate.

Amortizacija zajma

Prethodne formule omogućuju nam da kreiramo naš raspored po period, da znamo koliko ćemo mjesečno plaćati glavnicu i kamate i da znamo koliko nam preostaje za plaćanje.

Izrada rasporeda zajma

Za izradu rasporeda zajma koristit ćemo različite gore navedene formule i proširiti ih na broj razdoblja.

U prvom stupcu razdoblja unesite "1" kao prvo razdoblje, a zatim povucite ćeliju prema dolje. U našem slučaju potrebno nam je 120 razdoblja jer je 10-godišnje plaćanje zajma pomnoženo s 12 mjeseci jednako 120.

Drugi stupac je mjesečni iznos koji moramo plaćati svakog mjeseca, a koji je stalan u cijelom rasporedu kredita. Da biste izračunali količinu, unesite sljedeću formulu u ćeliju našeg prvog razdoblja:

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

Treći stupac je glavni koji će se otplaćivati ​​mjesečno. Na primjer, za 40. razdoblje vratit ćemo glavnicu u iznosu od 945, 51 USD na naš mjesečni ukupni iznos od 1161, 88 USD.

Za izračunavanje otkupljenog iznosa glavnice koristimo sljedeću formulu:

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

Četvrti stupac je kamata, za koju pomoću formule izračunavamo otplatu glavnice na naš mjesečni iznos kako bismo otkrili koliki iznos kamate treba platiti:

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

Peti stupac sadrži preostali iznos za plaćanje. Na primjer, nakon 40. plaćanja, morat ćemo platiti 83, 994.69 USD na 120 000 USD.

Formula je sljedeća:

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

Formula koristi kombinaciju glavnice u razdoblju ispred ćelije koja sadrži glavnicu posuđenog. To se razdoblje počinje mijenjati kad kopiramo i povlačimo ćeliju prema dolje. Tablica u nastavku pokazuje da nam se na kraju 120 razdoblja otplaćuje zajam.