Věděli jste, že můžete použít Excel k výpočtu splátek úvěru? Tento článek vás provede všemi kroky, které potřebujete k tomu. (Viz také: Hypoteční kalkulačky: Jak pracují .)

Pomocí aplikace Excel můžete lépe pochopit hypotéku ve třech jednoduchých krocích. Prvním krokem je stanovení měsíční platby. Druhým je zjistit úrokovou sazbu a třetí je najít časový plán půjček. Chcete-li tak učinit, můžete vytvořit tabulku v aplikaci Excel, která vám řekne: Intenzivní sazby; výpočet úvěru na dobu trvání; rozložení úvěru, jakož i amortizace a výpočet měsíčního nájemného.

Výpočet úvěru za měsíční nájem

Za prvé uvidíme, jak provést výpočet měsíční platby za hypotéku. Jinými slovy, za použití roční míry zájmu, 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 (rychlost, délka, současná hodnota [future_value]; typ)

Znak mínus před PMT je nutný, protože vzorec vrací záporné číslo. Prvními třemi argumenty jsou míra půjčky, délka půjčky (počet období) a půjčená osoba. Poslední dva argumenty jsou nepovinné, zbylá hodnota výchozí je 0, splatná předem (za 1) nebo na konci (pro 0).

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 rychlost používáme časové pásmo, počet měsíců (zde 120 měsíců po dobu 10 let vynásobených 12 měsíci) a nakonec uvedeme hlavní vypůjčené. Naše měsíční splátka činí $ 1, 161. 88 za 10 let.

Hypoteční výpočet úrokových sazeb

Viděli jsme, jak nastavit výpočet měsíční splátky za hypotéku. Ale možná bychom chtěli stanovit maximální měsíční platbu, kterou si můžeme dovolit, a to také ukazuje počet let, za které bychom ji museli splácet. Z tohoto důvodu bychom rádi věděli odpovídající roční úrokovou sazbu.

Výpočet úrokové sazby pro úvěr

Jak je znázorněno na výše uvedené obrazovce, nejprve vypočítáme období (měsíčně v našem případě) a poté roční sazbu. Použitý vzorec bude RATE, jak je ukázáno na obrázku nahoře, je napsáno takto:

= RATE (budoucnost; pmt; present_value; [future_value]; [999]) První tři argumenty jsou délka úvěru (počet období) a měsíční splátku splátek jistiny vypůjčené. Poslední tři argumenty jsou nepovinné a výchozí hodnota je 0, termínový argument pro splatnost v předstihu (pro 1) nebo na konec (pro 0) je také nepovinný a nakonec argument pro odhad je nepovinný, ale může být poskytnout počáteční odhad sazby.

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

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

měsíční platba musí mít negativní znamení. To je důvod, proč znaménko mínus před vzorec. Naše sazba je 0,294%.

použijeme vzorec

= (1 + B5) je 12-1 ^ = (1 + 0,294%) ^ 12-1 %. Jinými slovy, půjčit 120 000 dolarů za 13 let, abychom zaplatili měsíčně 960 dolarů, bychom měli sjednávat půjčku s roční sazbou 3,58%. Hypoteční výpočet za délku úvěru

Nyní se ukážeme, jak získat délku půjčky, když znáte roční sazbu, hlavní půjčku a měsíční splátku, která má být splacena. Jinými slovy, jak dlouho budeme muset splácet hypotéku ve výši 120 000 USD se sazbou 3,10% a měsíční platbou ve výši 1, 100?

Počet splátek za úvěr

Vzorec, který použijeme, je NPER, jak je ukázáno na obrázku nahoře, a je napsán takto:

= NPER (rate; pmt; present_value; [future_value]; [typ])

Prvními třemi argumenty jsou roční sazba půjčky, měsíční platba potřebná k splacení úvěru a hlavní půjčka. Poslední dva argumenty jsou nepovinné, zbytková hodnota je výchozí hodnota 0, termínový argument splatný předem (pro 1) nebo na konci (pro 0) je také nepovinný.

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

Poznámka: Příslušné údaje v měsíční platbě musí být označeny záporným znaménkem. Proto máme před znamením mínus před vzorem. Délka úhrady činí 127. 97 období (v našem případě měsíce).

Použijeme vzorec = B5 / 12 = 127. 97/12 pro počet let k dokončení splácení úvěru. Jinými slovy, půjčit 120 000 dolarů, s roční sazbou 3,10% a platit $ 1, 100 měsíčně, bychom měli splácet splatnost za 128 měsíců nebo 10 let a 8 měsíců.

Rozložení úvěru

Úvěrová splátka se skládá ze dvou věcí, jistiny a úroku. Úrok je vypočten pro každé období, například měsíční splátky v průběhu 10 let, dává nám 120 období.

Výše ​​uvedená obrazovka zobrazuje rozpis půjčky (celkové období rovnající se 120) pomocí vzorců PPMT a IPMT. Argumenty obou vzorců jsou stejné a jsou rozděleny takto:

= - PPMT (rychlost; num_period; length; principal; [residual]; [terme])

= - INTPER (rate; délka, hlavní [reziduální], [terme])

Argumenty jsou stejné jako pro PMT vzorec vidět v první části, s výjimkou num_period, který je přidán, aby ukázal období, po které rozdělit úvěr, jistinu a zájem o ni. Uveďme příklad:

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

= - INTPER (1 + B2) ^ (1/12) -1; %) ^ (1/12) -1; 1; 10 * 12; 120000)

Výsledkem je výsledek zobrazený na obrazovce "Rozložení úvěru" v analyzovaném období, což je "1" , nebo v prvním měsíci.Za tohle zaplatíme 1161 dolarů. 88, rozdělených na 856 dolarů, 20 hlavních a 305 dolarů. 68 úroků.

Výpočet úvěru Excel

Nyní je také možné vypočítat splácení jistiny a úroku na několik období, jako jsou například prvních 12 měsíců nebo prvních 15 měsíců.

= - CUMPRINC (rychlost, délka, hlavní, start_date; end_date; typ)

= CUMIPMT (rychlost, délka, principu a termín (které jsou povinné), které jsme již v první části viděli s formulacem PMT. Ale zde potřebujeme také argumenty start_date a end_date. První označuje začátek analyzovaného období a druhý konec. Vezměme si příklad:

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

= CUMIPMT (1 + B2) , 1, 12, 0)

= - CUMIPMT (1 + 3, 10%) ^ (1/12) -1, 10 * 12, 120000; jeden zobrazený na obrazovce "Cumul 1. rok", takže analyzované období se pohybují od 1 do 12, od prvního období (první měsíc) do dvanáctého (12. měsíc). Za rok bychom zaplatili 10 419 dolarů, 55 dlužníků a 3 522. 99 úroků.

Amortizace úvěru

Předchozí vzorce nám umožňují vytvořit časové období podle období, kolik budeme platit měsíčně v jistině a úroku a kolik je zaplaceno.

Vytvořte schéma úvěru v aplikaci Excel

Chcete-li vytvořit plán půjček, použijeme výše popsané vzorce a rozšíříme je o počet období.

Ve sloupci prvního období jednoduše zadejte "1" jako první období a pak přetáhněte buňku dolů. V našem případě potřebujeme 120 období, protože 10letá výplata úvěru se vynásobí 12 měsíci = 120.

Druhý sloupec je měsíční částka, kterou musíme zaplatit každý měsíc, což je konstantní v celém rozvrhu úvěru. Pro výpočet vložte následující vzorec do buňky našeho prvního období:

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

Třetí sloupec je jistina, která bude splacena měsíčně. Například pro 40. období zaplatíme 945 dolarů. 51 z celkové měsíční částky ve výši 1, 161. 88. Pro výpočet výše splacené 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 zájem, pro který vypočítáme splácenou jistinu z naší měsíční částky, (1 + 3, 10%) ^ (1/12) je třeba zaplatit velkou úrokovou sazbu podle vzorce:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) ; 1; 10 * 12; 120000)

Pátý sloupec obsahuje částku, která je zaplacena. 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)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; hlavní za uplynulé období s buňkou obsahující vypůjčenou jistinu. Tato perioda se začne měnit při kopírování a přetažením buňky dolů.Níže uvedený snímek obrazovky ukazuje, že na konci 120 období je náš úvěr splacen.