Naplánujte splátky úvěru pomocí vzorců Excelu

Splácení úvěru je úkon splácení peněz dříve vypůjčených od věřitele, obvykle prostřednictvím řady pravidelných plateb, které zahrnují jistinu a úroky. Věděli jste, že k výpočtu splátek půjčky můžete použít softwarový program Excel?

Tento článek je podrobným průvodcem nastavením výpočtů půjček.

Klíč s sebou

• Pomocí aplikace Excel získáte informace o své hypotéce stanovením měsíční splátky, úrokové sazby a splátkového kalendáře.
• Můžete se hlouběji podívat na rozpis půjčky s excelem a vytvořit splátkový kalendář, který vám vyhovuje.
• Pro každý krok jsou k dispozici výpočty, které můžete vyladit, aby vyhovovaly vašim konkrétním potřebám.
• Rozdělení a prozkoumání vaší půjčky krok za krokem může způsobit, že se proces splácení bude cítit méně ohromující a lépe zvládnutelný.

Pochopení vaší hypotéky

Pomocí Excelu můžete lépe porozumět své hypotéce ve třech jednoduchých krocích. První krok určuje měsíční platbu. Druhým krokem je výpočet úrokové sazby a třetí krok určuje splátkový kalendář.

Můžete si vytvořit tabulku v Excelu, která vám řekne úrokovou sazbu, výpočet úvěru na dobu trvání úvěru, rozklad úvěru, amortizaci a měsíční splátku.

Vypočítejte měsíční platbu

Nejprve je zde uveden způsob výpočtu měsíční splátky hypotéky. Pomocí roční úrokové sazby, jistiny a doby trvání můžeme určit částku, která má být splacena měsíčně.

Vzorec, jak je znázorněn na obrázku výše, je napsán následovně:

= -PMT (rate; length; present_value; [future_value]; [type])

Znaménko minus před PMT je nutné, protože vzorec vrací záporné číslo. První tři argumenty jsou sazba půjčky, délka půjčky (počet období) a půjčka jistiny. Poslední dva argumenty jsou volitelné, zbytková hodnota je implicitně nula; splatné předem (za jednu) nebo na konci (za nulu), je rovněž volitelné.

Vzorec Excel použitý pro výpočet měsíční splátky úvěru je:

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

Vysvětlení: Pro sazbu používáme měsíční sazbu (období sazby), poté spočítáme počet období (120 za 10 let vynásobených 12 měsíci) a nakonec označíme vypůjčenou jistinu. Naše měsíční platba bude za 10 let 1, 161, 88 $.

Vypočítejte roční úrokovou sazbu

Viděli jsme, jak nastavit výpočet měsíční splátky hypotéky. Možná však budeme chtít nastavit maximální měsíční splátku, kterou si můžeme dovolit, a také zobrazit počet let, během kterých bychom museli půjčku splácet. Z tohoto důvodu bychom chtěli znát odpovídající roční úrokovou sazbu.

Jak je uvedeno na obrázku výše, nejprve vypočítáme sazbu období (v našem případě měsíčně) a poté roční sazbu. Použitý vzorec bude RATE, jak je uvedeno na obrázku výše. Je psáno následovně:

= RATE (Nper; pmt; present_value; [future_value]; [type])

První tři argumenty jsou délka úvěru (počet období), měsíční splátka na splácení úvěru a jistina půjčená. Poslední tři argumenty jsou volitelné a zbytková hodnota je implicitně nula; argument argumentu pro řízení splatnosti předem (pro jednoho) nebo na konci (pro nulu) je rovněž volitelný. A konečně argument odhadu je volitelný, ale může poskytnout počáteční odhad míry.

Vzorec Excel používaný k výpočtu úrokové sazby je:

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

Poznámka: odpovídající údaje v měsíční platbě musí být označeny záporným znaménkem. Proto je před vzorcem znaménko mínus. Období sazby je 0, 294%.

Používáme vzorec = (1 + B5) je 12-1 ^ = (1 + 0, 294%) ^ 12-1 k získání roční sazby naší půjčky, což je 3, 58%. Jinými slovy, půjčit si 120 000 dolarů na 13 let a platit 960 $ měsíčně, měli bychom sjednat půjčku s roční maximální sazbou 3, 58%.

Použití Excelu je skvělý způsob, jak sledovat, co dlužíte, a přijít s harmonogramem splácení, který minimalizuje poplatky, které by vám nakonec mohly být dlužné.

Stanovení délky úvěru

Uvidíme, jak určit délku půjčky, když znáte roční sazbu, vypůjčenou jistinu a měsíční splátku, která má být splacena. Jinými slovy, jak dlouho budeme muset splácet hypotéku 120 000 $ s úrokovou sazbou 3, 10% a měsíční splátkou 1 100 $ ">

Vzorec, který použijeme, je NPER, jak je uvedeno na obrázku výše, a je psán následovně:

= NPER (sazba; pmt; present_value; [future_value]; [type])

První tři argumenty jsou roční sazba půjčky, měsíční splátka potřebná k splacení půjčky a půjčka jistiny. Poslední dva argumenty jsou volitelné, zbytková hodnota je implicitně nula. Termín argument splatný předem (pro jednoho) nebo na konci (pro nulu) je také volitelný.

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

Poznámka: odpovídající údaje v měsíční platbě musí být označeny záporným znaménkem. To je důvod, proč máme znaménko minus před vzorcem. Délka úhrady je 127, 97 období (v našem případě měsíce).

Pro dokončení splácení půjčky použijeme vzorec = B5 / 12 = 127, 97 / 12 pro počet let. Jinými slovy, půjčit si 120 000 $, s roční sazbou 3, 10% a platit 1100 $ měsíčně, měli bychom splatit splatnosti po dobu 128 měsíců nebo 10 let a 8 měsíců.

Rozklad úvěru

Úvěrová splátka se skládá z jistiny a úroků. Úrok se počítá pro každé období - například měsíční splátky za 10 let nám poskytnou 120 období.

Tabulka výše ukazuje rozdělení úvěru (celková doba rovnající se 120) pomocí vzorců PPMT a IPMT. Argumenty obou vzorců jsou stejné a dělí se takto:

= -PPMT (rychlost; num_period; délka; jistina; [zbytkový]; [termín])

Argumenty jsou stejné jako pro již viděný vzorec PMT, s výjimkou „num_period“, které se přidává k zobrazení období, během kterého se má úvěr rozdělit vzhledem k jistině a úroku. Zde je příklad:

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

Výsledek je zobrazen na snímku obrazovky nad „Úvěrový rozklad“ za analyzované období, což je „jeden;“ to znamená první období nebo první měsíc. Platíme 1 161, 88 $ rozdělených na 856, 20 $ jistiny a 305, 68 $ úroky.

Výpočet půjčky v Excelu

Je také možné vypočítat splátky jistiny a úroků za několik období, například prvních 12 měsíců nebo prvních 15 měsíců.

= -CUMPRINC (rychlost; délka; hlavní; začátek_datum; konec_datum; typ)

Najdeme argumenty, rychlost, délku, princip a termín (které jsou povinné), které jsme již viděli v první části se vzorcem PMT. Zde však potřebujeme také argumenty "start_date" a "end_date". "Start_date" označuje začátek analyzované periody a "end_date" označuje konec analyzované periody.

Zde je příklad:

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

Výsledek je zobrazen na snímku „Cumul 1. rok“, takže analyzovaná období se pohybují od jednoho do 12 prvního období (od prvního měsíce) do dvanáctého (12. měsíce). Za rok bychom platili jistinu 10 419, 55 $ a úroky 3 522 99, 99.

Amortizace úvěru

Předchozí vzorce nám umožňují vytvořit plánovací období podle období, vědět, kolik zaplatíme měsíčně jistinu a úroky, a vědět, kolik zbývá zaplatit.

Vytvoření úvěrového plánu

K vytvoření splátkového kalendáře použijeme výše uvedené vzorce a rozšíříme je o několik období.

Do prvního sloupce období zadejte jako první období „1“ a poté přetáhněte buňku dolů. V našem případě potřebujeme 120 období, protože 10letá splátka úvěru vynásobená 12 měsíci se rovná 120.

Druhý sloupec je měsíční částka, kterou musíme každý měsíc platit - což je konstantní po celou dobu trvání úvěru. Chcete-li vypočítat částku, vložte do buňky našeho prvního období následující vzorec:

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

Třetí sloupec je jistina, která bude splacena měsíčně. Například za 40. období splatíme jistinu 945, 51 USD z naší celkové měsíční částky 1 161, 88 $.

K výpočtu splátky jistiny používáme následující vzorec:

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

Čtvrtý sloupec je úrok, za který pomocí vzorce vypočítáme jistinu splacenou z naší měsíční částky, abychom zjistili, kolik úroku má být zaplaceno:

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

Pátý sloupec obsahuje částku zbývající na zaplacení. Například po 40. platbě budeme muset zaplatit 83, 994, 69 $ za 120 000 $.

Vzorec je následující:

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

Vzorec používá kombinaci jistiny v období před buňkou obsahující jistinu půjčenou. Toto období se začíná měnit, když zkopírujeme a přetáhneme buňku dolů. Níže uvedená tabulka ukazuje, že na konci 120 období je naše půjčka splacena.