Excel

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

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

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

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

  • диференцирана;
  • Рента.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    2. Отидете в съветника за функции в Microsoft Excel

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

    3. Отворете прозореца с аргументи на функцията на PLC в Microsoft Excel

  3. След това се отваря прозорецът на аргументите на оператора на PLT .

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

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

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

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

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

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

    4. Прозорецът на аргументите на функцията на PLT в Microsoft Excel

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

    5. Резултат от изчисляването на месечното плащане в Microsoft Excel

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

    6. Общ размер на плащанията в Microsoft Excel

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

Сума за надплащане за заем в Microsoft Excel

Урокът: Съветник за функции в Excel

Стъпка 2: подробно описание на плащанията

А сега с помощта на други оператори на Axel ще направим месечни данни за плащанията, за да видим колко в определен месец плащаме на кредитния орган и колко е интересът. За тази цел изготвяме таблица в Excel, която ще попълним с данни. Редовете на тази таблица ще съответстват на съответния период, т.е. на месеца. Като се има предвид, че периодът на кредитиране за нас е 24 месеца, броят на редовете също ще е подходящ. В графите се посочват плащанията на органа по заема, изплащането на лихвата, общото месечно плащане, което е сумата от предходните две колони и оставащата сума, която трябва да бъде платена.

Таблица на изплащанията в Microsoft Excel

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

    2. Вмъкване на функция в Microsoft Excel

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

    3. Отворете прозореца с аргументи на функцията OSPLT в Microsoft Excel

  3. Появява се прозорецът на аргументите на оператора OSPLT. Той има следния синтаксис:

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

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

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

    4. Прозорецът на аргументите на функцията OBSFT в Microsoft Excel

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

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

    5. Период аргумент в прозореца на аргументите на функцията OBSF в Microsoft Excel

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

    6. Резултатът от изчисляването на функцията на OSPLT в Microsoft Excel

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

    7. Филтър в Microsoft Excel

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

    8. Размерът на плащането за тялото на заема на месец в Microsoft Excel

  8. Сега трябва да направим месечно изчисление на лихвените плащания. За тези цели ще използваме оператора PRPLT . Изберете първата празна клетка в графата "Плащане на лихви" . Кликнете върху бутона "Вмъкване на функция" .

    9. Отидете в съветника за функции в Microsoft Excel

  9. В прозореца на Wizards of functions в категорията "Financial" ние избираме името на PRPLT . Кликнете върху бутона "OK" .

    10. Отидете в прозореца на аргументите на функцията PRPLT в Microsoft Excel

  10. Прозорецът на аргументите на функцията PRPLT се стартира . Синтаксисът му е както следва:

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

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

    11. Прозорец на аргументите на функцията на PRPLT в Microsoft Excel

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

    12. Резултатът от изчисляването на функцията на PRPLT в Microsoft Excel

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

    13. Графикът на плащанията по лихви за заем в Microsoft Excel

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

    14. Сума от общото месечно плащане в Microsoft Excel

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

    15. Общо месечно плащане в Microsoft Excel

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

    16. Баланс, който трябва да бъде платен след първия месец на кредитиране в Microsoft Excel

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

    17. Вмъкване на функция в Microsoft Excel

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

    18. Отворете прозореца с аргументи на функцията SUM в Microsoft Excel

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

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

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

    19. Прозорецът на аргументите на функцията SUM в Microsoft Excel

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

    20. Филтър в Microsoft Excel

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

Изчисляване на салдото за плащане на заема в Microsoft Excel

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

Оригиналните данни са променени в Microsoft Excel

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

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