V dnešním videu se podíváme na to, jak v Excelu vytvořit kalkulačku, která počítá úspory. A to přesněji úspory, které potřebujeme naspořit do důchodu tak, aby nám vystačili, když z nich budeme chtít v důchodu čerpat částku x. Kalkulačku samozřejmě můžete použít i k jiným účelům než jen k výpočtu spoření na důchod, ale pro nás to dnes bude hlavní cíl. P
Excelový soubor ke stažení
Pro stažení hotové kalkulačky zadejte jméno a email:
A rovnou se vrhneme na tvorbu kalkulačky.
Hotovou kalkulačku máme v Excelu rozdělenou do několika sekcí. V horní části máme záhlaví, které shrnuje nejdůležitější parametry, tedy kolik za zvolenou dobu celkem naspoříme, kolik jsme zainvestovali celkem a kolik jsme obdrželi na úrocích za zvolené období. V levé části kalkulačky máme vstupní informace, které musíme vyplnit a počáteční výpočty, jako kolik peněz budeme chtít v důchodu z úspor vybírat, kolik potřebujeme naspořit atd. V prostřední části máme v tabulce zobrazený vývoj investice v letech. Pravá část kalkulačky obsahuje tabulku čerpání v letech v důchodu z naspořených peněz a graf zobrazující celkovou hodnotu investice a celkové zainvestované částky v letech.
Vstupní informace
Excelovou kalkulačku začneme tvořit od vstupních informací. Základem dnešní kalkulačky je to, že si nejprve stanovíme, kolik peněz bychom potřebovali v důchodu čerpat z úspor. Když si stanovíme částku, kterou budeme měsíčně potřebovat čerpat, tak ji přepočítáme na budoucí hodnotu. Vlivem inflace totiž dnešní částka není ta samá, kterou budeme potřebovat v budoucnosti. Když stanovíme, kolik peněz budeme potřebovat, tak dopočítáme, kolik peněz budeme muset tedy celkem naspořit. A z toho následně dopočítáme, kolik musíme ročně spořit, abychom náš cíl splnili.
Barevně označené buňky se v kalkulačce doplňují, v transparentních buňkách se budou nacházet vzorce.
První vstupní informací je současný věk. Tato buňka je vyplňovací. Řekněme, že kalkulačku budeme vyplňovat za někoho, komu je v současné době 35 let. Do buňky tedy vyplníme věk 35 let.
Další dvě buňky jsou rovněž vyplňovací. První informací, kterou budeme doplňovat, je odhad budoucího důchodu. Řekněme, že počítáme, že budeme v důchodu pobírat třeba 20 000 Kč měsíčně. Do buňky předpokládaný důchod tedy vyplníme 20 000 Kč. V další doplňovací buňce vyplníme měsíční náklady, které předpokládáme, že budeme v důchodu mít. Můžeme vyjít ze současného rodinného rozpočtu, který snížíme o položky, které v důchodu odpadnou. Řekněme, že počítáme, že naše výdaje v důchodu budou zhruba 30 000 Kč měsíčně. V buňce k důchodu budu potřebovat vybírat spočítáme, kolik nám bude měsíčně chybět. Což je rozdíl mezi předpokládaným důchodem a měsíčními náklady. Tím pádem víme, že nám v důchodu bude chybět zhruba 10 000 Kč měsíčně. Což je 120 000 Kč ročně. Částku 120 000 Kč ročně bychom tedy ideálně potřebovali čerpat z naspořených peněz.
Na úspory a investice ovšem působí inflace, takže musíme ve výpočtech zohlednit i inflaci. V další buňce tedy vyplníme předpokládanou výši inflace. V modelu budeme pracovat s průměrnou roční inflací ve výši 3 %. Poslední vstupní informací je, kolik let nám zbývá do důchodu. Kalkulačku vyplňuje někdo, komu je zrovna 35 let, takže mu do důchodu zbývá zhruba 30 let. Doplníme tedy 30 let.
A začneme s prvním výpočtem. Víme, že budeme chtít k důchodu čerpat 120 000 Kč ročně, což ale není ta samá částka, kterou budeme čerpat za 30 let. A to vlivem inflace. Částka 120 000 Kč ročně je to dnes, ale za 30 let bude hodnota 120 000 Kč vlivem inflace daleko vyšší. Musíme tedy spočítat, jaká je budoucí hodnota dnešních 120 000 Kč při inflaci 3 %.
To spočítáme díky finanční funkci BUDHODNOTA. Ve funkci BUDHODNOTA je prvním parametrem, který se vyplňuje sazba, to je v tomto případě inflace. Následuje parametr doba, což je počet let, které zbývají do důchodu. Parametr splátka nemáme, ale máme budoucí hodnotu, což je částka 120 000 Kč. To je vše co ve funkci potřebujeme a jelikož chceme, aby se hodnota vrátila jako kladné číslo, tak před funkci BUDHODNOTA napíšeme mínus. Teď můžeme funkci potvrdit a vrátila se hodnota 291 tisíc Kč. Vlivem inflace bude tedy hodnota dnešních 120 000 Kč za 30 let zhruba 291 tisíc Kč. Teď tedy víme, kolik budeme chtít z uspořených peněz ročně čerpat.
Teď musíme spočítat, kolik peněz musíme celkem naspořit, abychom tuto částku mohli ročně čerpat. K tomu budeme potřebovat další dvě vstupní informace. Předpokládáme, že naspořené peníze v důchodu nebudeme mít jen schované pod polštářem, ale že je budeme mít rovněž uložené v bance za nějaký úrok. A jelikož již budeme v důchodu, tak předpokládáme, že peníze budeme mít uložené konzervativně, tedy s nižším úrokem. Počítáme s inflací 3 %, takže by bylo dobré mít peníze uložené alespoň za 3,5 %. A druhý údaj, který potřebujeme je, předpokládaný počet let, který budeme v důchodu. Zde se samozřejmě jedná o odhad, my budeme předpokládat, že budeme v důchodu 15 let, tedy zhruba do 80 let. Vycházíme tedy z toho, že budeme chtít z naspořených peněz čerpat 291 tisíc po dobu zhruba 15 let.
Teď můžeme dopočítat, kolik peněz potřebujeme do důchodu naspořit. K tomu použijeme další finanční funkci SOUČHODNOTA. Ve funkci SOUČHODNOTA je prvním parametrem sazba. Tentokrát je to sazba, za kterou budeme mít uspořené peníze uložené v důchodu. Následuje parametr doba, tedy kolik let předpokládáme, že budeme v důchodu. A parametr splátka je hodnota peněz, které budeme chtít v důchodu čerpat. A opět jelikož chceme mít hodnotu jako kladné číslo, tak před funkci SOUČHODNOTA napíšeme mínus. Funkci potvrdíme a funkce vrátila hodnotu 3,3 mil. Kč. To je částka, kterou bychom měli mít před důchodem naspořenou, pokud budeme chtít po dobu 15 let z úspor čerpat 291 tisíc Kč ročně.
Poslední, co zbývá dopočítat je, kolik tedy musíme ročně spořit. Víme, že nám do důchodu zbývá 30 let, takže musíme zjistit, kolik musíme ročně spořit po dobu 30 let, abychom dosáhli celkové částky, kterou potřebujeme. K výpočtu potřebujeme v podstatě jen dvě dodatečné informace. Prvním údajem je úroková sazba, za kterou budeme v průměru spořit peníze před důchodem. Řekněme, že budeme mít peníze uložené například v průměru za 5 % ročně. A druhou informaci, kterou můžeme doplnit je částka, se kterou začínáme spořit. Řekněme, že máme už dosud naspořeno 100 000 Kč, které přihodíme ke spoření na důchod. Pokud nemáme naspořeno nic, tak zde vyplníme nulu.
Kolik tedy musíme ročně spořit? Použijeme finanční funkci PLATBA. Ve funkci PLATBA nejprve označíme sazbu, za kterou budeme peníze spořit. Následuje počet let, tedy počet let spoření, což jsou roky, které zbývají do důchodu. Parametrem současná hodnota je počáteční hodnota úspor a budoucí hodnota je cílová částka, kterou se snažíme naspořit. A opět hodnotu chceme v kladném formátu, takže před budoucí hodnotu napíšeme mínus. Funkci potvrdíme a víme, že abychom naspořili za 30 let zhruba 3,3 mil. Kč, tak musíme ročně spořit zhruba 44 000 Kč, což je měsíčně zhruba 3 666 Kč.
Z těchto výpočtů tedy plyne, že pokud budeme po dobu následujících 30 let spořit měsíčně 3 666 Kč za průměrný úrok 5 %, tak naspoříme celkem 3,35 mil. Kč. Ze kterých si budeme moci dovolit vybírat po patnáct let částku 291 tisíc Kč ročně při 3 % inflaci, což je zhruba měsíčně částka odpovídající dnešním 10 000 Kč.
Kalkulačka samozřejmě reaguje na změny. Můžeme se tak podívat, co se s našimi výpočty stane, pokud změníme některý ze vstupů. Co když nám není 35 let, ale 40 let a do důchodu nám tedy zbývá pouze 25 let? V takovém případě bychom měsíčně museli spořit částku 4 461 Kč. Naopak, pokud začneme brzy, třeba ve 25 letech, tak nám do důchodu zbývá 40 let. V takovém případě nám stačí spořit 2 624 Kč. A nebo budeme mít peníze uložené za vyšší úrok, třeba za 7 % a v takovém případě nám stačí odkládat něco okolo 1 257 Kč měsíčně. Hrát si můžete rovněž s inflací. Vrátíme se k původnímu střadateli, kterému bylo 35 let a měl 30 let do důchodu a spořil za 5 % ročně. Co když inflace nebude 3 % ale 5 %? V takovém případě se celý výpočet zhoršuje a my musíme o to více spořit, abychom dosáhli teď již vyšší cílové částky. Vrátíme výpočet do původního zadání a budeme pokračovat v tvorbě kalkulačky.
Výpočty doplníme o dvě tabulky. V první tabulce zobrazíme průběh spoření v jednotlivých letech a ve druhé tabulce zobrazíme čerpání. V první tabulce máme několik sloupců. Tabulka bude ukazovat roky, po které spoříme, stejně jako věk střadatele. Následovat bude částka, kterou ročně zainvestujeme, celková zainvestovaná částka a zhodnocení v jednotlivých letech, stejně jako kumulativní zhodnocení celkem. Poslední sloupec bude zobrazovat celkovou hodnotu úspor.
Tabulka spoření / investice
A začneme prvním řádkem. V prvním řádku vyplníme pouze jednu jedinou informaci, což je vstupní hodnota investice, se kterou začínáme. V příkladu jsme vyplnili, že začínáme spoření se 100 000 Kč, takže tuto částku doplníme propojením na buňku do barevné buňky zainvestováno. Poslední co, na prvním řádku doplníme je celková hodnota investice, což je stejná částka, tedy 100 000 Kč, takže buňky propojíme.
Na druhém řádku již doplníme všechny sloupce. Ve sloupci rok začneme v první buňce s rokem, kdy začneme spořit. Do barevné buňky tedy doplníme rok počátku spoření, třeba rok 2024. Ve druhé buňce ve sloupci rok bude již následovat vzorec, kde k předchozímu roku přičteme jedničku. Vzorec potvrdíme a stáhneme dolů až do roku, ve kterém plánujeme přestat spořit, tedy po 30 letech.
Ve druhém sloupci následuje věk střadatele. První buňka bude opět propojená se vstupním věkem, ve kterém začínáme spořit. Ve druhé buňce už bude vzorec, předchozí věk plus jedna, což nám opět po stažení dolů dá řadu let a končíme v roce, kdy odcházíme do důchodu, tedy ve věku 64 let.
Ve sloupci zainvestováno máme v první buňce propojenou již vstupní výši investice. Ve druhé buňce bude propojená hodnota roční částky, kterou spoříme, kterou jsme spočítali pomocí funkce PLATBA. Tuto buňku zafixujeme plně klávesou F4, jelikož tato částka bude stejná na každém řádku. Vzorec potvrdíme a pošleme dolů. Začali jsme tedy s částkou 100 000 Kč, kterou jsme měli naspořenou již z dřívějších dob a následně k této částce každý rok přidáme zhruba 44 000 Kč ročně.
Ve sloupci zainvestováno celkem bude uvedená kumulovaná částka investice. To znamená, že zde můžeme použít kumulativní sumu, takže do druhého řádku napíšeme funkci SUMA, kde označíme první buňku ve sloupci zainvestováno ku druhé buňce, přičemž první buňku ve funkci SUMA zafixujeme plně klávesou F4. Tím pádem, jak budeme funkci posouvat dolů, tak se budou jednotlivé částky investice načítat. Vzorec potvrdíme a stáhneme ho dolů, takže víme, že po 30 letech nainvestujeme celkem 1,42 mil. Kč.
Dalším sloupcem je zhodnocení. Tady musíme spočítat úrok každý rok. Úrok počítáme z celkové hodnoty investice, takže vynásobíme koncovou hodnotu investice, což v prvním roce je první hodnota počáteční investice a vynásobíme ji úrokem, za který spoříme během spořících 30 let. Buňka s úrokem musí být plně zafixována. Vzorec potvrdíme a stáhneme ho dolů. Zatím se úrok objevil pouze na prvním řádku, jelikož se úrok počítá z celkové hodnoty investice a tu zatím nemáme dopočítanou na každém řádku.
Dalším sloupcem je stejně jako u zainvestované částky kumulativní součet úroků. Opět si pomůžeme kumulativní sumou, kde označíme první buňku úroku plně zafixovanou ku druhé buňce úrok. Vzorec ukončíme, potvrdíme a stáhneme dolů. Opět máme zatím na každém řádku jen hodnotu úroku v prvním roce.
Zbývá poslední sloupec, kterým je celková hodnota investice. V tomto sloupci sečteme počáteční hodnotu investice s částkou zainvestovanou v prvním roce a zhodnocením v prvním roce. Potřebujeme, aby se tyto buňky posouvaly spolu se vzorcem, takže nemusíme buňky fixovat a pošleme je dolů. Tím, že vzorec pošleme dolů, tak se zároveň dopočítají i sloupce s úroky. Kontrolou výpočtu je to, že se poslední hodnota ve sloupci Hodnota investice shoduje s celkovou částkou, kterou jsme spočítali, že musíme naspořit pomocí funkce SOUČHODNOTA. Díky tabulce rovněž víme, jaké jsou úroky v jednotlivých letech, stejně tak víme, že celkem na úrocích obdržíme za 30 let zhruba 1,9 mil. Kč. Když sečteme celkovou zainvestovanou částku a celkové úroky, tak rovněž obdržíme hodnotu celkové investice.
Tím je tabulka přehledu investice v letech hotová. Tabulka je teď vyplněná dle vašich vstupních údajů. Pokud byste chtěli, aby tabulka reagovala na měnící se vstupní informace o věku, tak budeme muset vzorce v tabulce trochu upravit. Teď máme totiž vzorce jen na řádcích podle vstupních informací, pokud změníme počet let investice, z 30 let třeba na 35, tak se roky ani ostatní výpočty nepřizpůsobí. Aby se kalkulačka přizpůsobovala, musíme podmínit jednotlivé sloupce.
Ukážeme si to na prvních třech sloupcích. Začneme se sloupcem rok. První buňku necháme, tam jsme vyplnili počáteční rok investice. Ve druhé buňce rok použijeme ale podmínkovou funkci KDYŽ, kde ověříme podmínku, zda je předchozí rok menší než rok odchodu do důchodu snížený o jedničku. Tedy rok počátku investice, plně zafixovaná buňka, plus počet let co zbývá do důchodu, tato buňka musí být rovněž plně zafixovaná, mínus jedna. Pokud podmínka splněná bude, tak chceme vrátit předchozí rok plus jeden, což je vzorec, který jsme ve sloupci měli, a pokud podmínka splněná nebude, tak chceme vrátit nic, tedy dvě uvozovky. Funkci potvrdíme a pošleme ji dolů. Funkce vrátí pořadová čísla let, po které budeme spořit. Aby kalkulačka reagovala i na více než 30 let spoření, tak vzorec stáhneme o potřebný počet buněk dolů. Když teď změníme, že nebudeme spořit 30 let, ale 35 let, tak vzorec ve sloupci reaguje a vrátí více let.
Ve druhém sloupci věk necháme opět první buňku propojenou se vstupním věkem a ve druhé buňce ověříme podmínku, že pokud je buňka s rokem rovna ničemu, že chceme vrátit nic, pokud podmínka splněná není, je v ní tedy uvedený rok, tak chceme vrátit vzorec, který jsme ve sloupci již měli. Funkci potvrdíme a stáhneme ji dolů a rovněž ji stáhneme o několik řádů dolů navíc, aby reagovala na případné změny.
Ve stejném stylu bychom pokračovali ve všech ostatních sloupcích. Ještě si to ukážeme na dalším sloupci. Vždy ověříme podmínku, že pokud se buňka s rokem rovná prázdné buňce, že chceme rovněž vrátit prázdnou buňku, v opačném případě chceme vrátit vzorec, který jsme ve sloupci měli. Vzorec potvrdíme a stáhneme do o stejný počet buněk dolů jako předchozí sloupce.
Takto to uděláme pro všechny zbývající sloupce.
Tabulka čerpání
Ještě musíme dopočítat tabulku čerpání. Některé sloupce v tabulce se v tomto případě změní. První dva sloupce rok a věk zůstanou stejné, místo sloupce zainvestováno máme ale v tomto případě sloupec čerpáno a místo posledního sloupce hodnota investice bude zůstatek investice.
Začneme sloupcem rok. Rok musí navazovat na poslední rok investování, takže zde bude vzorec počáteční rok investice plus zbývající počet let do důchodu. Ve druhé buňce bude vzorec. Jak víme z předchozí tabulky, pokud chceme, aby kalkulačka reagovala na změny ve věku, tak vzorec rovnou zabalíme do podmínkové funkce KDYŽ. Funkce KDYŽ, kde podmínka je, že předchozí rok je nižší než rok odchodu do důchodu, buňka plně zafixovaná, plus počet let v důchodu, opět plně zafixovaná, mínus jedna. Pokud podmínka splněná je, tak chceme vrátit předchozí rok plus jedna a pokud není, tak chceme prázdnou buňku. Funkci potvrdíme a stáhneme ji opět o několik řádků níže.
Ještě musíme dopočítat tabulku čerpání. Některé sloupce v tabulce se v tomto případě změní. První dva sloupce rok a věk zůstanou stejné, místo sloupce zainvestováno máme ale v tomto případě sloupec čerpáno a místo posledního sloupce hodnota investice bude zůstatek investice. Začneme sloupcem rok. Rok musí navazovat na poslední rok investování, takže zde bude vzorec počáteční rok investice plus zbývající počet let do důchodu. Ve druhé buňce bude vzorec. Jak víme z předchozí tabulky, pokud chceme, aby kalkulačka reagovala na změny ve věku, tak vzorec rovnou zabalíme do podmínkové funkce KDYŽ. Funkce KDYŽ, kde podmínka je, že předchozí rok je nižší než rok odchodu do důchodu, buňka plně zafixovaná, plus počet let v důchodu, opět plně zafixovaná, mínus jedna. Pokud podmínka splněná je, tak chceme vrátit předchozí rok plus jedna a pokud není, tak chceme prázdnou buňku. Funkci potvrdíme a stáhneme ji opět o několik řádků níže.
Následuje věk. V první buňce je vzorec, současný věk plus počet let, kolik zbývá do důchodu. Ve druhé buňce je opět funkce KDYŽ, kde ověříme podmínku, že pokud je buňka s rokem rovna prázdné buňce, tak chceme vrátit prázdnu buňku a pokud podmínka splněná není, tak chceme předchozí věk plus jedna. Potvrdíme a stáhneme dolů.
Následuje sloupec zbývá naspořeno. V prvním roce v důchodu je tato částka rovna počáteční částce, kterou máme v důchodu naspořenou, tedy cílová částka. Ve druhé buňce bude opět podmínková funkce KDYŽ, kde ověříme stejnou podmínku, že pokud je rok roven prázdné buňce, tak chceme prázdnou buňku, jinak chceme hodnotu zůstatku. Vzorec stáhneme dolů. Jelikož zatím nemáme doplněný sloupec Zůstatek, tak zde máme prázdné buňky.
Pokračujeme sloupcem výběry. To je stále stejná částka, kterou jsme určili ve vstupních informacích pomocí funkce BUDHODNOTA. Částka bude stále stejná, takže buňky propojíme, plně zafixované a zabalíme to do stejné podmínkové funkce KDYŽ.
Ve sloupci zhodnocení dopočítáme zhodnocení zbývajících úspor. Na počátku jsme si řekli, že naspořené peníze nevybereme a neschováme je pod polštářem, ale že je necháme v bance se úročit, takže se jedná o násobek zbývajících úspor krát úrok, který inkasujeme v důchodu, tato buňka musí být plně zafixovaná. A opět to zabalíme do podmínkové funkce KDYŽ.
Poslední sloupec zůstatek úspor je počátek zbývajících úspor mínus výběr plus zhodnocení. Opět zabaleno do funkce KDYŽ. Funkci pošleme dolů a kontrolou nám je to, že poslední buňka je rovna nule, tedy po patnácti letech jsme vyčerpali všechny úspory.
Tabulka čerpání je hotová.
Excelový graf investice
Zbývá dodělat graf, ve kterém zobrazíme celkovou hodnotu investice a celkovou zainvestovanou částku v letech. A data zobrazíme v bodovém grafu se spojnicemi. Na kartě Vložení vybereme bodový graf se spojnicí a vloží se prázdný graf. Klikneme do grafu a vybereme Vybrat data. Začneme přidávat jednotlivé datové řady. Klikneme na přidat a název řady je zainvestován celkem, hodnoty na ose x jsou roky. A nezapomeneme označit i ty buňky navíc, kde máme vzorec. Hodnota osy y je sloupec zainvestováno celkem. Potvrdíme a přidáme druhou datovou sadu. Název řady je hodnota, hodnota x jsou opět roky a hodnoty y jsou hodnoty ve sloupci hodnota, tedy celková hodnota investice. Potvrdíme a graf je hotový, graf můžeme ještě podle potřeby zformátovat.
Záhlaví přehledu
Poslední, co zbývá dodělat je záhlaví. V něm jen zobrazíme souhrnné informace. Klikneme do záhlaví do buňky E. Zde chceme zobrazit celkovou naspořenou částku. Celkovou naspořenou částku máme ve vstupních výpočtech, takže můžeme napsat v uvozovkách Naspořeno celkem s dvojtečkou a mezerou a spojíme to s buňkou. Potvrdíme a narazíme na problém, že teď je celá buňka textem a proto nemáme zformátované číslo. Buňku s číslem ale můžeme zabalit do funkce HODNOTA.NA.TEXT, kde formát určíme. Napíšeme před buňku s číslem funkci a textem je buňka a formát je # ### Kč. Formát musí být v uvozovkách. Potvrdíme a máme celkovou naspořenou částku ve správném formátu.
=”Naspořeno celkem: “&HODNOTA.NA.TEXT(B20;”# ### Kč”)
V buňce J chceme zobrazit celkovou hodnotu zainvestovaných peněz. Tuto částku nikde přímo uvedenou nemáme. Jedná se technicky o maximální hodnotu ze sloupce zainvestováno celkem, jelikož ale máme kalkulačku dynamickou, tak nemůžeme jen tak označit poslední buňku, protože když změníme roky, tak se maximální částka posune. Můžeme to ale vyřešit pomocí funkce MAX. A napíšeme to rovnou jako celou funkci. Rovná se, sousloví Zainvestováno celkem v uvozovkách, to celé spojené s funkcí HODNOTA.NA.TEXT, kde textem je MAX ze sloupce zainvestováno celkem a formát je opět formát pro měnu v uvozovkách. Ukončíme funkci a potvrdíme.
=”Zainvestováno celkem: “&HODNOTA.NA.TEXT(MAX(H6:H43);”# ### Kč”)
To samé uděláme i buňce P, kde chceme mít celkový úrok. Takže sousloví Úrok celkem v uvozovkách, spojený s funkcí HODNOTA.NA.TEXT, kde textem je MAX ze sloupce celkového úroku a formát je stejný jako v předchozích případech. Ukončíme funkci a záhlaví je hotové.
=”Úrok celkem: “&HODNOTA.NA.TEXT(MAX(J6:J43);”# ### Kč”)
Jedna odpověď
🎇🥂 Šťastný Nový Rok 2024 , především pevné zdraví, osobní pohodu, pracovní úspěchy a
ať se Vám s Akademií Excelu v novém roce daří a splní se Vám Vaše přání!!
Oldřich Svoboda, senior