Splátkový plán v tabulce

Proč splátkový plán v tabulce funguje lépe než „v hlavě“

Splátkový plán v Excelu nebo Google Sheets vytvoří transparentní amortizační kalendář pro každý dluh, udrží cash-flow realistické a umožní simulovat mimořádné splátky, změny úroků či různá pořadí splácení (lavina/sněhová koule). Získáte tak nástroj pro každodenní rozhodování i dlouhodobá strategická srovnání (RPMN, celkové přeplacení, datum bezdlužnosti).

Vstupní údaje: minimum, bez kterého tabulka nemá smysl

• Jistina (zůstatek) každého dluhu.
• Úroková sazba p.a. (fixní nebo proměnlivá) a den splátky.
• Zbývající počet měsíců (nebo cílová splatnost).
• Minimální povinná splátka, poplatky, výše a frekvence mimořádných splátek.
• Pořadí priorit (lavina = nejvyšší úrok první; sněhová koule = nejmenší zůstatek první; hybrid podle vašich pravidel).

Struktura pracovního sešitu: čistota a replikovatelnost

• List Vstupy: přehled všech úvěrů (1 řádek = 1 dluh) s parametry.
• List Plán: agregovaný cash-flow za všechny dluhy po měsících; prostor pro scénáře.
• Listy Amortizace_<Název dluhu>: detailní řádky pro každý měsíc splácení daného dluhu.
• Volitelně: Dashboard s grafy (pokles jistiny, celkové úroky, datum bezdlužnosti).

Sloupce v listu Vstupy (příklad)

Základní vzorce: PMT, IPMT, PPMT, datumy a rozvrh

Excel i Google Sheets používají stejné finanční funkce. Váš oddělovač argumentů může být „;“ nebo „,“ podle lokality (pokud vzorec hlásí chybu, vyzkoušejte druhou variantu). Základní funkce:

• PMT – výpočet konstantní anuitní splátky: PMT(úrok_měsíčně; počet_měsíců; -jistina).
• IPMT – úroková část splátky ve zvoleném měsíci: IPMT(úrok_měsíčně; pořadí; celkem; -jistina).
• PPMT – jistinová část splátky: PPMT(úrok_měsíčně; pořadí; celkem; -jistina).

Pro proměnné splátky (minimum + mimořádné) je praktičtější počítat úrok = zůstatek × (p.a./12) a jistinu = zaplacené – úrok. Tím lze flexibilně měnit mimořádné splátky bez přepočtu PMT.

List Amortizace_dlužný: povinné sloupce a logika

• Období (1, 2, 3…)
• Datum splátky (sekvence od prvního data: předchozí datum + 1 měsíc)
• Počáteční zůstatek
• Úrok (= počáteční zůstatek × roční_sazba/12)
• Standardní splátka (PMT nebo minimální povinná)
• Mimořádná splátka (volitelná; podle plánu nebo volných peněz)
• Jistina (= standardní + mimořádná − úrok)
• Konečný zůstatek (= počáteční − jistina; minimum 0)
• Kumulované úroky (součet po řádcích)

První řádky:

• Období 1: Počáteční zůstatek = jistina ze vstupů; Datum = zadaný den splátky.
• Období n: Počáteční zůstatek = předchozí konečný zůstatek; Datum = EDATE(předchozí_datum; 1).

Konkrétní vzorce (příklad; upravte odkazy na buňky)

• Úrok (řádek 2): =Počáteční_zůstatek × (Úrok_p.a./12)
• Jistina: =MAX(0; Standardní_splátka + Mimořádná − Úrok)
• Konečný zůstatek: =MAX(0; Počáteční_zůstatek − Jistina)
• Kumulované úroky: =Předchozí_kumulované + Úrok
• Datum splátky (od 2. řádku): =EDATE(předchozí_datum; 1)

Pokud používáte konstantní anuitu místo minima: PMT(Úrok_p.a./12; Počet_měsíců; -Jistina). U posledních řádků ošetřete přeplacení: pokud je konečný zůstatek menší než standardní splátka, nastavte finální splátku na přesné dorovnání (IF min).

Agregovaný plán pro více dluhů: lavina, sněhová koule a hybrid

1. V listu Plán vytvořte řádky pro měsíce a sloupce pro Minimální splátky dohromady, Akcelerační suma, Dluh #1, Dluh #2…
2. Rozdělování akcelerační sumy:
   • Lavina: vyberte dluh s nejvyšším aktuálním úrokem a směrujte celou akcelerační sumu na jeho mimořádnou splátku, dokud nezmizí. Poté přejděte na další.
   • Sněhová koule: stejné, ale pořadí podle nejmenšího zůstatku.
   • Hybrid: např. nejprve všechny mikrodluhy pod 300 €, poté lavina.
3. Technicky můžete akcelerační sumu do tabulky přenést pomocí odkazů: v listu Amortizace_dlh ve sloupci Mimořádná splátka použijte odkaz na příslušnou buňku v listu Plán pro daný měsíc (s kontrolou, zda je zůstatek > 0).

Nastavení datové integrity: ověření, pojmenované rozsahy, šablony

• Ověření dat: úrok 0–100 %, negativní hodnoty zakázat; den splátky 1–28.
• Pojmenované rozsahy (Excel i Sheets): RATE_PA, EXTRA_PAYMENT; zjednodušíte vzorce.
• Kopírovatelné šablony listů: Amortizace_dlh zkopírujte pro každý dluh a jen překryjte vstupy.

Scénáře a citlivost: co když změníme úrok/splátku?

• Excel – What-If Analysis > Data Table: připravená tabulka, do které nalijete různé sazby (řádková proměnná) a akcelerační splátky (sloupcová proměnná). Cílové buňky: datum bezdlužnosti, celkové úroky.
• Google Sheets – SEQUENCE + ARRAYFORMULA: vytvořte vektor sazeb a pesimistických/optimistických mimořádných plateb a vraťte výsledky pomocí vlastních odkazů na výpočtové buňky.

Proměnné sazby a refixace: změny v čase

Pokud máte dluh s proměnlivou sazbou nebo fixací, přidejte sloupec „Sazba platná od data“. V amortizačním listu pak použijte LOOKUP/VLOOKUP/XLOOKUP podle data splátky, aby se měsíční úrok měnil automaticky v den refixace.

Poplatky a RPMN: aby součet odpovídal realitě

• Sloupec Poplatek měsíčně připočítejte ke splátce v každém měsíci; do úroku nevstupuje, ale zvyšuje celkové náklady.
• Jednorázové poplatky (poskytnutí, kataster, znalecký posudek) evidujte na začátku v listu Plán; zobrazte je v Dashboardu jako počáteční náklady.

Praktická pravidla pro mimořádné splátky

• Přidejte sloupec „Nečekané příjmy“ (bonusy, vratky daní) v listu Plán. Automaticky přeneste 50 % na akcelerační sumu.
• V amortizačních listech ošetřete poslední splátku pomocí IF: pokud by konečný zůstatek byl záporný, dorovnejte mimořádnou splátku na přesný zůstatek.

Vizualizace: motivace na první pohled

• Čára zůstatku jistiny (vývoj po měsících; všechny dluhy společně i jednotlivě).
• Sloupcový graf úrok vs. jistina (stacked column) pro každý měsíc – vidíte, jak roste podíl jistiny.
• „Teploměr“ mimořádných splátek – cílová suma za rok a dosažený stav.

Kontingenční mini-dashboard

• Celkové úroky (součet všech úroků ze všech amortizačních listů).
• Datum bezdlužnosti (MAX posledních dat s nenulovým zůstatkem).
• Úspora vůči minimu (scénář „pouze minima“ vs. „minima + akcelerace“).

Nejčastější chyby a jak se jim vyhnout

• Míchání roční a měsíční sazby: vždy dělte p.a. sazbu dvanácti.
• Chybějící „MIN/MAX“ pojistky u posledních řádků: bez nich vyjde záporný zůstatek.
• Nesprávný oddělovač argumentů: pokud PMT/PPMT/IPMT nefungují, změňte „;“ na „,“ nebo naopak.
• Přepisování ručně místo prolinkování: používejte odkazy z listu Vstupy, ne pevně zadaná čísla.

Kontrolní checklist před „ostrou provozem“

• Je součet jistin v listu Plán rovný součtu počátečních zůstatků všech amortizačních listů?
• Roste kumulovaný úrok monotónně a neroste po splacení dluhu?
• Jsou mimořádné splátky vázány pouze na dluhy s kladným zůstatkem?
• Mění se sazba automaticky k datu refixace (pokud máte proměnné sazby)?
• Víte jedním vstupem zvýšit akcelerační sumu a všude se přepočte?

Rozšíření pro pokročilé

• INDEX/MATCH nebo XLOOKUP na dynamické mapování priorit podle nejvyšší aktuální sazby nebo nejnižšího zůstatku.
• QUERY (Sheets) pro rychlé filtry „živých“ dluhů (zůstatek > 0).
• Podmíněné formátování na zvýraznění měsíce splacení každého dluhu.
• Scénář „šok“: od měsíce M snižte akcelerační sumu o X; sledujte posun data bezdlužnosti.

Mini-návod: sestavení jednoho amortizačního listu (krok za krokem)

1. Do horních buněk vložte Jistinu, Sazbu p.a., Datum první splátky, Minimální splátku.
2. V řádku 2 nastavte Období = 1, Datum = první datum, Počáteční zůstatek = Jistina.
3. Úrok = Počáteční × (Sazba/12). Jistina = MAX(0; Min_splátka + Mimořádná − Úrok).
4. Konečný = MAX(0; Počáteční − Jistina). Kumulovaný_úrok = Úrok + předchozí Kumulovaný_úrok.
5. Řádek 3: Období +1; Datum = EDATE(předchozí; 1); Počáteční = předchozí Konečný. Zkopírujte vzorce dolů.
6. Přidejte pojistku: pokud Konečný zůstatek v dalším řádku = 0, přestaňte počítat úrok a splátky (IF).

Jak spojit všechny dluhy do jednoho časového pohledu