Výpočet anuitnej platby v programe Microsoft Excel

Pred prijatím úveru by bolo pekné vypočítať všetky platby za to. To ušetrí dlžníka v budúcnosti z rôznych neočakávaných problémov a sklamaní, keď sa ukáže, že preplatok je príliš veľký. Pomocou tohto výpočtu môžete použiť nástroje Excel. Dozvieme sa, ako vypočítať výplaty anuitných úverov v tomto programe.

Výpočet platby

Po prvé, musím povedať, že existujú dva druhy úverových platieb:

• diferencované;
• Anuita.

Pri diferencovanej schéme klient vstúpi do banky rovnaký mesačný podiel platieb na úverový orgán plus úrokové platby. Hodnota úrokových platieb klesá každý mesiac, pretože sa znižuje objem úveru, z ktorého sa počítajú. Zníži sa tak aj celková mesačná platba.

Pri anuitnej schéme sa používa trochu iný prístup. Klient robí každý mesiac rovnakú sumu celkovej platby, ktorá pozostáva z platieb pre úverový orgán a platby úrokov. Spočiatku sú úrokové platby za celú sumu pôžičky, ale s poklesom úrokovej sadzby sa znižuje aj úroková sadzba. Celková výška platby však zostáva nezmenená vzhľadom na mesačné zvýšenie sumy platieb na úverový orgán. Časom sa teda podiel na celkovej mesačnej platbe znižuje a zvyšuje sa špecifická váha platby pre organizáciu. Súčasne sa celková mesačná platba počas trvania úveru nezmení.

Len na výpočet anuitnej platby sa zastavíme. Okrem toho je to skutočné, pretože v súčasnosti väčšina bánk používa tento systém. To je tiež výhodné pre zákazníkov, pretože v tomto prípade sa celková suma platby nezmení, zostáva pevná. Zákazníci vždy vedia, koľko zaplatí.

Krok 1: Výpočet mesačnej splátky

Pri výpočte mesačného poplatku pri použití schémy anuity v programe Excel existuje špeciálna funkcia - PLT . Patrí do kategórie finančných operátorov. Vzorec pre túto funkciu je nasledujúci:

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

Ako môžete vidieť, táto funkcia má dosť argumentov. Je pravda, že posledné dva z nich nie sú povinné.

Argument "Bet" označuje úrokovú sadzbu za určité obdobie. Ak sa napríklad použije ročná sadzba, ale pôžička sa vypláca mesačne, ročná sadzba by sa mala rozdeliť na 12 a výsledok sa použije ako argument. Ak sa používa štvrťročná platobná metóda, potom by sa v tomto prípade mala ročná sadzba rozdeliť o 4 atď.

"Kper" sa vzťahuje na celkový počet splátkových období. To znamená, že ak je pôžička čerpaná na jeden rok s mesačnou platbou, potom sa počet období považuje za 12 , ak je na dva roky, potom je počet období 24 . Ak je pôžička čerpaná na dva roky so štvrťročnou platbou, potom je počet období 8 .

"Ps" označuje súčasnú hodnotu. Jednoducho povedané, ide o celkovú sumu úveru na začiatku úveru, teda sumu, ktorú si požičiavate bez zohľadnenia úrokov a ďalších dodatočných platieb.

"Bs" je budúca hodnota. Táto suma, ktorá bude súčasťou úveru v čase dokončenia úverovej zmluvy. Vo väčšine prípadov sa tento argument rovná "0" , keďže dlžník na konci úverového obdobia musí plne urovnať s veriteľom. Zadaný argument je nepovinný. Preto ak sa vynechá, považuje sa za nulovú.

Argument "Typ" špecifikuje výpočtový čas: na konci alebo na začiatku obdobia. V prvom prípade je hodnota "0" a druhá - "1" . Väčšina bankových inštitúcií používa túto možnosť s platbou na konci obdobia. Tento argument je tiež voliteľný a ak sa vynechá, predpokladá sa, že je nulový.

Teraz je čas prejsť na konkrétny príklad výpočtu mesačného poplatku pomocou funkcie PLT. Na výpočet používame tabuľku s počiatočnými údajmi, v ktorých je uvedená úroková sadzba z úveru ( 12% ), hodnota úveru ( 500 000 rubľov ) a obdobie úveru ( 24 mesiacov ). V tomto prípade sa platba uskutočňuje mesačne na konci každého obdobia.

1. Vyberte prvok na hárku, v ktorom sa zobrazí výsledok výpočtu, a kliknite na ikonu "Vložiť funkciu" umiestnenú v blízkosti riadku vzorca.
2. Otvorí sa okno sprievodcu funkcií . V kategórii "Finančné" vyberieme názov "PLT" a klikneme na tlačidlo "OK" .
3. Potom sa otvorí okno argumentov operátora PLT .

   V poli "Sadzba" by ste mali zadať sumu úroku za obdobie. To sa dá robiť ručne, stačí len percento, ale máme v zozname v samostatnej bunke na hárku, takže jej dajme odkaz. Nastavte kurzor do poľa a potom kliknite na príslušnú bunku. Ale ako si pamätáme, v našej tabuľke je stanovená ročná úroková sadzba a platobné obdobie sa rovná mesiacu. Preto rozdeľujeme ročnú sadzbu, alebo skôr odkaz na bunku, v ktorej je obsiahnutá, číslom 12 , čo zodpovedá počtu mesiacov v roku. Rozdelenie sa vykonáva priamo v poli okna s argumentmi.

   V poli "Kper" sa nastaví kreditné obdobie. Máme to 24 mesiacov. Číslo 24 môžete zadať ručne, ale my, ako v predchádzajúcom prípade, označujeme odkaz na umiestnenie tohto indikátora v zdrojovej tabuľke.

   V poli "Ps" je uvedená pôvodná hodnota úveru. To sa rovná 500 000 rubľov . Rovnako ako v predchádzajúcich prípadoch uvádzame odkaz na element listu, ktorý obsahuje tento indikátor.

   Pole "Bs" označuje výšku pôžičky po úplnej platbe. Ako si pamätáte, táto hodnota je takmer vždy nula. V tomto poli sme nastavili číslo "0" . Hoci tento argument možno úplne vynechať.

   V poli "Typ" uvádzame na začiatku alebo na konci mesiaca platba. My, rovnako ako vo väčšine prípadov, vyrábame na konci mesiaca. Preto sme nastavili číslo "0" . Rovnako ako v prípade predchádzajúceho argumentu, nemôžete v tomto poli zadať nič, potom program predpokladá, že má hodnotu nula.

   Po zadaní všetkých údajov kliknite na tlačidlo "OK" .

4. Potom sa výsledok výpočtu zobrazí v bunke, ktorú sme vybrali v prvom odseku tejto príručky. Ako vidíte, hodnota mesačnej celkovej platby za úver je 23.536,74 rubľov . Neodstraňujte znamienko "-" predtým, než túto sumu. A Axel poukazuje na to, že ide o peňažný tok, teda o stratu.
5. Pri výpočte celkovej výšky platby za celé obdobie pôžičky, berúc do úvahy splatenie úverového orgánu a mesačný úrok, postačuje vynásobiť hodnotu mesačnej platby ( 23536,74 rubľov ) počtom mesiacov ( 24 mesiacov ). Ako vidíte, celková suma platieb za celé obdobie pôžičky v našom prípade bola 564881,67 rubľov .
6. Teraz môžete vypočítať výšku preplatku na úver. K tomu je potrebné odobrať z celkovej sumy platieb z úveru, vrátane úrokov a pôžičku orgán, počiatočnú čiastku požičal. Pamätáme však, že prvá z týchto hodnôt už má znak "-" . Preto sa v našom konkrétnom prípade ukázalo, že je potrebné zložiť. Ako vidíte, celková suma preplatku za úver za celé obdobie bola 64881,67 rubľov .

Lekcia: Sprievodca funkciami v programe Excel

Krok 2: podrobnosti o platbách

A teraz s pomocou iných operátorov spoločnosti Axel vykonáme mesačné informácie o platbách, aby sme videli, koľko v konkrétnom mesiaci zaplatíme úverovému orgánu a koľko je záujem. Na tento účel nakreslíme tabuľku v programe Excel, ktorú vyplníme údajmi. Riadky tejto tabuľky zodpovedajú príslušnému obdobiu, teda mesiacu. Vzhľadom na to, že kreditné obdobie pre nás je 24 mesiacov, potom bude vhodný aj počet riadkov. V stĺpcoch je uvedená platba úverového orgánu, úhrada úrokov, celková mesačná platba, ktorá je súčtom predchádzajúcich dvoch stĺpcov a zvyšná suma, ktorá sa má zaplatiť.

1. Na určenie výšky platby pre úverový orgán využívame funkciu OSPLT , ktorá je práve navrhnutá na tieto účely. Nastavte kurzor v bunke nachádzajúcej sa v riadku "1" av stĺpci "Platba v pôžičke . " Kliknite na tlačidlo "Vložiť funkciu" .
2. Prejdite na sprievodcu funkciami . V kategórii "Finančné" označujeme názov "OSPLT" a klikneme na tlačidlo "OK" .
3. Zobrazí sa okno argumentov operátora OSPLT. Má nasledujúcu syntax:

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

   Ako vidíte, argumenty tejto funkcie sa takmer úplne zhodujú s argumentmi operátora PLT , namiesto voliteľného argumentu "Type" bol pridaný iba povinný argument "Period" . Označuje číslo výplatnej lehoty a v našom konkrétnom prípade číslo mesiaca.

   Vyplníme polia okna argumentov funkcie OSPLT, ktoré sú už známe tým istými údajmi, ktoré boli použité pre funkciu PLT . Len pri zohľadnení skutočnosti, že v budúcnosti bude vzorec kopírovaný pomocou značky na vyplnenie, je potrebné, aby boli všetky odkazy v poliach absolútne, aby sa nemenili. Na to je potrebné umiestniť znak dolára pred každou hodnotou súradníc pozdĺž zvislých a vodorovných čiar. Ale je to jednoduchšie, stačí vybrať súradnice a stlačiť funkčné tlačidlo F4 . Znak dolára bude automaticky umiestnený na správnych miestach. Tiež nezabudnite, že ročná sadzba by mala byť rozdelená na 12 .

4. Ale stále máme ďalší nový argument, ktorý funkcia PLT nemala. Tento argument je "Obdobie" . V príslušnom poli nastavte odkaz na prvú bunku v stĺpci "Obdobie" . Tento prvok listu obsahuje číslo "1" , ktoré označuje číslo prvého mesiaca pripísania. Na rozdiel od predchádzajúcich polí však v zadanom poli ponecháme relatívnu referenciu a nedopustíme ju absolútne.

   Po zadaní všetkých údajov, o ktorých sme hovorili vyššie, kliknite na tlačidlo "OK" .

5. Potom sa v bunke, ktorú sme predtým pridelili, zobrazí čiastka platby pre úverový orgán za prvý mesiac. Bude to 18536,74 rubľov .
6. Potom, ako sme už uviedli vyššie, mali by sme tento vzorec skopírovať do ostatných buniek v stĺpci pomocou popisu výplne. Ak to chcete urobiť, nastavte kurzor do pravého dolného rohu článku obsahujúceho vzorec. Kurzor sa premení na kríž, ktorý sa nazýva značka naplnenia. Uchopte ľavé tlačidlo myši a presuňte ho na koniec tabuľky.
7. V dôsledku toho sa všetky bunky v stĺpci vyplnia. Teraz máme splátkový kalendár mesačne. Ako bolo uvedené vyššie, výška platby za tento článok sa zvyšuje.
8. Teraz potrebujeme vykonať mesačný výpočet úrokových platieb. Pre tieto účely použijeme operátor PRPLT . Vyberte prvú prázdnu bunku v stĺpci "Úrokové platby" . Kliknite na tlačidlo "Vložiť funkciu" .
9. V okne Sprievodcov funkcií v kategórii "Finančné" vyberieme názov PRPLT . Kliknite na tlačidlo "OK" .
10. Zobrazí sa okno s argumentom funkcie PRPLT . Jeho syntax je nasledujúci:

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

    Ako vidíte, argumenty tejto funkcie sú absolútne identické s podobnými prvkami operátora OSPLT . Preto sme do okna vložili rovnaké údaje, ktoré sme zadali do predchádzajúceho okna argumentov. Nesmieme však zabúdať, že odkaz v poli "Obdobie" by mal byť relatívny a vo všetkých ostatných poliach by sa súradnice mali dostať do absolútnej podoby. Potom kliknite na tlačidlo "OK" .

11. Potom sa v príslušnej bunke zobrazí výsledok výpočtu výšky úrokovej platby za úver za prvý mesiac.
12. Aplikujeme značku plnenia, skopírujeme vzorec na zvyšné prvky stĺpca, čím získame mesačný plán platieb úrokov z úveru. Ako vidíme, ako sa už povedalo, hodnota tohto typu platieb klesá z mesiaca na mesiac.
13. Teraz musíme vypočítať celkovú mesačnú platbu. Pre tento výpočet by sa nemal uchýliť k žiadnemu operátorovi, keďže sa dá použiť jednoduchý aritmetický vzorec. Pridávame obsah buniek prvého mesiaca v stĺpcoch "Platba úverovým orgánom" a "Platba úrokov" . Za týmto účelom nastavte znak "=" na prvú prázdnu bunku stĺpca "Celková mesačná platba" . Potom kliknite na dva vyššie uvedené prvky a nastavte medzi nimi znamienko "+" . Stlačte kláves Enter .
14. Ďalej pomocou stĺpca na vyplnenie, ako v predchádzajúcich prípadoch, vyplníme stĺpec s údajmi. Ako vidíme, po celú dobu trvania zmluvy bude výška celkovej mesačnej platby vrátane platby na úverový orgán a úhrada úrokov 23536,74 rubľov . V skutočnosti sme tento počet už spočítali pomocou PLT . Ale v tomto prípade je to jasnejšie, presne ako výška platby za úverový orgán a úroky.
15. Teraz je potrebné pridať údaje do stĺpca, kde sa zostatok sumy úveru bude zobrazovať mesačne, čo je ešte potrebné zaplatiť. V prvej bunke v stĺpci Zostatok na výplatu bude výpočet najjednoduchší. Musíme odstrániť pôvodnú sumu úveru, ktorá je uvedená v tabuľke s primárnymi údajmi, platba za úverový orgán za prvý mesiac v tabuľke vysporiadania. Ale vzhľadom na to, že jedno z čísel, ktoré už máme so znamením "-" , nemali by byť odňaté, ale zložené. Vykonajte to a stlačte tlačidlo Enter .
16. Výpočet zostatku na platbu po druhom a nasledujúcich mesiacoch bude o niečo komplikovanejší. Aby sme to urobili, musíme od začiatku úveru odobrať z tela úveru celkovú sumu platieb pre úverový orgán za predchádzajúce obdobie. Nastavte znak "=" v druhej bunke stĺpca "Zostatok na výplatu" . Ďalej uvádzame odkaz na bunku, ktorá obsahuje pôvodné množstvo úveru. Vykonáme to absolútne výberom a stlačením klávesu F4 . Potom zadáme znak "+" , pretože druhá hodnota bude pre nás negatívna. Potom kliknite na tlačidlo "Vložiť funkciu" .
17. Spustí sa Sprievodca funkciami , v ktorom je potrebné prejsť do kategórie "Matematické" . Tu vyberieme nápis "SUM" a klikneme na tlačidlo "OK" .
18. Otvorí sa okno s argumentom funkcie SUM . Určený operátor slúži na zhrnutie údajov v bunkách, ktoré musíme vykonať v stĺpci "Platba orgánom úveru". Má nasledujúcu syntax:

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

    Argumenty sú odkazy na bunky obsahujúce čísla. Kurzor sme nastavili v poli "Číslo1" . Potom stlačte ľavé tlačidlo myši a zvoľte prvé dve bunky v stĺpci "Platba kreditným orgánom" . V poli, ako vidíte, bol zobrazený odkaz na rozsah. Skladá sa z dvoch častí oddelených dvojbodkou: odkazy na prvú bunku rozsahu a na poslednú. Aby sme mohli v budúcnosti skopírovať označený vzorec pomocou značky naplnenia, urobíme prvú časť odkazu na absolútny rozsah. Vyberte ho a kliknite na funkčné tlačidlo F7 . Druhá časť odkazu zostáva relatívna. Teraz, keď použijete značku naplnenia, prvá bunka rozsahu bude fixovaná a posledná bunka bude natiahnutá, keď sa posunie nadol. To je to, čo potrebujeme na splnenie našich cieľov. Potom kliknite na tlačidlo "OK" .

19. Takže výsledok zostatku úverového dlhu po druhom mesiaci je zobrazený v bunke. Teraz od tejto bunky skopírujeme vzorec na vyprázdnenie prvkov stĺpca pomocou rukoväte plnenia.
20. Mesačný výpočet zostatkov na úhradu z úveru sa uskutočňuje počas celého úverového obdobia. Ako sa očakávalo, na konci obdobia je táto suma nula.

Preto sme nielen vypočítali platbu za úver, ale zorganizovali sme druh úverovej kalkulačky. Kto bude konať na anuitnej schéme. Ak v pôvodnej tabuľke napríklad zmeníme výšku úveru a ročnú úrokovú sadzbu, potom v záverečnej tabuľke dôjde k automatickému prepočtu údajov. Preto môže byť použitý nielen v konkrétnom prípade, ale môže byť použitý v rôznych situáciách na výpočet opcií na úver v anuitnom systéme.

Lekcia: Finančné funkcie v programe Excel

Ako môžete vidieť v domácej aplikácii programu Excel, môžete ľahko vypočítať celkovú mesačnú pôžičku na anuitnom systéme, pričom na tento účel použijete operátora PLT . Okrem toho pomocou funkcií OPST a MTEF je možné vypočítať výšku platieb pre úverový orgán a úroky za stanovené obdobie. Ak použijete všetky tieto batožiny funkcií dohromady, je možné vytvoriť silnú úverovú kalkulačku, ktorú môžete použiť na výpočet anuitnej platby.

• ← Nastavte deň v týždni podľa dátumu v programe Microsoft Excel
• Aplikácia funkcie BPR v programe Microsoft Excel →