Tvorba hypoteční kalkulačky v Excelu
Publikováno: 1.11.2019
Kombinace finanční funkce PLATBA() a jednoduchého programování v jazyce VBA
- splátka hypotéky
- PLATBA()
- programování VBA
V dnešním příspěvku vytvořím excelovskou hypoteční kalkulačku, která bude kombinovat finanční funkci PLATBA() a trochu programování v jazyce VBA.
Naprogramuji 2 jednoduché procedury (makra), které budou hýbat s úrokovou sazbou nahoru a dolů. Nakonec vytvořená makra přiřadím ovládacím prvkům formuláře pro pohodlné použití.
Anuitní splátka hypotéky
Funkci PLATBA() již není třeba více komentovat, neboť jsem jí věnoval jeden z předchozích příspěvků. Budu vycházet ze stejných parametrů hypotéky, tj. výše hypotečního úvěru 3.915.000 Kč (LTV 80 %), doba splatnosti 30 let a roční úroková sazba 3,09 %.
V buňce C11 je vložena funkce PLATBA(), která na základě daných parametrů vrátí měsíční splátku hypotéky 16.696 Kč.
Jak by vypadala splátka, kdyby se sazba změnila nahoru nebo dolů? Stačí přepsat buňku s úrokovou sazbou a funkce splátku přepočítá. To je sice fajn, ale komfortnější by bylo, kdybychom si napsali makro, navázali jej na ovládací prvek a pak pouhým kliknutím myši mohli měnit výši splátky.
Zvýšení úrokové sazby
První procedura VBA zvyší úrokovou sazbu o 0,10 %, pří každém kliknutí na tlačítko. To znamená, že makro načte úrokovou sazbu, která je v buňce C6 a zvýší ji o 0,10 %. Poté vrátí zpět do buňky C6 upravenou úrokovou sazbu.
Snížení úrokové sazby
Stejným způsobem bychom mohli napsat i snížení úrokové sazby. V tomto případě však nastává problém, že se s úrokovou sazbou můžeme dostat do intervalu záporných úrokových sazeb. To by znamenalo, že nám banka platí úroky za to, že jsme si u ní vypůjčili peníze. Proto tuto variantu v kódu makra vyloučím.
Do proměnné urokova_sazba načtu hodnotu z buňky C6. Poté pomocí konktrukce If ... Else ... End if omezím pokles úrokové sazby na nulu. Jde o stejnou logiku jako v případě funkce KDYŽ().
Přiřazení makra ovládacím prvkům
Poslední krokem je vložení tlačítek do listu sešitu a přiřazení vytvořených maker. Tlačítka najdete na kartě Vývojář v sekci Ovládací prvky.
Tlačítkem Zvýšení sazby zvýšíte úrokovou sazbu a tím pádem i splátku hypotéky a tlačítkem Snížení sazby snížíte úrokovou sazbu.
Snížení úrokové sazby o 1 procentní bod snížilo splátku hypotéky přibližně o 2.000 Kč měsíčně.
Další tlačítka s makrem můžete vytvořit například pro změnu výše hypotéky a změnu doby splatnosti. Fantazii se meze nekladou.
