Razporedite odplačila posojila s Excelovimi formulami

Odplačilo posojila je dejanje vračila denarja, prej izposojenega pri posojilodajalcu, običajno z nizom periodičnih plačil, ki vključujejo glavnico in obresti. Ali ste vedeli, da lahko uporabite programski program Excel za izračun posojila?

Ta članek je navodila po korakih za pripravo izračunov posojila.

Ključni odvzemi

• Uporabite Excel, da se odločite za hipoteko z določitvijo mesečnega plačila, obrestne mere in razporeda posojila.
• Lahko si podrobneje ogledate razčlenitev posojila z excelom in ustvarite načrt odplačevanja, ki vam ustreza.
• Za vsak korak so na voljo izračuni, ki jih lahko prilagodite svojim potrebam.
• Če se korak po korakih razkrije in preuči vaše posojilo, se postopek odplačevanja zdi manj preobsežen in bolj obvladljiv.

Razumevanje vaše hipoteke

S pomočjo Excela lahko v treh preprostih korakih bolje razumete hipoteko. Prvi korak določa mesečno plačilo. Drugi korak izračuna obrestno mero, tretji korak pa določi razpored posojila.

V Excelu lahko sestavite tabelo, ki vam bo povedala obrestno mero, izračun posojila za čas trajanja posojila, razdelitev posojila, amortizacijo in mesečno plačilo.

Izračunajte mesečno plačilo

Najprej je tukaj, kako izračunati mesečno plačilo za hipoteko. Z letno obrestno mero, glavnico in trajanjem lahko določimo znesek, ki ga je treba vrniti mesečno.

Formula, kot je prikazana na zgornjem posnetku zaslona, ​​je zapisana na naslednji način:

= -PMT (hitrost; dolžina; sedanja vrednost; [prihodnja vrednost]; [vrsta])

Znak minus pred PMT je nujen, saj formula vrne negativno število. Prvi trije argumenti so stopnja posojila, dolžina posojila (število obdobij) in glavnica izposojene. Zadnja dva argumenta sta neobvezna, preostala vrednost je privzeta na nič; plačilo vnaprej (za enega) ali na koncu (za nič), je tudi neobvezno.

Formula Excel, ki se uporablja za izračun mesečnega plačila posojila, je:

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

Pojasnilo: Za stopnjo uporabimo mesečno obrestno mero (obdobje obrestne mere), nato izračunamo število obdobij (120 za 10 let, pomnoženo z 12 meseci) in na koncu navedemo izposojeno glavnico. Naše plačilo bo v 10 letih znašalo 1.161, 88 USD.

Izračunajte letno obrestno mero

Videli smo, kako nastaviti hipoteko za izračun mesečnega plačila. Morda pa bomo želeli določiti najvišje mesečno plačilo, ki si ga lahko privoščimo, ki prikazuje tudi število let, v katerih bi morali odplačati posojilo. Zaradi tega bi radi vedeli ustrezno letno obrestno mero.

Kot je prikazano na zgornjem posnetku zaslona, ​​najprej izračunamo stopnjo obdobja (v našem primeru mesečno) in nato letno stopnjo. Uporabljena formula bo RATE, kot je prikazano na zgornjem posnetku zaslona. Napisano je tako:

= RATE (Nper; pmt; sedanja vrednost; [bodoča_vrednost]; [vrsta])

Prvi trije argumenti so dolžina posojila (število obdobij), mesečno plačilo za odplačilo posojila in izposojena glavnica. Zadnji trije argumenti so izbirni, preostala vrednost pa je privzeta na nič; terminski argument za upravljanje zapadlosti vnaprej (za eno) ali na koncu (za nič) ni prav tako neobvezen. Končno je argument ocene neobvezen, vendar lahko poda začetno oceno stopnje.

Formula Excel, ki se uporablja za izračun posojilne stopnje, je:

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

Opomba: ustrezni podatki v mesečnem plačilu morajo biti negativni. Zato je pred formulo znak minus. Obdobje je 0, 294%.

Uporabljamo formulo = (1 + B5) je 12-1 ^ = (1 + 0, 294%) ^ 12-1, da dobimo letno stopnjo našega posojila, ki je 3, 58%. Z drugimi besedami, za najem 120.000 dolarjev v 13 letih za plačilo 960 dolarjev mesečno bi se morali pogajati o posojilu po letni najvišji stopnji 3, 58%.

Uporaba programa Excel je odličen način za spremljanje dolga in oblikovanje razporeda za odplačilo, ki bo minimiziral morebitne provizije, ki jih morda dolgujete.

Določitev dolžine posojila

Zdaj bomo videli, kako določiti dolžino posojila, ko boste vedeli letno obrestno mero, izposojeno glavnico in mesečno plačilo, ki ga je treba vrniti. Z drugimi besedami, kako dolgo bomo morali odplačati hipoteko v višini 120.000 USD s stopnjo 3, 10% in mesečnim plačilom v višini 1100 USD ">

Formula, ki jo bomo uporabili, je NPER, kot je prikazano na zgornjem posnetku zaslona in je zapisana na naslednji način:

= NPER (stopnja; pmt; sedanja vrednost; [prihodnja vrednost]; [vrsta])

Prvi trije argumenti so letna stopnja posojila, mesečno plačilo, potrebno za poplačilo posojila, in glavnica izposojena. Zadnja dva argumenta sta neobvezna, preostala vrednost je privzeta na nič. Izraz argument, ki se plačuje vnaprej (za enega) ali na koncu (za nič), prav tako ni obvezen.

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

Opomba: ustrezni podatki v mesečnem plačilu morajo biti negativni. Zato imamo pred formulo znak minus. Dolžina povračila je 127, 97 obdobja (v našem primeru mesecev).

Za dokončanje odplačevanja posojila bomo uporabili formulo = B5 / 12 = 127, 97 / 12. Z drugimi besedami, če bi se zadolžili za 120.000 dolarjev z letno obrestno mero 3, 10% in za plačilo 1.100 dolarjev mesečno, bi morali odplačevati ročnosti za 128 mesecev ali 10 let in 8 mesecev.

Razkritje posojila

Plačilo posojila je sestavljeno iz glavnice in obresti. Obresti se izračunajo za vsako obdobje - na primer mesečna odplačila v 10 letih nam bodo dala 120 obdobij.

Zgornja tabela prikazuje razčlenitev posojila (skupno obdobje 120) po formuli PPMT in IPMT. Argumenti obeh formul so enaki in so razdeljeni na naslednji način:

= -PPMT (stopnja; število_period; dolžina; glavnica; [preostanek]; [izraz])

Argumenti so enaki kot že ugotovljena formula PMT, razen "num_period", ki se doda, da se prikaže obdobje, v katerem je treba razbiti posojilo glede na glavnico in obresti. Tu je primer:

= -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 posnetku zaslona nad "Razgradnja posojila" v analiziranem obdobju, kar je "eno;" torej prvo obdobje oz. Plačujemo 1.161, 88 dolarjev, razčlenjene na glavnico 856.20 dolarjev in 305, 68 dolarja obresti.

Izračun posojila v Excelu

Možno je izračunati tudi odplačilo glavnice in obresti za več obdobij, na primer prvih 12 mesecev ali prvih 15 mesecev.

= -CUMPRINC (stopnja; dolžina; glavna; začetni datum; končni datum; vrsta)

Najdemo argumente, stopnjo, dolžino, glavnico in izraz (ki so obvezni), ki smo jih že videli v prvem delu s formulo PMT. Toda tukaj potrebujemo tudi argumente "start_date" in "end_date". "Začetni datum" označuje začetek obdobja, ki ga je treba analizirati, "končni datum" pa konec obdobja, ki ga je treba analizirati.

Tu je primer:

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

Rezultat je prikazan na posnetku zaslona "Cumul 1. leto", zato se analizirana obdobja gibljejo od enega do 12. prvega obdobja (prvi mesec) do dvanajstega (12. meseca). V letu dni bi plačali 10.419, 55 USD glavnice in 3.522, 99 USD obresti.

Amortizacija posojila

Predhodne formule nam omogočajo, da oblikujemo svoje časovno obdobje po obdobjih, da vemo, koliko bomo plačevali mesečno glavnico in obresti in da vemo, koliko nam ostane.

Izdelava seznama posojil

Za izdelavo razporeda posojil bomo uporabili različne zgoraj opisane formule in jih razširili na število obdobij.

V prvem stolpcu obdobja vnesite "1" kot prvo obdobje in nato povlecite celico navzdol. V našem primeru potrebujemo 120 obdobij, saj je 10-letno plačilo posojila, pomnoženo z 12 meseci, enako 120.

Drugi stolpec je mesečni znesek, ki ga moramo plačati vsak mesec - kar je konstantno v celotnem razporedu posojil. Če želite izračunati količino, v celico prvega obdobja vstavite naslednjo formulo:

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

Tretji stolpec je glavnica, ki se bo odplačevala mesečno. Na primer, bomo v 40. obdobju odplačali 945, 51 dolarja glavnice v skupnem mesečnem znesku 1, 161, 88 dolarja.

Za izračun odkupljenega glavnega zneska uporabimo naslednjo formulo:

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

Četrti stolpec so obresti, za katere s formulo izračunamo vrnjeno glavnico na naš mesečni znesek, da ugotovimo, koliko obresti je treba plačati:

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

Peti stolpec vsebuje znesek, ki ga je treba plačati. Na primer, po 40. plačilu bomo morali plačati 83.994, 69 dolarja na 120.000 dolarjev.

Formula je naslednja:

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

Formula uporablja kombinacijo glavnice v obdobju pred celico, ki vsebuje izposojeno glavnico. To obdobje se začne spreminjati, ko celico kopiramo in povlečemo navzdol. Spodnja tabela kaže, da se ob koncu 120 obdobij naše posojilo odplačuje.