Splátkový plán v tabulce: Systematický přehled úvěrových závazků

Tae Teo

Proč si vytvořit vlastní splátkový plán v Excelu/Google Sheets

Dobře navržený splátkový plán vám poskytne přesný přehled o budoucích splátkách, úrocích, celkových nákladech a dopadu mimořádných splátek. Navíc umožní rychle simulovat scénáře typu „co kdyby“ (změna sazby, doby splatnosti či výše splátky) a udržet disciplínu ve splátkovém kalendáři.

Stručná specifikace modelu, který vytvoříme

  • Vstupy: jistina (počáteční dluh), roční úroková sazba, počet měsíců, datum první splátky, typ splácení (anuita), případně mimořádné splátky.
  • Výstupy: měsíční splátka, rozpis (datum, splátka, úroková část, jistinová část, zůstatek), souhrn úroků, grafy, varování (podmíněné formátování).
  • Rozšíření: proměnné sazby v čase, odklad splátek, dvě strategie zrychleného splácení (avalanche a snowball), konsolidace více dluhů.

Poznámka k formátům funkcí: V Excelu se v některých regionech používá jako oddělovač argumentů ; místo ,. V Google Sheets je standardně ,. Příklady uvádíme s čárkou.

Příprava vstupní sekce (parametrů)

  1. Na samostatném listu (např. Vstupy) vytvořte buňky:
    • B2: Jistina (EUR) – např. 10000
    • B3: Roční úroková sazba – např. 9%
    • B4: Počet měsíců – např. 36
    • B5: Datum 1. splátky – např. 2025-10-15
    • B6: Fixní anuitní režim? – logická hodnota TRUE/FALSE (nepovinné)
  2. V buňce B7 vypočítejte měsíční splátku (anuita):
    • =PMT(B3/12, B4, -B2) – výsledek je záporný, proto záporná jistina.
    • Pokud je splátka placena na začátku období, použijte volitelný 5. argument type=1: =PMT(B3/12, B4, -B2, , 1).

Konstruování amortizační tabulky

Na listu Plán vytvořte záhlaví sloupců (řádek 1): Datum, Období, Splátka, Úrok, Jistina, Zůstatek, Mimořádná splátka, Poznámka.

  1. A2 (Datum 1. splátky): =Vstupy!B5
  2. A3 (a dále): =EDATE(A2,1) (posun o 1 měsíc)
  3. B2 (Období): =1; B3: =B2+1 a kopírujte dolů.
  4. C2 (Splátka): odkaz na vstupní splátku =Vstupy!B7 (při proměnných sazbách odkazujte na lokální PMT – viz níže).
  5. F1 (Zůstatek – počáteční): Do F2 vložte =Vstupy!B2.
  6. D2 (Úrok): =F2*(Vstupy!B3/12)
  7. E2 (Jistina): =C2-D2
  8. F3 (nový zůstatek): =F2-E2-IF(G2="",0,G2) – odečteme jistinu i případnou mimořádnou splátku ve sloupci G.
  9. Kopírujte vzorce v řádku 2 směrem dolů minimálně na počet řádků zadaný v Vstupy!B4.

Alternativa s přesnými finančními funkcemi: Místo ručního rozkladu můžete použít:

  • Úroková část: =IPMT(Vstupy!B3/12, B2, Vstupy!B4, -Vstupy!B2)
  • Jistinová část: =PPMT(Vstupy!B3/12, B2, Vstupy!B4, -Vstupy!B2)

Funkce IPMT a PPMT přesně sledují anuitu, ale při mimořádných splátkách je praktičtější držet se manuálního rozkladu (úrok = zůstatek × měsíční sazba; jistina = splátka − úrok) a zůstatek korigovat o mimořádné platby.

Přidání mimořádných splátek a automatický přepočet

  1. Sloupec G ponechte pro volitelné mimořádné splátky (kladná čísla). Při zadání této částky se zůstatek ve F okamžitě sníží.
  2. Chcete-li zkrátit dobu splácení při pevné anuitní splátce, ve sloupci C nechte splátku konstantní a nechte zůstatek „doběhnout“ dříve (poslední řádky se stanou nulovými dříve než počet období).
  3. Chcete-li snížit splátku a ponechat délku splácení, v řádku po mimořádné splátce přepočítejte novou splátku:
    • Ck: =PMT(Vstupy!B3/12, Vstupy!B4-B(k-1), -F(k)), kde k je řádek po mimořádné splátce a B(k-1) je již splacený počet období.

Změna úrokové sazby v čase (refinancování či refixace)

  1. Na listu Sazby vytvořte tabulku: Platí od data, Roční sazba.
  2. Na listu Plán si do pomocného sloupce (např. H) vytáhněte platnou sazbu pro dané datum pomocí XLOOKUP (Excel) nebo LOOKUP (Sheets):
    • Excel: =XLOOKUP(A2, Sazby!A:A, Sazby!B:B, , 1) (režim aproximativní shody „menší nebo rovné“)
    • Sheets: =LOOKUP(A2, Sazby!A:A, Sazby!B:B)
  3. Úrok pak počítejte jako =F2*(H2/12).
  4. Pokud chcete po změně sazby přepočítat anuitu, v daném řádku vypočítejte novou splátku:
    • =PMT(H2/12, Vstupy!B4-B1, -F2)

Ošetření odkladu splátek (grace period)

Pokud v některých měsících nesplácíte jistinu, ale nabíhají úroky, můžete do sloupce Poznámka zadat kód (např. "ODKLAD") a ve sloupci E (Jistina) použít:

=IF(H2="ODKLAD", 0, C2-D2)

Tímto způsobem se v daném měsíci zaplatí pouze úrok, jistina zůstane nezměněná.

Dvě strategie zrychleného splácení

  • Avalanche (lavina): prioritně směřujte mimořádné splátky na dluh s nejvyšší sazbou. Výsledkem je nejnižší suma úroků.
  • Snowball (sněhová koule): upřednostněte nejmenší zůstatky. Rychlé „výhry“ zvyšují motivaci a disciplínu.

V modelu s více dluhy (list Dluhy) mějte sloupce Věřitel, Zůstatek, Sazba, Min. splátka. Dodatečnou částku směrujte podle zvolené strategie – v plánu to reprezentuje vyšší hodnoty ve sloupci G (Mimořádná) v odpovídajících řádcích.

Konsolidace více dluhů do jednoho

  1. Vypočítejte součet zůstatků a vážený průměr sazeb (pouze informativně). Skutečná sazba konsolidace je dána novou smlouvou.
  2. Vytvořte nový plán s jistinou = součet zůstatků, sazbou konsolidačního úvěru a požadovanou dobou splatnosti.
  3. Do původních plánů vložte do sloupce G mimořádnou splátku ve výši celého zůstatku k datu konsolidace, čímž tyto plány uzavřete.

Kontrolní součty a metriky

  • Celkem zaplaceno: =SUM(C:C) přes celý plán.
  • Celkové úroky: =SUM(D:D).
  • Průměrná měsíční sazba: =AVERAGE(H:H)/12 (pokud měníte sazby v čase).
  • Počet měsíců do splacení: počet řádků do prvního F=0 (lze pomocí MATCH(0,F:F,0) s omezeným rozsahem).

Podmíněné formátování a validace

  • Varování při záporném zůstatku: pravidlo pro F:F „<0“ – zvýrazněte červeně.
  • Termín splátky: pro A:A zvýrazněte data v průběhu následujících 7 dní (vzorec např. =AND(A2>=TODAY(), A2<=TODAY()+7)).
  • Ověření dat: ve G:G (mimořádné) povolte pouze čísla ≥0; v A:A povolte pouze data.

Grafy pro rychlý přehled

  1. Průběh zůstatku: vložte čárový graf z dvojice Datum–Zůstatek.
  2. Rozklad splátky: sloupcový graf pro Úrok a Jistinu (stacked) v čase.
  3. Porovnání scénářů: pokud vytvoříte více variant (list pro každý), vytvořte souhrnný graf ze sloupců „Celkové úroky“ podle scénáře.

Scénáře „co kdyby“ bez přepisování modelu

  • Excel – Správce scénářů: definujte scénáře „Základ“, „+20 EUR měsíčně“, „Refinanc −2 p.b.“ a porovnejte souhrny.
  • Google Sheets – Data > Named functions: vytvořte pomocné pojmenované buňky (např. sadza, mesiace, jistina) a scénáře uložte do tabulky; přepínejte pomocí XLOOKUP podle vybraného scénáře.

Časté chyby a jak jim předcházet

  • Zaměňování sazby: používejte měsíční sazbu ročná/12 a konzistentní období (měsíce k měsícům).
  • Nesprávná znaménka: finanční funkce očekávají záporný cash flow pro jistinu (-B2), výsledkem je kladná splátka.
  • Nedostatečný počet řádků: vygenerujte minimálně tolik řádků, kolik je měsíce, plus rezervu, pokud plán zkracujete mimořádnými splátkami.
  • Prázdná data: pokud A:A obsahuje prázdné buňky, vzorce v řádku mohou vracet #VALUE!. Zabalte je např. do =IF(A2="", "", výpočet).
  • Zaokrouhlování: drobné zůstatky typu 0,01 EUR na konci řešte pomocí ROUND(…, 2) nebo jednorázovou korekcí poslední splátky.

Minimalistická tabulka – hotový kostrový vzorec

Předpoklady: Vstupy!B2=jistina, B3=roční sazba, B4=měsíce, B5=první datum, B7=PMT.

Sloupec Vzorec v řádku 2 Popis
A (Datum) =IF(ROW()=2, Vstupy!B5, EDATE(A1,1)) Rostoucí měsíce
B (Období) =ROW()-1 1,2,3…
C (Splátka) =Vstupy!B7 Anuitní spl

Súvisiace články

Postavení zástavního věřitele

Právní postavení zástavního věřitele zajišťuje jeho věcněprávní a závazková práva na uspokojení zástavou. Zástavní právo vzniká smlouvou nebo zákonem a jeho platnost vůči třetím osobám závisí na publicitě, zejména zápisu či předání zástavy.

Nabídková cena cenného papíru

Cena nabízená za cenný papír (Bid Price, Redemption Price) představuje částku, kterou je investor ochoten zaplatit při nákupu cenného papíru. Je klíčovým indikátorem tržního sentimentu a investiční důvěry.