Naplánujte splátky pôžičiek pomocou vzorcov Excel

Splácanie úveru je úkon splácania peňazí, ktoré boli predtým požičané od veriteľa, zvyčajne prostredníctvom série pravidelných platieb, ktoré zahŕňajú istinu a úroky. Vedeli ste, že na výpočet splátok úveru môžete použiť softvérový program Excel?

Tento článok je krok za krokom sprievodcom nastavením výpočtov úverov.

Kľúčové jedlá

• Pomocou programu Excel získate informácie o hypotéke určením vašej mesačnej platby, úrokovej sadzby a časového plánu pôžičky.
• Môžete sa podrobnejšie pozrieť na členenie pôžičky s vynikajúcou hodnotou a vytvoriť splátkový kalendár, ktorý vám vyhovuje.
• K dispozícii sú výpočty pre každý krok, ktoré môžete vyladiť, aby vyhovovali vašim špecifickým potrebám.
• Členenie a skúmanie vašej pôžičky krok za krokom môže spôsobiť, že sa proces splácania bude cítiť menej ohromujúci a spravovateľnejší.

Pochopenie vašej hypotéky

Pomocou programu Excel môžete lepšie porozumieť svojej hypotéke v troch jednoduchých krokoch. Prvý krok určuje mesačnú platbu. Druhým krokom je výpočet úrokovej sadzby a tretí krok určuje splátkový kalendár.

V Exceli si môžete zostaviť tabuľku, ktorá vám oznámi úrokovú sadzbu, výpočet úveru na dobu trvania úveru, rozklad úveru, amortizáciu a mesačnú platbu.

Vypočítajte mesačnú platbu

Najskôr je uvedený postup výpočtu mesačnej splátky hypotéky. Pomocou ročnej úrokovej sadzby, istiny a doby trvania môžeme určiť sumu, ktorá sa má splatiť mesačne.

Vzorec, ako je uvedený na obrázku vyššie, je napísaný takto:

= -PMT (rýchlosť, dĺžka, Present_Value [future_value] [typ])

Znamienko mínus pred PMT je potrebné, pretože vzorec vráti záporné číslo. Prvé tri argumenty sú výška úveru, dĺžka úveru (počet období) a požičaná istina. Posledné dva argumenty sú voliteľné, zvyšková hodnota je predvolená na nulu; splatné vopred (za jednu) alebo na konci (za nulu) je tiež dobrovoľné.

Vzorec programu Excel používaný na výpočet mesačnej splátky úveru je:

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

Vysvetlenie: Pre sadzbu používame mesačnú sadzbu (obdobie sadzby), potom vypočítame počet období (120 za 10 rokov vynásobených 12 mesiacmi) a nakoniec označíme vypožičanú istinu. Naša mesačná platba bude 1 1081, 88 dolárov za 10 rokov.

Vypočítajte ročnú úrokovú sadzbu

Videli sme, ako nastaviť výpočet mesačnej splátky hypotéky. Možno by sme však chceli nastaviť maximálnu mesačnú platbu, ktorú si môžeme dovoliť, a ktorá tiež zobrazuje počet rokov, počas ktorých by sme museli úver splatiť. Z tohto dôvodu by sme chceli poznať príslušnú ročnú úrokovú sadzbu.

Ako je znázornené na obrázku vyššie, najprv vypočítame sadzbu obdobia (v našom prípade mesačne) a potom ročnú sadzbu. Použitý vzorec bude RATE, ako je to znázornené na obrázku vyššie. Je napísané takto:

= RATE (počet_období, PMT, Present_Value [future_value] [typ])

Prvé tri argumenty sú dĺžka úveru (počet období), mesačná splátka na splatenie úveru a požičaná istina. Posledné tri argumenty sú voliteľné a zvyšková hodnota je predvolená na nulu; argument pre riadenie splatnosti vopred (pre jednu) alebo na konci (pre nulu) je tiež voliteľný. Nakoniec argument odhad je voliteľný, ale môže poskytnúť počiatočný odhad sadzby.

Vzorec Excel použitý na výpočet úrokovej sadzby je:

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

Poznámka: zodpovedajúce údaje v mesačnej platbe musia byť označené záporným znamienkom. Preto je pred vzorcom znamienko mínus. Obdobie sadzby je 0, 294%.

Používame vzorec = (1 + B5) je 12-1 ^ = (1 + 0, 294%) ^ 12-1 na získanie ročnej sadzby našej pôžičky, ktorá je 3, 58%. Inými slovami, požičať si 120 000 dolárov na 13 rokov na zaplatenie 960 dolárov mesačne, mali by sme si dohodnúť pôžičku s ročnou maximálnou sadzbou 3, 58%.

Používanie programu Excel je vynikajúci spôsob, ako sledovať, čo dlhujete, a prísť s plánom splácania, ktorý minimalizuje poplatky, ktoré by ste mohli v konečnom dôsledku zaplatiť.

Určenie dĺžky úveru

Teraz zistíme, ako určiť dĺžku úveru, keď viete ročnú sadzbu, vypožičanú istinu a mesačnú splátku, ktorá sa má splatiť. Inými slovami, ako dlho budeme musieť splácať hypotéku 120 000 dolárov s úrokovou sadzbou 3, 10% a mesačnou splátkou 1 100 USD “>

Vzorec, ktorý použijeme, je NPER, ako je to znázornené na obrázku vyššie, a je napísané nasledovne:

= NPER (rýchlosť, PMT, Present_Value [future_value] [typ])

Prvé tri argumenty sú ročná sadzba úveru, mesačná splátka potrebná na splatenie úveru a požičaná istina. Posledné dva argumenty sú voliteľné, zvyšková hodnota je predvolená na nulu. Pojem argument splatný vopred (pre jedného) alebo na konci (pre nulu) je tiež voliteľný.

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

Poznámka: zodpovedajúce údaje v mesačnej platbe musia byť označené záporným znamienkom. Preto máme pred vzorcom znamienko mínus. Dĺžka úhrady je 127, 97 obdobia (v našom prípade mesiace).

Na dokončenie splácania úveru použijeme vzorec = B5 / 12 = 127, 97 / 12. Inými slovami, požičať si 120 000 dolárov, s ročnou úrokovou sadzbou 3, 10% a platiť 1100 dolárov mesačne, mali by sme splatiť splatnosť 128 mesiacov alebo 10 rokov a 8 mesiacov.

Rozklad úveru

Úverová splátka sa skladá z istiny a úrokov. Úrok sa vypočíta pre každé obdobie - napríklad mesačné splátky za 10 rokov nám poskytnú 120 období.

Vyššie uvedená tabuľka zobrazuje rozdelenie pôžičky (celkové obdobie rovnajúce sa 120) pomocou vzorcov PPMT a IPMT. Argumenty týchto dvoch vzorcov sú rovnaké a sú rozdelené takto:

= -PPMT (rýchlosť, num_period, dĺžka, hlavné [zvyškového] [výraz])

Argumenty sú rovnaké ako v prípade už uvedeného vzorca PMT, s výnimkou výrazu „num_period“, ktorý sa pridáva na zobrazenie obdobia, v ktorom sa má pôžička rozdeliť vzhľadom na istinu a úrok. Tu je príklad:

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

Výsledok je uvedený na snímke obrazovky nad „Rozklad úveru“ počas analyzovaného obdobia, ktoré je „jedno;“ to znamená prvé obdobie alebo prvý mesiac. Platíme 1 161, 88 dolárov rozdelených na 856, 20 dolárov istiny a úroky 305, 68 dolárov.

Výpočet pôžičky v Exceli

Je tiež možné vypočítať splátky istiny a úrokov za niekoľko období, napríklad prvých 12 mesiacov alebo prvých 15 mesiacov.

= -CUMPRINC (rýchlosť, dĺžka, hlavné, start_date, dátum_konca; typ)

Nájdeme argumenty, rýchlosť, dĺžku, istinu a termín (ktoré sú povinné), ktoré sme už videli v prvej časti so vzorcom PMT. Tu však potrebujeme aj argumenty „počiatočný dátum“ a „konečný dátum“. „Start_date“ označuje začiatok obdobia, ktoré sa má analyzovať, a „end_date“ označuje koniec obdobia, ktoré sa má analyzovať.

Tu je príklad:

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

Výsledok je zobrazený na snímke „Cumul 1. rok“, takže analyzované obdobia sa pohybujú od jedného do 12 prvého obdobia (prvý mesiac) do dvanásteho (12. mesiac). Za rok by sme zaplatili istinu 10 419, 55 dolárov a úroky 3 522 99, 99 dolárov.

Amortizácia úveru

Predchádzajúce vzorce nám umožňujú vytvárať naše plánovacie obdobie podľa obdobia, vedieť, koľko budeme platiť mesačne v istine a úrokoch, a vedieť, koľko zostáva zaplatiť.

Vytvorenie úverového plánu

Na vytvorenie rozvrhu pôžičiek použijeme rôzne vzorce uvedené vyššie a rozšírime ich o počet období.

V prvom stĺpci periódy zadajte ako prvú periódu „1“ a potom potiahnite bunku nadol. V našom prípade potrebujeme 120 období, pretože 10-ročná splátka úveru vynásobená 12 mesiacmi sa rovná 120.

Druhý stĺpec predstavuje mesačnú sumu, ktorú musíme platiť každý mesiac - ktorá je konštantná počas celého úverového plánu. Na výpočet sumy vložte do bunky nášho prvého obdobia nasledujúci vzorec:

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

Tretí stĺpec je istina, ktorá bude splatená mesačne. Napríklad za 40. obdobie splatíme istinu 945, 51 USD z našej mesačnej celkovej sumy 1 161, 88 USD.

Na výpočet vyplatenej istiny používame nasledujúci vzorec:

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

Štvrtý stĺpec je úrok, za ktorý pomocou vzorca vypočítame splátky istiny z našej mesačnej sumy, aby sme zistili, koľko úrokov sa má zaplatiť:

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

Piaty stĺpec obsahuje sumu, ktorá sa má vyplatiť. Napríklad po 40. platbe budeme musieť zaplatiť 83 994, 69 dolárov za 120 000 dolárov.

Vzorec je nasledujúci:

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

Vzorec používa kombináciu istiny v období pred bunkou obsahujúcou požičanú istinu. Toto obdobie sa začína meniť, keď kopírujeme a presúvame bunku nadol. Nasledujúca tabuľka ukazuje, že na konci 120 období je naša pôžička splatená.