Vzorec pro výpočet anuitní splátky v Excelu

Výpočet plateb půjček je snazší a rychlejší pomocí aplikace Microsoft Office Excel. Mnohem více času se stráví ručním výpočtem. Tento článek se zaměří na anuitní platby, vlastnosti jejich výpočtu, výhody a nevýhody.

Co je to anuitní splátka

Způsob měsíčního splácení úvěru, při kterém se vložená částka po celou dobu trvání úvěru nemění. Tito. k určitým datům každého měsíce člověk ukládá určitou částku peněz až do úplného splacení půjčky.

Úroky z úvěru jsou navíc již zahrnuty v celkové částce zaplacené bance.

Klasifikační anuita

Anuitní splátky lze rozdělit do následujících typů:

1. Pevný. Platby, které se nemění, mají pevnou sazbu bez ohledu na vnější podmínky.
2. Měna. Možnost změnit výši platby v případě poklesu nebo zvýšení směnného kurzu.
3. indexováno. Platby v závislosti na úrovni, ukazatel inflace. Během výpůjční doby se jejich velikost často mění.
4. Proměnné. Anuita, která se může měnit v závislosti na stavu finančního systému, instrumentů.

Dávej pozor! Pevné platby jsou výhodnější pro všechny dlužníky, protože mají malé riziko.

Výhody a nevýhody anuitních plateb

Pro lepší pochopení tématu je nutné prostudovat klíčové vlastnosti tohoto typu splácení úvěru. Má následující výhody:

• Stanovení konkrétní výše platby a data jejího zaplacení.
• Vysoká dostupnost pro dlužníky. O rentu může požádat téměř každý bez ohledu na svou finanční situaci.
• Možnost snížení výše měsíční splátky při zvýšení inflace.

Ne bez nevýhod:

• Vysoké hodnocení. Dlužník přeplatí větší částku peněz ve srovnání s rozdílovou platbou.
• Problémy vyplývající z touhy splatit dluh v předstihu.
• Žádné přepočty pro předčasné platby.

Jaká je splátka půjčky?

Anuitní výplata má tyto složky:

• Úroky placené osobou při splácení půjčky.
• Část jistiny.

Výsledkem je, že celková výše úroku téměř vždy převyšuje částku, kterou dlužník přispěl ke snížení dluhu.

Základní vzorec anuitních plateb v Excelu

Jak již bylo zmíněno výše, v Microsoft Office Excel můžete pracovat s různými typy plateb za půjčky a zálohy. Výjimkou není ani renta. Obecně platí, že vzorec, pomocí kterého můžete rychle vypočítat anuitní příspěvky, je následující:  

Důležité! Je nemožné otevřít závorky ve jmenovateli tohoto výrazu pro jeho zjednodušení.

Hlavní hodnoty vzorce jsou dešifrovány takto:

• AP – anuitní platba (název je zkrácený).
• O – velikost hlavního dluhu dlužníka.
• PS – úroková sazba nabízená měsíčně konkrétní bankou.
• C je počet měsíců, po které úvěr trvá.

Pro asimilaci informací stačí uvést několik příkladů použití tohoto vzorce. O nich se bude dále diskutovat.

Příklady použití funkce PMT v Excelu

Dáme jednoduchou podmínku problému. Je nutné vypočítat měsíční splátku úvěru, pokud banka předloží úrok 23% a celková částka je 25000 3 rublů. Půjčka bude trvat XNUMX roky. Problém je řešen podle algoritmu:

1. Vytvořte obecnou tabulku v Excelu na základě zdrojových dat.

2. Aktivujte funkci PMT a zadejte pro ni argumenty do příslušného pole.
3. Do pole „Sázka“ zadejte vzorec „B3/B5“. To bude úroková sazba úvěru.
4. Do řádku „Nper“ napište hodnotu ve tvaru „B4*B5“. Jedná se o celkový počet splátek za celou dobu trvání úvěru.
5. Vyplňte pole „PS“. Zde musíte uvést počáteční částku převzatou z banky a napsat hodnotu „B2“.

6. Ujistěte se, že po kliknutí na „OK“ ve zdrojové tabulce byla vypočtena hodnota „Měsíční platba“.

Dodatečné informace! Záporné číslo znamená, že dlužník utrácí peníze.

Příklad výpočtu výše přeplatku na úvěru v Excelu

V tomto problému musíte vypočítat částku, kterou přeplatí osoba, která si vzala půjčku 50000 27 rublů s úrokovou sazbou 5% po dobu 12 let. Celkem dlužník provede XNUMX plateb ročně. Řešení:

1. Zkompilujte původní datovou tabulku.

2. Od celkové výše plateb odečtěte počáteční částku podle vzorce «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Musí být vložen do řádku vzorců v horní části hlavní nabídky programu.
3. V důsledku toho se výše přeplatků objeví v posledním řádku vytvořeného talíře. Dlužník navíc přeplatí 41606 rublů.

Vzorec pro výpočet optimální měsíční splátky úvěru v Excelu

Úkol s následující podmínkou: klient zaregistroval bankovní účet na 200000 4 rublů s možností měsíčního doplňování. Je nutné vypočítat výši platby, kterou musí člověk provést každý měsíc, aby po 2000000 letech měl na svém účtu 11 XNUMX XNUMX rublů. Sazba je XNUMX %. Řešení:

1. Vytvořte tabulku na základě původních dat.

2. Zadejte vzorec do vstupního řádku aplikace Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» a na klávesnici stiskněte „Enter“. Písmena se budou lišit v závislosti na buňkách, ve kterých je tabulka umístěna.
3. Zkontrolujte, zda je výše příspěvku vypočítána automaticky v posledním řádku tabulky.

Dávej pozor! Aby tedy klient nashromáždil 4 rubly ve výši 2000000 11 28188 % za XNUMX let, musí každý měsíc uložit XNUMX XNUMX rublů. Mínus ve výši naznačuje, že klient utrpí ztráty tím, že peníze bance poskytne.

Vlastnosti použití funkce PMT v Excelu

Obecně je tento vzorec napsán takto: =PMT(rychlost; nper; ps; [bs]; [typ]). Funkce má následující vlastnosti:

1. Při výpočtu měsíčních příspěvků se bere v úvahu pouze roční sazba.
2. Při specifikaci úrokové sazby je důležité provést přepočet na základě počtu splátek za rok.
3. Místo argumentu „Nper“ ve vzorci je uvedeno konkrétní číslo. Toto je platební období.

Výpočet platby

Obecně se anuitní výplata počítá ve dvou fázích. Pro pochopení tématu je třeba každou z fází posuzovat samostatně. O tom se bude dále diskutovat.

Fáze 1: výpočet měsíční splátky

Chcete-li v Excelu vypočítat částku, kterou musíte každý měsíc platit za půjčku s pevnou sazbou, musíte:

1. Zkompilujte zdrojovou tabulku a vyberte buňku, ve které chcete zobrazit výsledek, a nahoře klikněte na tlačítko „Vložit funkci“.

2. V seznamu funkcí vyberte „PLT“ a klikněte na „OK“.

3. V dalším okně nastavte argumenty funkce s uvedením odpovídajících řádků v kompilované tabulce. Na konci každého řádku musíte kliknout na ikonu a poté vybrat požadovanou buňku v poli.

4. Po vyplnění všech argumentů se do řádku pro zadání hodnot zapíše příslušný vzorec a v poli tabulky „Měsíční platba“ se objeví výsledek výpočtu se znaménkem mínus.

Důležité! Po výpočtu splátky bude možné spočítat částku, kterou dlužník za celou dobu půjčky přeplatí.

Fáze 2: platební údaje

Výši přeplatku lze vypočítat měsíčně. Díky tomu člověk pochopí, kolik peněz každý měsíc utratí za půjčku. Detailní výpočet se provádí následovně:

1. Vytvořte tabulku na 24 měsíců.

2. Umístěte kurzor do první buňky tabulky a vložte funkci „OSPLT“.

3. Stejným způsobem vyplňte argumenty funkce.

4. Při vyplňování pole „Období“ se musíte odkázat na první měsíc v tabulce s uvedením buňky 1.

5. Zkontrolujte, zda je vyplněna první buňka ve sloupci „Platba tělem půjčky“.
6. Chcete-li vyplnit všechny řádky prvního sloupce, musíte buňku roztáhnout na konec tabulky

7. Vyberte funkci „PRPLT“ pro vyplnění druhého sloupce tabulky.
8. Vyplňte všechny argumenty v otevřeném okně podle obrázku níže.

9. Vypočítejte celkovou měsíční platbu sečtením hodnot v předchozích dvou sloupcích.

10. Chcete-li vypočítat „splatný zůstatek“, musíte k platbě na těle půjčky přidat úrokovou sazbu a natáhnout ji na konec desky, aby se vyplnily všechny měsíce půjčky.

Dodatečné informace! Při výpočtu zbytku musí být znaky dolaru zavěšeny na vzorec tak, aby se při roztažení nevysunul.

Výpočet anuitních splátek úvěru v Excelu

Funkce PMT je zodpovědná za výpočet anuity v Excelu. Principem výpočtu je obecně provést následující kroky:

1. Zkompilujte původní datovou tabulku.
2. Sestavte si splátkový kalendář na každý měsíc.
3. Vyberte první buňku ve sloupci „Platby úvěru“ a zadejte kalkulační vzorec „PLT ($B3/12;$B$4;$B$2)“.
4. Výsledná hodnota je roztažena pro všechny sloupce desky.

Výpočet splátky jistiny dluhu v MS Excel

Anuitní platby musí být prováděny měsíčně v pevných částkách. A úroková sazba se nemění.

Výpočet zůstatku jistiny (s BS=0, typ=0)

Předpokládejme, že půjčka ve výši 100000 10 rublů je přijata na 9 let za 1%. Je nutné vypočítat výši jistiny dluhu v 3. měsíci XNUMX. roku. Řešení:

1. Sestavte datový list a vypočítejte měsíční splátku pomocí výše uvedeného vzorce PV.
2. Pomocí vzorce vypočítejte podíl platby potřebné na splacení části dluhu «=-PMT-(PS-PS1)*položka=-PMT-(PS +PMT+PS*položka)».
3. Vypočítejte výši jistiny za 120 období pomocí známého vzorce.
4. Pomocí operátora HPMT zjistěte výši zaplaceného úroku za 25. měsíc.
5. Zkontrolujte výsledek.

Výpočet částky jistiny, která byla zaplacena mezi dvěma obdobími

Tento výpočet se nejlépe provádí jednoduchým způsobem. Pro výpočet částky v intervalu pro dvě období musíte použít následující vzorce:

• =«-BS(položka; con_period; plt; [ps]; [typ]) /(1+typ *položka)».
• = “+ BS(sazba; počáteční_období-1; plt; [ps]; [typ]) /IF(počáteční_období =1; 1; 1+typ *sazba)“.

Dávej pozor! Písmena v závorkách jsou nahrazena konkrétními hodnotami.

Předčasné splacení se zkrácenou dobou splatnosti nebo splátkou

Pokud potřebujete zkrátit dobu půjčky, budete muset provést dodatečné výpočty pomocí operátoru IF. Bude tedy možné kontrolovat nulový zůstatek, kterého by nemělo být dosaženo před koncem platebního období.

Pro snížení plateb je potřeba přepočítat příspěvek za každý předchozí měsíc.

Úvěrová kalkulačka s nepravidelnými splátkami

Existuje několik možností anuity, kdy může dlužník vložit variabilní částky v kterýkoli den v měsíci. V takové situaci se zůstatek dluhu a úroků počítá za každý den. Zároveň v Excelu potřebujete:

1. Zadejte dny v měsíci, za které se platby provádějí, a uveďte jejich počet.
2. Zkontrolujte záporné a kladné částky. Negativní jsou preferovány.
3. Počítejte dny mezi dvěma daty, kdy byly peníze vloženy.

Výpočet pravidelné platby v MS Excel. Termínovaný vklad

V Excelu můžete rychle vypočítat výši pravidelných plateb za předpokladu, že již byla nashromážděna pevná částka. Tato akce se provádí pomocí funkce PMT po zkompilování počáteční tabulky.

Proč investovat do čističky vzduchu?

Výpočet anuitních plateb v Excelu je tedy jednodušší, rychlejší a efektivnější. Za jejich výpočet je odpovědný operátor PMT. Podrobnější příklady naleznete výše.