Jak používat funkci plt v excelu. Příklady funkce PMT v Excelu: výpočet splátek anuitního úvěru

11.12.2023

Funkce PMT vrací částku periodické platby pro anuitu na základě konstantních částek plateb a konstantních úrokových sazeb. Funkce má následující syntaxi:

PLT (sazba; nper; ps; bs; typ).

Argumenty funkce PMT

Povinný argument. Úroková sazba za jedno období.

Poznámka: pokud je vyjádřena v procentech za rok, pak je třeba tuto hodnotu vydělit počtem období

Povinný argument (konstantní hodnota). Počet období plateb.

Poznámka: Tato hodnota se nemůže změnit během celého výplatního období. Tato hodnota obvykle zahrnuje splátky jistiny a úroků, nikoli však daně nebo poplatky.

Povinný argument. Současná (aktuální) hodnota, tj. celková částka, která se aktuálně rovná počtu budoucích plateb.

Poznámka: V případě platby peněžních prostředků se zobrazuje jako záporné číslo a v případě příjmu peněžních prostředků kladné číslo. Pokud je argument vynechán, pak je nastaven na 0. V tomto případě musí být zadána hodnota argumentu ps.

Volitelný argument. Hodnota budoucí hodnoty, tj. požadovaný zůstatek finančních prostředků po poslední platbě.

Poznámka: Pokud je argument "bs" vynechán, předpokládá se, že je 0 (například budoucí hodnota půjčky je 0).

Volitelný argument. Označuje, kdy má být platba provedena.

Poznámka: Argument je nula, pokud je platba provedena na konci období, a jeden, pokud je na začátku. "Typ" je výchozí nula.

Funkce PMT v Excelu je zařazena do kategorie „Finanční“. Vrací částku pravidelné platby za anuitu, přičemž bere v úvahu konstantní částky plateb a úrokovou sazbu. Pojďme se na to blíže podívat.

Syntaxe a vlastnosti funkce PMT

Syntaxe funkce: rychlost; nper; ps; [bs]; [typ].

Dekódování argumentů:

Sazba je úrok z úvěru.
Nper – celkový počet splátek úvěru.
Ps – současná hodnota, rovná se počtu budoucích plateb (výše úvěru).
Fs je budoucí hodnota půjčky po poslední platbě (pokud je argument vynechán, předpokládá se, že budoucí hodnota je 0).
Typ – volitelný argument, který určuje, zda je platba provedena na konci období (hodnota 0 nebo nepřítomnost) nebo na začátku (hodnota 1).

Vlastnosti provozu PMT:

Do výpočtu pravidelné platby jsou zahrnuty pouze splátky jistiny a úrokové platby. Daně, provize, dodatečné příspěvky a platby rezerv někdy spojené s půjčkou se neberou v úvahu.
Při zadávání argumentu „Rate“ je nutné vzít v úvahu frekvenci výpočtu úroků. U 6% půjčky je čtvrtletní sazba 6%/4; za měsíční sazbu – 6 %/12.
Argument „Nper“ udává celkový počet splátek úvěru. Pokud osoba platí měsíční splátky tříleté půjčky, pak se pro specifikaci argumentu použije hodnota 3*12.

Příklady funkce PMT v Excelu

Aby funkce fungovala správně, musíte správně zadat počáteční údaje:

Velikost půjčky je označena znaménkem mínus, protože Úvěrová instituce tyto peníze „dává“ a „ztrácí“. Chcete-li zaznamenat hodnotu úrokové sazby, musíte použít formát procent. Pokud je zapsáno v číselné formě, použije se desetinné číslo (0,08).

Klikněte na tlačítko fx („Vložit funkci“). Otevře se okno Průvodce funkcí. V kategorii „Finanční“ vyberte funkci PMT. Doplňte argumenty:

Když je kurzor v poli jednoho nebo druhého argumentu, zobrazí se níže „nápověda“: co je třeba zadat. Protože zdrojová data byla zadána do excelové tabulky, použili jsme jako argumenty odkazy na buňky s odpovídajícími hodnotami. Můžete ale zadat i číselné hodnoty.

Poznámka! V poli „Sazba“ je roční úroková hodnota vydělena 12: splátky úvěru se provádějí měsíčně.

Měsíční splátky úvěru v souladu s podmínkami uvedenými jako argumenty činí 1 037,03 RUB.

Chcete-li zjistit celkovou částku, kterou je třeba zaplatit za celé období (jistina plus úrok), vynásobte měsíční splátku půjčky hodnotou „Nper“:

PMT(Sázka;Počet_na;Ps;[Bs];[Typ])

PS

Vzorec PS slouží k výpočtu současné hodnoty investice. Tato funkce je inverzní k operátoru PLT. Má přesně stejné argumenty, ale místo argumentu současné hodnoty ( "PS"), která se skutečně počítá, je uvedena výše pravidelné platby ( "plt"). Syntaxe je tedy:

PS(Sazba;Počet_za;Plt;[Bs];[Typ])

NPV

Následující výkaz se používá k výpočtu čisté současné hodnoty nebo diskontované hodnoty. Tato funkce má dva argumenty: diskontní sazbu a hodnotu plateb nebo příjmů. Pravda, druhý z nich může mít až 254 opcí představujících peněžní toky. Syntaxe tohoto vzorce je:

NPV(Sazba;Hodnota1;Hodnota2;…)

NABÍDKA

Funkce NABÍDKA vypočítá úrokovou sazbu z anuity. Argumenty pro tento operátor jsou počet teček ( "Kol_per"), výši pravidelné platby ( "plt") a částku platby ( "Ps"). Kromě toho existují další volitelné argumenty: budoucí hodnota ( "Bs") a označení na začátku nebo konci období, že platba bude provedena ( "Typ"). Syntaxe vypadá takto:

SÁZKA(číslo_na;Plt;Ps[Bs];[Typ])

ÚČINEK

Operátor ÚČINEK Vypočítá skutečnou (nebo efektivní) úrokovou sazbu. Tato funkce má pouze dva argumenty: počet období v roce, za které se úrok počítá, a také nominální sazbu. Jeho syntaxe vypadá takto:

EFEKT(číslo_sazba,číslo_za)

Zvažovali jsme pouze nejoblíbenější finanční funkce. Obecně je počet operátorů z této skupiny několikanásobně větší. Tyto příklady však jasně demonstrují efektivitu a snadnost použití těchto nástrojů, které uživatelům značně usnadňují výpočty.

Článek pojednává o finančních funkcích PLT(), OSPLT(), PRPLT(), NPER(), RATE(), PS(), BS(), a také GENERAL INCOME() a GENERAL PAYMENT(), které se používají pro výpočet parametrů anuitního schématu .

Tento článek je součástí série článků o výpočtu parametrů anuity. Seznam všech článků na našem webu o anuitě.

Tento článek obsahuje krátkou část o anuitní teorii, stručný popis anuitních funkcí a jejich argumenty a odkazy na články s příklady použití těchto funkcí.

Trochu teorie

Anuita (někdy se používají termíny „nájemné“, „finanční renta“) je jednosměrný cash flow, jehož prvky jsou stejní ve velikosti a jsou vyráběny prostřednictvím stejná časová období(například když jsou platby prováděny ročně ve stejných částkách).

Syntax PRPLT (sazba; perioda; nper; ps; bs, typ). (2.13)

Argumenty funkce znamenají: nabídka

doba- specifikuje období, za které musí být úrokové platby nalezeny, hodnota musí být v rozsahu od 1 do „nper“;

nper- celkový počet platebních období pro anuitu;

ps- současná hodnota nebo celková částka, která je aktuálně ekvivalentní řadě budoucích plateb, nazývaná také částka jistiny;

bs- požadovaná hodnota budoucí hodnoty nebo zůstatku peněžních prostředků po poslední platbě;

typ- číslo 0 nebo 1 označující, kdy má být platba provedena. Pokud je tento argument vynechán, je nastaven na 0.

Pokud tato funkce není dostupná nebo vrací chybu # NAME?, nainstalujte a načtěte doplněk „Analysis package“. Chcete-li to provést, v nabídce Servis Vybrat tým Doplňky aplikace Excel. Ze seznamu doplňků vyberte Balíček analýzy a stiskněte tlačítko OK. V případě potřeby postupujte podle pokynů instalačního technika.

Řešení: PRPLT (10 % / 12, 1, 12 * 3; 800) = - 6,667 tisíc UAH.

Příklad 2.28. Díky ročním příspěvkům po dobu 6 let byl vytvořen fond ve výši 500 tisíc UAH. Je třeba spočítat, jaký výnos majiteli investice přinesly za poslední rok, pokud roční sazba byla 17,5 %.

Řešení: Příjem za poslední rok (6 období) byl:

PRPLT (17,5 %; 6; 6;; 500) = 66,48110268 tisíc UAH.

Ročně bylo splatné PMT (17,5 %; 6;; 500) = - 53,627 tisíc UAH.

Výše splátky jistiny úvěru (splácení dluhu), která je splácena ve stejných splátkách na konci nebo na začátku každého zúčtovacího období, za zadané období se vypočítá pomocí funkce Excel OSPLT:

Syntax OSPLT (sazba; období; nper; ps; bs; typ) (2.14)

nebo se zjistí jako rozdíl mezi pevnou pravidelnou platbou a úrokem z nesplacené části úvěru. Argumenty funkce znamenají: nabídka- úroková sazba za období;

doba- nastavuje periodu, hodnota musí být v rozsahu od 1 do "nper";

nper- celkový počet období ročních anuitních plateb;

ps- současná hodnota, tj. celková částka, která se rovná počtu budoucích plateb;

typ- číslo 0 nebo 1 označující, kdy má být platba provedena.

Příklad 2.29. Určete výši splátky jistiny u dvouleté půjčky ve výši 2 000 UAH. za první měsíc ve výši 10 % ročně. Úroky se počítají měsíčně.

Řešení: Splátka jistiny úvěru za první měsíc:

OSPLT (10 % / 12, 1, 2 * 12; 2000) = - 75,62 UAH.

Kumulovaný příjem z úvěru (výše úrokových plateb), který je splácen ve stejných platbách na konci nebo na začátku každého zúčtovacího období, je v Excelu vypočítán funkcí OBECNÁ PLATBA mezi dvěma platebními obdobími.

Syntax OBECNÁ PLATBA (sazba; nper; ps;

počáteční období; období; typ). (2.15)

Argumenty funkce znamenají: nabídka- úroková sazba; nper ps počáteční_období -

end_period -

typ- toto je volba doby platby.

Příklad 2.30. Vydán úvěr na nákup nemovitosti ve výši 125 tisíc UAH. po dobu 30 let ve výši 9 % ročně, úrok je připisován měsíčně. Určete výši úrokových plateb a) za druhý rok, b) za první měsíc.

Řešení: Kumulativní úroková platba za druhý rok (od 13. do 24. období) bude:

OBECNÁ PLATBA (9 % / 12; 30 * 12; 125 000; 13; 24; 0) = - 11135,23 UAH. Jedna platba za první měsíc bude:

OBECNÁ PLATBA (9 % / 12; 30 * 12; 125 000, 1, 1, 0) = - 937,50 UAH. Stejná hodnota bude získána při výpočtu pomocí vzorce:

PRPLT (9 % / 12, 1, 30 * 12; 125 000) = - 937,50 UAH. V Excelu funkce CELKOVÝ PŘÍJEM vypočítá kumulativní (kumulativní) částku zaplacenou na splacení jistiny půjčky mezi dvěma obdobími:

Syntax CELKOVÉ PŘÍJMY (sazba; nper;

ps; počáteční období; období; typ). (2.16)

Argumenty funkce znamenají:

nabídka- úroková sazba;

nper- je celkový počet platebních období;

ps- jsou náklady na investici v současné době;

počáteční_období - toto je číslo prvního období zahrnutého do výpočtu. Platební lhůty jsou číslovány od 1;

end_period - toto je číslo posledního období zahrnutého do výpočtu;

typ- toto je volba doby platby.

Příklad 2.31. Vydán úvěr ve výši 125 tisíc UAH. po dobu 30 let ve výši 9 % ročně, úrok je připisován měsíčně. Stanovte výši základních plateb: a) za první měsíc; b) druhý rok (platby od 13. období do 24.).

řešení:

a) CELKOVÝ PŘÍJEM (9 % / 12; 30 * 12; 125 000, 1, 1, 0) = - 68,27827118 UAH;

b) je-li půjčka splacena ve stejných platbách na konci každého zúčtovacího období, bude výše splátky dluhu za druhý rok:

CELKOVÝ PŘÍJEM (9 % / 12; 30 * 12; 125 000; 13; 24; 0) = - +934,1071234 UAH. Období od 13. do 24. dne tvoří druhý rok.

Laboratorní práce č. 2

Práce s finančními funkcemi.

Co kdyby analýza

Cíl práce: Naučte se pracovat s finančními funkcemi Excelu

a provést analýzu typu what-if

1 Finanční funkce v ekonomických výpočtech

2 Prognózování pomocí analýzy What-if

Finanční funkce v ekonomických výpočtech

Funkce PMT. Výpočet měsíční splátky úvěru

Funkce PMT určuje výši pravidelných plateb pro anuitu na základě konstantních částek plateb a konstantních úrokových sazeb.

Příklad 1 Určete měsíční splátku, pokud banka poskytuje půjčku ve výši 140 000 rublů. se splátkami 5 let 8,5 % ročně s měsíčními splátkami. Poslední platba by měla být 10 000 rublů.

Zapišme data do excelové tabulky podle Obr. 1)

1 Vyberte buňku V 6 a klikněte na funkční tlačítko Vložit (sign f x nalevo od řádku vzorců). Zobrazí se okno Průvodce funkcí, vyberte kategorii Finance.

2 Klikněte na funkci PMT, přetáhněte okno PMT na prázdné místo na obrazovce , uvolnit stůl a

Obrázek 1 Výpočet anuity vyplňte její pole:

▪ Pole Nabídka- toto je procento za měsíc,

zadejte 0,085,

▪ Nper– počet platebních období, tzn. 5 let*12 měsíců, zadejte 5*12

▪ NZ– celková částka všech plateb od aktuálního okamžiku, zadejte 140 000,

▪ Bs– budoucí hodnota, zadejte 130000 se znaménkem „-“, protože Platíme my, ne banka,

§ Typ– platba na konci měsíce, zadejte tedy 0 nebo nic.

3 Stiskněte OK.

Výsledek: asi 2738 rub. Chcete-li splatit 130 000 rublů, musíte platit měsíčně. po dobu 5 let (na konci období je poslední platba dalších 10 000 rublů)

2 Prognózování pomocí analýzy What-if

Analýza What-if vám umožňuje předpovědět hodnotu funkce (matematické, finanční, statistické atd.), když se změní její argumenty. Existují tři způsoby, jak předpovídat hodnoty: pomocí tabulek pro vyhledávání dat, pomocí skriptů a pomocí výběru parametrů a hledání řešení.

1 způsob. Tabulka vyhledávání dat – Toto je rozsah buněk, který ukazuje, jak změna hodnot vyhledávání ovlivní výsledek vrácený vzorcem. Pokud buňka obsahuje vzorec, který obsahuje prvky z jiných buněk, změna hodnoty v jedné nebo více buňkách změní výsledek v buňce obsahující vzorec.

Příklad 2 Společnost poskytla půjčku na 80 000 rublů. po dobu 3 let. Definovat:

měsíční splátky s úrokovými sazbami 7 %, 8 % a 9 % ročně,

Měsíční splátky s úrokovou sazbou 5 %, doba půjčky 5 let a výše půjčky 100 000 RUB.

1 Uveďme substituční tabulku ve tvaru (obr. 2):

Obrázek 2 Tabulka suplování

2 Zadejte vzorec platby PMT (B3/12;B4*12;B5) do buňky D2 ručně nebo přes okno PMT z Průvodce funkcí (viz příklad 1), vypočtená hodnota funkce -2470,17 rublů se objeví v D2.

3 Změňme hodnotu buňky B3 o 8% a v D2 dostaneme částku platby -2506,91 rublů.

4 Změňme hodnotu buňky B3 o 9%, dostaneme v D2 částku platby -2543,98 rublů.

5 Změňme současně hodnoty buněk: B3 o 5%, B4 o 5 a B5 o 100 000, dostaneme v D2 částku platby -1887,12 rublů.

Substituční tabulka musí obsahovat jednu z buněk vzorec.

Metoda 2. scénář - je to sada zástupných hodnot používaných k predikci chování modelu. Můžete vytvořit a uložit několik různých scénářů na jeden list aplikace Excel a přepnout na kterýkoli z nich, abyste viděli výsledky a vybrali ten nejlepší.

Příklad 3 Vypišme si možnosti dosazení dat z bodů 2 a 3 příkladu 2 ve formě scénářů.

Chcete-li vytvořit skript, musíte provést následující:

1 Z nabídky Nástroje vyberte příkaz Skripty.

2 V okně Správce skriptů, které se otevře, klepněte na tlačítko Přidat.

3 Zadejte název scénáře, například "Rate 7%".

4 V poli Vyměnitelné buňky zadejte buňky (oddělené dvojtečkou), které chcete změnit, v tomto případě buňku B3.

5 Stiskněte tlačítko OK.

6 V dialogovém okně Hodnoty scénáře, které se otevře, zadejte pro každou měněnou buňku novou hodnotu nebo vzorec; v tomto případě zadejte do B3 číslo 0,07. Klepněte na tlačítko OK. Je vhodné uložit počáteční model what-if jako skript a pojmenovat jej například „Počáteční hodnoty“. V opačném případě, když definujete nové vyměnitelné buňky, budou původní data ztracena.

Chcete-li zobrazit skript, musíte použít tlačítko Ustoupit v okně Správce skriptů. Klepnutím na tlačítko Souhrn v dialogovém okně Správce scénářů můžete získat souhrnnou zprávu v samostatném listu nazvaném Struktura scénáře, který ukazuje dopad různých scénářů na jednu nebo více buněk výsledků. Značky "+"("-") vlevo a nahoře umožňují rozbalit (sbalit) jednotlivé části sestavy. Vyměnitelná pole jsou zvýrazněna šedě.

3 způsobem. Výběr parametru. Při výběru parametru se mění hodnota ovlivňující buňky (parametru), dokud vzorec závislý na této buňce nevrátí zadanou hodnotu.

Příklad 4 Podmínka příkladu 1. Společnost může platit ne více než 2500 rublů měsíčně. Určete, jaká by měla být poslední platba za toto.

1. Vyberte buňku B6:

2. V nabídce Nástroje vyberte příkaz Výběr parametrů.

V okně Výběr parametrů:

V poli Nastavit v buňce – se zadá B6,

Do pole Hodnota zadejte -2500

Do pole Změna hodnoty buňky zadejte B3 (buňka poslední platby),

Klikněte OK.

Výsledek: poslední platba = -27716 rub.

Při výběru parametru musí jedna z buněk obsahovat také vzorec , protože tabulka je vyhledávací tabulka.

Příkaz Search for Solution z menu Tools slouží k současnému výběru několika parametrů za účelem maximalizace nebo minimalizace obsahu cílové buňky a je podrobně popsán v laboratorní práci č. 7 (Excel-7).

Kontrolní otázky

1 Jak zobrazit aplikaci Průvodce funkcí?

2 Jakou operaci provádí funkce PMT, co se zadává do jejích polí Norm, Nper, Nz, Bs, Typ?

3 Účel a metody analýzy „Co kdyby“?

4 Co je „Tabulka substitucí“, jaké je složení jejích buněk?

5 Co je to skript, jak jej vytvořit, zobrazit a získat závěrečnou zprávu na samostatném listu?

6 Podstata operace Výběr parametru, jak se provádí?

Úkoly

1 Dokončete úkol z příkladu 1 a změňte částku půjčky na 140 000· n, Kde n- číslo studenta v učitelském deníku. Udělejte totéž pro novou částku úvěru, změňte roční úrok z 8,5 % na 5 % a dobu trvání úvěru z 5 na 10 let.

2 Proveďte analýzu „co kdyby“ pomocí substituční tabulky v příkladu 2 a změňte výši půjčky na 80 000·n, kde n je číslo studenta v učitelském deníku.

3 Formulujte ve formě scénářů všechny operace z odstavce 1 (dva scénáře) a odstavce 2 (čtyři scénáře) tohoto zadání pro laboratorní práci.

4 Dokončete úkol z příkladu 4 a změňte částku měsíční platby na n·100.

1Název, účel, obsah díla

2 Písemné odpovědi na bezpečnostní otázky

Než si vezmete půjčku, bylo by dobré si spočítat všechny splátky na ní. To ušetří dlužníkovi v budoucnu různé nečekané potíže a zklamání, když se ukáže, že přeplatek je příliš velký. S tímto výpočtem mohou pomoci nástroje Excelu. Pojďme zjistit, jak vypočítat anuitní splátky úvěru v tomto programu.

Nejprve je třeba říci, že existují dva typy splátek úvěru:

Diferencované;
Anuita.

U diferencovaného schématu platí klient bance měsíčně stejný podíl plateb na úvěrovém těle plus úrokové platby. Výše úrokových plateb se každý měsíc snižuje s tím, jak se snižuje objem úvěru, ze kterého se počítají. Klesá tak i celková měsíční splátka.

Anuitní schéma využívá trochu jiný přístup. Klient platí měsíční splátku ve stejné výši, která se skládá ze splátek na úvěrový orgán a úrokových plateb. Zpočátku se platby úroků počítají z celé částky úvěru, ale jak se výše úvěru snižuje, klesá i připisování úroků. Celková výše platby však zůstává nezměněna kvůli měsíčnímu nárůstu výše plateb na těle půjčky. Postupem času tedy klesá podíl úroků na celkové měsíční platbě a zvyšuje se podíl platby na tělo. Samotná celková měsíční splátka se přitom po celou dobu úvěru nemění.

Zaměříme se na výpočet anuitní splátky. Navíc je to relevantní, protože v současné době většina bank používá tento konkrétní režim. Je to také výhodné pro klienty, protože v tomto případě se celková částka platby nemění a zůstává pevná. Klienti vždy vědí, kolik mají zaplatit.

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

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

PLT(sazba;nper;ps;bs;typ)

Jak vidíte, tato funkce má poměrně velké množství argumentů. Pravda, poslední dva z nich nejsou povinné.

Argument "Nabídka" označuje úrokovou sazbu za konkrétní období. Pokud je například použita roční sazba, ale splátka úvěru je prováděna měsíčně, je třeba roční sazbu vydělit 12 a výsledek použijte jako argument. Pokud je použit typ čtvrtletní platby, musí být roční sazba vydělena 4 atd.

"Nper" udává celkový počet období splácení úvěru. To znamená, že pokud je úvěr sjednán na jeden rok s měsíčními splátkami, pak se bere v úvahu počet období 12 , pokud na dva roky, tak počet období je 24 . Pokud je úvěr sjednán na dva roky se čtvrtletní splátkou, pak se počet období rovná 8 .

"Ps" označuje aktuální hodnotu v současné době. Jednoduše řečeno, jedná se o celkovou výši půjčky na začátku půjčování, tedy částku, kterou si půjčíte, bez úroků a dalších dodatečných plateb.

"Bs" je budoucí hodnota. Jedná se o částku, která bude tělem půjčky v době uzavření smlouvy o půjčce. Ve většině případů se tento argument rovná «0» , protože dlužník musí věřiteli zaplatit celou částku na konci doby trvání půjčky. Zadaný argument je volitelný. Pokud je tedy vynechán, považuje se za rovný nule.

Argument "Typ" určuje dobu výpočtu: na konci nebo na začátku období. V prvním případě nabývá hodnoty «0» a ve druhém - "1". Většina bankovních institucí využívá možnost platby na konci období. Tento argument je také volitelný a pokud je vynechán, považuje se za nulový.

Nyní je čas přejít na konkrétní příklad výpočtu měsíční splátky pomocí funkce PMT. Pro výpočet používáme tabulku s počátečními údaji, která ukazuje úrokovou sazbu úvěru ( 12% ), částka půjčky ( 500 000 rublů) a dobu půjčky ( 24 měsíců). Platba se provádí měsíčně na konci každého období.

Vyberte prvek na listu, do kterého se zobrazí výsledek výpočtu, a klikněte na ikonu "Vložit funkci", který se nachází vedle řádku vzorců.

Okno se spouští Funkční průvodci. V kategorii "Finanční" zvýrazněte jméno "PLT" a stiskněte tlačítko "OK".

Poté se otevře okno argumentů operátora PLT.
V terénu "Nabídka" Měli byste zadat úrokovou sazbu za období. To lze provést ručně jednoduchým nastavením procent, ale v našem případě je to uvedeno v samostatné buňce na listu, takže na něj dáme odkaz. Umístěte kurzor do pole a poté klikněte na odpovídající buňku. Ale jak si pamatujeme, roční úroková sazba je stanovena v naší tabulce a doba splatnosti se rovná měsíci. Roční sazbu, respektive vazbu na buňku, ve které je obsažena, proto vydělíme číslem 12 , což odpovídá počtu měsíců v roce. Dělení provádíme přímo v poli okna argumentů.

V terénu "Nper" Doba půjčky je stanovena. Je nám roven 24 měsíce. Do pole můžete zadat číslo 24 ručně, ale stejně jako v předchozím případě uvádíme odkaz na umístění tohoto indikátoru ve zdrojové tabulce.

V terénu "Ps" je uvedena počáteční výše úvěru. Je to rovné 500 000 rublů. Stejně jako v předchozích případech označujeme odkaz na prvek listu, který tento indikátor obsahuje.

V terénu "Bs" Výše půjčky je uvedena po úplném zaplacení. Jak si pamatujeme, tato hodnota je téměř vždy rovna nule. V tomto poli nastavte číslo «0» . I když tento argument lze zcela vynechat.

V terénu "Typ" uveďte na začátku nebo na konci měsíce, kdy byla platba provedena. V našem případě se jako ve většině případů provádí na konci měsíce. Proto jsme nastavili číslo «0» . Stejně jako v případě předchozího argumentu nemůžete do tohoto pole nic zadat, pak bude program standardně předpokládat, že obsahuje hodnotu rovnou nule.

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

Poté se výsledek výpočtu zobrazí v buňce, kterou jsme zvýraznili v prvním odstavci této příručky. Jak vidíte, celková měsíční splátka úvěru je 23536,74 rublů. Nenechte se zmást znakem „-“ před touto částkou. Excel tedy naznačuje, že se jedná o výdaj finančních prostředků, tedy ztrátu.

Aby bylo možné vypočítat celkovou výši platby za celou dobu úvěru, s přihlédnutím ke splátkám těla půjčky a měsíčnímu úroku, stačí vynásobit částku měsíční splátky ( 23536,74 rublů) počtem měsíců ( 24 měsíců). Jak vidíte, celková výše splátek za celou dobu půjčky v našem případě byla 564881,67 rublů.

Nyní si můžete spočítat výši přeplatku na úvěru. Chcete-li to provést, musíte odečíst počáteční vypůjčenou částku od celkové výše splátek půjčky, včetně úroků a těla půjčky. Ale pamatujeme si, že první z těchto hodnot je již podepsána «-» . Proto se v našem konkrétním případě ukazuje, že je třeba je přidat. Jak vidíte, celková výše přeplatku na úvěru za celé období byla 64881,67 rublů.

Fáze 2: platební údaje

A nyní s pomocí dalších operátorů Excelu budeme provádět měsíční detaily plateb, abychom viděli, kolik v konkrétním měsíci zaplatíme za tělo půjčky a jaká je výše úroku. Pro tyto účely si nakreslíme v Excelu tabulku, kterou vyplníme daty. Řádky této tabulky budou odpovídat odpovídajícímu období, tedy měsíci. Vzhledem k tomu, že naše výpůjční doba je 24 měsíc, pak bude odpovídající i počet řádků. Ve sloupcích je uvedena splátka úvěrového těla, splátka úroku, celková měsíční splátka, která je součtem předchozích dvou sloupců, a zbývající částka k úhradě.

Pro určení výše platby za výpůjční tělo používáme funkci OSPLT, která je právě pro tyto účely určena. Umístěte kurzor do buňky, která je v řádku "1" a ve sloupci „Platba úvěrovým orgánem“. Klikněte na tlačítko "Vložit funkci".

Pojďme Průvodce funkcí. V kategorii "Finanční" označit jméno "OSPLT" a stiskněte tlačítko "OK".

Otevře se okno argumentů operátora OSPT. Má následující syntaxi:
OSPLT(Sazba;Období;Nper;Ps;Bs)

Jak vidíte, argumenty této funkce se téměř zcela shodují s argumenty operátora PLT, pouze místo volitelného argumentu "Typ" přidán požadovaný argument "Doba". Udává číslo platebního období a v našem konkrétním případě číslo měsíce.

Vyplnění nám již známých polí okna argumentů funkce OSPLT stejná data, která byla použita pro funkci PLT. Jen s ohledem na skutečnost, že v budoucnu bude vzorec zkopírován pomocí značky plnění, musíte všechny odkazy v polích učinit absolutními, aby se nezměnily. To vyžaduje umístění znaku dolaru před každou hodnotu svislé a vodorovné souřadnice. Ale je to jednodušší, když jednoduše zvýrazníte souřadnice a stisknete funkční klávesu F4. Znak dolaru bude automaticky umístěn na správná místa. Nezapomeňte také, že roční sazbu je třeba vydělit 12 .

Ještě tu ale máme jeden nový argument, který funkce neměla PLT. Tento argument "Doba". V příslušném poli nastavte odkaz na první buňku sloupce "Doba". Tento prvek listu obsahuje číslo "1", která udává číslo prvního měsíce půjčování. Na rozdíl od předchozích polí však v zadaném poli ponecháme odkaz relativní a neučiníme jej absolutním.
Po zadání všech údajů, o kterých jsme mluvili výše, klikněte na tlačítko "OK".

Poté se v buňce, kterou jsme předtím zvýraznili, zobrazí výše splátky úvěru za první měsíc. Bude to činit 18536,74 rublů.

Poté, jak je uvedeno výše, bychom měli tento vzorec zkopírovat do zbývajících buněk sloupce pomocí úchytu výplně. Chcete-li to provést, umístěte kurzor do pravého dolního rohu buňky, která obsahuje vzorec. Kurzor se převede na křížek, který se nazývá značka výplně. Podržte levé tlačítko myši a přetáhněte jej dolů na konec tabulky.

V důsledku toho jsou vyplněny všechny buňky sloupce. Nyní máme měsíční splátkový kalendář. Jak je uvedeno výše, výše platby podle tohoto článku se zvyšuje s každým novým obdobím.

Nyní musíme provést měsíční výpočet úrokových plateb. Pro tyto účely budeme využívat operátora PRPLT. Vyberte první prázdnou buňku ve sloupci "Platba úroků". Klikněte na tlačítko "Vložit funkci".

V otevřeném okně Funkční průvodci v kategorii "Finanční" jméno zvýrazníme PRPLT. Klikněte na tlačítko "OK".

Otevře se okno s argumenty funkce PRPLT. Jeho syntaxe vypadá takto:
PRPLT(Sazba;Období;Nper;Ps;B)

Jak vidíte, argumenty této funkce jsou naprosto totožné s podobnými prvky operátoru OSPLT. Jednoduše tedy do okna zadáme stejná data, která jsme zadali v předchozím argumentovém okně. Nezapomeňte, že odkaz v poli "Doba" musí být relativní a ve všech ostatních polích musí být souřadnice převedeny do absolutního tvaru. Poté klikněte na tlačítko "OK".

Poté se v příslušné buňce zobrazí výsledek výpočtu výše úrokové platby za půjčku za první měsíc.

Pomocí značky naplnění zkopírujeme vzorec do zbývajících prvků sloupce, čímž získáme měsíční harmonogram plateb úroků za úvěr. Jak vidíme, jak již bylo řečeno dříve, výše tohoto typu plateb se měsíc od měsíce snižuje.

Nyní musíme vypočítat celkovou měsíční splátku. Pro tento výpočet se nemusíte uchýlit k žádnému operátorovi, protože můžete použít jednoduchý aritmetický vzorec. Přidejte obsah buněk prvního měsíce sloupců „Platba úvěrovým orgánem“ A "Platba úroků". Za tímto účelem nainstalujeme značku «=» do první prázdné buňky sloupce "Celková měsíční platba". Poté klikněte na dva výše uvedené prvky a umístěte mezi ně znak «+» . zmáčknout tlačítko Vstupte.

Dále pomocí značky výplně, jako v předchozích případech, vyplníme sloupec daty. Jak vidíte, po celou dobu trvání smlouvy bude výše celkové měsíční splátky včetně platby úvěrového orgánu a splátky úroků 23536,74 rublů. Ve skutečnosti jsme tento ukazatel již dříve počítali pomocí PLT. Ale v tomto případě je to prezentováno jasněji, a to jako výše platby za půjčku a úrok.

Nyní je potřeba doplnit údaje do sloupce, kde se bude měsíčně zobrazovat zůstatek částky úvěru, kterou ještě zbývá doplatit. V první buňce sloupce "Zůstatek k zaplacení" výpočet bude nejjednodušší. Od počáteční výše úvěru, která je uvedena v tabulce s primárními údaji, musíme ve kalkulační tabulce odečíst splátku úvěru za první měsíc. Ale vzhledem k tomu, že jedno z našich čísel už má znaménko «-» , pak by se neměly odečítat, ale sčítat. Uděláme to a stiskneme tlačítko Vstupte.

Výpočet zůstatku k vyplacení po druhém a dalších měsících však bude poněkud složitější. K tomu potřebujeme na začátku půjčování odečíst celkovou výši plateb na úvěrovém orgánu za předchozí období od úvěrového orgánu. Instalace cedulky «=» v druhé buňce sloupce "Zůstatek k zaplacení". Dále označíme odkaz na buňku, která obsahuje počáteční částku půjčky. Udělejte to absolutní tak, že jej vyberete a stisknete klávesu F4. Pak dáme znamení «+» , protože naše druhá hodnota již bude záporná. Poté klikněte na tlačítko "Vložit funkci".

Začíná Průvodce funkcí, ve kterém je třeba přejít do kategorie "Matematický". Tam nápis zvýrazníme "SOUČET" a stiskněte tlačítko "OK".

Otevře se okno s argumenty funkce SOUČET. Zadaný operátor se používá k sumarizaci dat v buňkách, což je to, co musíme udělat ve sloupci „Platba úvěrovým orgánem“. Má následující syntaxi:
SUM(číslo1;číslo2;…)

Argumenty jsou odkazy na buňky obsahující čísla. Umístíme kurzor do pole "Číslo 1". Poté podržte levé tlačítko myši a vyberte první dvě buňky sloupce na listu „Platba úvěrovým orgánem“. Jak vidíte, pole zobrazuje odkaz na rozsah. Skládá se ze dvou částí oddělených dvojtečkou: odkaz na první buňku v rozsahu a odkaz na poslední. Aby bylo možné v budoucnu kopírovat zadaný vzorec pomocí značky plnění, uděláme první část odkazu rozsahu absolutní. Vyberte jej a stiskněte funkční tlačítko F4. Druhou část odkazu necháme relativní. Nyní, když použijete úchyt výplně, první buňka v rozsahu bude zmrazena a poslední buňka se při pohybu dolů roztáhne. To je to, co potřebujeme k dosažení našich cílů. Dále klikněte na tlačítko "OK".

V buňce se tedy zobrazí výsledek zůstatku úvěrového dluhu po druhém měsíci. Nyní, počínaje touto buňkou, zkopírujeme vzorec do prázdných prvků sloupce pomocí značky výplně.

Měsíční výpočet zůstatků úvěrů se provádí za celou dobu úvěru. Jak se očekávalo, na konci období je tato částka nulová.

Nepočítali jsme tedy jen splátku úvěru, ale zorganizovali jakousi úvěrovou kalkulačku. Která bude fungovat podle anuitního schématu. Pokud například v původní tabulce změníme výši úvěru a roční úrokovou sazbu, pak se údaje automaticky přepočítají do výsledné tabulky. Lze jej tedy použít nejen jednou pro konkrétní případ, ale lze jej použít v různých situacích pro výpočet úvěrových možností v rámci anuitního schématu.

Jak vidíte, pomocí programu Excel doma si můžete snadno vypočítat celkovou měsíční splátku úvěru v rámci anuitního schématu pomocí operátora pro tyto účely PLT. Navíc pomocí funkcí OSPLT A PRPLT Můžete vypočítat výši plateb za úvěrový orgán a úroky za určité období. Společným využitím všech těchto funkcí je možné vytvořit výkonnou úvěrovou kalkulačku, kterou lze pro výpočet anuitní splátky použít vícekrát.

Syntaxe a vlastnosti funkce PMT

Syntaxe funkce: rychlost; nper; ps; [bs]; [typ].

Dekódování argumentů:

Sazba je úrok z úvěru.
Nper – celkový počet splátek úvěru.
Ps – současná hodnota, rovná se počtu budoucích plateb (výše úvěru).
Fs je budoucí hodnota půjčky po poslední platbě (pokud je argument vynechán, předpokládá se, že budoucí hodnota je 0).
Typ – volitelný argument, který určuje, zda je platba provedena na konci období (hodnota 0 nebo nepřítomnost) nebo na začátku (hodnota 1).

Vlastnosti provozu PMT:

Do výpočtu pravidelné platby jsou zahrnuty pouze splátky jistiny a úrokové platby. Nezahrnuje daně, poplatky, dodatečné poplatky nebo platby rezerv někdy spojené s půjčkou.
Při zadávání argumentu „Rate“ je nutné vzít v úvahu frekvenci výpočtu úroků. U 6% půjčky je čtvrtletní sazba 6%/4; za měsíční sazbu – 6 %/12.
Argument „Nper“ udává celkový počet splátek úvěru. Pokud osoba platí měsíční splátky tříleté půjčky, pak se pro specifikaci argumentu použije hodnota 3*12.

Příklady funkce PMT v Excelu

Aby funkce fungovala správně, musíte správně zadat počáteční údaje:

Klikněte na tlačítko fx („Vložit funkci“). Otevře se okno Průvodce funkcí. V kategorii „Finanční“ vyberte funkci PMT. Doplňte argumenty:

Poznámka! V poli „Sazba“ je roční úroková hodnota vydělena 12: splátky úvěru se provádějí měsíčně.

Měsíční splátky úvěru v souladu s podmínkami uvedenými jako argumenty činí 1 037,03 RUB.

Chcete-li zjistit celkovou částku, kterou je třeba zaplatit za celé období (jistina plus úrok), vynásobte měsíční splátku půjčky hodnotou „Nper“:

Vynechme z výpočtu měsíčních splátek úvěru platbu uskutečněnou na začátku období:

Chcete-li to provést, musíte zadat hodnotu 1 jako argument „Typ“.

Detail výpočtu provedeme pomocí funkcí OSPT a PRPLT. Pomocí prvního zobrazíme tělo půjčky a pomocí druhého ukážeme úrok.

Pro podrobný výpočet si vytvořte tabulku:

Spočítejme si tělo půjčky pomocí funkce OSPLT. Argumenty se vyplňují analogicky s funkcí PMT:

V poli „Období“ uveďte číslo období, za které se počítá jistina.

Argumenty funkce PRPLT vyplníme stejným způsobem:

Vzorce duplikujeme až do posledního období. Pro výpočet celkové platby sečteme tělo půjčky a úroky.

Vypočítáme zůstatek jistiny dluhu. Dostaneme takovou tabulku:

Celková splátka úvěru je stejná jako měsíční splátka vypočtená pomocí funkce PMT. Toto je konstantní hodnota, protože Uživatel si vzal anuitní úvěr.

Funkci PMT lze tedy použít k výpočtu měsíčních vkladů nebo splátek úvěru za předpokladu, že úroková sazba a částky jsou konstantní.