Hypoteční kalkulačka v Excelu je velmi jednoduchá na tvorbu. V dnešním díle si detailně ukážeme, jak si v Excelu můžete vytvořit vaši vlastní hypoteční kalkulačku, a to včetně amortizačního neboli splátkového kalendáře a ukazatelů LTV, DTI a DSTI. Hypoteční kalkulačka může být rovněž vytvořena ve dvou verzích, tu první verzi hypoteční kalkulačky budete moci vytvořit ve všech verzích Excelu. Tu druhou verzi hypoteční kalkulačky, plně dynamický amortizační kalendář, si mohou vytvořit pouze ti, kteří jsou předplatitelé služby Office 365.
Excelový soubor ke stažení
Hypoteční kalkulačka tvorba
Pro výpočet splátky hypotéky potřebujeme několik základních údajů neboli vstupů. Jelikož budeme chtít tyto vstupy během výpočtů měnit, tak si všechny vstupní informace napíšeme do samostatných řádků a ve vzorcích se budeme jen odkazovat na tyto buňky. V horní části excelového sešitu mám předpřipravené vstupy, které budou základem pro tvorbu hypoteční kalkulačky. Oranžově jsou označená pole, která jsou variabilní, nebudou v nich tedy žádné vzorce a bude na vás abyste si do nich vyplnili požadované hodnoty.
Hypoteční kalkulačka příprava vstupů
Náš hypotetický příklad pro tvorbu kalkulačky vypadá následovně. Máme rodinu, která si chce koupit nemovitost v celkové kupní ceně 5,5 mil. Kč. Naspořeno má přesně 20 % z celkové kupní ceny. Kvůli výši splátek by chtěli 30 letou splatnost a jejich čistý měsíční příjem, po odečtení životních nákladů, je přesně 50 000 Kč. Pro tvorbu hypoteční kalkulačky nám tyto údaje budou stačit.
První hodnotou, kterou pro výpočet hypotéky potřebujeme, je výše kupní ceny nemovitosti. Nejedná se o částku, kterou si chceme půjčit, ale o celkovou kupní cenu nemovitosti. V našem příkladu byla celková hodnota nemovitosti 5,5 mil. Kč, takže tuto hodnotu doplníme do buňky C2.
Druhým vstupem je výše LTV, neboli, kolik % z celkové kupní ceny nemovitosti potřebujeme půjčit v podobě hypotečního úvěru. Naše hypotetická rodina má naspořeno 20 % z kupní ceny nemovitosti, takže požadované LTV je 80 %. Do buňky C3 tedy doplníme hodnotu 80 %. Pokud bychom chtěli žádat o 90 % hypotéku, tak bychom do pole C3 zadali hodnotu 90 %.
Prvním výpočtem v buňce C4 je výše bankovního úvěru. Všimněte si, že buňka C4 u bankovního úvěru není obarvená na oranžovo, což znamená, že se za ní bude skrývat vzorec.
Abychom zjistili požadovanou výši hypotečního úvěru, tak jednoduše vynásobíme kupní cenu nemovitosti a LTV. Tedy 5,5 mil. Kč vynásobíme hodnotou 80 %.
Od banky si tedy potřebujeme půjčit 4,4 mil. Kč. Kolik potřebujeme vlastních zdrojů? To je rozdíl kupní ceny od výše poskytnutého bankovního úvěru. Pokud si tedy naše hypotetická rodina chce pořídit bydlení v hodnotě 5,5 mil. Kč, tak u 80 % LTV musí mít naspořeno 1,1 mil. Kč. Pokud by jí banka půjčila 90 %, tak by jí stačilo mít naspořeno pouze 550 tisíc Kč.
Dalším variabilním vstupem, který doplníme je úroková sazba, a to do buňky C6. Může se jednat o průměrnou úrokovou sazbu na trhu nebo o sazbu, na kterou jste již přímo dostali nabídku v bance. Pro náš hypotetický příklad doplníme například sazbu 4,25 % p.a., což značí, že se jedná o roční úrokovou sazbu. Jelikož budeme počítat v našem modelu ale s měsíčním splácením, tak potřebujeme roční úrokovou sazbu převést na měsíční vyjádření. Jednoduše v buňce C7 roční úrokovou sazbu vydělíme číslem 12.
Dalším variabilním vstupem je celková splatnost úvěru v letech, zde doplníme do buňky C8 z našeho příkladu požadavek na splatnost ve výši 30 let. Amortizační kalendář bude ale počítat měsíční splátky, takže si do další buňky C9 ještě vyjádříme dobu splatnosti v měsících. Tedy 30 krát 12 měsíců.
Posledním vstupem, který potřebujeme do buňky C10 je čistý měsíční příjem. V našem příkladu měla rodina příjem ve výši 50 000 Kč.
Vstupní data máme připravená. Pro výpočet splátky hypotéky a limitů DTI a DSTI nám tyto vstupy budou stačit.
Výpočet anuitní splátky
Začneme výpočtem anuitní měsíční splátky v buňce C12.
Anuitní měsíční splátky hypotečního úvěru spočítáme díky funkci PLATBA. Ti z vás, kteří mají Excel v angličtině, tak se jedná o funkci PMT, neboli zkratka pro Payment.
=PLATBA/PMT (sazba; pper; současná hodnota)
Funkce PLATBA má tři základní parametry. Prvním parametrem je sazba. Jelikož hypotéku plánujeme splácet měsíčně, tak doplníme měsíční úrokovou sazbu, kterou jsme si dopředu spočítali. Druhý parametrem je pper, tedy počet období. Plánovaná doba splatnosti hypotéky je 30 let, jenže budeme splácet měsíčně, takže použijeme splatnost v měsících. Třetím povinným argumentem je současná hodnota. V tomto případě se jedná se o celkovou výši hypotečního úvěru. Označíme tedy pole s bankovním úvěrem. Pokud vyplníte pouze výši bankovního úvěru, tak se vám výsledná splátka zobrazí s mínusovým znaménkem, což je z pohledu cashflow v pořádku. Jedná se o mínusovou platbu z pohledu vašich osobních financí. Ve splátkových modelech je ovšem praktičtější používat kladnou hodnotu. Zde máte dvě možnosti, buď před současnou hodnotu napsat znaménko mínus a nebo napsat znaménko mínus před celou funkci PLATBA. Výsledek je stejný.
Výpočet ukazatelů DTI a DSTI
Proto, abychom mohli posoudit, zda splníme parametry DTI a DSTI, tak musíme znát i čistou výši měsíčních příjmů žadatelů. To jsou celkové měsíční příjmy od kterých se odečtou životní náklady, případně jiné splátky úvěrů, které již splácíme. V našem příkladu má naše domácnost čistý měsíční příjem 50 000 Kč.
Ukazatele DTI a DSTI používají komerční banky k tomu, aby ověřili schopnost žadatele o hypotéku splácet.
Ukazatel DTI
Ukazatel DTI je ukazatelem celkového dluhu žadatele vyjádřený jako násobek jeho čistého ročního příjmu. Od 1. dubna 2022 bude tento ukazatel nastavený na hodnotu 8,5 pro žadatele nad 36 let věku, pro mladší žadatele bude povolený až na hodnotu 9,5. Zkusíme pro náš hypotetický příklad nastavit do buňky C14 hodnotu 8,5 a uvidíme, zda rodina projde. Víme, že ukazatel DTI je poveleným násobkem čistého ročního příjmu žadatele. Pokud tedy známe celkový měsíční čistý příjem a násobek, můžeme jednoduše spočítat limit povoleného zadlužení. V buňce C15 Maximální úvěr tedy ověříme, kolik si z pohledu banky můžeme maximálně půjčit. Měsíční čistý příjem vynásobíme 12 měsíci a hodnotou ukazatele DTI, což je toto číslo. 8,5 násobek ročního příjmu hypotetické rodiny je tedy 5,1 mil. Kč. Banka tedy této rodině hypoteticky s jejich příjmy půjčí až 5,1 mil. Kč. Což je více než si v našem příkladu plánují půjčit.
Ukazatel DSTI
Druhým ukazatelem je DSTI. Jedná se o poměr mezi celkovou výší měsíčních splátek dluhů a čistým měsíčním příjmem. Takže zatímco ukazatel DTI hlídá celkové zadlužení žadatelů, tak tento úvěr hlídá podíl zadlužení na celkovém měsíčním příjmu. Od 1.dubna 2022 bude tento ukazatel povolený na maximální úrovni 45 % pro žadatele starší 36 let, a pro mladší bude ukazatel na 50 %. Opět zkusíme vyplnit do buňky C17 přísnější variantu 45 % a uvidíme, zda projdeme. Ukazatel DSTI se v buňce C18 vypočítá jako prostý násobek měsíčního čistého příjmu a zadaného ukazatele DSTI. To znamená, že banka teoreticky naši rodinu schválí, pokud se splátky dluhů bude pohybovat pod touto hodnotou.
Jak je ze vstupních údajů vidět, tak naše hypotetická rodina prošla oběma parametry. Zde je potřeba zmínit, že aby žadatel o hypoteční úvěr prošel, tak musí splnit oba ukazatele zároveň.
Hypoteční kalkulačka
Pro výpočet splátkového měsíčního kalendáře potřebuje několik sloupců. Budeme potřebovat sloupec pro pořadové číslo měsíce, sloupec pro výpočet anuitní splátky, sloupec pro výpočet úroku a jistiny a nakonec sloupec pro zůstatek úvěrů.
Jako první sloupec máme měsíc, tedy pořadové číslo měsíce, po které plánujeme splácet hypotéku. Jelikož předpokládáme, že nebudeme splácet více jak 30 let, tak si můžeme nastavit maximální počet měsíců na 360. Abychom nemuseli postupně ve sloupci B vyplňovat ručně čísla od 1 až do 360, tak do buňky B22 napíšeme jedničku, označíme tuto buňku B22 a na kartě Domů vybereme Vyplnit a zde Řady.
Řadu chceme vytvořit ve sloupci, a velikost kroku je o 1 a konečná hodnota 360 měsíců. Potvrdíme a doplnili se hodnoty od 1 do 360.
Dalším sloupcem je anuitní měsíční splátka. Použijeme stejný vzorec jako v buňce C12. Napíšeme funkci PLATBA nebo PMT pro ty, co používají anglickou verzi Excelu. Prvním parametrem je měsíční úroková sazba, označíme tedy buňku s měsíční sazbou (C7) a jelikož plánujeme vzorec stahovat směrem dolů, tak musíme buňku zafixovat klávesou F4 jak pro sloupce, tak řádky ($C$7).
Dalším parametrem je pper, což je období. Úvěr si plánujeme brát na 360 měsíců, takže označíme buňku s celkovou splatností C9, opět zafixujeme klávesou F4 na $C$9.
Posledním parametrem je hodnota, což je plánovaná výše úvěru se znaménkem mínus. Tedy buňka -C4, zafixovaná na -$C$4.
Potvrdíme a vzorec stáhneme dolů. Pokud ovšem změníme dobu splatnosti z 30 let na 25 let a zkontrolujeme tabulku úplně dole, tak zjistíme, že hodnota měsíční splátky se přepočítala, ale funkce platba počítá i pro měsíce, které jsou již po době splatnosti. Bylo by o mnohem lepší, kdyby splátkový kalendář přestal počítat pro měsíce, ve kterých už bude úvěr splacený. Vrátíme se tedy ke vzorci a zabalíme ho do jednoduché funkce KDYŽ. Napíšeme funkci KDYŽ, a podmínka je, že pokud je pořadové číslo měsíce v buňce B22 menší nebo rovno celkové splatnosti v měsících (C9), tak chceme počítat funkci PLATBA, a pokud podmínka splněná nebude, tak chceme vrátit nulu.
V dalších dvou sloupcích chceme spočítat, kolik z měsíční splátky dělají úroky a kolik skutečná splátka jistiny. Začneme úroky. Naštěstí i pro výpočet úroků existuje v Excelu funkce, kterou můžeme použít. Jedná se o funkci PLATBA.ÚROK, v angličtině IPMT jako Interest Payment.
=PLATBA.ÚROK/IPMT (sazba; za; pper; současná hodnota)
Prvním parametrem funkce je opět úrok, vyplníme tedy úrok z buňky C7 a nezapomeneme zafixovat klávesou F4 na $C$7. Druhým parametrem je za, což je měsíc, pro který chceme úrok spočítat, označíme proto první měsíc v buňce B22. Tuto buňku nemusíme fixovat, jelikož jak vzorec potáhneme dolů, tak potřebujeme, aby se měsíce měnily zároveň se vzorcem. Jako další parametr je pper, to známe, to je celková doba splatnosti v buňce C9 a zafixujeme klávesou F4 na $C$9. Posledním parametrem je opět hodnota úvěru, označíme proto výši úvěru v buňce C4, opět zafixujeme na $C$4 a opět před hodnotu úvěru napíšeme znaménko mínus. Ukončíme závorku a potvrdíme.
Dopočítala se nám platba úroku. Než vzorec pošleme dolů, tak ho ještě zabalíme do funkce KDYŽ, abychom ošetřili, že vzorec přestane počítat potom, co úvěr splatíme. Napíšeme funkci KDYŽ, a podmínka bude úplně stejná jako u anuitní splátky. Tedy když bude měsíc v buňce B22 menší nebo roven celkové splatnosti, tak chceme počítat funkci PLATBA.ÚROK, a pokud bude větší, tak chceme vrátit hodnotu nula. Konec závorky a potvrdíme. Vzorec je hotový a můžeme ho poslat dolů.
Pro výpočet splátky jistiny nebo úmoru, můžeme použít dvě možnosti. Prvním způsobem je, že od anuitní měsíční splátky můžete odečíst platbu úroků. Splátka úroků a jistiny se totiž musí rovnat anuitní splátce. A nebo použijeme vzorec. Pro splátku úmoru se používá vzorec PLATBA.ZÁKLAD, v angličtině PPMT jako Principal Payment.
=PLABA.ZÁKLAD/PPMT (sazba; za; pper; současná hodnota)
Parametry funkce jsou stejné jako u funkce PLATBA.ÚROK. Jako první označíme měsíční úrok (C7), zafixujeme, jako druhý měsíc, pro který počítáme splátku jistiny (B22), tentokrát nefixujeme, následuje celková splatnost v měsících (C9) a hodnota úvěru s mínusem (C4). I tuto funkci zabalíme rovnou do funkce KDYŽ. Potvrdíme a pošleme pro ostatní řádky dolů.
Posledním sloupcem splátkového kalendáře je zůstatek úvěru v každém měsíci. Zde po jednotlivých měsících uvidíme, kolik bance stále dlužíme. V prvním měsíci je zůstatek úvěru roven původní výše úvěru (C4) mínus zaplacené jistině v prvním měsíci (E22). Ve druhém měsíci je to ovšem již zůstatek úvěru v prvním měsíci (F22) mínus zaplacená jistina ve druhém měsíci (E23). Nemusíme tyto buňky fixovat, jelikož potřebujeme, aby se buňky posouvaly spolu se vzorcem. I tuto funkci ovšem zabalíme do funkce KDYŽ. Použijeme stejnou logiku, jako u předešlých 3 příkladů. Teď můžeme vzorec poslat dolů. Kontrola pro vás, pokud jste vše udělali správně, ta vám na konci splatnosti musí ve sloupci F na posledním řádku vyjít nula.
Měsíční splátkový kalendář pro hypoteční úvěr máte hotový. Pokud jste předplatitelem služby Office 365, tak lze tuto kalkulačku vytvořit ještě za pomocí nových funkcí v Excelu, které jsou dostupné pouze předplatitelům Office 365. Výhodou této kalkulačky je, že se smršťuje dle celkové splatnosti, kterou zadáte.
Odkaz na navazující video pro členy Akademie Excelu
