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

Количественият анализ на финансовите данни в Excel по време на такива операции се свежда до изчисляването на следните основни характеристики:

текуща стойност (настояща стойност - PV)поток на плащане;

бъдещата стойност (бъдеща стойност - FV)поток на плащане;

сумата на отделно плащане (плащане - P);

норми на възвръщаемост (цени) под формата на лихвен процент (лихвен процент - r);

брой периоди финансова сделка(например години, месеци и т.н.).

Използваните методи се основават на техниката за изчисляване на сложна лихва.

Електронната таблица на Excel предоставя широки възможности за моделиране на такива изчисления с помощта на съответните финансови функции, вградени в Excel: BS(), NPER(), RATE(), PMT(), PS().

Всички функции от тази група имат еднакъв набор от основни аргументи:

ü лихвен процент (норма на възвръщаемост или цена на заетите средства);

ü срок(брой периоди) на операцията;

ü размера на периодичното плащане;

ü първоначална сума;

ü бъдеща стойност (стойност)Пари;

ü вид изчисляване на лихвата(1 - началото на периода, 0 - края на периода).

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

Да предположим, че вашата фирма реши да създаде специален фонд, за да изплаща своите дългосрочни задължения(кредити, заеми), чийто падеж ще дойде например след 5 години, чрез периодично (годишно) попълване на депозита в банката. Първоначалният размер на депозита е 10 000 хиляди рубли. Размерът на годишните плащания е 1000 хиляди рубли. Лихвен процент върху банкова сметка – 15%.

Необходимо е да се определи стойността на фонда до края на 5-та година.

За да решите задачата, направете следното:

1) Въведете необработените данни в работния лист. Диапазонът на таблица C3:C6 съдържа входни данни за изчисляване на стойността на паричния фонд, т.е. бъдеща стойностинвестиции (инвестиции).

2) Поставете курсора в клетката на работния лист, където ще бъде изчислена бъдещата стойност на инвестицията (депозита), в този случай в клетка C7.

3) Изпълнете командата Формули / Библиотека с функции / Вмъкване на функцияили щракнете върху бутона Ленти с формули. Категория Финансовиизберете функция от списъка BS() . Кликнете ДОБРЕ.

4) Excel ще покаже прозорец за въвеждане на аргументите на избраната функция. Във всяко поле за заявка въведете препратка към клетка, която съдържа желаната стойност.

BS() функция - позволява ви да определите бъдещата стойност на вноската (Бъдеща стойност - FV)въз основа на периодични постоянни плащания при зададени стойности на лихвения процент, броя на периодите на плащане и първоначалния размер на депозита.

Функцията има следния синтаксис:

=BS(норма; брой периоди; заплащане; nc; вид),

където: норма– лихвен процент (доходност на депозита);

брой периоди– срок (брой периоди) на операцията;

заплащане- размера на периодичното плащане;

nz- първоначалната цена на инвестициите (депозити);

тип– вид изчисляване на лихвата, е незадължителен аргумент.

(0 - в края на периода; 1 в началото на периода). По подразбиране лихвата се изчислява в края на периода.

Обърнете внимание на характеристиките на посочване на аргументи:

Ако лихвеният процент е зададен като абсолютна стойност, той трябва да бъде под формата на десетична дроб, например, както в примера: 15% - 0,15. Периодичното плащане и първоначалната сума са дадени със знак минус, т.к в тази операция за фирмата те означават изплащане (разход) на парични средства. Такива правила се прилагат за всички финансови функции. В зависимост от условието на задачата, стойностите на периодичното плащане и първоначалната цена могат да бъдат въведени във функцията под формата на положителни и отрицателни стойности. Зависи кой икономически субект извършва такива изчисления. Освен това можете да посочите крайния резултат, върнат от функцията, който се показва в долната част на диалоговия прозорец за въвеждане на аргумент, по време на стъпката за въвеждане на аргумент.

MS Excel, когато изчислява характеристиките на паричните анюитети, изразява всеки показател въз основа на следното съотношение:

където: HC– начална (настояща) стойност на депозита;

BS i– бъдещата стойност на депозита чрез броя на периодите аз;

норма– лихвен процент (норма на възвращаемост);

заплащане– периодично плащане;

аз– пореден номер на периода на поведение на финансовата операция;

тип– вид изчисляване на лихвата.

Така бъдещата стойност на инвестициите се определя по формулата:

За нашето предприятие бъдещата стойност на банковия депозит в края на 5-та година ще бъде както следва:

=BS(0,15;5;-1000;-10000) (Върнат резултат: 26855,95 хиляди рубли).

За банка, която определя бъдещия размер на възстановяването на този депозит, функцията ще изглежда така:

=BS(0,15;5;1000;10000) (Върнат резултат: -26855,95 хиляди рубли).

Функция NPER(). - ви позволява да определите броя на плащанията (постъпленията) на средства, ако са известни лихвеният процент, периодичното плащане, първоначалните и бъдещите стойности на платежните потоци.

Да предположим, че искате да определите броя на периодите на плащане (в този пример- брой години). Функцията ще изглежда така:

=NPER(0.15;1000;10000;26855.95)(Върнат резултат: 5),

където: 0,15 – лихвен процент по депозита; 1000 – периодично плащане; 10000 – първоначалната сума на депозита; 26855,95 е бъдещата стойност на депозита.

Функция RATE(). – изчислява лихвения процент, който в зависимост от условията на операцията може да действа или като цена, или като норма на възвръщаемост за тази операция. Има аргументи:

RATE(nper, pmt, ps, [bs], [тип], [прогноза])

където: nper- задължително. Общия брой периоди за плащане на годишното плащане;

плт- задължително. Извършено плащане във всеки период; тази стойност не може да се променя през целия период на плащане. Обикновено аргументът "plt" се състои от плащането на главницата и лихвата, но не включва други данъци и такси. Ако е пропуснат, аргументът "ps" е задължителен.

пс- задължително. Настояща (текуща) стойност, т.е. общата сума, която в момента е еквивалентна на определен брой бъдещи плащания;

bs -по желание. Стойността на бъдещата стойност, т.е. желания баланс на средства след последното плащане. Ако аргументът "bs" е пропуснат, се приема, че е 0 (например бъдещата стойност за заем е 0).

тип- по желание. Числото 0 или 1, показващо кога трябва да се извърши плащането (0 или пропуснато - в края на периода, 1 - в началото на периода;

прогноза- по желание. Очаквана стойност на залога. Ако аргументът на прогнозата е пропуснат, неговата стойност се приема за 10%.

Нека в нашия пример лихвеният процент по банков депозит е неизвестен. След това, за да го изчислим, използваме функцията ПРЕДЛОЖЕНИЕ() :

=СТАВКА(5;-1000;-10000;26855.95)(Върнат резултат: 15%).

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

PMT() функция - използва се в случай, че е необходимо да се определи размерът на периодичното плащане по заем за дадени стойности на бъдещата стойност на инвестициите въз основа на постоянни плащания, срок, лихвен процент и настояща стойност на инвестициите. Има следните аргументи:

PMT(ставка; nper; plt; [bs]; [тип])

Където: предложение -необходим аргумент. Лихва по кредит;

kper - озадължителен аргумент. Общият брой плащания по кредита;

пс - озадължителен аргумент. Настоящата стойност или общата сума, която в момента е равна на брой бъдещи плащания, наричана още основна сума;

bs - nнезадължителен аргумент. Желаната стойност на бъдещата стойност, т.е. желаното салдо след последното плащане. Ако този аргумент е пропуснат, се приема, че е 0 (бъдещата стойност на заема е 0);

типе незадължителен аргумент. Числото 0 (нула) или 1, което показва кога се дължи изплащането.

Формулата за изчисляване на периодичното плащане въз основа на горното съотношение ще бъде както следва:

Да предположим, че в нашия пример е необходимо да се определи размерът на периодичното плащане, като се имат предвид входните параметри. Функцията ще изглежда така:

=PLT(0,15;5;-10000;26855,95)(Върнат резултат: -1000).

Отрицателен резултат за фирмата означава изтичане на парични средства. За една банка е точно обратното.

PS() функция - ви позволява да определите текущ(т.е. в момента на започване на операцията - настояща стойност) разход за анюитет, ако са известни 4 задължителни параметъра (лихвен процент; брой периоди; първоначална цена; бъдеща стойност на средствата). Има следните аргументи:

PS(скорост, изчистване, plt, [bs], [тип])

където: предложение- задължително. Лихвен процент за периода. Например, ако се получи заем за автомобил при 10 процента годишно и плащанията се извършват месечно, лихвеният процент на месец ще бъде 10% / 12 (0,83%). Въведете 10%/12, 0,83% или 0,0083 във формулата като стойност на аргумента "ставка".

nper- задължително. Общият брой периоди на плащане за годишното плащане. Например, ако е получен заем за 4 години за закупуване на кола и плащанията се извършват месечно, тогава заемът има 4 * 12 (или 48) периода. Въведете числото 48 като стойност на аргумента "nper" във формулата.

плт- задължително. Плащане, извършвано всеки период, което не се променя през целия анюитетен период. Обикновено аргументът "plt" се състои от главница и лихвени плащания, но не включва други такси или данъци. Например месечно плащане по заем в размер на 10 000 рубли. при 12 процента годишно за 4 години ще бъде 263,33 рубли. Въведете числото -263,33 във формулата като стойност на аргумента "plt".

bs- по желание. Стойността на бъдещата стойност, т.е. желания баланс след последното плащане. Ако аргументът "bs" е пропуснат, се приема, че е 0 (например бъдещата стойност за заем е 0). Да предположим, че за конкретна цел е необходимо да натрупате 50 000 рубли. за 18 години: в този случай бъдещата стойност е 50 000 рубли. Ако приемем, че дадената лихва остава непроменена, можете да определите колко трябва да заделяте всеки месец. Ако аргументът "bs" е пропуснат, трябва да се използва аргументът "plt".

тип- по желание. Числото 0 или 1, показващо кога се дължи изплащането.

За условието на нашия проблем, използването на тази функция ни позволява да получим отговор на въпроса: „Каква сума трябва да бъде инвестирана в банка на депозит, за да получите депозит от 26 855,95 хиляди рубли за 5 години. с годишно попълване на депозита с 1000 хиляди рубли, ако годишният банков процент е 15%?

Формула за определяне на текущата (реалната) стойност на депозита (PS):

За нашия пример синтаксисът на функцията би бил:

=PS (0,15;5;-1000;26855,95)(Върнат резултат: -10000).

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

Да предположим, с тримесечно попълване на депозита и начисляване на лихви, функцията PS() ще приеме следната форма:

=PS (0,15/4;5*4;-1000;26855,95)(Върнат резултат: 1035,09).

Има стотици онлайн програми за финансово планиране. Всички те са лесни за използване, но с ограничена функционалност. MS Excel на техния фон е истински комбайн. Разполага с 53 финансови формули за всички случаи, като е полезно да знаете три от тях за контрол и планиране на бюджета.

PMT функция

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

PMT(ставка;nper;ps;bs;тип)

• Предложение- лихвата по кредита.
• Kperе общият брой плащания по кредита.
• Пс- стойността, дадена на текущия момент, или общата сума, която в момента е еквивалентна на редица бъдещи плащания, наричана още основна сума.
• Bs- необходимата стойност на бъдещата стойност или остатъка от средства след последното плащане. Ако аргументът "bs" е пропуснат, тогава се приема, че е 0 (нула), т.е. за заем, например, стойността "bs" е 0.

Функция RATE

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

RATE(nper;plm;ps;bs;тип;прогноза)

• Kper- общия брой периоди за плащане на годишното плащане.
• Plt- извършено плащане във всеки период; тази стойност не може да се променя през целия период на плащане. Обикновено аргументът "plt" се състои от плащането на главницата и лихвата, но не включва други данъци и такси. Ако е пропуснат, аргументът "ps" е задължителен.
• Пс- настоящата (текуща) стойност, т.е. общата сума, която в момента е еквивалентна на редица бъдещи плащания.
• bs (незадължителен аргумент)- стойността на бъдещата стойност, т.е. желания баланс на средства след последното плащане. Ако аргументът "fc" е пропуснат, се приема, че е 0 (например бъдещата стойност за заем е 0).
• Тип (незадължителен аргумент)- числото 0 (нула), ако трябва да платите в края на периода, или 1, ако трябва да платите в началото на периода.
• Прогноза (незадължителен аргумент)- очакваната стойност на залога. Ако аргументът на прогнозата е пропуснат, неговата стойност се приема за 10%. Ако функцията RATE не се сближава, опитайте да промените стойността на аргумента на прогнозата. Функцията RATE обикновено се събира, ако стойността на този аргумент е между 0 и 1.

Функция ЕФЕКТ

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

http://bg.excelfunctions.eu/%D0%91%D0%A1/A%D0%BD%D0%B3%D0%BB%D0%B8%D0%B9%D1%81%D0%BA%D0 %BE%D0%BC

http://office.microsoft.com/en-us/excel-help/HP010069823.aspx

bs функция

Приложим за: Microsoft Office Excel 2007

Тюлен

Покажи всички

Връща бъдещата стойност на инвестиция въз основа на периодични, постоянни (равни суми) плащания и постоянен лихвен процент.

Синтаксис

BS(предложение ;nper;плт;ps;тип)

За повече информация относно аргументите на функцията FN, както и за други финансови функции, вижте описанието на функцията PS.

Предложение- лихвен процент за периода.

Kper- общ брой периоди на анюитетно плащане.

Plt- извършено плащане във всеки период; тази стойност не може да се променя през целия период на плащане. Обикновено аргументът "plt" се състои от плащането на главницата и лихвата, но не включва други данъци и такси. Ако е пропуснат, аргументът "ps" е задължителен.

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

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

Забележки

Уверете се, че избраните мерни единици за аргументите "rate" и "nper" съвпадат. За месечни плащания по четиригодишен заем при 12 процента годишно използвайте 12%/12 за аргумента процент и 4*12 за аргумента nper. За годишни плащания по един и същ заем използвайте 12% за аргумента процент и 4 за аргумента nper.

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

Пример 1

Инструкции

ЗАБЕЛЕЖКА. Годишният лихвен процент се разделя на 12, тъй като сложната лихва се начислява ежемесечно.

PMT функция

Приложим за: Microsoft Office Excel 2007

Тюлен

Покажи всички

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

Синтаксис

PMT(предложение;nper;пс;bs; тип)

За по-подробно описание на аргументите на функцията PMT вижте описанието на функцията PS.

Предложение- лихвата по кредита.

Kperе общият брой плащания по кредита.

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

Bs- необходимата стойност на бъдещата стойност или остатък от средства след последното плащане. Ако аргументът bs е пропуснат, тогава се приема, че е 0 (нула), т.е. за заем, например, стойността на bs е 0.

Тип- числото 0 (нула) или 1, което показва кога трябва да се извърши изплащането.

Забележки

Плащанията, върнати от функцията PMT, включват главница и лихви, но не включват данъци, резервни плащания или такси, понякога свързани със заем.

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

съвет.За да намерите общата сума, изплатена по време на интервала на изплащане, умножете стойността, върната от функцията PMT, по „nper“.

Пример 1

За да направите този пример по-лесен за разбиране, копирайте го на празен лист.

Копиране на пример

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

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

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

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

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

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

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

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

PMT(ставка;nper;ps;bs;тип)

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

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

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

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

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

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

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

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



2. Прозорецът се стартира Помощници за функции. Категория "Финансови"подчертайте името "PLT"и щракнете върху бутона Добре.



3. Това ще отвори прозореца с аргументи на оператора. PMT.

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

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

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

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

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

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



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



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



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

Етап 2: подробности за плащането

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

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



2. Отидете на Съветник за функции. Категория "Финансови"забележете името "OSPLT"и натиснете бутона Добре.



3. Стартира се прозорецът с аргументи на OSPLT оператора. Има следния синтаксис:

   OSPLT(Ставка;Период;Nper;Ps;Bs)

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

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



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

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



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



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



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



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



9. В отворения прозорец Помощници за функциив категория "Финансови"избираме името HPMT. Извършване на натискане на бутон Добре.



10. Стартира се прозорецът с аргументи на функцията HPMT. Синтаксисът му изглежда така:

    RRP(ставка;период;Nper;Ps;Bs)

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



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



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



13. Сега трябва да изчислим общото месечно плащане. За това изчисление не трябва да прибягвате до никакъв оператор, тъй като можете да използвате проста аритметична формула. Добавяне на съдържанието на клетките от първия месец на колоните „Плащане върху тялото на кредита“и "Плащане на лихва". За да направите това, поставяме знака «=» до първата празна клетка в колоната "общо месечно плащане". След това кликваме върху горните два елемента, като поставяме знак между тях «+» . Кликнете върху ключа Въведете.



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



15. Сега трябва да добавите данни към колоната, която ще показва месечното салдо на сумата на кредита, която все още трябва да бъде платена. В първата клетка на колоната „Платим остатък“изчислението ще бъде най-лесно. От първоначалната сума на кредита, която е посочена в таблицата с първични данни, трябва да извадим плащането за тялото на кредита за първия месец в таблицата за изчисление. Но предвид факта, че един от номерата, които вече имаме, е подписан «-» , то те не трябва да се отнемат, а да се добавят. Направете го и кликнете върху бутона Въведете.



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



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



18. Стартира се прозорецът с аргументи на функцията SUM. Посоченият оператор служи за обобщаване на данните в клетките, което трябва да направим в колоната „Плащане върху тялото на кредита“. Има следния синтаксис:

    SUM(число1, число2,...)

    Аргументите са препратки към клетки, които съдържат числа. Поставяме курсора в полето "Номер 1". След това задръжте левия бутон на мишката и изберете първите две клетки от колоната на листа „Плащане върху тялото на кредита“. Както можете да видите, връзката към диапазона се показва в полето. Състои се от две части, разделени с двоеточие: препратка към първата клетка в диапазона и към последната. За да можем да копираме посочената формула с помощта на маркера за запълване в бъдеще, правим първата част от препратката към диапазона абсолютна. Изберете го и натиснете функционалния клавиш F4. Оставяме втората част на връзката относителна. Сега, когато използвате манипулатора за запълване, първата клетка в диапазона ще бъде фиксирана, а последната клетка ще се разшири, докато се движи надолу. Това е необходимо, за да постигнем целите си. След това щракнете върху бутона Добре.



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



20. Извършва се месечно изчисление на дължимите остатъци по кредита за целия кредитен период. Очаквано в края на мандата тази сума е нула.

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

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

Функцията PMT в Excel е включена в категорията "Финансови". Връща сумата на периодичното плащане за анюитет, като взема предвид постоянството на сумите на плащането и лихвения процент. Нека разгледаме по-подробно.

Синтаксис и характеристики на функцията PMT

Синтаксис на функцията: скорост; nper; ps; [bs]; [тип].

Разширение на аргумента:

• Процентът е лихвата по кредита.
• Nper е общият брой плащания по кредита.
• Ps е настоящата стойност, еквивалентна на серия от бъдещи плащания (сума на заема).
• Fs е бъдещата стойност на заема след последното плащане (ако аргументът е пропуснат, бъдещата стойност се приема за 0).
• Типът е незадължителен аргумент, който показва дали плащането се извършва в края на периода (стойност 0 или нищо) или в началото (стойност 1).

Характеристики на функционирането на PMT:

1. В изчислението на периодичните плащания са включени само главница и лихвени плащания. Данъци, комисионни, допълнителни вноски, резервни плащания, понякога свързани със заем, не се вземат предвид.
2. При задаване на аргумента "Процент" е необходимо да се вземе предвид честотата на изчисляване на лихвата. За кредит при 6% тримесечната лихва е 6%/4; за месечна ставка - 6% / 12.
3. Аргументът Nper указва общия брой плащания по заема. Ако дадено лице прави месечни плащания по тригодишен заем, тогава 3*12 се използва за задаване на аргумента.

Примери за PMT функция в Excel

За да работи правилно функцията, трябва да въведете правилно първоначалните данни:

Сумата на кредита е посочена със знак минус, т.к кредитната институция „дава“, „губи“ тези пари. За да запишете стойността на лихвения процент, трябва да използвате процентен формат. Ако е написано в цифри, тогава се използва десетичното число (0,08).

Натиснете бутона fx ("Вмъкване на функция"). Отваря се прозорецът на съветника за функции. В категорията "Финанси" изберете функцията PMT. Попълнете аргументите:

Когато курсорът е в полето на един или друг аргумент, отдолу се показва „подсказка“: какво трябва да се въведе. Тъй като изходните данни бяха въведени в електронна таблица на Excel, ние използвахме препратки към клетки със съответните стойности като аргументи. Но можете да въведете и числови стойности.

Забележка! В полето "Процент" стойността на годишната лихва се дели на 12: плащанията по кредита се извършват месечно.

Месечните плащания по заема в съответствие с условията, посочени като аргументи, възлизат на 1037,03 рубли.

За да намерите общата сума, която трябва да бъде изплатена за целия период (главница плюс лихва), умножете месечното плащане по кредита по стойността Nper:

PMT(Скорост;Брой_транс;Ps;[Bs];[Тип])

PS

Формула PSизползвани за изчисляване на настоящата стойност на инвестиция. Тази функция е обратна на оператора PMT. Той има абсолютно същите аргументи, но вместо аргумента на настоящата стойност ( "PS"), която е действително изчислена, се посочва сумата на периодичното плащане ( "Plt"). Синтаксисът съответно е:

PS(Ставка;Брой_за;Pmt;[Bs];[Тип])

NPV

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

NPV(ставка;стойност1;стойност2;…)

ПРЕДЛОЖЕНИЕ

функция ПРЕДЛОЖЕНИЕизчислява лихвения процент по анюитет. Аргументите на този оператор са броя на периодите ( "Col_per"), стойността на редовното плащане ( "Plt") и сумата на плащането ( "пс"). Освен това има допълнителни незадължителни аргументи: бъдеща стойност ( "BS") и посочва в началото или в края на периода плащането ще бъде извършено ( "Тип"). Синтаксисът приема следната форма:

RATE(Брой_на, Pmt, Ps[Bs], [Тип])

ЕФЕКТ

Оператор ЕФЕКТизчислява действителния (или ефективен) лихвен процент. Тази функция има само два аргумента: броя на периодите в годината, за които се прилага лихва, както и номиналния процент. Синтаксисът му изглежда така:

ЕФЕКТ(номинална_ставка, брой_на)

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

В статията се разглеждат финансовите функции PMT(), OSPLT(), PRPMT(), NPER(), RATE(), PS(), BS(), както и TOTAL INCOME() и TOTAL PAYMENT(), които се използват да се изчислят параметрите на анюитетната схема .

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

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

Малко теория

Анюитет (понякога се използват термините „анюитет“, „финансов анюитет“) е еднопосоченпаричен поток, елементите на който са същите в размери произведени чрез равни периоди от време(например, когато плащанията се извършват ежегодно на равни суми).

Синтаксис RPMT (ставка; период; nper; ps; bs; тип). (2.13)

Функционалните аргументи означават: предложение

месечен цикъл- задава периода, за който е необходимо да се намерят лихвени плащания, стойността трябва да бъде в диапазона от 1 до "nper";

nper- общ брой периоди на анюитетно плащане;

пс- стойността, намалена към настоящия момент или общата сума, която в момента е еквивалентна на редица бъдещи плащания, наричана още главница;

bs- необходимата стойност на бъдещата стойност или остатък от средства след последното плащане;

тип- числото 0 или 1, което показва кога трябва да се извърши изплащането. Ако този аргумент е пропуснат, се приема, че е 0.

Ако тази функция не е налична или връща грешка # ИМЕ ?, след това инсталирайте и изтеглете добавката "Analysis Toolkit". За да направите това, в менюто ОбслужванеИзберете екип Excel добавки.В списъка с добавки изберете Пакет за анализи натиснете бутона ДОБРЕ.Следвайте инструкциите на инсталатора, ако е необходимо.

Решение: HPMT (10% / 12, 1, 12 * 3; 800) = - 6,667 хиляди UAH.

Пример 2.28.Поради годишните удръжки за 6 години беше сформиран фонд от 500 хиляди UAH. Необходимо е да се изчисли какъв доход са донесли инвестициите на собственика през последната година, ако годишният процент е 17,5%.

Решение:Доход на Миналата година(6 периода) възлиза на:

HPMT (17,5%; 6; 6;; 500) = 66,48110268 хиляди UAH.

Предполагаше се PMT годишно (17,5%; 6;; 500) = - 53,627 хиляди UAH.

Размерът на главницата по кредита (погасяване на дълга), която се изплаща на равни вноски в края или в началото на всеки отчетен период, за посочения период се изчислява с помощта на функцията Excel OSPL:

Синтаксис OSPLT (ставка; период; nper; ps; bs; тип) (2.14)

или се намира като разликата между фиксирано периодично плащане и лихвата върху непогасената част от заема. Функционалните аргументи означават: предложение- лихвен процент за периода;

месечен цикъл- задава периода, като стойността трябва да е в диапазона от 1 до "nper";

nper- общ брой периоди на годишно плащане на анюитет;

пс- настоящата стойност, т.е. общата сума, която е еквивалентна на редица бъдещи плащания;

тип- числото 0 или 1, което показва кога трябва да се извърши изплащането.

Пример 2.29.Определете размера на главницата от двугодишен заем от 2000 UAH. за първия месец в размер на 10% годишно. Изчисляване на лихвата месечно.

Решение:Основна вноска по кредита за първия месец:

OSPLT (10% / 12, 1, 2 * 12; 2000) = - 75,62 UAH.

Натрупаният доход по заем (сумата на лихвените плащания), който се изплаща на равни вноски в края или в началото на всеки отчетен период, между два периода на плащане се изчислява в Excel чрез функцията ОБЩО ПЛАЩАНЕ.

Синтаксис ОБЩО ПЛАЩАНЕ (залог; nper; ps;

начален период; конпериод; тип). (2.15)

Функционалните аргументи означават: предложение- лихвен процент; nper пс начален_период -

краен_период -

типе моментът на плащането.

Пример 2.30.Публикувано заем за закупуване на недвижими имоти в размер на 125 000 UAH. за период от 30 години при 9% годишно, лихвата се начислява ежемесечно. Определете размера на лихвените плащания а) за втората година, б) за първия месец.

Решение:Кумулативното лихвено плащане за втората година (от 13-ия период до 24-ия) ще бъде:

ОБЩО ПЛАЩАНЕ (9% / 12; 30 * 12; 125 000; 13; 24; 0) = - 11135,23 UAH. Едно плащане за първия месец ще бъде:

ОБЩО ПЛАЩАНЕ (9% / 12; 30 * 12; 125 000, 1, 1, 0) = - 937,50 UAH Същата стойност ще се получи при изчисляване по формулата:

HPMT (9% / 12, 1, 30 * 12; 125 000) = - 937,50 UAH В Excel функцията TOTAL INCOME изчислява кумулативната (кумулативна) сума, платена за изплащане на главницата на заем между два периода:

Синтаксис ОБЩ ДОХОД (ставка; nper;

ps; начален период; конпериод; тип). (2.16)

Функционалните аргументи означават:

предложение- лихвен процент;

nperе общият брой периоди на плащане;

псе текущата стойност на инвестицията;

начален_период -това е номерът на първия период, включен в изчислението. Периодите за плащане се номерират от 1;

краен_период -това е номерът на последния период, включен в изчисленията;

типе моментът на плащането.

Пример 2.31.Публикувано заем в размер на 125 000 UAH. за период от 30 години при 9% годишно, лихвата се начислява ежемесечно. Определете размера на основните плащания: а) за първия месец; б) втората година (плащания от 13-ти период до 24-ти).

решение:

а) ОБЩ ДОХОД (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68,27827118 UAH;

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

ОБЩ ДОХОД (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934,1071234 UAH. Периодите от 13 до 24 съставляват втората година.