Изчисляване на анюитетната заплата в Excel

Преди да вземете кредита, би било хубаво да се изчислят всички плащания по него. Това ще спаси кредитополучателя в бъдеще от различни неочаквани проблеми и разочарования, когато се окаже, че надплащането е твърде голямо. Помощ за това изчисление може инструментите на програмата Excel. Нека да разберем как да изчислим анюитетните плащания по кредита в тази програма.

Изчисляване на плащането

На първо място, трябва да се каже, че има два вида кредитни плащания:

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

С диференцирана схема, клиентът носи месечен равен дял на плащанията по тялото на заем плюс плащанията към лихвите. Размерът на лихвените плащания всеки месец намалява, тъй като тялото на заема се намалява, от което се изчисляват. По този начин общото месечно плащане също се намалява.

Анна леля схема използва малко по-различен подход. Клиентът прави един размер на общото плащане месечно, което се състои от плащания по отношение на заема и плащане на интерес. Първоначално лихвените вноски се номерират по цялата сума на заема, но тъй като тялото намалява, интересът е намален и начисляващ лихвен процент. Но общият размер на плащането остава непроменен поради месечното увеличение на размера на плащанията от страна на органа по кредита. Така с течение на времето делът на интереса към общото месечно плащане пада и пропорцията на тялото расте. В същото време общото месечно плащане не се променя през целия срок.

Само за изчисляване на анюитетното плащане ще спрем. Особено това е уместно, тъй като сега повечето банки използват тази конкретна схема. Това е удобно за клиентите, защото в този случай общият размер на плащането не се променя, оставайки фиксирани. Клиентите винаги знаят колко трябва да платите.

Стъпка 1: Изчисляване на месечни вноски

За да се изчисли месечният принос, когато се използва анюирната верига в Excele, има специална функция - PPT. Тя се отнася до категорията на финансовите оператори. Формулата на тази функция е както следва:

= Ppt (скорост; kper; ps; bs; тип)

Както виждаме, посочената функция има доста голям брой аргументи. Вярно е, че последните две от тях не са задължителни.

Аргументът "ставка" показва процентна ставка за определен период. Ако, например, се използва годишен темп, но плащането на кредита се извършва ежемесечно, тогава годишната ставка трябва да бъде разделена на 12 и резултатът се използва като аргумент. Ако се прилага тримесечен вид плащане, в този случай годишният залог трябва да бъде разделен на 4 и т.н.

"CPER" означава общият брой на периодите на кредитни плащания. Това е, ако заемът е взет за една година с месечното плащане, тогава броят на периодите се счита за 12, ако две години, тогава броят на периодите - 24. Ако заемът е взет за две години с тримесечно плащане, тогава Броят на периодите е 8.

"PS" показва настоящата стойност в момента. Говорейки с прости думи, това е общата сума на кредита в началото на кредитирането, т.е. сумата, която сте заета, с изключение на лихви и други допълнителни плащания.

"BS" е бъдеща цена. Тази стойност ще бъде кредитен орган по време на приключването на договора за кредит. В повечето случаи този аргумент е "0", тъй като кредитополучателят в края на кредитния период трябва да се успокои напълно с кредитора. Посоченият аргумент не е задължителен. Следователно, ако се спусне, се счита за нула.

Аргументът "тип" определя времето за изчисление: в края или в началото на периода. В първия случай, това отнема стойност "0", а във втория - "1". Повечето банкови институции използват точно опцията с плащане в края на периода. Този аргумент също е по избор и ако е пропуснат, се смята, че е нула.

Сега е време да се премести в определен пример за изчисляване на месечния принос, използвайки PL функцията. За да изчислим, използваме таблица с изходните данни, когато се посочва лихвеният процент по кредита (12%), стойността на кредита (500 000 рубли) и периода на заема (24 месеца). В същото време плащането се извършва ежемесечно в края на всеки период.

1. Изберете елемента на листа, в който ще се покаже резултатът от резултата и щракнете върху иконата "Вмъкване на функция", поставена в близост до формула.



2. Стартира прозорецът на Window Wizard. В категорията "Финанси" разпределя името "PLT" и кликнете върху бутона "OK".



3. След това отваря аргументите на оператора на PL.

   В полето "Цена" трябва да въведете процентната стойност за периода. Това може да се направи ръчно, просто поставяне на процент, но е показан в отделна клетка на листа, така че ще дадем връзка към нея. Инсталирайте курсора в полето и след това кликнете върху съответната клетка. Но, както си спомняме, имаме годишния лихвен процент в нашата таблица, а периодът на плащане е равен на месеца. Ето защо ние разделяме годишния залог и по-скоро връзката към клетката, в която се съдържа от номер 12, съответстващ на броя на месеците през годината. Разделяйте директно в полето на прозореца на аргумента.

   В полето CPER, кредитирането е настроено. Той е равен на 24 месеца. Можете да приложите ръчно полето номер 24, но ние, както в предишния случай, задайте връзка към местоположението на този индикатор в таблицата с източника.

   В областта "PS" показва първоначалната стойност на кредита. Тя е равна на 500 000 рубли. Както и в предишните случаи, ние определяме линк към листовия елемент, който съдържа този индикатор.

   В областта "BS" показва степента на заема след пълното си плащане. Както си спомняте, тази стойност почти винаги е нула. Инсталирайте в това поле номера "0". Въпреки че този аргумент обикновено може да бъде пропуснат.

   В полето "тип" ние уточняваме в началото или в края на месеца плащането е направено. Ние, както в повечето случаи, се произвежда в края на месеца. Затова поставяме номера "0". Както в случая с предишния аргумент, е възможно да влезете в това поле, след което програмата по подразбиране ще приеме, че е равно на него.

   След като бъдат въведени всички данни, натиснете бутона "OK".



4. След това в клетката, която разпределяхме в първия параграф на това ръководство, се показва резултатът от изчислението. Както можете да видите, величината на месечното общо плащане по кредита е 23536.74 рубли. Нека не объркате знака "-" преди тази сума. Така че изгнанието показва, че това е потокът от пари, т.е. загуба.



5. За да се изчисли общата сума на плащането за целия период на заема, като се вземе предвид възстановяването на органа на заема и месечен интерес, по-скоро умножаване на размера на месечното плащане (23536.74 рубли) за броя на месеците (24 месеца) \ t ). Както можете да видите, общият размер на плащанията за целия период на заема в нашия случай е 564881.67 рубли.



6. Сега можете да изчислите размера на надплащането на кредита. За да направите това, е необходимо да се отнеме от общия размер на плащанията по кредита, включително лихвите и органа на заема, първоначалната заявена сума. Но ние помним, че първата от тези ценности вече със знака "-". Ето защо, в конкретно, нашият случай се оказва, че те трябва да бъдат сгънати. Както виждаме, общата надплащане на заема през целия период е 64881.67 рубли.

Урок: Магистър на функциите в Excel

Етап 2: Детайли за плащане

И сега, с помощта на други оператори на Excel, ние правим месечни подробности за плащанията, за да видим колко в определен месец плащаме чрез органа на заема и колко е размерът на лихвата. За тези цели, ковач в таблицата с изгнание, която ще попълним данните. Линиите на тази таблица ще бъдат отговорни за съответния период, т.е. месеца. Като се има предвид, че периодът на кредитиране е 24 месеца, броят на редовете също ще бъде подходящ. Колоните посочват кредитен орган, лихвени плащания, общо месечно плащане, което е сумата от предишните две колони, както и оставащата сума за плащане.

1. За да се определи размера на плащането от страна на кредита, използвайте функцията OSP, която е просто предназначена за тези цели. Ние установяваме курсора в клетката, който се намира в линията "1" и в колоната "плащане от страна на заема". Кликнете върху бутона "Paste Function".



2. Отидете на капитана на функциите. В категорията "Финансови", ние отбелязваме името "OSPLT" и кликнете върху бутона "OK".



3. Аргументите на аргументите на оператора на OSP започват. Той има следния синтаксис:

   = OSPult (процент; период; kper; ps; bs)

   Както можем да видим, аргументите на тази функция почти съвпадат с аргументите на оператора PLT, само вместо незадължителния аргумент "тип" добави задължителен аргумент "период". Това показва броя на периода на плащане и в конкретния ни случай на броя на месеца.

   Попълнете аргументите на функциите на функциите на OSR, които вече са ни познат от същите данни, които са били използвани за PL функцията. Просто дават факта, че в бъдеще копирането на формула ще се използва чрез маркер за пълнене, трябва да направите всички връзки в полетата абсолютно, така че да не се променят. Това изисква да се постави знак за долар преди всяка стойност на вертикални и хоризонтални координати. Но е по-лесно да го направите, просто изберете координатите и кликнете върху функционалния ключ F4. Знакът на долара ще бъде поставен на правилните места автоматично. Ние също не забравяме, че годишният залог трябва да бъде разделен на 12.



4. Но имаме още един нов аргумент, който не е от PL функцията. Този аргумент "период". В съответното поле, задайте позоваване на първата клетка на колоната "Период". Този елемент на листа съдържа номера "1", който обозначава броя на първия месец на кредитиране. Но за разлика от предишните полета, оставяме връзката спрямо определеното поле и не правим абсолютно от него.

   След всички данни, за които говорихме по-горе, се въвеждат, натиснете бутона "OK".



5. След това, в клетката, която преди това сме разпределили, ще се появи размерът на плащането от органа на заема за първия месец. Тя ще бъде 18536.74 рубли.



6. След това, както е споменато по-горе, трябва да копираме тази формула към останалите колони, използвайки маркер за пълнене. За да направите това, поставете курсора в долния десен ъгъл на клетката, който съдържа формулата. Курсорът се превръща в кръст, който се нарича маркер за пълнене. Кликнете върху левия бутон на мишката и го издърпайте до края на таблицата.



7. В резултат на това всички клетъчни колони са запълнени. Сега имаме графика на плащането на заем месечно. Както бе споменато по-горе, размерът на плащането по този член се увеличава с всеки нов период.



8. Сега трябва да направим месечно изчисляване на плащанията от интерес. За тези цели ще използваме оператора на PRT. Ние разпределяме първата празна клетка в колоната "Плащания процент". Кликнете върху бутона "Paste Function".



9. В функциите на капитана на функциите в категорията "финансова" ние произвеждаме имената на Namp. Изпълнете бутона "OK".



10. Прозорецът на аргументите на функцията TRP започва. Неговият синтаксис изглежда така:

    = PRT (скорост; период; процесор; ps; bs)

    Както можем да видим, аргументите на тази функция са абсолютно идентични с подобни елементи на оператора на ОСП. Ето защо, просто въведете същите данни в прозореца, който влязохме в предишния прозорец на аргументите. Ние не забравяме, че позоваването в полето "период" трябва да бъде роднина и във всички други области координатите трябва да бъдат заведени в абсолютната форма. След това кликнете върху бутона "OK".



11. След това в съответната клетка се показва резултатът от изчисляването на размера на плащането за лихви за първия месец.



12. Прилагайки маркера за пълнене, направете копиране на формулата в останалите елементи на колоната, по този начин, получавайки месечен график за процентите за заема. Както можем да видим, както беше казано по-рано, от месец до месец стойността на този вид плащане намалява.



13. Сега трябва да изчислим цялото месечно плащане. За това изчисление човек не трябва да прибягва до всеки оператор, тъй като можете да използвате проста аритметична формула. Сгъваме съдържанието на клетките от първия месец на колоните "плащане от страна на заема" и "пълен интерес". За да направите това, задайте знака "=" в първата празна клетка на колоната "Общо месечно плащане". След това кликнете върху двата над елемента, като зададете знака "+" между тях. Кликнете върху клавиша Enter.



14. След това използвайте маркера за пълнене, както в предишните случаи, попълнете колоната за данни. Както можем да видим по време на цялото действие на договора, размерът на общото месечно плащане, което включва плащане от органа по заема и плащане на лихви, ще бъде 23536.74 рубли. Всъщност, ние вече изчислихме този индикатор, преди да използвате PPT. Но в този случай тя се представя по-ясно, точно като сума на плащането от страна на органа на заема и лихвите.



15. Сега трябва да добавите данни към колоната, където балансът на сумата на кредита се показва ежемесечно, което все още се изисква да платите. В първата клетка на колоната "баланс за плащане" изчислението ще бъде най-лесно. Трябва да бъдем отнета от първоначалния размер на кредита, който е посочен в таблицата с първични данни, плащането от органа по кредита за първия месец в изчислената таблица. Но предвид факта, че една от числата вече отиваме със знака "-", тогава те не трябва да се отнемат, а да се сгънат. Ние го правим и кликнете върху бутона ENTER.



16. Но изчисляването на баланса за плащане след второто и следващите месеци ще бъде малко по-сложно. За да направите това, трябва да отнемем от органа на заема до началото на отпускането на общия размер на плащанията от органа на заема за предходния период. Инсталирайте знака "=" във втората клетка на колоната "Дворецът да плати". След това посочете линк към клетката, която съдържа първоначалния размер на кредита. Ние го правим абсолютно, подчертаване и натискане на клавиша F4. След това поставяме знака "+", тъй като имаме второ значение и толкова отрицателно. След това кликнете върху бутона "Insert Function".



17. Стартира майсторът на функциите, в който трябва да се преместите в категорията "Математически". Там ние разпределяме надписа "Суми" и натиснете бутона "OK".



18. Прозорецът на аргументите започва функционалните аргументи. Посоченият оператор служи за обобщаване на данните в клетките, които трябва да изпълним в колоната "Плащане по кредита". Той има следния синтаксис:

    = Суми (номер1; номер2; ...)

    Като аргументи, препратки към клетки, в които се съдържат номера. Ние поставяме курсора в полето "номер1". След това закрепете левия бутон на мишката и изберете първите две клетки на колоната на кредитния корпус на листа. В полето, както виждаме, се появи връзка към диапазона. Състои се от две части, разделени от дебелото черво: препратки към първия диапазон от обхвата и на последния. За да можете да копирате определената формула в бъдеще от маркер за пълнене, ние правим първата връзка към абсолютния диапазон. Маркираме го и кликнете върху функционалния клавиш F4. Втората част на референцията и оставя роднина. Сега, когато използвате маркер за пълнене, първият диапазон от обхвата ще бъде фиксиран и последният ще се разтегне, докато се движи надолу. Това е необходимо за нас да изпълним целите. След това кликнете върху бутона "OK".



19. Така че, резултатът от баланса на кредитния дълг след втория месец се изхвърля в клетката. Сега, започнете с тази клетка, ние правим копирането на формулата в празни колони, използвайки маркер за пълнене.



20. Месечното изчисляване на останките за плащане на заема се прави за целия кредитен период. Както трябва да бъде, в края на крайния срок, тази сума е нула.

Така не просто изчислихме плащането на кредита, но организирахме един вид кредитен калкулатор. Което ще действа с анюитетна схема. Ако в таблицата с източника, например, променяме размера на кредита и годишния лихвен процент, след това в крайната таблица ще има автоматично преизчисляване на данни. Следователно, тя може да се използва не само веднъж за конкретен случай, а да се прилага в различни ситуации за изчисляване на кредитни варианти за схема за анюитет.

Урок: Финансови функции в Excel

Както можете да видите, използвайки програмата Excel у дома, можете лесно да изчислите цялостния месечен заем плащане на анюитеционната схема, като използвате PL оператора за тези цели. Освен това, с помощта на функции на OSR и PRT, е възможно да се изчисли размерът на плащанията от органа на заема и процентите за определения период. Прилагайки всички тези функции на багажа заедно, възможно е да се създаде мощен кредитен калкулатор, който може да се използва повече от веднъж, за да се изчисли анюитетното плащане.