Израчунавање исплате ануитета у програму Мицрософт Екцел

Пре узимања зајма, било би лепо рачунати све исплате за то. Ово ће у будућности спасити зајмопримца из разних неочекиваних проблема и разочарања, када се испостави да је преплаћивање превелике. Помоћ у овом прорачуну може да се користи за Екцел. Хајде да сазнамо како израчунати ануитетна плаћања кредита у овом програму.

Обрачун плаћања

Пре свега, морам рећи да постоје две врсте исплата кредита:

• Диференциран;
• Ануитет.

Са диференцираном шемом, клијент улази у банку једнак месечни удео плаћања на тијело кредита плус камате. Вриједност каматних исплата се смањује сваког мјесеца, пошто се тијело кредита од којег се обрачунава смањује. Тако се и смањује укупна месечна уплата.

Са схемом ануитета користи се нешто другачији приступ. Клијент врши исти износ укупне исплате сваког месеца, који се састоји од плаћања на органу зајма и исплате камате. У почетку, плаћање камата је за пун износ кредита, али пошто се тијело смањује, камата се такође смањује. Међутим, укупан износ плаћања остаје непромењен због месечног повећања износа исплата на кредитном тијелу. Стога с временом се смањује удио камате у укупном месечном уплати, а специфична тежина плаћања за тијело се повећава. Истовремено, укупна месечна уплата се не мења током трајања кредита.

Само на рачуну ануитета, зауставићемо. Штавише, ово је стварно, јер тренутно већина банака користи ову шему. Такође је погодан за купце, јер у овом случају се укупан износ плаћања не мења, остајући фиксиран. Корисници увек знају колико да плате.

Корак 1: Обрачун месечне рате

Да бисте израчунали месечну накнаду приликом коришћења схеме ануитета у Екцел-у, постоји посебна функција - ПЛТ . Спада у категорију финансијских оператера. Формула за ову функцију је следећа:

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

Као што видите, ова функција има пуно аргумената. Истина, последња два од њих нису обавезна.

Аргумент "Бет" означава каматну стопу за одређени период. Ако се, на пример, користи годишња стопа, али се зајам плаћа месечно, онда се годишња стопа треба поделити са 12 и резултат који се користи као аргумент. Ако се користи квартални метод плаћања, у овом случају годишња стопа треба поделити са 4 , итд.

"Кпер" се односи на укупан број отплата кредита. То јест, ако се кредит узима једном године уз месечну исплату, онда се број периода сматра 12 , ако је за двије године, онда је број периода 24 . Ако се кредит узима за две године уз квартално плаћање, онда је број периода 8 .

"Пс" означава садашњу вредност. Једноставним речима, ово је укупан износ кредита на почетку кредита, односно износ који сте позајмљивали, без узимања у обзир камата и других додатних уплата.

"Бс" је будућност. Овај износ, који ће бити тијело кредита у тренутку завршетка уговора о кредиту. У већини случајева, овај аргумент је једнак "0" , јер се зајмодавац у потпуности измири са зајмодавцем на крају кредитног периода. Наведени аргумент је необавезан. Према томе, ако је изостављено, сматра се да је нула.

Аргумент "Тип" одређује време израчунавања: на крају или на почетку периода. У првом случају, она има вредност "0" , ау другом - "1" . Већина банкарских институција користи опцију са плаћањем на крају периода. Овај аргумент је такође необавезан, а ако се изостави претпоставља се да је нула.

Сада је време да пређемо на конкретан пример израчунавања месечне накнаде користећи функцију ПЛТ-а. За рачунање користимо табелу са почетним подацима који показују каматну стопу на кредит ( 12% ), вредност кредита ( 500.000 рубаља ) и рок трајања кредита ( 24 месеца ). У овом случају, плаћање се врши месечно на крају сваког периода.

1. Изаберите елемент на листу у којем ће се приказати резултати израчунавања и кликнути на икону "Инсерт фунцтион" која се налази близу траке формуле.
2. Покренут је прозор чаробњака функција . У категорији "Финансијски" , изаберемо назив "ПЛТ" и кликните на дугме "ОК" .
3. Након тога отвара се прозор аргумената оператора ПЛТ-а .

   У пољу "Стопа" треба унети износ камате за тај период. Ово се може урадити ручно, само ставити проценат, али имамо је у посебној ћелији на листу, па дајмо јој линк. Поставите курсор у поље, а затим кликните на одговарајућу ћелију. Али, како се сећамо, у нашој табели је утврђена годишња каматна стопа, а рок отплате је једнак месецу. Због тога подијелимо годишњу стопу, односно, упућивање на ћелију у којој се налази, према броју 12 , што одговара броју мјесеци у години. Подела се врши директно у пољу прозора аргумената.

   У пољу "Кпер" постављен је кредитни период. Имамо га 24 месеца. Можете да унесете број 24 ручно, али ми, као у претходном случају, означимо везу са локацијом овог индикатора у изворној табели.

   У пољу "Пс" означена је оригинална вредност зајма. То је једнако 500.000 рубаља . Као иу претходним случајевима, указујемо на везу са елементом листа који садржи овај индикатор.

   Поље "Бс" означава износ кредита након пуног плаћања. Као што се сећаш, ова вредност је скоро увек нула. Поставили смо број "0" у овом пољу. Иако се овај аргумент може изоставити у потпуности.

   У пољу "Врста" указујемо на почетку или на крају месеца уплате. Ми, као иу већини случајева, произведемо се крајем месеца. Стога смо поставили број "0" . Као и код претходног аргумента, у овом пољу не можете ништа ништа унети, онда ће програм подразумевано претпоставити да има вриједност нуле.

   Након што унесете све податке, кликните на дугме "ОК" .

4. Након тога, резултат израчунавања се приказује у ћелији коју смо одабрали у првом ставу овог приручника. Као што видите, вредност месечног укупног износа за кредит је 23.536,74 рубаља . Немојте да будете збуњени знаком "-" пре ове количине. Дакле Акел истиче да је то готовински ток, односно губитак.
5. Да би се израчунао укупан износ плаћања за цео период кредита, узимајући у обзир отплату тијела зајма и мјесечне камате, довољно је помножити вриједност месечног плаћања ( 23536,74 рубаља ) по броју мјесеци ( 24 мјесеца ). Као што можете видети, укупан износ плаћања за цео период кредита у нашем случају износио је 564881,67 рубаља .
6. Сада можете израчунати износ преплаћене на зајам. Да би то учинили, неопходно је одузети од укупног износа исплата на зајму, укључујући камату и орган зајма, почетни износ позајмљен. Али се сећамо да прва од ових вредности већ има знак "-" . Према томе, у нашем конкретном случају, испоставља се да их треба преклопити. Као што видите, укупан износ преплаћене позајмице за целокупни период износио је 64881,67 рубаља .

Лекција: Функцијски чаробњак у Екцелу

Корак 2: детаљно објашњење плаћања

А сада уз помоћ других Акел оператора извршићемо месечне детаље о плаћању како бисмо видели колико у одређеном мјесецу плаћамо на кредитном тијелу и колико је камата. У ту сврху, нацртаћемо таблицу у Екцелу, коју ћемо попунити са подацима. Редови ове табеле одговараће одговарајућем времену, односно месецу. С обзиром да је кредитни период за нас 24 месеца, онда ће и број редова бити одговарајући. Колоне означавају плаћање тијела зајма, плаћање камате, укупну месечну исплату, што је збир претходних две колоне и преостали износ који треба платити.

1. Да би се утврдио износ плаћања за тело зајма, користимо функцију ОСПЛТ-а , која је управо дизајнирана за ове сврхе. Поставите курсор у ћелију која се налази у реду "1" иу колони "Исплата тела зајма". Кликните на дугме "Убаци функцију" .
2. Идите у Чаробњак за функције . У категорији "Финансијски" маркирамо назив "ОСПЛТ" и кликните на дугме "ОК" .
3. Покренут је прозор аргумената оператора ОСПЛТ. Има следећу синтаксу:

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

   Као што видите, аргументи ове функције се скоро у потпуности поклапају са аргументима ПЛТ оператора, умјесто опционалног "типа" аргумента додан је само обавезни аргумент "Период" . Она означава број периода исплате, ау нашем конкретном случају број месеца.

   Попуњавамо поља прозора аргумената ОСПЛТ функције коју већ знамо истим подацима који су кориштени за функцију ПЛТ-а . Само узимајући у обзир чињеницу да ће у будућности формула бити копирана помоћу маркера испуњавања, потребно је да све везе у пољима буду апсолутне тако да се не мијењају. За ово је потребно поставити знак за долар пре сваке вриједности координата дуж вертикалних и хоризонталних линија. Али лакше је то учинити, једноставно избором координата и притискањем функцијског тастера Ф4 . Знак за долар ће се аутоматски поставити на праве локације. Такође, не заборавите да би годишњу стопу требало поделити са 12 .

4. Али још увијек имамо још један нови аргумент који ПЛТ функција није имала. Овај аргумент је "Период" . У одговарајућем пољу поставите референцу на прву ћелију колоне "Период" . Овај елемент листе садржи број "1" , који означава број првог месеца кредитирања. Али за разлику од претходних поља, у наведеном пољу остављамо референтни релатив и не чини га апсолутним.

   Након што унесете све податке о којима смо говорили изнад, кликните на дугме "ОК" .

5. Након тога, у ћелији коју смо претходно доделили, приказан је износ плаћања на тијело за кредит за први мјесец. То ће бити 18536,74 рубаља .
6. Затим, као што је горе поменуто, ову формулу треба копирати на остатак ћелија у колони користећи ручицу за пуњење. Да бисте то урадили, поставите курсор у доњи десни угао ћелије која садржи формулу. Курсор се претвара у крст, који се назива маркер за пуњење. Затворите леви тастер миша и превуците га до краја стола.
7. Као резултат, попуњене су све ћелије у колони. Сада имамо месечни распоред отплате кредита. Као што је горе поменуто, износ плаћања за овај чланак са сваким новим периодом се повећава.
8. Сада морамо извршити месечни обрачун камате. У ове сврхе ћемо користити ПРПЛТ оператер. Изаберите прву празну ћелију у колони "Плаћање камате" . Кликните на дугме "Убаци функцију" .
9. У прозору чаробњака функција у категорији "Финансијски" , изаберемо име ПРПЛТ-а . Кликните на дугме "ОК" .
10. Покреће се прозор аргумента ПРПЛТ функције. Њена синтакса је следећа:

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

    Као што видите, аргументи ове функције су апсолутно идентични аналогним елементима оператора ОСПЛТ . Због тога смо само ставили у прозор исте податке које смо унели у претходни прозор аргумената. Међутим, не заборављамо да би референца у пољу "Период" требало да буде релативна, ау свим осталим областима координате треба довести у апсолутну форму. Након тога, кликните на дугме "ОК" .

11. Затим је резултат израчунавања износа камате за кредит за први месец приказан у одговарајућој ћелији.
12. Примјењујући ознаку испуњавања, копирамо формулу на остале елементе колоне, на тај начин добијамо месечни распоред плаћања за камату на зајам. Као што видимо, као што је раније речено, вриједност ове врсте плаћања се смањила из мјесеца у мјесец.
13. Сада морамо да израчунамо укупну месечну уплату. За ову обрачун не треба се прибјегавати било којем оператеру, јер се може користити једноставна аритметичка формула. Додајемо садржај ћелија првог месеца колона "Плаћање од стране тела за кредит" и "Плаћање камате" . Да бисте то урадили, поставите знак "=" у прву празну ћелију колоне "Укупна месечна плаћања" . Затим кликните на два горња елемента, подесите знак "+" између њих. Притисните тастер Ентер .
14. Даље, користећи маркер пуњења, као иу претходним случајевима, попуњавамо колону са подацима. Као што видимо, током читаве радње уговора износ укупне месечне уплате, укључујући плаћање на органу зајма и плаћање камате, износи 23536,74 рубаља . У ствари, већ смо бројали ову цифру уз помоћ ПЛТ-а . Али у овом случају то је више изражено, баш као и износ плаћања за кредитно тијело и камату.
15. Сада вам је потребно додати податке у колону, гдје ће се стање месечног износа кредита приказивати месечно, што је још увијек потребно за плаћање. У првој ћелији колоне "Баланс до плаћања", израчунавање ће бити најједноставније. Морамо да одузмемо од првобитног износа кредита, који је наведен у табели са примарним подацима, плаћање на кредитном тијелу за први мјесец у табели за поравнање. Али, имајући у виду чињеницу да један од бројева које већ имамо са знаком "-" , не треба их одузети, већ се преклапати. Урадите то и притисните тастер Ентер .
16. Али обрачун биланса плаћања после другог и наредних месеци бит ће нешто компликованији. Да бисмо то урадили, морамо од почетка зајма одузети од тијела кредита укупан износ плаћања на кредитном тијелу за претходни период. Поставите знак "=" у другу ћелију колоне "Стање на плаћање" . Затим, индицирамо везу до ћелије, која садржи изворни износ кредита. Ми то учинимо апсолутно избором и притиском на тастер Ф4 . Онда ставимо знак "+" , јер ће друга вриједност бити негативна за нас. Након тога кликните на дугме "Инсерт фунцтион" .
17. Почиње чаробњак за функцију , у којем је потребно прећи у категорију "Математички" . Тамо изаберемо натпис "СУМ" и кликните на дугме "ОК" .
18. Отвара се прозор аргумента СУМ функције. Наведени оператор служи за сумирање података у ћелијама, које морамо извршити у колони "Плаћање од стране тела зајма". Има следећу синтаксу:

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

    Аргументи су референца на ћелије које садрже бројеве. Поставили смо курсор у поље "Нумбер1" . Затим држите левим тастером миша и изаберите прве две ћелије колоне "Тачан по кредиту" на листу. На пољу, као што видите, приказана је референца на опсег. Састоји се од два дела одвојена дебелим цревом: референце на прву ћелију опсега и на последњи. Да бисмо могли да копирамо назначену формулу у будућности помоћу маркера за пуњење, направићемо први део референце на апсолутни опсег. Изаберите је и кликните на функцијски тастер Ф7 . Други део референца је релативан. Сада, када користите маркер пуњења, прва ћелија опсега ће бити фиксирана, а последња ћелија ће се истегнути док се помера надоле. То је оно што треба да испуни наше циљеве. Затим кликните на дугме "ОК" .

19. Дакле, резултат биланса кредита након другог месеца је приказан у ћелији. Сада, почевши од ове ћелије, копирамо формулу да празне елементе колоне користимо помоћу ручке за пуњење.
20. Мјесечни обрачун биланса за исплату на зајам се врши за цијели кредитни период. Као што се очекивало, на крају периода тај износ је нула.

Дакле, нисмо само израчунали исплату за кредит, већ смо организовали неку врсту калкулатора кредита. Ко ће деловати на ануитетној шеми. Ако у оригиналној табели, на пример, променимо износ кредита и годишњу каматну стопу, онда ће у завршној табели бити аутоматски прерачунавање података. Стога се може користити не само једном за одређени случај, већ се може користити у различитим ситуацијама за израчунавање опција за кредит на ануитети.

Лекција: Финансијске функције у Екцелу

Као што можете видети, користећи Екцел програм код куће, лако можете израчунати укупну месечну исплату на ануитети, користећи за те сврхе оператор ПЛТ . Поред тога, уз помоћ функција ОПСТ- а и МТЕФ-а, могуће је израчунати износ плаћања на органу зајма и на камате за наведени период. Приликом коришћења целог тог пртљага функција, могуће је направити моћан кредитни калкулатор који можете користити више пута за израчунавање ануитета.

• ← Подесите дан у недељи по датуму у Мицрософт Екцелу
• Примена БПР функције у Мицрософт Екцелу →