Proč plán splácení v tabulce funguje lépe než „v hlavě“
Plán splácení 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 bezdluhovosti).
Vstupní údaje: minimum, bez kterého tabulka nedává smysl
- Základní jistina (zůstatek) každého dluhu.
- Úroková sazba p.a. (fixní nebo variabilní) 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 jako první; sněhová koule = nejmenší zůstatek jako první; hybrid podle vašich pravidel).
Struktura pracovního sešitu: čistota a replikovatelnost
- List Vstupy: přehled všech půjček (1 řádek = 1 dluh) s parametry.
- List Plán: agregovaný cash-flow za všechny dluhy po měsících; místo 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 bezdluhovosti).
Sloupce v listu Vstupy (příklad)
| Název dluhu | Jistina (€) | Úrok p.a. (%) | Min. splátka (€) | Splatnost (měsíce) | Den splátky | Typ (fixní/variabilní) | Poplatek měsíčně (€) | Priorita |
|---|---|---|---|---|---|---|---|---|
| Kreditní karta A | 2 500 | 24 | 75 | – | 10 | variabilní | 0 | 1 (lavina) |
| Spotřebitelský úvěr B | 6 800 | 12 | 150 | 48 | 15 | fixní | 2 | 2 |
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 druhý variant). 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 v zadané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 variabilní splátky (minimum + mimořádné) je praktičtější počítat úrok = zůstatek × (p.a./12) a jistinu = zaplacené – úrok. Tak můžete flexibilně měnit mimořádné splátky bez přepočtu PMT.
List Amortizace_dlh: 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 přes řádky)
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
- V listu Plán vytvořte řádky pro měsíce a sloupce pro Minimální splátky dohromady, Akcelerační částku, Dluh #1, Dluh #2…
- Rozdělování akcelerační částky:
- Lavina: vyberte dluh s nejvyšším aktuálním úrokem a směrujte celou akcelerační částku na jeho mimořádnou splátku, dokud nezmizí. Poté přesuňte na další.
- Sněhová koule: stejné, ale pořadí podle nejmenšího zůstatku.
- Hybrid: například nejdříve všechny mikrodluhy pod 300 €, pak lavina.
- Technicky můžete akcelerační částku 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ěřování, pojmenované oblasti, šablony
- Data Validation: úrok 0–100 %, záporné hodnoty zakázat; den splátky 1–28.
- Pojmenované oblasti (Excel i Sheets): RATE_PA, EXTRA_PAYMENT; zjednodušíte vzorce.
- Kopírovatelné šablony listů: Amortizace_dlh zkopírujte pro každý dluh a pouze přelinkujte vstupy.
Scénáře a citlivost: co když změníme úrok/splátku?
- Excel – Analýza co kdyby > Tabulka dat: připravená tabulka, do které vložíte různé sazby (řádková proměnná) a akcelerační splátky (sloupcová proměnná). Cílové buňky: datum bezdluhovosti, 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ěnnou 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 seděl s realitou
- Sloupec Poplatek měsíčně připočtěte ke splátce v každém měsíci; do úroku nevstupuje, ale zvyšuje celkové náklady.
- Jednorázové poplatky (poskytnutí, katastr, 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 „Neočekávané příjmy“ (bonusy, vrácení daní) v listu Plán. Automaticky přesuňte 50 % na akcelerační částku.
- 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 přesně na zůstatek.
Vizualizace: motivace na první pohled
- Čára zůstatku jistiny (průběh po měsících; všechny dluhy dohromady i jednotlivě).
- Skládaný sloupcový graf úrok vs. jistina pro každý měsíc – vidíte, jak roste podíl jistiny.
- „Teploměr“ mimořádných splátek – cílová částka 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 bezdluhovosti (MAX posledních dat s nenulovým zůstatkem).
- Úspora oproti 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ělejte p.a. sazbu dvanácti.
- Chybějící „MIN/MAX“ pojistky u posledních řádků: bez nich může vzniknout záporný zůstatek.
- Špatný 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 tvrdě zadaná čísla.
Kontrolní checklist před „ostrým 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 neklesá 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í částku a všude se přepočítá?
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) na rychlé filtry „živých“ dluhů (zůstatek > 0).
- Podmíněné formátování pro zvýraznění měsíce splacení každého dluhu.
- Scénář „šok“: od měsíce M snižte akcelerační částku o X; sledujte posun data bezdluhovosti.
Mini-návod: sestavení jednoho amortizačního listu (krok za krokem)
- Do horních buněk vložte Jistinu, Sazbu p.a., Datum první splátky, Minimální splátku.
- V řádku 2 nastavte Období = 1, Datum = první datum, Počáteční = Jistinu.
- Úrok = Počáteční × (Sazba/12). Jistina = MAX(0; Min_splátka + Mimořádná − Úrok).
- Konečný = MAX(0; Počáteční − Jistina). Kumulovaný_úrok = Úrok + předchozí Kumulovaný_úrok.
- Řádek 3: Období +1; Datum = EDATE(předchozí; 1); Počáteční = předchozí Konečný. Zkopírujte vzorce dolů.
- Přidejte pojistku: pokud Konečný v dalším řádku = 0, přestaňte počítat úroky a splátky (IF).
