Перш, чым браць пазыку, нядрэнна было б разлічыць усе плацяжы па ім. Гэта зберажэ пазычальніка ў будучыні ад розных нечаканых непрыемнасцяў і расчараванняў, калі высветліцца, што пераплата занадта вялікая. Дапамагчы ў дадзеным разліку могуць інструменты праграмы Excel. Давайце высветлім, як разлічыць аннуитетные плацяжы па крэдыце ў гэтай праграме.
разлік аплаты
Перш за ўсё, трэба сказаць, што існуе два віды крэдытных плацяжоў:
- дыферэнцыраваныя;
- Аннуитетные.
Пры дыферэнцыраванай схеме кліент уносіць у банк штомесяц роўную долю выплат па целе крэдыту плюс плацяжы па працэнтах. Велічыня працэнтных выплат кожны месяц памяншаецца, бо памяншаецца цела пазыкі, з якога яны разлічваюцца. Такім чынам і агульны штомесячны плацёж таксама памяншаецца.
Пры аннуитетной схеме выкарыстоўваецца некалькі іншы падыход. Кліент штомесяц уносіць аднолькавую суму агульнага плацяжу, які складаецца з выплат па целе крэдыту і аплаты працэнтаў. Першапачаткова працэнтныя ўнёскі налічваюцца на ўсю суму пазыкі, але па меры таго, як цела памяншаецца, скарачаецца і налічэнне адсоткаў. Але агульная сума аплаты застаецца нязменнай за кошт штомесячнага павелічэння велічыні выплат па целе крэдыту. Такім чынам, з цягам часу удзельная вага працэнтаў у агульным штомесячным плацяжы падае, а ўдзельная вага аплаты па целе расце. Пры гэтым сам агульны штомесячны плацёж на працягу ўсяго тэрміну крэдытавання не мяняецца.
Як раз на разліку аннуитетного плацяжу мы і спынімся. Тым больш, гэта актуальна, так як у цяперашні час большасць банкаў выкарыстоўваюць менавіта гэтую схему. Яна зручная і для кліентаў, бо ў гэтым выпадку агульная сума аплаты не мяняецца, застаючыся фіксаванай. Кліенты заўсёды ведаюць колькі трэба заплаціць.
Этап 1: разлік штомесячнага ўнёску
Для разліку штомесячнага ўнёску пры выкарыстанні аннуитетной схемы ў Эксэля існуе спецыяльная функцыя - ПЛТ. Яна адносіцца да катэгорыі фінансавых аператараў. Формула гэтай функцыі выглядае наступным чынам:
= ПЛТ (стаўка; кпер; пс; БС; тып)
Як бачым, якая змешчана функцыя валодае даволі вялікай колькасцю аргументаў. Праўда, апошнія два з іх не з'яўляюцца абавязковымі.
аргумент "Стаўка" паказвае на працэнтную стаўку за канкрэтны перыяд. Калі, напрыклад, выкарыстоўваецца гадавая стаўка, але плацёж па пазыцы вырабляецца штомесяц, то гадавую стаўку трэба падзяліць на 12 і атрыманы вынік выкарыстоўваць у якасці аргументу. Калі ўжываецца штоквартальны від аплаты, то ў гэтым выпадку гадавую стаўку трэба падзяліць на 4 і г.д.
"Кпер" пазначае агульная колькасць перыядаў выплат па крэдыту. Гэта значыць, калі пазыку бярэцца на адзін год з штомесячнай аплатай, то лік перыядаў лічыцца 12, Калі на два гады, то лік перыядаў - 24. Калі крэдыт бярэцца на два гады з штоквартальнай аплатай, то лік перыядаў роўна 8.
"Пс" паказвае прыведзеную кошт на сапраўдны момант. Кажучы простымі словамі, гэта агульная велічыня пазыкі на пачатак крэдытавання, гэта значыць, тая сума, якую вы бераце ў пазыку, без уліку працэнтаў і іншых дадатковых выплат.
"БС" - гэта будучая кошт. Гэтая велічыня, якую будзе складаць цела пазыкі на момант завяршэння крэдытнага дагавора. У большасці выпадкаў дадзены аргумент роўны "0", Так як пазычальнік на канец тэрміну крэдытавання павінен цалкам разлічыцца з крэдыторам. Ўказаны аргумент не з'яўляецца абавязковым. Таму, калі ён апускаецца, то лічыцца роўным нулю.
аргумент "Тып" вызначае час разліку: у канцы ці ў пачатку перыяду. У першым выпадку ён прымае значэнне "0", А ў другім - "1". Большасць банкаўскіх устаноў выкарыстоўваюць менавіта варыянт з аплатай у канцы перыяду. Гэты аргумент таксама з'яўляецца неабавязковым, і калі яго апусціць лічыцца, што ён роўны нулю.
Цяпер настаў час перайсці да канкрэтнага прыкладу разліку штомесячнага ўнёску пры дапамозе функцыі ПЛТ. Для разліку выкарыстоўваем табліцу з зыходнымі дадзенымі, дзе паказаная працэнтная стаўка па крэдыце (12%), Велічыня пазыкі (500000 рублёў) І тэрмін крэдыту (24 месяцы). Пры гэтым аплата вырабляецца штомесяц у канцы кожнага перыяду.
- Вылучаем элемент на лісце, у які будзе выводзіцца вынік разліку, і пстрыкаем па піктаграме "Уставіць функцыю", Размешчаную каля радкі формул.
- Вырабляецца запуск акенца майстры функцый. У катэгорыі "Фінансавыя" вылучаем найменне "ПЛТ" і ціснем на кнопку "OK".
- Пасля гэтага адкрываецца акно аргументаў аператара ПЛТ.
У полі "Стаўка" варта ўпісаць велічыню працэнтаў за перыяд. Гэта можна зрабіць уручную, проста паставіўшы працэнт, але ў нас ён паказаны ў асобнай вочку на лісце, таму дамо на яе спасылку. Усталёўваем курсор у полі, а затым клікаем па адпаведнай вочку. Але, як мы памятаем, у нас у табліцы зададзена гадавая працэнтная стаўка, а перыяд аплаты роўны месяцу. Таму дзелім гадавую стаўку, а дакладней спасылку на вочка, у якой яна змяшчаецца, на лік 12, Адпаведнае колькасці месяцаў у годзе. Дзяленне выконваем прама ў поле акна аргументаў.
У полі "Кпер" усталёўваецца тэрмін крэдытавання. Ён у нас роўны 24 месяцах. Можна занесці ў поле лік 24 ўручную, але мы, як і ў папярэднім выпадку, паказваем спасылку на месцазнаходжанне гэтага паказчыка ў зыходнай табліцы.
У полі "Пс" паказваецца першапачатковая велічыня пазыкі. яна роўная 500000 рублёў. Як і ў папярэдніх выпадках, паказваем спасылку на элемент ліста, у якім утрымліваецца дадзены паказчык.
У полі "БС" паказваецца велічыня пазыкі, пасля поўнай яго аплаты. Як памятаем, гэта значэнне практычна заўсёды роўна нулю. Усталёўваем ў дадзеным полі лік "0". Хоць гэты аргумент можна наогул апусціце.
У полі "Тып" паказваем ў пачатку або ў канцы месяца вырабляецца аплата. У нас, як і ў большасці выпадкаў, яна вырабляецца ў канцы месяца. Таму усталёўваем лік "0". Як і ў выпадку з папярэднім аргументам, у дадзенае поле можна нічога не ўводзіць, тады праграма па змаўчанні будзе лічыць, што ў ім размешчана значэнне роўнае нулю.
Пасля таго, як усе дадзеныя ўведзеныя, ціснем на кнопку "OK".
- Пасля гэтага ў вочка, якую мы выдзелілі ў першым пункце дадзенага кіраўніцтва, выводзіцца вынік вылічэнні. Як бачым, велічыня штомесячнага агульнай плацяжу па пазыцы складае 23536,74 рубля. Хай вас не бянтэжыць знак "-" перад дадзенай сумай. Так Эксэля паказвае на тое, што гэта расход грашовых сродкаў, гэта значыць, страту.
- Для таго, каб разлічыць агульную суму аплаты за ўвесь тэрмін крэдытавання з улікам пагашэння цела пазыкі і штомесячных адсоткаў, дастаткова перамнажаць велічыню штомесячнага плацяжу (23536,74 рубля) На колькасць месяцаў (24 месяцы). Як бачым, агульная сума плацяжоў за ўвесь тэрмін крэдытавання ў нашым выпадку склала 564881,67 рубля.
- Цяпер можна падлічыць суму пераплаты па крэдыце. Для гэтага трэба адняць ад агульнай велічыні выплат па крэдыту, уключаючы працэнты і цела пазыкі, пачатковую суму, узятую ў доўг. Але мы памятаем, што першае з гэтых значэнняў ужо са знакам "-". Таму ў канкрэтна нашым выпадку атрымліваецца, што іх трэба скласці. Як бачым, агульная сума пераплаты па крэдыце за ўвесь тэрмін склала 64881,67 рубля.
Урок: Майстар функцый у Эксэля
Этап 2: дэталізацыя плацяжоў
А цяпер з дапамогай іншых аператараў Эксэля зробім памесячна дэталізацыю выплат, каб бачыць, колькі ў пэўным месяцы мы плацім па целе пазыкі, а колькі складае велічыня адсоткаў. Для гэтых мэтаў рысы ў Эксэля табліцу, якую будзем запаўняць дадзенымі. Радкі гэтай табліцы будуць адказваць адпаведнага перыяду, гэта значыць, месяцу. Улічваючы, што перыяд крэдытавання ў нас складае 24 месяца, то і колькасць радкоў таксама будзе адпаведным. У слупках паказаная выплата цела пазыкі, выплата працэнтаў, агульны штомесячны плацёж, які з'яўляецца сумай папярэдніх двух калонак, а таксама астатняя сума да выплаты.
- Для вызначэння велічыні аплаты па целе пазыкі выкарыстоўваем функцыю ОСПЛТ, Якая як раз прызначана для гэтых мэтаў. Усталёўваем курсор у вочка, якая знаходзіцца ў радку "1" і ў слупку "Выплата па целе крэдыту". Ціснем на кнопку "Уставіць функцыю".
- пераходзім у майстар функцый. У катэгорыі "Фінансавыя" адзначаем найменне "ОСПЛТ" і ціснем кнопку "OK".
- Запускаецца акно аргументаў аператара ОСПЛТ. Ён мае наступны сінтаксіс:
= ОСПЛТ (Стаўка; Перыяд; Кпер; Пс; БС)Як бачым, аргументы дадзенай функцыі амаль цалкам супадаюць з аргументамі аператара ПЛТ, Толькі замест неабавязковага аргументу "Тып" дададзены абавязковы аргумент "Перыяд". Ён паказвае на нумар перыяду выплаты, а ў нашым канкрэтным выпадку на нумар месяца.
Запаўняем ўжо знаёмыя нам поля вокны аргументаў функцыі ОСПЛТ тымі самымі дадзенымі, што былі выкарыстаныя для функцыі ПЛТ. Толькі ўлічваючы той факт, што ў будучыні будзе прымяняцца капіраванне формулы з дапамогай маркера запаўнення, трэба зрабіць усё спасылкі ў палях абсалютнымі, каб яны не мяняліся. Для гэтага патрабуецца паставіць знак даляра перад кожным значэннем каардынатаў па вертыкалі і гарызанталі. Але лягчэй гэта зрабіць, проста вылучыўшы каардынаты і націснуўшы на функцыянальную клавішу F4. Знак даляра будзе расстаўленыя ў патрэбных месцах аўтаматычна. Таксама не забываем, што гадавую стаўку трэба падзяліць на 12.
- Але ў нас застаецца яшчэ адзін новы аргумент, якога не было ў функцыі ПЛТ. гэты аргумент "Перыяд". У адпаведнае поле усталёўваем спасылку на першую вочка слупка "Перыяд". Дадзены элемент ліста утрымлівае ў сабе лік "1", Якое абазначае нумар першага месяца крэдытавання. Але ў адрозненне ад папярэдніх палёў, у паказаным поле мы пакідаем спасылку адноснай, а не робім з яе абсалютную.
Пасля таго, як усе дадзеныя, пра якія мы казалі вышэй, уведзены, ціснем на кнопку "OK".
- Пасля гэтага ў вочку, якую мы раней вылучылі, адлюструецца велічыня выплаты па целе пазыкі за першы месяц. яна складзе 18536,74 рубля.
- Затым, як ужо гаварылася вышэй, нам варта скапіяваць дадзеную формулу на астатнія вочкі слупка з дапамогай маркера запаўнення. Для гэтага ўсталёўваны курсор у ніжні правы кут вочкі, у якой утрымліваецца формула. Курсор пераўтворыцца пры гэтым у крыжык, які называецца маркерам запаўнення. Зацісканы левую кнопку мышы і цягнем яго ўніз да канца табліцы.
- У выніку ўсё вочкі слупка запоўненыя. Цяпер мы маем графік выплаты цела пазыкі памесячна. Як і гаварылася ўжо вышэй, велічыня аплаты па дадзеным артыкуле з кожным новым перыядам павялічваецца.
- Цяпер нам трэба зрабіць месячны разлік аплаты па працэнтах. Для гэтых мэтаў будзем выкарыстоўваць аператар ПРПЛТ. Вылучаем першую пустое вочка ў Стоўбцах "Выплата па працэнтах". Ціснем на кнопку "Уставіць функцыю".
- У запусцім акне майстры функцый у катэгорыі "Фінансавыя" вырабляем вылучэнне найменні ПРПЛТ. Выконваем пстрычка па кнопцы "OK".
- Адбываецца запуск вокны аргументаў функцыі ПРПЛТ. Яе сінтаксіс выглядае наступным чынам:
= ПРПЛТ (Стаўка; Перыяд; Кпер; Пс; БС)Як бачым, аргументы дадзенай функцыі абсалютна ідэнтычныя аналагічным элементам аператара ОСПЛТ. Таму проста заносім у акно тыя ж дадзеныя, якія мы ўводзілі ў папярэднім акне аргументаў. Не забываем пры гэтым, што спасылка ў полі "Перыяд" павінна быць адноснай, а ва ўсіх іншых палях каардынаты трэба прывесці да абсалютнага ўвазе. Пасля гэтага пстрыкаем па кнопцы "OK".
- Затым вынік разліку сумы аплаты па працэнтах за крэдыт за першы месяц выводзіцца ў адпаведную вочка.
- Ужыўшы маркер запаўнення, вырабляем капіраванне формулы ў астатнія элементы слупка, такім спосабам атрымаўшы памесячны графік аплат па працэнтах за пазыку. Як бачым, як і было сказана раней, з месяца ў месяц велічыня гэтага віду плацяжу памяншаецца.
- Цяпер нам трэба будзе разлічыць агульны штомесячны плацёж. Для гэтага вылічэнні нельга звяртацца да якога-небудзь аператару, так як можна скарыстацца простай арыфметычнай формулай. Складаем змесціва вочак першага месяца слупкоў "Выплата па целе крэдыту" і "Выплата па працэнтах". Для гэтага ўсталёўваны знак "=" у першую пустое вочка слупка "Агульная штомесячная выплата". Затым клікаем па двух вышэйпаказаным элементам, усталяваўшы паміж імі знак "+". Ціснем на клавішу Enter.
- Далей з дапамогай маркера запаўнення, як і ў папярэдніх выпадках, запаўняем калонку дадзенымі. Як бачым, на працягу ўсяго дзеяння дагавора сума агульнага штомесячнага плацяжу, які ўключае плацёж па целе пазыкі і аплату працэнтаў, складзе 23536,74 рубля. Уласна гэты паказчык мы ўжо разлічвалі раней пры дапамозе ПЛТ. Але ў дадзеным выпадку гэта прадстаўлена больш наглядна, менавіта як сума аплаты па целе пазыкі і працэнтах.
- Зараз трэба дадаць дадзеныя ў слупок, дзе будзе штомесяц адлюстроўвацца рэшту сумы па крэдыце, які яшчэ патрабуецца заплаціць. У першай вочку слупка "Рэшту да выплаты" разлік будзе самы просты. Нам трэба адняць ад першапачатковай велічыні пазыкі, якая паказаная ў табліцы з першаснымі дадзенымі, плацёж па целе крэдыту за першы месяц у разліковай табліцы. Але, улічваючы той факт, што адно з лікаў у нас ужо ідзе са знакам "-", То іх варта не адняць, а скласці. Робім гэта і ціснем на кнопку Enter.
- А вось вылічэнне астатку да выплаты пасля другога і наступных месяцаў будзе некалькі складаней. Для гэтага нам трэба адняць ад цела крэдыту на пачатак крэдытавання агульную суму плацяжоў па целе пазыкі за папярэдні перыяд. усталёўваем знак "=" у другой вочку слупка "Рэшту да выплаты". Далей паказваем спасылку на вочка, у якой утрымліваецца першапачатковая сума крэдыту. Робім яе абсалютнай, вылучыўшы і націснуўшы на клавішу F4. Затым ставім знак "+", Так як другое значэнне ў нас і так будзе адмоўным. Пасля гэтага клікаем па кнопцы "Уставіць функцыю".
- запускаецца майстар функцый, У якім трэба перамясціцца ў катэгорыю "Матэматычныя". Там вылучаем надпіс "Сум" і ціснем на кнопку "OK".
- Запускаецца акно аргументаў функцыі сум. Ўказаны аператар служыць для таго, каб сумаваць дадзеныя ў вочках, што нам і трэба выканаць у слупку "Выплата па целе крэдыту". Ён мае наступны сінтаксіс:
= Сум (число1; число2; ...)У якасці аргументаў выступаюць спасылкі на ячэйкі, у якіх утрымліваюцца колькасці. Мы ўсталёўваем курсор у полі "Число1". Затым зацісканы левую кнопку мышы і вылучаем на лісце першыя дзве ячэйкі слупка "Выплата па целе крэдыту". У поле, як бачым, адлюстравалася спасылка на дыяпазон. Яна складаецца з двух частак, падзеленых двукроп'ем: спасылкі на першую вочка дыяпазону і на апошнюю. Для таго, каб у будучыні мець магчымасць скапіяваць паказаную формулу з дапамогай маркера запаўнення, робім першую частку спасылкі на дыяпазон абсалютнай. Вылучаем яе і ціснем на функцыянальную клавішу F4. Другую частку спасылкі так і пакідаем адноснай. Цяпер пры выкарыстанні маркера запаўнення першая вочка дыяпазону будзе замацавана, а апошняя будзе расцягвацца па меры прасоўвання ўніз. Гэта нам і трэба для выканання пастаўленых мэтаў. Далей ціснем на кнопку "OK".
- Такім чынам, вынік астатку крэдытнай запазычанасці пасля другога месяца выводзіцца ў вочка. Цяпер, пачынаючы з дадзенай ячэйкі, вырабляем капіраванне формулы у пустыя элементы слупка з дапамогай маркера запаўнення.
- Памесячны разлік рэшткаў да аплаты па крэдыце зроблены за ўвесь крэдытны перыяд. Як і пакладзена, на канец тэрміну гэтая сума роўная нулю.
Такім чынам, мы зрабілі не проста разлік аплаты па крэдыце, а арганізавалі своеасаблівы крэдытны калькулятар. Які будзе дзейнічаць па аннуитетной схеме. Калі ў зыходнай табліцы мы, напрыклад, памяняем велічыню пазыкі і гадавы працэнтнай стаўкі, то ў выніковай табліцы адбудзецца аўтаматычны пералік дадзеных. Таму яе можна выкарыстоўваць не толькі адзін раз для канкрэтнага выпадку, а ўжываць у розных сітуацыях для разліку крэдытных варыянтаў па аннуитетной схеме.
Урок: Фінансавыя функцыі ў Excel
Як бачым, пры дапамозе праграмы Excel ў хатніх умовах можна без праблем разлічыць агульны штомесячны крэдытны плацёж па аннуитетной схеме, выкарыстоўваючы для гэтых мэтаў аператар ПЛТ. Акрамя таго, пры дапамозе функцый ОСПЛТ і ПРПЛТ можна вырабіць разлік велічыні плацяжоў па целе крэдыту і па працэнтах за названы перыяд. Ужываючы ўвесь гэты багаж функцый разам, існуе магчымасць стварыць магутны крэдытны калькулятар, які можна будзе выкарыстоўваць не адзін раз для вылічэнні аннуитетного плацяжу.
