Tabuľka splátkového plánu

Prečo si vytvoriť vlastný plán splácania v Exceli/Google Sheets

Dobre navrhnutý plán splácania vám dá presný prehľad o budúcich splátkach, úrokoch, celkových nákladoch a dopade mimoriadnych splátok. Navyše umožní rýchlo simulovať „čo ak“ scenáre (zmena sadzby, doby splatnosti či výšky splátky) a udržať disciplínu v splátkovom kalendári.

Stručná špecifikácia modelu, ktorý postavíme

• Vstupy: istina (počiatočný dlh), ročná úroková sadzba, počet mesiacov, dátum prvej splátky, typ splácania (anuitné), prípadne mimoriadne splátky.
• Výstupy: mesačná splátka, rozpis (dátum, splátka, úroková časť, istinová časť, zostatok), sumár úrokov, grafy, varovania (podmienené formátovanie).
• Rozšírenia: meniteľné sadzby v čase, odklad splátok, dva stratégie zrýchleného splácania (avalanche a snowball), konsolidácia viacerých dlhov.

Poznámka k formátom funkcií: V Exceli sa v niektorých regiónoch používa ako oddeľovač argumentov ; namiesto ,. V Google Sheets je štandardne ,. Príklady uvádzame s čiarkou.

Príprava vstupnej sekcie (parametrov)

1. Na samostatnom hárku (napr. Vstupy) vytvorte bunky:
   • B2: Istina (EUR) – napr. 10 000
   • B3: Ročná úroková sadzba – napr. 9%
   • B4: Počet mesiacov – napr. 36
   • B5: Dátum 1. splátky – napr. 2025-10-15
   • B6: Fixný anuitný režim? – logická hodnota TRUE/FALSE (nepovinné)
2. V bunke B7 vypočítajte mesačnú splátku (anuita):
   • =PMT(B3/12, B4, -B2) – výsledok je záporný, preto záporná istina.
   • Ak je splátka platená na začiatku obdobia, použite voliteľný 5. argument type=1: =PMT(B3/12, B4, -B2, , 1).

Konštrukcia amortizačnej tabuľky

Na hárku Plán vytvorte hlavičku stĺpcov (riadok 1): Dátum, Obdobie, Splátka, Úrok, Istina, Zostatok, Mimoriadna splátka, Poznámka.

1. A2 (Dátum 1. splátky): =Vstupy!B5
2. A3 (a nižšie): =EDATE(A2,1) (posun o 1 mesiac)
3. B2 (Obdobie): =1; B3: =B2+1 a skopírujte nadol.
4. C2 (Splátka): odkaz na vstupnú splátku =Vstupy!B7 (alebo pri meniacich sa sadzbách odkazovať na lokálny PMT – viď nižšie).
5. F1 (Zostatok – počiatočný): Do F2 vložte =Vstupy!B2.
6. D2 (Úrok): =F2*(Vstupy!B3/12)
7. E2 (Istina): =C2-D2
8. F3 (nový zostatok): =F2-E2-IF(G2="",0,G2) – odrátame istinu a prípadnú mimoriadnu splátku v G.
9. Skopírujte vzorce v riadku 2 smerom nadol aspoň na Vstupy!B4 riadkov.

Alternatíva s presnými finančnými funkciami: Namiesto ručného rozkladu môžete použiť:

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

Funkcie IPMT a PPMT presne sledujú anuitu, no pri mimoriadnych splátkach je praktickejšie držať sa manuálneho rozkladu (úrok = zostatok × mesačná sadzba; istina = splátka − úrok) a zostatok korigovať o mimoriadne platby.

Pridanie mimoriadnych splátok a automatické prepočet

1. Stĺpec G nechajte pre voliteľné mimoriadne splátky (kladné čísla). Pri zadanej sume sa zostatok v F okamžite zníži.
2. Ak chcete skrátiť dobu splatnosti pri pevnej anuitnej splátke, v stĺpci C nechajte splátku konštantnú a nechajte zostatok „dobehnúť“ skôr (posledné riadky sa stanú nulové skôr než počet období).
3. Ak chcete znížiť splátku a ponechať dĺžku, v riadku po mimoriadnej splátke prepočítajte novú splátku:
   • Ck: =PMT(Vstupy!B3/12, Vstupy!B4-B(k-1), -F(k)), kde k je riadok po mimoriadnej splátke a B(k-1) je už odsplatený počet období.

Zmena úrokovej sadzby v čase (refinanc či refixácia)

1. Na hárku Sadzby vytvorte tabuľku: Platí od dátumu, Ročná sadzba.
2. Na hárku Plán si do pomocného stĺpca (napr. H) vytiahnite platnú sadzbu pre daný dátum pomocou XLOOKUP (Excel) alebo LOOKUP (Sheets):
   • Excel: =XLOOKUP(A2, Sadzby!A:A, Sadzby!B:B, , 1) (režim približnej zhody „menšie alebo rovné“)
   • Sheets: =LOOKUP(A2, Sadzby!A:A, Sadzby!B:B)
3. Úrok potom počítajte ako =F2*(H2/12).
4. Ak chcete po zmene sadzby prepočítať anuitu, v danom riadku vypočítajte novú splátku:
   • =PMT(H2/12, Vstupy!B4-B1, -F2)

Ošetrenie odkladu splátok (grace period)

Ak niektoré mesiace nesplácate istinu, ale nabiehajú úroky, môžete do stĺpca Poznámka zadať kód (napr. "ODKLAD") a v stĺpci E (Istina) použiť:

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

Takto sa v danom mesiaci zaplatí iba úrok, istina zostane nezmenená.

Dve stratégie zrýchleného splácania

• Avalanche (lavína): prioritne posielajte mimoriadne splátky na dlh s najvyššou sadzbou. Výsledkom je najnižší súčet úrokov.
• Snowball (snehová guľa): prioritizujte najmenšie zostatky. Rýchle „výhry“ zvyšujú motiváciu a disciplínu.

V modeli s viacerými dlhmi (hárok Dlhy) majte stĺpce Veriteľ, Zostatok, Sadzba, Min. splátka. Dodatočnú čiastku smerujte podľa zvolenej stratégie – v pláne to reprezentuje vyššia G (Mimoriadna) v daných riadkoch.

Konsolidácia viacerých dlhov do jedného

1. Vypočítajte súčet zostatkov a váženú priemernú sadzbu (len informačne). Skutočná sadzba konsolidácie je daná novou zmluvou.
2. Vytvorte nový plán s istinou = súčet zostatkov, sadzbou konsolidačného úveru a požadovanou dobou.
3. Do pôvodných plánov vložte do G mimoriadnu splátku vo výške celého zostatku k dátumu konsolidácie, čím ich uzavriete.

Kontrolné súčty a metriky

• Celkom zaplatené: =SUM(C:C) nad trvaním plánu.
• Celkové úroky: =SUM(D:D).
• Priemerná mesačná sadzba: =AVERAGE(H:H)/12 (ak meníte sadzby v čase).
• Počet mesiacov do splatenia: počet riadkov do prvého F=0 (možno cez MATCH(0,F:F,0) s obmedzeným rozsahom).

Podmienené formátovanie a validácie

• Varovanie pri zápornom zostatku: pravidlo pre F:F „<0“ – zvýraznite červenou.
• Deadline splátky: pre A:A zvýraznite dátumy v priebehu nasledujúcich 7 dní (vzorec napr. =AND(A2>=TODAY(), A2<=TODAY()+7)).
• Overenie dát: v G:G (mimoriadne) povoľte iba čísla >=0; v A:A povoľte iba dátumy.

Grafy pre rýchly prehľad

1. Priebeh zostatku: vložte čiarový graf z dvojice Dátum–Zostatok.
2. Rozklad splátky: stĺpcový graf pre Úrok a Istina (stacked) v čase.
3. Porovnanie scenárov: ak vytvoríte viac variantov (hárok pre každý), spravte súhrnný graf zo stĺpcov „Celkové úroky“ podľa scenára.

Scenáre „čo ak“ bez prepisovania modelu

• Excel – Správca scenárov: definujte scenáre „Základ“, „+20 EUR mesačne“, „Refinanc −2 p.b.“ a porovnajte súhrny.
• Google Sheets – Data > Named functions: vytvorte pomocné menované bunky (napr. sadza, mesiace, istina) a scenáre si uložte do tabuľky; prepínajte XLOOKUPom podľa vybraného scenára.

Časté chyby a ako im predísť

• Zamieňanie sadzby: používajte mesačnú sadzbu ročná/12 a konzistentné obdobia (mesiace k mesiacom).
• Nesprávne znamienka: finančné funkcie očakávajú záporný cash flow pre istinu (-B2), výsledkom je kladná splátka.
• Nedostatočný počet riadkov: vygenerujte aspoň toľko riadkov, koľko je mesiace, plus rezerva, ak plán skracujete mimoriadnymi splátkami.
• Prázdne dátumy: ak A:A obsahuje prázdne bunky, vzorce v riadku môžu vracať #VALUE!. Obalte ich napr. =IF(A2="", "", výpočet).
• Zaokrúhľovanie: mierne zostatky typu 0,01 EUR na konci riešte cez ROUND(…, 2) alebo jednorazovou korekciou poslednej splátky.

Minimalistická tabuľka – hotový kostrový vzorec

Predpoklady: Vstupy!B2=istina, B3=ročná sadzba, B4=mesiace, B5=prvý dátum, B7=PMT.

Rozšírenie: mesačná splátka ako percento príjmu

Ak chcete disciplinovane zvyšovať splátku s rastom príjmu, na hárku Vstupy majte stĺpec Mesiac, Čistý príjem, % na dlh. Potom splátku v pláne nastavte na:

=MAX(PMT(sadzba/12, zost_mes, -zostatok), príjem*percento)

Takto nikdy neklesnete pod minimálnu anuitnú požiadavku a zároveň smerujete prebytky na rýchlejšie splatenie.

Rozšírenie: poplatky a RPMN

• Jednorazový poplatok: pridajte do riadku 1 stĺpec „Poplatok na začiatku“ a znížte oň disponibilnú istinu (alebo ho sledujte osobitne v súhrne nákladov).
• Pravidelný mesačný poplatok: pridajte do stĺpca C (Splátka) komponent + Poplatok, úrok a istina sa stále rozkladajú len z finančnej časti.
• RPMN (efektívne náklady): použite =RATE(n, -mesačná_platba, čistý_príjem_z_úveru)*12 na aproximáciu, prípadne =XIRR(toky, dátumy) s presnými cash flow.

Kontrolný checklist pred používaním

• Test „nulového zostatku“: posledný zostatok ≤ 0,01 EUR.
• Zmena sadzby alebo mimoriadna splátka mení buď dĺžku, alebo výšku splátky podľa zvoleného režimu.
• Graf zostatku je monotónne klesajúci; ak nie, je problém v znamienkach alebo prepočte po zmene parametrov.

Tipy pre tímové používanie a auditovateľnosť

• Používajte menované oblasti (istina, sadza, mesiace) – zlepší sa čitateľnosť.
• Uzamknite vzorcové bunky a povoľte editovať iba vstupy a stĺpec Mimoriadna.
• Vytvorte verziu so scenármi a históriu zmien (v Google Sheets automaticky).

Praktický mini-príklad (číselne)

Istina: 10 000 EUR; ročná sadzba: 9 %; 36 mesiacov; prvá splátka 15.10.2025.
Mesačná splátka: =PMT(0,09/12;36;-10000) ≈ 318,41 EUR.
Prvý mesiac: úrok ≈ 10000×0,09/12=75; istina ≈ 243,41; zostatok po 1. splátke ≈ 9756,59. Mimoriadna splátka 500 EUR v 6. mesiaci zníži zostatok a skráti dobu splácania o ~1–2 mesiace (presne podľa vášho modelu).

Vlastný plán v Exceli/Sheets je spoľahlivý nástroj, ako dostať pôžičky pod kontrolu, robiť informované rozhodnutia (refinanc, mimoriadne splátky) a merať reálny pokrok. Začnite s jednoduchým anuitným modelom, pridajte mimoriadne splátky, scenáre a grafy. Keď si postup osvojíte, rozšírenia (meniteľná sadzba, odklad, viac dlhov) zvládnete pridať pomocou pár ďalších stĺpcov a funkcií.