Splátkový kalendář je příklad užitečného a zároveň jednoduchého výpočetního modelu v Excelu. Jeho vytvoření není složité, pokud využijeme finanční funkce Excelu, které nabízejí potřebné výpočty.
Vstupní parametry jako je výše úvěru, úroková míra a doba splácení mohou být zadány přímo do výpočetních funkcí nebo jsou uloženy v definované oblasti.
Ukážeme si postupně 3 způsoby vytváření splátkového kalendáře:
- Standardním způsobem pomocí statických funkcí
- Pokročilým způsobem pomocí dynamických funkcí
- Inovativním způsobem pomocí funkce LET
Splátkový kalendář pomocí statických funkcí – postup
Vstupní parametry pro splátkový kalendář zadáme v oblasti C2:C4
Ve splátkovém kalendáři vytvoříme nadpisy sloupců a zapíšeme do sloupce Měsíc ručně pořadová čísla období 1..12 (v dalším pokračování zaměřeném na dynamické funkcí si ukážeme, jak lze generovat požadovaný počet období pomocí hodnoty v buňce C3 – Počet měsíců)
- Do buňky F7 ve sloupci Splátka zapíšeme funkci =PLATBA s parametry načtenými ze vstupních buněk.
Protože výše úrokové míry 6% v buňce C4 je roční úroková míra a splácení úvěru je nastaveno na každý měsíc, musíme pro potřeby funkce vydělit roční úrokovou míru počtem měsíců v roce, tedy 6%/12.
Výsledkem výpočtu funkce PLATBA je výpočet konstatní měsíční splátky, kterou budeme každý měsíc po dobu 12 měsíců platit. Tato platba se skládá z úhrady jistiny a úroku.
2. Do buňky E8 ve sloupci Úrok zapíšeme funkci =PLATBA.ÚROK s parametry načtenými ze vstupních buněk a ze sloupce MĚSÍC.
Opět platí stejné pravidlo pro hodnotu výše úrokové míry – protože výše úrokové míry 6% v buňce C4 je roční úroková míra a splácení úvěru je nastaveno na každý měsíc, musíme pro potřeby funkce vydělit roční úrokovou míru počtem měsíců v roce, tedy 6%/12.
Dlužná počáteční částka zapsaná v buňce C2 je pro potřeby výpočtu úroku zadána se záporným znaménkem – touto přípravou si usnadníme další dílčí výpočty v tomto modelu.
Výsledkem výpočtu funkce PLATBA.ÚROK je výpočet proměnné čásky úroku, kterou budeme každý měsíc po dobu 12 měsíců platit. Tato částka se průběžně snižuje, což je standardní způsob platby úvěru – na počátku splácení je výše úroku v celkové splátce vyšší než na konci splácení. Výše jistiny naopak v průběhu splácení v rámci jednotlivých splátek narůstá.
3. Do buňky D8,která představuje výši dlužné částky na začátku splácení vložíme odkaz na buňku C2 obsahující hodnotu 100 000.
Po zaplacení 1. splátky bude zbývat k úhradě částka vypočtená pomocí funkce SUMA v buňce G8. Tato částka je vypočtena pomocí předchozí trojice hodnot v tomtéž řádku.
4. V dalším řádku kalendáře, který představuje druhé splátkové období, načteme do sloupce Dlužná částka POČÁTEČNÍ hodnotu z předchozího řádku sloupce Dlužná částka ZBÝVAJÍCÍ.
Částka zbývající uhradit z úvěru po první splátce, se tedy stává vstupní hodnotou pro výpočet druhé splátky.
5. Zkopírujeme všechny vzorce vytvořené v předchozích krocích až k poslednímu řádku splátkového kalendáře.
6. V posledním kroku vytvoříme pomocí funkce SUMA celkový součet splátek a zaplacených dílčích plateb úroku – pomocí těchto součtů tedy zjistíme, jak vysokou částku zaplatíme za celé období nad výši původně získané částky.
Tento model založený na statických funkcích dokáže přizpůsobit výpočet změnám výše půjčky a výše úrokové míry, ale při změně počtu měsíců bychom museli provést ruční úpravu počtu řádků kalendáře.
V dalším pokračování tohoto návodu si ukážeme, jak lze pomocí nových dynamických funkcí vytvořit splátkový kalendář, který bude reagovat nejen na změnu výše půjčky a výše úrokové míry, ale bude vytvářet i požadované nové řádky kalendáře při změně délky splátkového období (počtu měsíců).
Splátkový kalendář z tohoto návodu si můžete stáhnout zde
