Vytvoření plánu amortizace úvěru v Excelu může být cennou dovedností, která vám umožní efektivně vizualizovat a spravovat splátky úvěru. Amortizační plán je tabulka, která podrobně popisuje každou pravidelnou platbu u amortizačního úvěru (typicky hypotečního úvěru nebo úvěru na auto). Rozdělí každou platbu na úrok a jistinu a po každé platbě zobrazí zbývající zůstatek. Pojďme se ponořit do podrobného průvodce vytvořením takového rozvrhu v Excelu.

Co je amortizační plán?

Vytvořte amortizační plán v Excelu

Vytvořte amortizační plán pro různý počet období

Vytvořte amortizační plán s dodatečnými platbami

Vytvořte amortizační plán (s dodatečnými platbami) pomocí šablony Excel

Co je amortizační plán?

Amortizační plán je podrobná tabulka používaná při výpočtech úvěru, která zobrazuje proces splácení úvěru v průběhu času. Amortizační plány se běžně používají pro půjčky s pevnou sazbou, jako jsou hypotéky, půjčky na auta a osobní půjčky, kde částka platby zůstává konstantní po celou dobu trvání půjčky, ale poměr platby vůči úroku versus jistina se v průběhu času mění.

Pro vytvoření plánu amortizace úvěru v Excelu jsou skutečně klíčové vestavěné funkce PMT, PPMT a IPMT. Pojďme pochopit, co každá funkce dělá:

• Funkce PMT: Tato funkce se používá k výpočtu celkové platby za období za půjčku na základě konstantních plateb a konstantní úrokové sazby.
• Funkce IPMT: Tato funkce vypočítá úrokovou část platby za dané období.
• Funkce PPMT: Tato funkce se používá k výpočtu hlavní části platby za určité období.

Pomocí těchto funkcí v Excelu můžete vytvořit podrobný amortizační plán, který zobrazuje úrok a jistinu každé platby spolu se zbývajícím zůstatkem půjčky po každé platbě.

Vytvořte amortizační plán v Excelu

V této části představíme dvě odlišné metody, jak vytvořit amortizační plán v Excelu. Tyto metody vyhovují různým uživatelským preferencím a úrovním dovedností a zajišťují, že kdokoli, bez ohledu na své znalosti Excelu, může úspěšně sestavit podrobný a přesný plán amortizace své půjčky.

Vzorce nabízejí hlubší pochopení základních výpočtů a poskytují flexibilitu pro přizpůsobení plánu podle konkrétních požadavků. Tento přístup je ideální pro ty, kteří chtějí mít praktickou zkušenost a jasný přehled o tom, jak je každá platba rozdělena na jistinu a úrok. Nyní si rozeberme proces vytváření amortizačního plánu v Excelu krok za krokem:

⭐️ Krok 1: Nastavte informace o úvěru a tabulku amortizace

1. Zadejte relativní informace o půjčce, jako je roční úroková sazba, doba půjčky v letech, počet plateb za rok a částka půjčky do buněk, jak je znázorněno na následujícím obrázku:
   
2. Potom vytvořte tabulku amortizace v aplikaci Excel se zadanými popisky, jako je období, platba, úrok, jistina, zbývající zůstatek v buňkách A7:E7.
3. Do sloupce Období zadejte čísla období. V tomto příkladu je celkový počet plateb 24 měsíců (2 roky), takže do sloupce Období zadáte čísla 1 až 24. Viz snímek obrazovky:
   
4. Jakmile nastavíte tabulku se štítky a čísly období, můžete pokračovat v zadávání vzorců a hodnot do sloupců Platba, Úrok, Jistina a Zůstatek na základě specifik vaší půjčky.

⭐️ Krok 2: Vypočítejte celkovou částku platby pomocí funkce PMT

Syntaxe PMT je:

Zadejte následující vzorec do buňky B7 a poté přetažením úchytu výplně dolů vyplňte tento vzorec do dalších buněk a uvidíte konstantní částku platby pro všechna období. Viz snímek obrazovky:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Krok 3: Vypočítejte úrok pomocí funkce IPMT

V tomto kroku vypočítáte úrok za každé platební období pomocí funkce IPMT aplikace Excel.

Zadejte následující vzorec do buňky C7 a poté přetáhněte úchyt výplně dolů po sloupci, abyste vyplnili tento vzorec a získali úrok pro každé období.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Krok 4: Vypočítejte jistinu pomocí funkce PPMT

Po výpočtu úroku za každé období je dalším krokem při vytváření plánu amortizace výpočet jistiny každé platby. To se provádí pomocí funkce PPMT, která je určena k určení jistiny platby za konkrétní období na základě konstantních plateb a konstantní úrokové sazby.

Syntaxe IPMT je:

Syntaxe a parametry pro vzorec PPMT jsou totožné s těmi, které byly použity v dříve diskutovaném vzorci IPMT.

Zadejte následující vzorec do buňky D7 a poté přetažením úchytu výplně dolů ve sloupci vyplňte hlavní hodnotu pro každé období. Viz snímek obrazovky:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Krok 5: Vypočítejte zbývající zůstatek

Po výpočtu jak úroku, tak jistiny každé platby je dalším krokem ve vašem amortizačním plánu výpočet zbývajícího zůstatku úvěru po každé platbě. Toto je klíčová část plánu, protože ukazuje, jak se zůstatek úvěru v průběhu času snižuje.

1. Do první buňky sloupce vašeho zůstatku – E7 zadejte následující vzorec, což znamená, že zbývající zůstatek bude původní částka úvěru minus část jistiny první platby:

   =B4-D7

   
2. Pro druhé a všechna následující období vypočítejte zbývající zůstatek odečtením splátky jistiny běžného období od zůstatku předchozího období. Použijte prosím následující vzorec do buňky E8:

   =E7-D8

    Poznámka: Odkaz na buňku rovnováhy by měl být relativní, takže se aktualizuje při přetahování vzorce dolů.
   
3. A pak přetáhněte úchyt výplně dolů do sloupce. Jak vidíte, každá buňka se automaticky upraví tak, aby vypočítala zbývající zůstatek na základě aktualizovaných splátek jistiny.
   

⭐️ Krok 6: Udělejte si shrnutí půjčky

Po nastavení podrobného plánu amortizace může vytvoření shrnutí půjčky poskytnout rychlý přehled o klíčových aspektech vaší půjčky. Tento souhrn bude obvykle zahrnovat celkové náklady na půjčku, celkový zaplacený úrok.

● Chcete-li vypočítat celkové platby:

=SUM(B7:B30)

● Chcete-li vypočítat celkový úrok:

=SUM(C7:C30)

⭐️ Výsledek:

Nyní byl úspěšně vytvořen jednoduchý, ale komplexní plán splácení úvěru. viz snímek obrazovky:

Vytvořte amortizační plán pro různý počet období

V předchozím příkladu vytvoříme splátkový kalendář úvěru pro pevný počet plateb. Tento přístup je ideální pro vyřízení konkrétního úvěru nebo hypotéky, kde se podmínky nemění.

Pokud však chcete vytvořit flexibilní plán amortizace, který lze opakovaně použít pro půjčky s různou dobou, což vám umožní upravit počet plateb podle potřeby pro různé scénáře půjček, budete muset použít podrobnější metodu.

⭐️ Krok 1: Nastavte informace o úvěru a tabulku amortizace

1. Zadejte relativní informace o půjčce, jako je roční úroková sazba, doba půjčky v letech, počet plateb za rok a částka půjčky do buněk, jak je znázorněno na následujícím obrázku:
   
2. Potom vytvořte tabulku amortizace v aplikaci Excel se zadanými popisky, jako je období, platba, úrok, jistina, zbývající zůstatek v buňkách A7:E7.
3. Do sloupce Období zadejte nejvyšší počet plateb, které byste mohli u jakékoli půjčky zvážit, například zadejte čísla v rozmezí od 1 do 360. To může pokrýt standardní 30letou půjčku, pokud provádíte měsíční platby.
   

⭐️ Krok 2: Upravte platební, úrokové a základní vzorce pomocí funkce IF

Do odpovídajících buněk zadejte následující vzorce a poté přetažením úchytu rozšiřte tyto vzorce dolů na maximální počet platebních období, které jste nastavili.

● Platební vzorec:

K výpočtu platby obvykle používáte funkci PMT. Chcete-li začlenit příkaz IF, vzorec syntaxe je:

Takže vzorce jsou tyto:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Úrokový vzorec:

Vzorec syntaxe je:

Takže vzorce jsou tyto:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Základní vzorec:

Vzorec syntaxe je:

Takže vzorce jsou tyto:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Krok 3: Upravte vzorec zbývajícího zůstatku

U zbývajícího zůstatku můžete obvykle odečíst jistinu od předchozího zůstatku. S příkazem IF jej upravte takto:

● První buňka rovnováhy: (E7)

=B4-D7

● Druhá buňka rovnováhy: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Krok 4: Udělejte si shrnutí půjčky

Po nastavení amortizačního plánu s upravenými vzorci je dalším krokem vytvoření souhrnu úvěru.

● Chcete-li vypočítat celkové platby:

=SUM(B7:B366)

● Chcete-li vypočítat celkový úrok:

=SUM(C7:C366)

⭐️ Výsledek:

Nyní máte k dispozici komplexní a dynamický plán amortizace v Excelu, doplněný podrobným shrnutím úvěru. Kdykoli upravíte dobu splatnosti, celý plán amortizace se automaticky aktualizuje, aby odrážel tyto změny. Podívejte se na ukázku níže:

Vytvořte amortizační plán s dodatečnými platbami

Dodatečnými splátkami nad rámec plánovaných splátek lze půjčku splatit rychleji. Harmonogram amortizace, který zahrnuje mimořádné platby, když je vytvořen v Excelu, ukazuje, jak tyto dodatečné platby mohou urychlit splacení půjčky a snížit celkový zaplacený úrok. Můžete to nastavit takto:

⭐️ Krok 1: Nastavte informace o úvěru a tabulku amortizace

1. Zadejte relativní informace o půjčce, jako je roční úroková sazba, doba půjčky v letech, počet plateb za rok, částka půjčky a extra platba do buněk, jak je znázorněno na následujícím obrázku:
   
2. Poté vypočítejte plánovanou platbu.
   Kromě vstupních buněk je pro naše následné výpočty potřeba další předdefinovaná buňka - plánovaná částka platby. Jedná se o běžnou výši splátky úvěru za předpokladu, že nebudou provedeny žádné další platby. Použijte prosím následující vzorec do buňky B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Poté vytvořte tabulku amortizace v Excelu:
   • V buňkách A8:G8 nastavte zadané popisky, jako je Období, Rozvrh platby, Mimořádná platba, Celková platba, Úrok, Jistina, Zbývající zůstatek;
   • Do sloupce Období zadejte nejvyšší počet plateb, které byste mohli u jakékoli půjčky zvážit. Vyplňte například čísla v rozmezí od 0 do 360. To může pokrýt standardní 30letou půjčku, pokud provádíte měsíční platby;
   • Pro období 0 (v našem případě řádek 9) načtěte hodnotu zůstatku pomocí tohoto vzorce = B4, která odpovídá počáteční výši úvěru. Všechny ostatní buňky v tomto řádku by měly zůstat prázdné.
   • 

⭐️ Krok 2: Vytvořte vzorce pro plán amortizace s dodatečnými platbami

Postupně zadejte následující vzorce do odpovídajících buněk. Abychom zlepšili zpracování chyb, zabalíme tento a všechny budoucí vzorce do funkce IFERROR. Tento přístup pomáhá vyhnout se mnoha potenciálním chybám, které by mohly nastat, pokud některá ze vstupních buněk zůstane prázdná nebo obsahuje nesprávné hodnoty.

● Vypočítejte plánovanou platbu:

Do buňky B10 zadejte následující vzorec:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Vypočítejte extra platbu:

Do buňky C10 zadejte následující vzorec:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Vypočítejte celkovou platbu:

Do buňky D10 zadejte následující vzorec:

=IFERROR(B10+C10, "")

● Vypočítejte jistinu:

Do buňky E10 zadejte následující vzorec:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Spočítejte si úrok:

Do buňky F10 zadejte následující vzorec:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Vypočítejte zbývající zůstatek

Do buňky G10 zadejte následující vzorec:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Jakmile dokončíte každý ze vzorců, vyberte oblast buněk B10:G10 a pomocí úchytu výplně přetáhněte a rozšiřte tyto vzorce dolů přes celou sadu platebních období. Pro všechna nevyužitá období budou buňky ukazovat 0s. Viz snímek obrazovky:

⭐️ Krok 3: Udělejte si shrnutí půjčky

● Získejte plánovaný počet plateb:

=B2:B3

● Získejte skutečný počet plateb:

=COUNTIF(D10:D369,">"&0)

● Získejte celkové dodatečné platby:

=SUM(C10:C369)

● Získejte celkový úrok:

=SUM(F10:F369)

⭐️ Výsledek:

Pomocí těchto kroků vytvoříte dynamický plán amortizace v aplikaci Excel, který zohledňuje mimořádné platby.

Vytvořte amortizační plán pomocí šablony Excel

Vytvoření amortizačního plánu v Excelu pomocí šablony je přímočará a časově efektivní metoda. Excel poskytuje vestavěné šablony, které automaticky vypočítávají úrok, jistinu a zbývající zůstatek každé platby. Zde je návod, jak vytvořit plán amortizace pomocí šablony Excel:

1. klikněte Soubor > Nový, do vyhledávacího pole zadejte amortizační plán, a stiskněte tlačítko vstoupit klíč. Poté kliknutím na šablonu vyberte šablonu, která nejlépe vyhovuje vašim potřebám. Například zde vyberu šablonu jednoduché kalkulačky úvěru. Viz snímek obrazovky:
   
2. Jakmile vyberete šablonu, klikněte na Vytvořit otevřete jako nový sešit.
3. Poté zadejte své vlastní podrobnosti o půjčce, šablona by měla automaticky vypočítat a vyplnit rozvrh na základě vašich vstupů.
4. Nakonec si uložte nový sešit plánu amortizace.