Excel

Výpočet anuitní platby v aplikaci Microsoft Excel

Před přijetím půjčky by bylo hezké vypočítat všechny platby za to. To ušetří dlužníka v budoucnosti z různých nečekaných problémů a zklamání, když se ukáže, že přeplatky jsou příliš velké. Nápověda v tomto výpočtu může být nástrojem Excel. Zjistíme, jak v tomto programu vypočítat výplaty anuitní půjčky.

Výpočet platby

Zaprvé musím říci, že existují dva typy půjček:

  • Diferencované;
  • Anuita.

S diferencovaným systémem vstupuje klient do banky stejný měsíční podíl plateb na orgánu úvěru plus úrokové platby. Hodnota úrokových plateb klesá každý měsíc, jelikož se sníží objem úvěru, z něhož jsou vypočteny. Celková měsíční platba je tak také snížena.

S anuitní schématem se používá mírně odlišný přístup. Klient provádí každý měsíc stejnou částku celkové platby, která se skládá z plateb na úv ě ru a úhrady úroku. Zpočátku jsou platby úroků za celou částku úvěru, ale s tím, jak se tělo snižuje, se také sníží úroky. Celková částka platby však zůstává nezměněna z důvodu měsíčního zvýšení částky plateb u subjektu půjčky. V průběhu času se tedy podíl úroků na celkové měsíční splátce snižuje a specifická váha platby za tělo se zvyšuje. Zároveň se celková měsíční splátka během doby trvání úvěru nezmění.

Jen na výpočtu anuitní platby se zastavíme. Navíc je to skutečné, neboť v současné době většina bank využívá tento systém. Je to také výhodné pro zákazníky, protože v takovém případě se celková částka platby nezmění a zůstane pevně stanovena. Zákazníci vždy vědí, kolik zaplatí.

Krok 1: Výpočet měsíční splátky

Pro výpočet měsíčního poplatku při použití anuitní schématu v aplikaci Excel existuje speciální funkce - PLT . Patří do kategorie finančních operátorů. Vzorec pro tuto funkci je následující:

=ПЛТ(ставка;кпер;пс;бс;тип)

Jak můžete vidět, tato funkce má spoustu argumentů. Je pravda, že poslední dva z nich nejsou povinné.

Argument "Bet" označuje úrokovou sazbu za určité období. Pokud se například použije roční sazba, ale půjčka se vyplácí měsíčně, pak by měla být roční sazba rozdělena na 12 a výsledek použit jako argument. Pokud se používá čtvrtletní platební metoda, v tomto případě by měla být roční sazba rozdělena na 4 , atd.

"Kper" se vztahuje k celkovému počtu splátek. To znamená, že pokud je půjčka za jeden rok s měsíční platbou, pak se počet období považuje za 12 , pokud je po dobu dvou let, pak je počet období 24 . Pokud je půjčka čerpána na dva roky se čtvrtletní platbou, pak je počet období 8 .

"Ps" označuje současnou hodnotu. Jednoduše řečeno, jedná se o celkovou částku půjčky na začátku půjčky, tj. Částku, kterou si půjčíte, aniž bychom vzali v úvahu úroky a další dodatečné platby.

"Bs" je budoucí hodnota. Tato částka, která bude součástí úvěru v okamžiku dokončení úvěrové smlouvy. Ve většině případů se tento argument rovná "0" , protože dlužník na konci období půjčky musí plně uspokojit s věřitelem. Zadaný argument je nepovinný. Proto je-li vynechána, považuje se za nulovou.

Argument "Typ" určuje čas výpočtu: na konci nebo na začátku období. V prvním případě se jedná o hodnotu "0" a druhou hodnotu "1" . Většina bankovních institucí tuto možnost využívá s platbou na konci období. Tento argument je také nepovinný a pokud je vynechán, předpokládá se, že je nulový.

Nyní je čas přejít na konkrétní příklad výpočtu měsíčního poplatku za použití funkce PLT. Pro výpočet používáme tabulku s počátečními údaji, která uvádí úrokovou sazbu z úvěru ( 12% ), hodnotu půjčky ( 500 000 rublů ) a dobu úvěru ( 24 měsíců ). V takovém případě se platba provádí měsíčně na konci každého období.

  1. Vyberte prvek na listu, na který se zobrazí výsledek výpočtu, a klikněte na ikonu "Vložit funkci", která se nachází v blízkosti vzorce.

    2. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

  2. Otevře se okno Průvodce funkcí . V kategorii "Finanční" vybereme název "PLT" a klikneme na tlačítko "OK" .

    3. Přejděte do okna argumentů funkce PLC v aplikaci Microsoft Excel

  3. Potom se otevře okno argumentů operátora PLT .

    V poli "Hodnotit" byste měli zadat částku úroku za období. To lze provést ručně, stačí jen uvést procentní podíl, ale uvedeme jej v samostatné buňce na listu, takže jej dejte odkaz. Nastavte kurzor v poli a klepněte na odpovídající buňku. Ale jak si pamatujeme, v naší tabulce je stanovena roční úroková sazba a platební období se rovná měsíci. Proto rozdělujeme roční početnost, resp. Odkaz na buňku, ve které je obsažena, číslem 12 , což odpovídá počtu měsíců v roce. Dělení se provádí přímo v poli okna s argumenty.

    V poli "Kper" se nastaví doba připsání. Máme to po dobu 24 měsíců. Číslo 24 můžete zadat ručně, ale my, stejně jako v předchozím případě, označujeme odkaz na umístění tohoto indikátoru ve zdrojové tabulce.

    V poli "Ps" je uvedena původní hodnota úvěru. To se rovná 500 000 rublům . Stejně jako v předchozích případech uváděme odkaz na element listu, který obsahuje tento indikátor.

    Pole "Bs" označuje výši půjčky po úplné platbě. Jak si pamatujete, tato hodnota je téměř vždy nula. V tomto poli nastavíme číslo "0" . Tento argument lze zcela vynechat.

    V poli "Typ" uvedeme na začátku nebo na konci měsíce platba. My, stejně jako ve většině případů, vyrábíme na konci měsíce. Proto jsme nastavili číslo "0" . Stejně jako u předchozího argumentu nemůžete v tomto poli zadat nic, pak program implicitně předpokládá, že má hodnotu nula.

    Po zadání všech údajů klikněte na tlačítko "OK" .

    4. Okno argumentu funkce PLT v aplikaci Microsoft Excel

  4. Poté se výsledek výpočtu zobrazí v buňce, kterou jsme vybrali v prvním odstavci této příručky. Jak můžete vidět, hodnota měsíční celkové platby za půjčku je 23.536,74 rublů . Nenechte se zmást znaménkem "-" před touto částkou. Takže Axel poukazuje na to, že se jedná o peněžní tok, tedy ztrátu.

    5. Výsledek výpočtu měsíční platby v aplikaci Microsoft Excel

  5. Pro výpočet celkové částky platby za celé období půjčky, s přihlédnutím k splacení úvěrového subjektu a měsíční úrokové sazby, stačí vynásobit hodnotu měsíční platby ( 23536,74 rublů ) počtem měsíců ( 24 měsíců ). Jak vidíte, celková částka plateb za celé období půjčky v našem případě činila 564881,67 rublů .

    6. Celková částka plateb v aplikaci Microsoft Excel

  6. Nyní můžete vypočítat výši přeplatku na úvěr. K tomu je třeba se zbavit celkové částky plateb z úvěru, včetně úroků a úvěrového orgánu, počáteční částku vypůjčená. Ale pamatujeme si, že první z těchto hodnot již má znak "-" . Proto se v našem konkrétním případě ukázalo, že je třeba je skládat. Jak vidíte, celková výše přeplatku za úvěr za celé období činila 64881,67 rublů .

Částka přeplatku za půjčku v aplikaci Microsoft Excel

Lekce: Průvodce funkcemi v aplikaci Excel

Krok 2: podrobnosti o platbách

A nyní s pomocí dalších operátorů společnosti Axel provedeme měsíční platby, abychom zjistili, kolik za daný měsíc zaplatíme za úvěr a jak velký je zájem. Pro tento účel nakreslíme tabulku v aplikaci Excel, kterou vyplníme s daty. Řádky této tabulky budou odpovídat odpovídajícímu období, tedy měsíci. Vzhledem k tomu, že doba připsání úvěru je pro nás 24 měsíců, bude také vhodný počet řádků. Ve sloupcích je uvedena platba subjektu půjčky, úhrada úroků, celková měsíční platba, která je součtem předchozích dvou sloupců a zbývající částka, která má být zaplacena.

Tabulka výplaty v aplikaci Microsoft Excel

  1. K určení výše platby pro úverovou instituci používáme funkci OSPLT , která je právě určena pro tyto účely. Nastavte kurzor v buňce, která je v řádku "1" a ve sloupci "Platba tělem půjčky". Klikněte na tlačítko "Vložit funkci" .

    2. Vložit funkci v aplikaci Microsoft Excel

  2. Přejděte do Průvodce funkcemi . V kategorii "Finanční" označujeme název "OSPLT" a klikneme na tlačítko "OK" .

    3. Přejděte do okna argumentů funkce OSPLT v aplikaci Microsoft Excel

  3. Zobrazí se okno argumentů operátora OSPLT. Má následující syntaxi:

    =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Jak vidíte, argumenty této funkce se téměř úplně shodují s argumenty operátoru PLT , místo volitelného argumentu "Type" byl přidán pouze povinný argument "Period" . Označuje číslo výplaty a v našem konkrétním případě číslo měsíce.

    Vyplníme pole okna argumentů funkce OSPLT, kterou již známe stejnými daty, které byly použity pro funkci PLT . Pouze vzít v úvahu skutečnost, že v budoucnu bude vzorec kopírován pomocí značky pro vyplnění, je nutné, aby všechny odkazy v polích byly absolutní, takže se nemění. K tomu je nutné před každou hodnotou souřadnic podél svislé a vodorovné čáry zadat dolarový znak. Ale je to jednodušší, jednoduše zvolíte souřadnice a stisknete funkční klávesu F4 . Označení dolaru bude automaticky umístěno na správných místech. Také nezapomeňte, že roční sazba by měla být dělena 12 .

    4. Okno argumentu funkce OBSFT v aplikaci Microsoft Excel

  4. Máme ale ještě další argument, který funkce PLT neměla. Tento argument je "Období" . Do příslušného pole nastavte odkaz na první buňku sloupce "Období" . Tento prvek listu obsahuje číslo "1" , které označuje číslo prvního měsíce připsání. Ale na rozdíl od předchozích polí ponecháváme ve specifikovaném poli relativní relaci a neděláme to absolutní.

    Po zadání všech údajů, o kterých jsme hovořili výše, klikněte na tlačítko "OK" .

    5. Period argument v okně argumentů funkce OBSF v aplikaci Microsoft Excel

  5. Poté se v buňce, kterou jsme předtím přidělila, zobrazí částka platby na úvěrovém subjektu za první měsíc. Bude to 18536.74 rublů .

    6. Výsledek výpočtu funkce OSPLT v aplikaci Microsoft Excel

  6. Poté, jak bylo uvedeno výše, měli bychom tento vzorec zkopírovat do zbytku buněk ve sloupci pomocí popisovače. Chcete-li to provést, nastavte kurzor do pravého dolního rohu buňky obsahující vzorec. Kurzor se převede na kříž, který se nazývá značka naplnění. Slepte levým tlačítkem myši a přetáhněte jej dolů na konec tabulky.

    7. Filler v aplikaci Microsoft Excel

  7. Výsledkem je vyplnění všech buněk ve sloupci. Nyní máme měsíční splátkový kalendář. Jak bylo uvedeno výše, výše platby za tento článek se každým novým obdobím zvyšuje.

    8. Výše platby za tělo úvěru za měsíc v aplikaci Microsoft Excel

  8. Nyní musíme provést měsíční výpočet úrokových plateb. Pro tyto účely použijeme operátor PRPLT . Vyberte první prázdnou buňku ve sloupci Úrokové platby . Klikněte na tlačítko "Vložit funkci" .

    9. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

  9. V okně Průvodců funkcí v kategorii "Finanční" vybereme název PRPLT . Klikněte na tlačítko "OK" .

    10. Přejděte do okna argumentu funkce PRPLT v aplikaci Microsoft Excel

  10. Zobrazí se okno argumentu funkce PRPLT . Jeho syntaxe je následující:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Jak je vidět, argumenty této funkce jsou zcela totožné s analogickými prvky operátora OSPLT . Proto jsme do okna vložili stejné údaje, které jsme zadali v předchozím okně argumentů. Nezapomínáme však na to, že odkaz v poli "Období" by měl být relativní a ve všech ostatních polích by měly být souřadnice přeneseny do absolutní podoby. Poté klikněte na tlačítko "OK" .

    11. Okno argumentů funkce PRPLT v aplikaci Microsoft Excel

  11. Poté se vypočítá výsledek výpočtu výše výplaty úroku z úvěru za první měsíc v odpovídající buňce.

    12. Výsledek výpočtu funkce PRPLT v aplikaci Microsoft Excel

  12. Při použití značky pro vyplnění zkopírujeme vzorec do zbývajících prvků sloupce, čímž získáme měsíční splátkový kalendář pro úroky z úvěru. Jak vidíme, jak již bylo řečeno, hodnota tohoto typu plateb se od měsíce k měsíci snižuje.

    13. Harmonogram plateb za úroky za půjčku v aplikaci Microsoft Excel

  13. Nyní musíme vypočítat celkovou měsíční platbu. Pro tento výpočet by se nemělo uchýlit k žádnému operátorovi, protože lze použít jednoduchý aritmetický vzorec. Přidáváme obsah buněk prvního měsíce sloupců "Platba orgánem půjčky" a "Výplata úroků" . Chcete-li to provést, nastavte znak "=" na první prázdnou buňku sloupce "Celkový měsíční platba" . Poté klikněte na dva výše uvedené prvky a mezi nimi nastavte znaménko "+" . Stiskněte klávesu Enter.

    14. Součet celkové měsíční platby v aplikaci Microsoft Excel

  14. Dále, pomocí značky pro vyplnění, jako v předchozích případech, vyplníme sloupec s daty. Jak vidíme, po celou dobu trvání smlouvy bude částka celkové měsíční splátky, včetně platby na úverový orgán, a úhrada úroků 23536,74 rublů . Ve skutečnosti jsme tento údaj již počítali s pomocí PLT . V tomto případě je však uvedena jasněji, přesněji jako částka platby za orgán půjčky a úroky.

    15. Celková měsíční platba v aplikaci Microsoft Excel

  15. Nyní je třeba přidat údaje do sloupce, kde se měsíčně zobrazí zůstatek částky úvěru, který je stále zaplacen. V první buňce sloupce Balance to Payout bude výpočet nejjednodušší. Potřebujeme se zbavit původní částky úvěru, která je uvedena v tabulce s primárními údaji, platba na úvěrovém orgánu za první měsíc v tabulce vypořádání. Ale vzhledem k tomu, že jedno z čísel, které již máme se znaménkem "-" , by nemělo být odebráno, ale složeno. Proveďte toto a stiskněte tlačítko Enter .

    16. Zůstatek, který je třeba vyplatit po prvním měsíci připsání v Microsoft Excel

  16. Výpočet zůstatku za platbu po druhém a následujících měsících bude poněkud komplikovanější. Abychom to mohli udělat, musíme na začátku půjčky oddělit od těla úvěru celkovou částku plateb orgánu pro úvěry za předchozí období. Zadejte znaménko "=" ve druhé buňce sloupce "Balance to Payout" . Dále uvedeme odkaz na buňku, která obsahuje původní částku úvěru. Zvolíme to stisknutím klávesy F4 . Poté přidáme znaménko "+" , protože druhá hodnota bude pro nás negativní. Poté klikněte na tlačítko "Vložit funkci" .

    17. Vložit funkci v aplikaci Microsoft Excel

  17. Spustí se Průvodce funkcemi , ve kterém je nutné přesunout do kategorie "Matematický" . Zde vybereme nápis "SUM" a klikneme na tlačítko "OK" .

    18. Přejděte do okna argumentů funkce SUM v aplikaci Microsoft Excel

  18. Otevře se okno s argumenty funkce SUM . Uvedený operátor slouží k shrnutí dat v buňkách, které musíme provést ve sloupci "Platba tělem půjčky". Má následující syntaxi:

    =СУММ(число1;число2;…)

    Argumenty jsou odkazy na buňky obsahující čísla. V poli "Číslo1" nastavíme kurzor. Potom podržte levé tlačítko myši a vyberte první dvě buňky sloupce "Výplata kreditními body" na listu. V poli, jak vidíte, byl zobrazen odkaz na rozsah. Skládá se ze dvou částí oddělených dvojtečkou: odkazy na první buňku rozsahu a na poslední. Aby bylo možné v budoucnu zkopírovat označený vzorec pomocí značky pro vyplnění, vytvoříme první část odkazu na absolutní rozsah. Vyberte jej a klikněte na funkční tlačítko F7 . Druhá část odkazu zůstává relativní. Nyní, když použijete značku naplnění, bude první buňka rozsahu fixována a poslední buňka bude nahozena, když se posune dolů. To je to, co potřebujeme k naplnění našich cílů. Poté klikněte na tlačítko "OK" .

    19. Okno argumentu funkce SUM v aplikaci Microsoft Excel

  19. Takže výsledek zůstatku úvěrového dluhu po druhém měsíci je zobrazen v buňce. Nyní, začínáme s touto buňkou, zkopírujeme vzorec tak, že prázdné elementy sloupců použijeme pomocí popisovače výplně.

    20. Filler v aplikaci Microsoft Excel

  20. Měsíční výpočet zůstatků na úhradu z úvěru se provádí za celé úvěrové období. Podle očekávání je tato částka na konci období nula.

Výpočet zůstatku na úhradu úvěru v aplikaci Microsoft Excel

Neměli jsme tedy pouze spočítat platbu za úvěr, ale uspořádali jsme druh kreditní kalkulačky. Kdo bude jednat na anuitní schéma. Pokud v původní tabulce změníme například výši úvěru a roční úrokovou sazbu, pak v závěrečné tabulce bude automaticky přepočítána data. Proto může být pro konkrétní případ použita pouze jednou, ale může být použita v různých situacích pro výpočet možností půjčky na anuitní schéma.

Původní data byla změněna v aplikaci Microsoft Excel

Lekce: Finanční funkce v aplikaci Excel

Jak můžete vidět pomocí programu Excel doma, můžete snadno vypočítat celkovou měsíční výpůjčku na anuitní schémě, za tím účelem využijete operátora PLT . Kromě toho je za pomoci funkcí OPST a MTEF možné vypočítat výši plateb u subjektu poskytujícího úvěr a úroků za stanovené období. Aplikovat všechny tyto funkce zavazadel dohromady, je možné vytvořit silnou úvěrovou kalkulačku, kterou můžete použít k výpočtu anuitní platby.