Розрахунок періодичних платежів. Оцінка інвестицій в MS Excel
Функції EXCEL дозволяють обчислювати такі величини, пов'язані з періодичними виплатами:
1) періодичні платежі, що здійснюються на основі постійної процентної ставки і не мінливі за весь час розрахунку (функція ПЛТ);
2) платежі за відсотками за конкретний період (функція ПРПЛТ);
3) суму платежів по відсотках за кілька періодів, що йдуть підряд (функція ОБЩПЛАТ);
4) основні платежі за позикою (за вирахуванням відсотків) за конкретний період (функція ОСПЛТ);
5) суму основних платежів за кілька періодів, що йдуть підряд (функція ОБЩДОХОД).
Всі ці величини обчислюються, наприклад, при розрахунку схеми рівномірного погашення позики. Припустимо, що позика погашається однаковими платежами в кінці кожного розрахункового періоду. Майбутня вартість цих платежів буде дорівнює сумі позики з нарахованими відсотками до кінця останнього розрахункового періоду, якщо в ньому передбачається повне погашення позики.
Поточна вартість виплат за позикою повинна дорівнювати справжньою сумі позики. Якщо відома сума позики, ставка відсотка, термін, на який видана позика, то можна розрахувати суму постійних періодичних платежів, необхідних для рівномірного погашення позики за допомогою функції ПЛТ.
Обчислені платежі включають в себе суму відсотків по непогашеної частини позики і основну виплату по позиці. Обидві величини залежать від номера періоду і можуть бути розраховані за допомогою функцій ПРПЛТ, ОСПЛТ. Накопичені за кілька періодів величини обчислюють функції ОБЩПЛАТ і ОБЩДОХОД.
Нижче наведена схема погашення позики в 70000 р. виданого терміном на 3 роки під 17% річних, розрахована за допомогою фінансових функцій EX
Таблиця 3 - Схема погашення позики
Сума позики на початок року
Функція ПЛТ обчислює величину виплати за один період на основі фіксованих періодичних виплат і постійної відсоткової ставки. Виплати, розраховані функцією ПЛТ, включають основні платежі і платежі за відсотками.
ПЛТ (ставка; кпер; пс; бс; тип)
Ставка - відсоткова ставка за позикою.
Кпер - загальне число виплат за позикою.
Пс - поточне значення (загальна сума, яку складуть майбутні платежі, звана також основною сумою),
Бс - майбутня сума (баланс готівки, якої потрібно досягти після останньої виплати). Якщо БС опущено, воно вважається рівним 0 (нулю), тобто майбутня сума позики, наприклад, дорівнює 0.
Тип - число 0 або 1, що означає, коли повинна проводитися виплата.
Припустимо, що необхідно накопичити 4000 р. за 3 роки, відкладаючи постійну суму в кінці кожного місяця. Якою має бути ця сума, якщо норма відсотка по вкладу складає 12% річних.
Рішення. Визначимо загальне число періодів нарахування відсотків і ставку відсотка за період з таблиці 1. Ці величини складуть відповідно 3 * 12 (аргумент кпер) і 12% / 12 (аргумент норма). Аргумент тип = 0, тому що за умовою це вклади постнумерандо. Розрахуємо величину щомісячних виплат:
ПЛТ (12% / 12,12 * 3 "4000) = -92,86р.
Припустимо, банк видав позику 200 млн. Р. на 4 роки під 18% річних. Позика видана на початку року, а погашення починається в кінці року однаковими платежами. Визначте розмір щорічного погашення позики.
Рішення. Щорічні платежі складуть:
Зверніть увагу, що для банку видана позика - це негативна величина, а обчислені щорічні надходження - позитивні значення.
Функція ПРПЛТ обчислює суму платежу за відсотками за заданий період на основі періодичних постійних виплат і постійної відсоткової ставки.
ПРПЛТ (ставка; період; кпер; пс; бс; тип)
Ставка - відсоткова ставка за період.
Період - період, для якого потрібно знайти прибуток; повинен знаходитися в інтервалі від 1 до кпер.
Кпер - загальне число періодів виплат річної ренти.
Пс - поточна вартість (загальна сум всіх майбутніх платежів з цього моменту).
Бс - майбутня вартість або баланс готівки, який потрібно досягти після останньої виплати. Якщо аргумент БС опущені, то передбачається, що він дорівнює 0 (наприклад, бз для позики дорівнює 0).
Тип - число 0 або 1, що означає, коли повинна проводитися виплата.
Обчисліть платежі за відсотками за перший місяць від трирічного позики в 800 тис. Р. з розрахунку 10% річних.
Рішення. Визначаємо число періодів і ставку за період: норма = 10% / 12, кпер = 12 * 3. Розрахунок проводимо за перший період:
ПРПЛТ (10% / 12,12 * 3,800) = -6,667 тис. Р.
Припустимо, що за рахунок щорічних відрахувань протягом 6 років був сформований фонд в 5 000 р. Визначимо, який дохід приносили вкладення власнику за останній рік, якщо річна ставка становила 17,5%:
Рішення. Дохід за останній рік (шостий період) склав:
ПРПЛТ (17,5%, 6,6 "5000) = 664,81 р.
Щорічно відраховувалося ПРПЛТ (17,5%, 6 "5000) = -536,27 р.
Функція.ОБЩПЛАТ обчислює накопичений дохід (суму платежів за відсотками) по позиці, який погашається рівними платежами в кінці або на початку кожного розрахункового періоду, між двома періодами виплат.
ОБЩПЛАТ (ставка; кол_пер; нз; нач_період; кон_період; тип)
Ставка - відсоткова ставка.
Кол_пер - загальна кількість періодів виплат.
Нз - поточне значення інвестиції.
Нач_період - номер першого періоду, який бере участь в обчисленнях. Періоди виплат нумеруються починаючи з 1.
Кон_період - номер останнього періоду, який бере участь в обчисленнях.
Тип - визначає, в який момент здійснюється виплата.
Нехай позику під нерухомість зроблений на наступних умовах: відсоткова ставка - 9% річних; терміном - 30 років, розмір позики - 125 000 тис. р. відсотки нараховуються щомісяця. Знайти суму виплат за відсотками за 2-й рік і за 1-й місяць позики.
Рішення. Визначаємо по таблиці 3.3 загальна кількість виплат і процентну ставку за період. Ці величини рівні 30 • 12 і 9% / 12 відповідно. Другий рік платежів - це періоди від 13 до 24. Загальна виплата за відсотками за другий рік складе
Одна виплата за перший місяць складе:
ОБЩПЛАТ (9% / 12,30 • 12,125000,1,1,0) = -937,5 тис. Р.
Це ж значення буде отримано при розрахунку за формулою:
ПРПЛТ (9% / 12, 1, 30 • 12, 125000)
Функція ОСПЛТ обчислює величину основного платежу (виплати заборгованості) по позиці, який погашається рівними платежами в кінці або на початку кожного розрахункового періоду, на вказаний період.
ОСПЛТ (ставка; період; кпер; пс; бс; тип)
Ставка - відсоткова ставка за період.
Період - задає період, значення повинно бути в інтервалі від 1 до кпер.
Кпер - загальне число періодів виплат річної ренти.
Пс - поточне значення s загальна сума, яку складуть майбутні платежі,
Бс - майбутня вартість або баланс готівки який потрібно досягти після останньої виплати. Якщо БС опущено, воно вважається рівним 0 (нулю), тобто майбутня вартість позики, наприклад, дорівнює 0.
Тип - число 0 або 1 позначає, коли повинна проводитися виплата.
Розрахуємо за допомогою ОСПЛТ колонку «Сума основного платежу по позиці» таблиці 3.
Рішення. Сума основного платежу за позикою (виплата заборгованості) виходить як різниця між фіксованою періодичною виплатою і відсотками по непогашеної частини позики: наприклад, для першого року
31680.16- 11900 = 19780.16тис. р.
Розмір основних виплат по позиці за допомогою функції ОСПЛТ EXCEL визначається так:
ОСПЛТ (17%, 1, 3, 70000) = -19 780,16 р.
ОСПЛТ (17%, 2, 3, 70000) = -23 142,78 р.
ОСПЛТ (17%, 3,3, 70000) = -27 077,06р.
Функція ОБЩДОХОД обчислює суму основних виплат по позиці, який погашається рівними платежами в кінці або на початку кожного розрахункового періоду, між двома періодами.
ОБЩДОХОД (ставка; кол_пер; нз; нач_період; кон_період; тип)
Ставка - відсоткова ставка.
Кол_пер - загальна кількість періодів виплат.
Нз - поточне значення інвестиції.
Нач_період - номер першого періоду, який бере участь в обчисленнях. Періоди виплат нумеруються починаючи з 1.
Кон_період - номер останнього періоду, який бере участь в обчисленнях.
Тип - визначає, в який момент здійснюється виплата.
Припустимо, видана позика розміром 1000 тис. Р. терміном на 6 років під 15% річних; відсотки нараховуються щоквартально. Визначимо величину основних виплат за 5-й рік.
Рішення. Визначаємо за таблицею 3 ставку відсотка за розрахунковий період (15% / 4) і загальне число розрахункових періодів (6 * 4). Періоди з 17 по 20 складають п'ятий рік. Якщо позика погашається рівними платежами в кінці кожного розрахункового періоду, то розмір виплати заборгованості за п'ятий рік составіт-
ОБЩДОХОД (15% / 4,6 • 4, 1000, 17, 20, 0) = -201.43 тис р