Формула для разліку аннуитетного плацяжу ў excel

Плацяжы па крэдыце лягчэй і хутчэй разлічваць з дапамогай Microsoft Office Excel. Значна больш часу сыходзіць на ручной разлік. У дадзеным артыкуле гаворка пойдзе аб аннуитетных плацяжах, асаблівасцях іх разліку, перавагах і недахопах.

Што такое рэнтны плацёж

Спосаб штомесячнага пагашэння крэдыту, пры якім сума ўкладу не мяняецца на працягу ўсяго перыяду крэдыту. Тыя. у пэўныя дні кожнага месяца чалавек уносіць пэўную суму грошай да поўнага пагашэння крэдыту.

Больш за тое, працэнты па крэдыце ўжо ўключаны ў агульную суму, выплачаную банку.

Класіфікацыя ануітэт

Аннуитетные выплаты можна падзяліць на наступныя віды:

1. Выпраўлена. Плацяжы, якія не змяняюцца, маюць фіксаваную стаўку незалежна ад знешніх умоў.
2. Валюта. Магчымасць змены сумы плацяжу ў выпадку падзення або росту курсу.
3. індэксуецца. Выплаты ў залежнасці ад узроўню, паказчыка інфляцыі. У перыяд крэдытавання іх памер часта мяняецца.
4. Пераменныя. Ануітэт, які можа змяняцца ў залежнасці ад стану фінансавай сістэмы, інструментаў.

Звяртаць увагу! Фіксаваныя выплаты пераважней для ўсіх пазычальнікаў, таму што маюць невялікі рызыка.

Перавагі і недахопы ануітэтных выплат

Каб лепш разабрацца ў тэме, неабходна вывучыць асноўныя асаблівасці гэтага віду плацяжоў па крэдыце. Ён мае наступныя перавагі:

• Ўстанаўленне канкрэтнай сумы плацяжу і даты яго выплаты.
• Высокая даступнасць для пазычальнікаў. Падаць заяўку на ануітэт можа практычна кожны, незалежна ад свайго матэрыяльнага становішча.
• Магчымасць памяншэння памеру штомесячнага плацяжу пры росце інфляцыі.

Не без недахопаў:

• Высокі паказчык. Пазычальнік пераплаціць вялікую суму грошай у параўнанні з дыферэнцыяваным плацяжом.
• Праблемы, якія ўзнікаюць з-за жадання датэрмінова пагасіць доўг.
• Без пераразлікаў пры датэрміновых плацяжах.

Што такое выплата па крэдыце?

Аннуитетный плацёж мае наступныя складнікі:

• Працэнты, якія выплачвае чалавек пры пагашэнні крэдыту.
• Частка асноўнай сумы.

У выніку агульная сума працэнтаў практычна заўсёды перавышае суму, унесеную пазычальнікам для памяншэння запазычанасці.

Асноўная формула ануітэтных выплат у Excel

Як было сказана вышэй, у Microsoft Office Excel можна працаваць з рознымі відамі плацяжоў па крэдытах і авансах. Ануітэт - не выключэнне. У цэлым формула, з дапамогай якой можна хутка разлічыць рэнтныя ўзносы, выглядае наступным чынам:  

Важна! У назоўніку гэтага выразу нельга раскрываць дужкі, каб спрасціць яго.

Асноўныя значэння формулы расшыфроўваюцца наступным чынам:

• АП – рэнтны плацёж (назва скарочаная).
• О – памер асноўнага доўгу пазычальніка.
• PS - працэнтная стаўка, якая штомесяц вылучаецца канкрэтным банкам.
• C - колькасць месяцаў, на якія доўжыцца пазыка.

Для засваення інфармацыі дастаткова прывесці некалькі прыкладаў выкарыстання гэтай формулы. Пра іх і пойдзе гаворка далей.

Прыклады выкарыстання функцыі PMT ў Excel

Прывядзем простую ўмову задачы. Разлічыць штомесячны плацёж па крэдыце неабходна, калі банк выстаўляе працэнты ў памеры 23%, а агульная сума складае 25000 рублёў. Тэрмін крэдытавання - 3 гады. Задача рашаецца па алгарытме:

1. Складзіце агульную табліцу ў Excel на аснове зыходных даных.

2. Актывуйце функцыю PMT і ўвядзіце яе аргументы ў адпаведнае поле.
3. У полі «Стаўка» увядзіце формулу «B3/B5». Гэта і будзе працэнтная стаўка па крэдыце.
4. У радку «Nпер» запішыце значэнне ў выглядзе «B4*B5». Гэта будзе агульная колькасць плацяжоў за ўвесь тэрмін крэдыту.
5. Запоўніце поле «PS». Тут трэба паказаць першапачатковую суму, узятую з банка, напісаўшы значэнне «В2».

6. Пераканайцеся, што пасля націску «ОК» у зыходнай табліцы было разлічана значэнне «Штомесячны плацёж».

Дадатковая інфармацыя! Адмоўны лік паказвае, што пазычальнік марнуе грошы.

Прыклад разліку сумы пераплаты па крэдыце ў Excel

У гэтай задачы вам трэба палічыць суму, якую пераплаціць чалавек, які ўзяў крэдыт у 50000 рублёў пад стаўку 27% на 5 гадоў. Усяго за год пазычальнік робіць 12 плацяжоў. рашэнне:

1. Складзіце зыходную табліцу даных.

2. Ад агульнай сумы плацяжоў адняць першапачатковую суму па формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Яе неабходна ўставіць у радок формул у верхняй частцы галоўнага меню праграмы.
3. У выніку сума пераплаты з'явіцца ў апошнім радку створанай таблічкі. Зверху пазычальнік пераплаціць 41606 рублёў.

Формула разліку аптымальнага штомесячнага плацяжу па крэдыце ў Excel

Заданне з умовай: у кліента зарэгістраваны рахунак у банку на 200000 4 рублёў з магчымасцю штомесячнага папаўнення. Неабходна разлічыць суму плацяжу, якую чалавек павінен уносіць кожны месяц, каб праз 2000000 гады на яго рахунку было 11 рублёў. Стаўка XNUMX%. рашэнне:

1. Стварыце электронную табліцу на аснове зыходных даных.

2. Увядзіце формулу ў радок уводу Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» і націсніце «Enter» з клавіятуры. Літары будуць адрознівацца ў залежнасці ад вочак, у якіх размешчана табліца.
3. Праверце, ці аўтаматычна разлічваецца сума ўнёску ў апошнім радку табліцы.

Звяртаць увагу! Такім чынам, каб кліент за 4 гадоў назапасіў 2000000 рублі па стаўцы 11%, яму неабходна штомесяц уносіць 28188 рублёў. Мінус у суме сведчыць аб тым, што кліент нясе страты, аддаючы грошы банку.

Асаблівасці выкарыстання функцыі PMT у Excel

У агульным выглядзе гэтая формула запісваецца так: =PMT(стаўка; nper; ps; [bs]; [тып]). Функцыя мае наступныя магчымасці:

1. Пры разліку штомесячных узносаў ўлічваецца толькі гадавая стаўка.
2. Пры ўказанні працэнтнай стаўкі важна зрабіць пераразлік зыходзячы з колькасці плацяжоў у год.
3. Замест аргумента «Nпер» у формуле паказваецца канкрэтная лічба. Гэта перыяд аплаты.

Разлік аплаты

У цэлым жа выплата рэнты разлічваецца ў два этапы. Для разумення тэмы неабходна разгледзець кожны з этапаў асобна. Пра гэта далей пойдзе гаворка.

1 этап: разлік штомесячнага ўзносу

Каб разлічыць у Excel суму, якую неабходна штомесяц плаціць па крэдыце з фіксаванай стаўкай, неабходна:

1. Складзіце зыходную табліцу і абярыце ячэйку, у якой вы хочаце адлюстраваць вынік, і націсніце кнопку «Уставіць функцыю» зверху.

2. У спісе функцый абярыце «PLT» і націсніце «ОК».

3. У наступным акне задайце аргументы для функцыі, пазначыўшы адпаведныя радкі ў складзенай табліцы. У канцы кожнага радка трэба націснуць на абразок, а затым выбраць патрэбную вочка ў масіве.

4. Калі ўсе аргументы будуць запоўненыя, у радок для ўводу значэнняў будзе прапісана адпаведная формула, а ў полі табліцы «Штомесячны плацёж» з'явіцца вынік разліку са знакам мінус.

Важна! Пасля разліку растэрміноўкі можна будзе разлічыць суму, якую пазычальнік пераплаціць за ўвесь перыяд крэдытавання.

Этап 2: дэталі аплаты

Суму пераплаты можна разлічваць штомесяц. У выніку чалавек будзе разумець, колькі грошай ён штомесяц будзе марнаваць на крэдыт. Дэталёвы разлік выконваецца наступным чынам:

1. Стварыце электронную табліцу за 24 месяцы.

2. Змесціце курсор у першую ячэйку табліцы і ўстаўце функцыю «OSPLT».

3. Такім жа чынам запоўніце аргументы функцыі.

4. Пры запаўненні поля «Перыяд» неабходна спасылацца на першы месяц у табліцы з указаннем ячэйкі 1.

5. Праверце, ці запоўнена першая ячэйка ў графе «Аплата целам крэдыту».
6. Каб запоўніць усе радкі першага слупка, трэба расцягнуць вочка да канца табліцы

7. Выберыце функцыю «PRPLT», каб запоўніць другі слупок табліцы.
8. У якое адкрылася акне запоўніце ўсе аргументы ў адпаведнасці са скрыншотам ніжэй.

9. Разлічыце агульны штомесячны плацёж, склаўшы значэнні ў двух папярэдніх слупках.

10. Каб разлічыць «Астатак да выплаты», вам трэба дадаць працэнтную стаўку да плацяжу па асноўнай частцы пазыкі і расцягнуць яе да канца пласціны, каб запоўніць усе месяцы пазыкі.

Дадатковая інфармацыя! Пры вылічэнні астатку на формулу неабходна павесіць знакі даляра, каб яна не з'язджала пры расцяжэнні.

Разлік ануітэтных плацяжоў па крэдыце ў excel

Функцыя PMT адказвае за разлік ануітэт ў Excel. Прынцып разліку ў агульным выглядзе заключаецца ў выкананні наступных этапаў:

1. Складзіце зыходную табліцу даных.
2. Пабудуйце графік пагашэння запазычанасці на кожны месяц.
3. Выберыце першую вочка ў графе «Плацяжы па крэдыце» і ўвядзіце формулу разліку «PLT ($B3/12;$B$4;$B$2)».
4. Атрыманае значэнне расцягваецца на ўсе слупкі пліты.

Разлік у MS Excel пагашэння асноўнай сумы доўгу

Ануітэтныя плацяжы павінны ўносіцца штомесяц у фіксаваных сумах. І працэнтная стаўка не мяняецца.

Разлік астатку асноўнай сумы (з BS=0, тып=0)

Дапусцім, узяты крэдыт у 100000 10 рублёў на 9 гадоў пад 1%. Разлічыць суму асноўнага доўгу неабходна ў 3-м месяцы XNUMX-га года. рашэнне:

1. Складзіце табліцу дадзеных і разлічыце штомесячны плацёж, выкарыстоўваючы прыведзеную вышэй формулу PV.
2. Разлічыце па формуле долю плацяжу, неабходную для пагашэння часткі доўгу «=-PMT-(PS-PS1)*пункт=-PMT-(PS +PMT+PS*пункт)».
3. Па вядомай формуле разлічыце суму асноўнага доўгу за 120 перыядаў.
4. З дапамогай аператара HPMT знайсці суму працэнтаў, выплачаных за 25 месяц.
5. Праверце вынік.

Разлік сумы асноўнага доўгу, якая была выплачана паміж двума перыядамі

Гэты разлік лепш зрабіць простым спосабам. Для разліку сумы ў інтэрвале за два перыяду трэба выкарыстоўваць наступныя формулы:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(стаўка; пачатковы_перыяд-1; plt; [ps]; [тып]) /КАЛІ(пачатковы_перыяд =1; 1; 1+тып *стаўка)”.

Звяртаць увагу! Літары ў дужках замяняюцца пэўнымі значэннямі.

Датэрміновае пагашэнне са скарочаным тэрмінам або выплатай

Пры неабходнасці скарачэння тэрміну пазыкі прыйдзецца правесці дадатковыя разлікі з дапамогай аператара IF. Так можна будзе кантраляваць нулявы баланс, які не павінен быць дасягнуты да заканчэння аплатнага перыяду.

Каб паменшыць выплаты, трэба пераразлічваць ўзнос за кожны папярэдні месяц.

Крэдытны калькулятар з нерэгулярнымі плацяжамі

Ёсць некалькі варыянтаў ануітэт, калі пазычальнік можа ўнесці зменныя сумы ў любы дзень месяца. У такой сітуацыі астатак доўгу і працэнты разлічваюцца за кожны дзень. Пры гэтым у Excel неабходна:

1. Увядзіце дні месяца, за якія вырабляюцца выплаты, і пакажыце іх колькасць.
2. Праверце адмоўныя і станоўчыя сумы. Перавага аддаецца адмоўным.
3. Падлічыце дні паміж дзвюма датамі ўнясення грошай.

Разлік перыядычнага плацяжу ў MS Excel. Тэрміновы ўклад

У Excel можна хутка разлічыць суму рэгулярных выплат пры ўмове, што фіксаваная сума ўжо назапашана. Гэта дзеянне выконваецца з дапамогай функцыі PMT пасля таго, як першапачатковая табліца была складзена.

заключэнне

Такім чынам, ануітэтныя плацяжы лягчэй, хутчэй і больш эфектыўна разлічыць у Excel. Адказнасць за іх разлік нясе аператар ФЭУ. Больш падрабязныя прыклады можна знайсці вышэй.