Vzorec na výpočet anuitnej platby v Exceli

Výpočet platieb pôžičky je jednoduchší a rýchlejší pomocou programu Microsoft Office Excel. Oveľa viac času sa strávi manuálnym výpočtom. Tento článok sa zameria na anuitné platby, vlastnosti ich výpočtu, výhody a nevýhody.

Čo je to anuitná splátka

Spôsob mesačného splácania úveru, pri ktorom sa počas celej doby úveru nemení vložená suma. Tie. v určité dátumy každého mesiaca osoba vloží určitú sumu peňazí až do úplného splatenia úveru.

Navyše, úroky z úveru sú už zahrnuté v celkovej sume zaplatenej banke.

Klasifikačná anuita

Anuitné platby možno rozdeliť do nasledujúcich typov:

1. Opravené. Platby, ktoré sa nemenia, majú pevnú sadzbu bez ohľadu na vonkajšie podmienky.
2. mena. Možnosť zmeniť výšku platby v prípade poklesu alebo zvýšenia výmenného kurzu.
3. indexované. Platby v závislosti od úrovne, indikátora inflácie. Počas výpožičnej doby sa ich veľkosť často mení.
4. Premenné. Anuita, ktorá sa môže meniť v závislosti od stavu finančného systému, nástrojov.

Venujte pozornosť! Pevné platby sú vhodnejšie pre všetkých dlžníkov, pretože majú malé riziko.

Výhody a nevýhody anuitných platieb

Pre lepšie pochopenie témy je potrebné naštudovať si kľúčové vlastnosti tohto typu splácania úveru. Má nasledujúce výhody:

• Stanovenie konkrétnej výšky platby a dátumu jej úhrady.
• Vysoká dostupnosť pre dlžníkov. O rentu môže požiadať takmer každý bez ohľadu na finančnú situáciu.
• Možnosť zníženia výšky mesačnej splátky pri zvýšení inflácie.

Nie bez nevýhod:

• Vysoká miera. Dlžník preplatí väčšiu sumu peňazí v porovnaní s rozdielovou splátkou.
• Problémy vyplývajúce z túžby splatiť dlh v predstihu.
• Žiadne prepočty pre predčasné platby.

Aká je splátka úveru?

Anuitná platba má tieto zložky:

• Úroky, ktoré platí osoba pri splácaní úveru.
• Časť istiny.

Výsledkom je, že celková výška úrokov takmer vždy prevyšuje sumu, ktorou dlžník prispel na zníženie dlhu.

Základný vzorec anuitnej platby v Exceli

Ako už bolo spomenuté vyššie, v programe Microsoft Office Excel môžete pracovať s rôznymi typmi platieb za úvery a preddavky. Výnimkou nie je ani anuita. Vo všeobecnosti je vzorec, pomocou ktorého môžete rýchlo vypočítať anuitné príspevky, nasledovný:  

Dôležité! Nie je možné otvárať zátvorky v menovateli tohto výrazu, aby sa to zjednodušilo.

Hlavné hodnoty vzorca sú dešifrované takto:

• AP – anuitná platba (názov je skrátený).
• O – výška hlavného dlhu dlžníka.
• PS – úroková sadzba ponúkaná na mesačnej báze konkrétnou bankou.
• C je počet mesiacov, počas ktorých úver trvá.

Na asimiláciu informácií stačí uviesť niekoľko príkladov použitia tohto vzorca. O nich sa bude ďalej diskutovať.

Príklady použitia funkcie PMT v Exceli

Dáme jednoduchú podmienku problému. Je potrebné vypočítať mesačnú splátku úveru, ak banka predloží úrok 23% a celková suma je 25000 3 rubľov. Pôžička bude trvať XNUMX roky. Problém sa rieši podľa algoritmu:

1. Vytvorte všeobecnú tabuľku v Exceli na základe zdrojových údajov.

2. Aktivujte funkciu PMT a zadajte jej argumenty do príslušného poľa.
3. Do poľa „Bet“ zadajte vzorec „B3/B5“. Toto bude úroková sadzba úveru.
4. Do riadku „Nper“ napíšte hodnotu v tvare „B4*B5“. Bude to celkový počet splátok za celú dobu trvania úveru.
5. Vyplňte pole „PS“. Tu musíte uviesť počiatočnú sumu prevzatú z banky a napísať hodnotu „B2“.

6. Uistite sa, že po kliknutí na „OK“ v zdrojovej tabuľke bola vypočítaná hodnota „Mesačná platba“.

Ďalšie informácie! Záporné číslo znamená, že dlžník míňa peniaze.

Príklad výpočtu výšky preplatku na úvere v Exceli

V tomto probléme musíte vypočítať sumu, ktorú preplatí osoba, ktorá si vzala pôžičku 50000 27 rubľov s úrokovou sadzbou 5% na 12 rokov. Celkovo dlžník zaplatí XNUMX platieb ročne. Riešenie:

1. Zostavte pôvodnú tabuľku údajov.

2. Od celkovej sumy platieb odpočítajte počiatočnú sumu podľa vzorca «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Musí sa vložiť do riadku vzorcov v hornej časti hlavnej ponuky programu.
3. V dôsledku toho sa výška preplatkov objaví v poslednom riadku vytvoreného taniera. Dlžník navyše preplatí 41606 rubľov.

Vzorec na výpočet optimálnej mesačnej splátky úveru v Exceli

Úloha s nasledujúcou podmienkou: klient zaregistroval bankový účet na 200000 4 rubľov s možnosťou mesačného dopĺňania. Je potrebné vypočítať výšku platby, ktorú musí osoba vykonať každý mesiac, aby po 2000000 rokoch mal na účte 11 XNUMX XNUMX rubľov. Sadzba je XNUMX %. Riešenie:

1. Vytvorte tabuľku založenú na pôvodných údajoch.

2. Zadajte vzorec do vstupného riadku programu Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» a stlačte „Enter“ na klávesnici. Písmená sa budú líšiť v závislosti od buniek, v ktorých je tabuľka umiestnená.
3. Skontrolujte, či sa výška príspevku automaticky vypočíta v poslednom riadku tabuľky.

Venujte pozornosť! Na to, aby klient za 4 rokov nahromadil 2000000 ruble so sadzbou 11%, musí každý mesiac vložiť 28188 rubľov. Mínus v sume naznačuje, že klient utrpí straty tým, že dá peniaze banke.

Funkcie používania funkcie PMT v Exceli

Vo všeobecnosti je tento vzorec napísaný takto: =PMT(miera; nper; ps; [bs]; [typ]). Funkcia má nasledujúce vlastnosti:

1. Pri výpočte mesačných príspevkov sa berie do úvahy iba ročná sadzba.
2. Pri špecifikácii úrokovej sadzby je dôležité prepočítať na základe počtu splátok za rok.
3. Namiesto argumentu „Nper“ vo vzorci je uvedené konkrétne číslo. Toto je platobné obdobie.

Výpočet platby

Vo všeobecnosti sa anuitná splátka počíta v dvoch etapách. Na pochopenie témy je potrebné zvážiť každú z fáz samostatne. O tom sa bude diskutovať ďalej.

1. fáza: výpočet mesačnej splátky

Ak chcete v Exceli vypočítať sumu, ktorú musíte každý mesiac zaplatiť za pôžičku s pevnou sadzbou, musíte:

1. Zostavte zdrojovú tabuľku a vyberte bunku, v ktorej chcete zobraziť výsledok, a kliknite na tlačidlo „Vložiť funkciu“ v hornej časti.

2. V zozname funkcií vyberte „PLT“ a kliknite na „OK“.

3. V ďalšom okne nastavte argumenty pre funkciu s uvedením zodpovedajúcich riadkov v zostavenej tabuľke. Na konci každého riadku musíte kliknúť na ikonu a potom vybrať požadovanú bunku v poli.

4. Po vyplnení všetkých argumentov sa do riadku na zadanie hodnôt zapíše príslušný vzorec a v poli tabuľky „Mesačná platba“ sa zobrazí výsledok výpočtu so znamienkom mínus.

Dôležité! Po vyčíslení splátky bude možné vypočítať sumu, ktorú dlžník preplatí za celú dobu úveru.

2. fáza: platobné údaje

Výšku preplatku je možné vypočítať mesačne. Vďaka tomu človek pochopí, koľko peňazí mesačne minie na pôžičku. Podrobný výpočet sa vykonáva takto:

1. Vytvorte tabuľku na 24 mesiacov.

2. Umiestnite kurzor do prvej bunky tabuľky a vložte funkciu „OSPLT“.

3. Rovnakým spôsobom vyplňte argumenty funkcie.

4. Pri vypĺňaní poľa „Obdobie“ sa musíte odvolávať na prvý mesiac v tabuľke s uvedením bunky 1.

5. Skontrolujte, či je vyplnená prvá bunka v stĺpci „Platba telom pôžičky“.
6. Ak chcete vyplniť všetky riadky prvého stĺpca, musíte bunku natiahnuť na koniec tabuľky

7. Vyberte funkciu „PRPLT“ na vyplnenie druhého stĺpca tabuľky.
8. Vyplňte všetky argumenty v otvorenom okne podľa obrázka nižšie.

9. Vypočítajte celkovú mesačnú platbu sčítaním hodnôt v predchádzajúcich dvoch stĺpcoch.

10. Ak chcete vypočítať „splatný zostatok“, musíte k platbe na telo pôžičky pridať úrokovú sadzbu a natiahnuť ju na koniec dosky, aby ste vyplnili všetky mesiace pôžičky.

Ďalšie informácie! Pri výpočte zvyšku musia byť znaky dolára zavesené na vzorec tak, aby sa pri natiahnutí nevysunul.

Výpočet anuitných splátok úveru v Exceli

Funkcia PMT je zodpovedná za výpočet anuity v Exceli. Princíp výpočtu vo všeobecnosti spočíva v vykonaní nasledujúcich krokov:

1. Zostavte pôvodnú tabuľku údajov.
2. Zostavte si splátkový kalendár na každý mesiac.
3. Vyberte prvú bunku v stĺpci „Platby pôžičky“ a zadajte vzorec na výpočet „PLT ($ B3/12; $ B $ 4; $ B $ 2)“.
4. Výsledná hodnota je natiahnutá pre všetky stĺpce dosky.

Výpočet splátky istiny dlhu v MS Excel

Anuitné platby sa musia vyplácať mesačne v pevných sumách. A úroková sadzba sa nemení.

Výpočet zostatku istiny (s BS=0, typ=0)

Predpokladajme, že pôžička vo výške 100000 10 rubľov sa vezme na 9 rokov s 1%. Výšku istiny dlhu je potrebné vypočítať v 3. mesiaci XNUMX. roku. Riešenie:

1. Zostavte si údajový list a vypočítajte mesačnú platbu pomocou vyššie uvedeného vzorca PV.
2. Pomocou vzorca vypočítajte podiel platby potrebnej na splatenie časti dlhu «=-PMT-(PS-PS1)*položka=-PMT-(PS +PMT+PS*položka)».
3. Vypočítajte výšku istiny za 120 období pomocou dobre známeho vzorca.
4. Pomocou operátora HPMT nájdite výšku zaplatených úrokov za 25. mesiac.
5. Skontrolujte výsledok.

Výpočet sumy istiny, ktorá bola zaplatená medzi dvoma obdobiami

Tento výpočet sa najlepšie vykoná jednoduchým spôsobom. Na výpočet sumy v intervale pre dve obdobia musíte použiť nasledujúce vzorce:

• =«-BS(položka; con_period; plt; [ps]; [typ]) /(1+typ *položka)».
• = „+ BS(sadzba; počiatočné_obdobie-1; plt; [ps]; [typ]) /IF(počiatočné_obdobie =1; 1; 1+typ *sadzba)“.

Venujte pozornosť! Písmená v zátvorkách sú nahradené konkrétnymi hodnotami.

Predčasné splatenie so zníženou lehotou alebo splátkou

Ak potrebujete skrátiť dobu pôžičky, budete musieť vykonať dodatočné výpočty pomocou operátora IF. Takže bude možné kontrolovať nulový zostatok, ktorý by nemal byť dosiahnutý pred koncom platobného obdobia.

Pre zníženie platieb je potrebné prepočítať príspevok za každý predchádzajúci mesiac.

Úverová kalkulačka s nepravidelnými splátkami

Existuje niekoľko možností anuity, kde môže dlžník vložiť variabilné sumy v ktorýkoľvek deň v mesiaci. V takejto situácii sa zostatok dlhu a úrokov počíta za každý deň. Zároveň v Exceli potrebujete:

1. Zadajte dni v mesiaci, za ktoré sa uskutočňujú platby, a uveďte ich počet.
2. Skontrolujte záporné a kladné sumy. Uprednostňujú sa negatívne.
3. Počítajte dni medzi dvoma dátumami, kedy boli vložené peniaze.

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

V Exceli môžete rýchlo vypočítať výšku pravidelných platieb za predpokladu, že už bola nahromadená pevná suma. Táto akcia sa vykoná pomocou funkcie PMT po zostavení úvodnej tabuľky.

záver

Výpočet anuitných platieb je teda v Exceli jednoduchší, rýchlejší a efektívnejší. Za ich výpočet je zodpovedný operátor PMT. Podrobnejšie príklady nájdete vyššie.