Розрахунок періодичних платежів

Функції EXCEL дозволяють обчислювати величини, зв'язані з періодичними виплатами:

1. періодичні платежі, здійснювані на основі постійної процентної ставки і не змінюються за увесь час розрахунку (функція ППЛАТ);

2. платежі по відсотках за конкретний період (функція ПЛПРОЦ);

3. суму платежів по відсотках за кілька періодів, що йдуть підряд (функція ОБЩПЛАТ);

4. основні платежі по позиці (за винятком відсотків) за конкретний період (функція ОСНПЛАТ);

5. суму основних платежів за кілька періодів, що йдуть підряд (функція ОБЩДОХОД).

Всі ці величини обчислюються, наприклад, при розрахунку схеми рівномірного погашення позики. Якщо позика погашається однаковими платежами наприкінці кожного розрахункового періоду, то майбутня вартість цих платежів буде дорівнює сумі позики з нарахованими відсотками до кінця останнього розрахункового періоду, при умові що в ньому передбачається повне погашення позики.

З іншого боку, що поточна вартість виплат по позиці повинна дорівнювати теперішній сумі позики. Якщо відома сума позики, ставка відсотка, термін, на який видана позика, то можна розрахувати суму постійних періодичних платежів, необхідних для рівномірного погашення позики за допомогою функції ППЛАТ.

Обчислені платежі містять у собі суму відсотків по непогашеній частині позики й основну виплату по позиці. Обидві величини залежать від номера періоду і можуть бути розраховані за допомогою функцій ПЛПРОЦ,
ОСНПЛАТ. Накопичені за декілька періодів величини обчислюють функції ОБЩПЛАТ і ОБЩДОХОД.

Нижче наведена схема погашення позики в 70000 грн., виданої терміном на 3 роки під 17 % річних, розрахована за допомогою фінансових функцій EXCEL. Розрахунки пояснюються в опису функцій.

Таблиця 7.2. Схема погашення позики

Рік	Сума позики на початок року	Загальна сума платежу	Платежі по відсотках	Сума основного платежу по позиці	Сума позики на кінець року
1	70000.00	31680.16	11900.00	19780.16	50219.84
2	50219.84	31680.16	8537.57	23142.78	27077.06
3	27077.06	31680.16	4603.10	27077.06	0
Разом		95040.47	25040.47	70000.00

7.3.5.1. Розрахунок постійних періодичних виплат.
      Функція ППЛАТ ( ПЛТ в Excel 2003 )

Функція обчислює величину виплати за один період на основі фіксованих періодичних виплат і постійної процентної ставки. Виплати, розраховані функцією ППЛАТ, включають основні платежі і платежі по відсотках. Значення функції ППЛАТ – це величина pmt з формули (3.1).

Синтаксис ППЛАТ(норма, кпер, нз, бс, тип).

Функція ППЛАТ застосовується в наступних розрахунках.

1. Нехай відома майбутня вартість фіксованих періодичних виплат, проведених на початку або наприкінці кожного розрахункового періоду. Потрібно розрахувати розмір цих виплат. Для цього можна використовувати формули (3.4) і (3.5,) (аргумент pmt ). Відповідна запис у EXCEL має вигляд:

ППЛАТ(норма, кпер, , бс, тип).

2. Припустимо, що розраховуються рівні періодичні платежі по позиці величиною нз, необхідні для повного погашення цієї позики через кпер число періодів. Поточна вартість цих виплат повинна дорівнювати поточній сумі позиці. Тому для розрахунку можна використовувати формули (3.8) і (3.9), виразивши з них аргумент pmt через поточну суму позики, ставку відсотка і число періодів. Відповідний розрахунок у EXCEL виконується по формулі:

ППЛАТ(норма, кпер, нз, ,тип).

Звичайне погашення відбувається наприкінці кожного розрахункового періоду. Для цього випадку формула має вигляд:

ППЛАТ(норма, кпер, нз),

Так як аргумент тип = 0.

Якщо позика погашається не цілком, тобто його майбутнє значення не дорівнює 0, то слід вказати аргумент бс, що буде дорівнює непогашеному залишку позики після усіх виплат.

Приклади.

Задача 1.

Припустимо, що необхідно нагромадити 4000 грн. за 3 роки, відкладаючи постійну суму наприкінці кожного місяця. Якою повинна бути ця сума, якщо норма відсотка по внеску складає 12 % річних.

Розв'язання.

Визначимо загальне число періодів нарахування відсотків і ставку відсотка за період по таблиці 7.1. Ці величини складуть відповідно 3×12 (аргумент кпер) і 12 %/12 (аргумент норма). Аргумент тип = 0, тому що за умовою це внески постнумерандо. Розрахуємо величину щомісячних виплат:

ППЛАТ(12 %/12, 12*3, , 4000) = – 92.86 грн.

Задача 2.

Нехай банк видав позичку 200 тис. грн. на 4 роки під 18 % річних. Позичка видана на початку року, а погашення починається наприкінці року однаковими платежами. Визначте розмір щорічного погашення позички.

Розв'язання.

Щорічні платежі складуть

ППЛАТ(18 %, 4, –200) = 74.35 тис. грн.

Зверніть увагу, що для банку видана позичка – це від'ємна величина, а обчислені щорічні надходження – позитивні значення.

Задача 3.

Розрахуємо колонку "Загальна сума платежу" таблиці 7.2.

Розв'язання.

ППЛАТ(17 %, 3, –70000) = 31680.16 грн.

 

Завдання для розрахунків

1. Визначте розміри періодичних внесків у фонд розміром 100 млн. грн., сформований за два роки щомісячними платежами, якщо процентна ставка складає 20 % річних.

Відповідь: 3422.91 тис. грн.

2. Визначте розмір щорічного погашення позики розміром 50 млн. грн., виданого на 3 роки під 38 % річних.
 Відповідь: 30.67 млн. грн.

 

7.3.5.2. Розрахунок платежів по відсотках.
               Функція ПЛПРОЦ ( ПРПЛТ в Excel 2003 )

Функція обчислює платежі по відсотках за заданий період на основі періодичних постійних виплат і постійної процентної ставки.

Синтаксис ПЛПРОЦ(норма, период, кпер, тс, бс, тип).

Функція призначена для наступних розрахунків.

1. При рівномірному погашенні позики постійна періодична виплата включає платежі по відсотках по непогашеній частині позики і виплату заборгованості. Так як непогашена частина позики зменшується в міру її погашення, то зменшується і частка платежів по відсотках у загальній сумі виплати, і збільшується частка виплати заборгованості. Щоб знайти розмір платежу по відсотках на конкретний період, слід використати формулу:

ПЛПРОЦ(норма, период, кпер, тс),

якщо погашення позики проводиться рівними платежами наприкінці кожного розрахункового періоду.

2. Нехай необхідно обчислити доход, що приносять постійні періодичні виплати за конкретний період. Цей доход являє собою суму відсотків, нарахованих на накопичену (з відсотками) до даного моменту сукупну величину вкладень. Розрахунок ведеться по формулі:

ПЛПРОЦ(норма, период, кпер, , бс, тип).

 

Приклади.

Задача 1.

Обчисліть платежі по відсотках за перший місяць від трирічної позики в 800 тис. грн. з розрахунку 10 % річних.

Розв'язання.

Визначаємо число періодів і ставку за період: норма = 10 %/12, кпер =12×3. Розрахунок робимо за перший період:

ПЛПРОЦ(10 %/12, 1, 12*3, 800) = – 6.667 тис. грн.

Задача 2.

Припустимо, що за рахунок щорічних відрахувань протягом 6 років був сформований фонд у 5 млн. грн. Визначити, який доход приносили вкладення власнику за останній рік, якщо річна ставка складала 17.5 %:

Розв'язання.

Доход за останній рік (6 період) склав

ПЛПРОЦ(17.5 %, 6, 6, 5000) = 664.81 тис. грн.

Щорічно відчислялося

ППЛАТ(17.5 %, 6, , 5000) = –536.27 тис. грн.

Задача 3.

Розрахуємо колонку "Платежі по відсотках" таблиці 3.4.

Розв'язання.

Розрахунок проводиться в такий спосіб. Платежі по відсотках за перший рік 70000 × 0.17 = 11900 грн. Із суми позики на початок року віднімаємо загальну суму платежу, одержуємо суму позики на кінець 1-го року і початок 2-го: 70000 – 31680.16 = 50219.84. По цій сумі нараховуємо 17 % за другий рік: 50219.84 × 0.17 = 8537.57. Аналогічно одержуємо суму відсотків за третій рік: 27077.06 × 0.17 = 4603.1. Розрахунок у EXCEL має вид:

ПЛПРОЦ(17 %, 1, 3, –70000) = 11900 грн.

ПЛПРОЦ(17 %, 2, 3, –70000) = 8537.57 грн.

ПЛПРОЦ(17 %, 3, 3, –70000) = 4603.10 грн.

 

7.3.5.3. Розрахунок суми платежів по відсотках по позиці.

       Функція ОБЩПЛАТ

Функція обчислює накопичений доход (суму платежів по відсотках) по позиці, що погашається рівними платежами наприкінці або початку кожного розрахункового періоду, між двома періодами виплат.

Синтаксис ОБЩПЛАТ(ставка, кол_пер, нз, нач_период, кон_период, тип).

Приклади.

Задача 1.

Нехай позика під нерухомість дана на наступних умовах: процентна ставка – 9 % річних; термін – 30 років, розмір позички – 125 000 грн., відсотки нараховуються щомісяця. Знайти суму виплат по відсотках за 2-й рік і за 1-й місяць позики.

Розв'язання.

Визначаємо по таблиці 7.1 загальне число виплат і процентну ставку за період. Ці величини рівні 30×12 і 9 %/12 відповідно. Другий рік платежів – це періоди від 13 по 24. Загальна виплата по відсотках за другий рік складе

ОБЩПЛАТ(9 %/12, 30*12, 125000, 13, 24, 0)= –11135.23 грн.

Одна виплата за перший місяць складе

ОБЩПЛАТ(9 %/12, 30*12, 125000, 1, 1, 0) = –937.5 грн.

Це ж значення буде отримано при розрахунку по формулі

ПЛПРОЦ(9 %/12, 1, 30*12, 125000).

 

7.3.5.4. Розрахунок основних платежів по позиці.
                  Функція ОСНПЛАТ. ( ОСПЛТ в Excel 2003 )

Функція обчислює величину основного платежу (виплати заборгованості) по позиці, що погашається рівними платежами наприкінці або початку кожного розрахункового періоду, на вказаний період.

Синтаксис ОСНПЛАТ(норма, период, кпер, тс, бс, тип).

Приклад.

Задача 1.

Розрахуємо за допомогою ОСНПЛАТ колонку "Сума основного платежу по позиці" таблиці 7.2.

Розв'язання.

Сума основного платежу по позиці (виплата заборгованості) визначається як різниця між фіксованою періодичною виплатою і відсотками по непогашеній частині позики: наприклад, для першого року

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 грн.

 

7.3.5.5. Розрахунок суми основних виплат по позиці.
Функція ОБЩДОХОД

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

Синтаксис

ОБЩЦОХОД(ставка, кол_пер, нз, нач_период, кон_период, тип).

Приклад.

Задача 1.

Нехай видана позичка розміром 1000 тис. грн. терміном на 6 років під 15 % річних; відсотки нараховуються щокварталу. Визначимо величину основних виплат за 5-й рік.

Розв'язання.

Визначаємо по таблиці 7.1 ставку відсотка за розрахунковий період
(15 %/4) і загальне число розрахункових періодів (6×4). Періоди з 17 по 20 складають п'ятий рік. Якщо позичка погашається рівними платежами наприкінці кожного розрахункового періоду, то розмір виплати заборгованості за п'ятий рік складе:

ОБЩЦОХОД(15 %/4, 6*4, 1000, 17, 20, 0) = –201.43 тис. грн.

 

Завдання для розрахунків

1. Розрахуйте таблицю погашення позики розміром 300 млн. грн., виданого на півроку під 20 % річних, якщо відсотки нараховуються щомісяця. Використовуйте функції ППЛАТ, ПЛПРОЦ, ОСНПЛАТ.

 

7.4. Визначення швидкості обороту інвестицій

EXCEL містить функції, що дозволяють розрахувати:

1. внутрішню швидкість обороту для ряду послідовних періодичних надходжень і виплат змінної величини (функція ВНДОХ);

2. внутрішню швидкість обороту для ряду нерегулярних надходжень і виплат змінної величини (функція ЧИСТВНДОХ);

3. внутрішню швидкість обороту для ряду періодичних надходжень і виплат змінної величини з урахуванням доходу від реінвестування (функція МВСД).

Функції ВНДОХ і ЧИСТВНДОХ обчислюють ітераційним методом норму дисконтування R, при якій чиста поточна вартість (NPV) дорівнює 0. Якщо відома ринкова норма доходу k, то обчислене значення можна використовувати як оцінку доцільності прийняття того чи іншого проекту вкладення засобів.

Проект приймається, якщо R>k і відкидається, якщо R<k. Підставою для такого рішення є те, що при R<k очікуваних доходів від проекту виявляється недостатньо для покриття усіх фінансових платежів, і прийняття такого проекту є економічно недоцільним. Відповідно, при R>k інвестор за рахунок доходів від проекту зможе не тільки виконати усі фінансові зобов'язання, але і отримати додатковий прибуток. Очевидно, що такий проект є економічно доцільним і його варто прийняти.

 

 

Функція ВНДОХ ( ВСД в Excel 2003 )

(4.1)

Функція обчислює внутрішню швидкість обороту інвестиції (внутрішню норму прибутковості) для ряду періодичних виплат і надходжень змінної величини. Значення функції обчислюється з формули (3.10) для NPV = 0:

 

де п – кількість виплат і надходжень;

value_і – значення виплат і надходжень;

R – внутрішня швидкість обороту.

Синтаксис ВНДОХ(значения, предположение).

Починаючи зі значення предположение, функція ВНДОХ виконує циклічні обчислення, поки не одержить результат з точністю 0.00001 відсотка. Якщо функція ВНДОХ не зможе одержати результат після 20 спроб, то повертає значення помилки #ЧИСЛО!.

У більшості випадків немає необхідності задавати аргумент предположение для обчислень за допомогою функції ВНДОХ. За замовчуванням аргумент предположение покладається рівним 0.1 (10 %). Якщо ВНДОХ видає значення помилки #ЧИСЛО! або якщо результат далекий від очікуваного, можна спробувати виконати обчислення ще раз з іншим значенням аргументу предположение .

Функції НПЗ і ВНДОХ взаємозалежні:

НПЗ(ВНДОХ(В1:В6),В1:В6) = 0

для однакових значень виплат і надходжень, що знаходяться в клітинках В1:В6.

Приклади.

Задача 1.

Припустимо, що витрати по проекту складуть 500 млн. грн. Очікувані доходи складуть 50 млн. грн., 100 млн. грн., 300 млн. грн., 200 млн. грн. протягом наступних 4 років. Оцінити економічну доцільність проекту по швидкості обороту інвестиції, якщо ринкова норма доходу 12 %.

Розв'язання.

Нехай клітинки А1:А5 містять значення –500, 50, 100, 300, 200. Внутрішня швидкість обороту інвестиції складе

ВНДОХ(А1:А5) = 9.25 %.

Це менше, ніж ринкова норма, тому проект повинен бути відкинутий.

Графічна інтерпретація даної задачі представлена нижче. Інвестиція розміром 500 млн. грн. (від'ємне значення на графіку) принесе доходи за наступні п'ять років, показані на графіку додатними значеннями, сумарна величина яких більше початкових витрат, але поточна вартість дорівнює їм при швидкості обороту інвестиції 9.25 %.

Рис. 7.1. Схема грошових потоків

 

Задача 2.

Очікується, що доходи по проекту протягом наступних 4 років складуть відповідно 50000 грн., 100000 грн., 300000 грн., 200000 грн. Визначити, якими повинні бути початкові витрати, щоб забезпечити швидкість обороту 10 %.

Розв'язання.

Для розв'язання такої задачі необхідно використовувати апарат підбору параметра пакета EXCEL, що викликається командою Сервис Þ Подбор параметра, так як відсутня відповідна фінансова функція EXCEL.

Нехай клітинки А2:А5 містять значення 50000, 100000, 300000, 200000. Помістимо в клітинку А1 прогнозоване значення витрат –300000. В клітинку В1 введемо функцію ВНДОХ(А1:А5). Встановивши курсор в клітинку В1, вибираємо в меню EXCEL команду Сервис Þ Подбор параметра і заповнюємо діалогове вікно в такий спосіб:

 

У результаті в клітинці А1 буде отримане значення витрат по проекту: А1 = –489339.

 

Завдання для розрахунків

1. Розрахуйте внутрішню швидкість обороту інвестиції, якщо витрати по проекту склали 200 млн. грн., а очікувані доходи в наступні 5 років складуть відповідно: 40, 60, 80, 90 і 100 млн. грн. Оцініть економічну ефективність проекту, якщо ринкова норма доходу складає 10 %.
Відповідь: 21 % > 10 % , тому проект економічно доцільний.

2. Визначте, якими повинні бути початкові витрати по проекту, щоб забезпечити такі доходи: 2, 5, 6, 8 і 10 млн. грн. при нормі доходу по проекту 9 %. Використайте апарат Подбор параметра.
Відповідь: 22.82 млн. грн.

 

Функція ЧИСТВНДОХ

Функція обчислює внутрішню швидкість обороту для ряду нерегулярних надходжень і виплат змінної величини. Значення, обчислене функцією
ЧИСТВНДОХ, – це процентна ставка, що відповідає чистій поточній вартості,

(4.2)

яка рівна нулю (формула (3.11)):

де п – кількість виплат і надходжень;

d_i – дата i-ої операції;

d₀ – дата 0-ої операції (початкова дата);

value_i – сума i-ої операції;

R – внутрішня швидкість обороту.

Синтаксис ЧИСТВНДОХ({сумма0; сумма1; ... ; сумма N}, {дата1; дата2; ...; датаN}, предп).

Метод обчислення такий же, що і для функції ВНДОХ. Функції
ЧИСТВНДОХ і ЧИСТНЗ взаємозалежні: для однакових значень надходжень (виплат) і дат

ЧИСТНЗ(ЧИСТВНДОХ (...), ...)= 0.

 

Приклади.

Задача 1.

Розглянемо дані задачі для функції ЧИСТНЗ. Визначимо, при яких ринкових умовах цей проект буде економічно доцільний.

Розв'язання.

Обчислимо внутрішню швидкість обороту. Ставка доходу, що відповідає нульовому XNPV, буде дорівнювати ЧИСТВНДОХ(A2:E2,А1:Е1) = 37.49 %. Цей проект економічно доцільний, якщо ринкова норма доходу менше, ніж обчислене значення (k< 37.49 %).

 

 

Функція МВСД

(4.3)

Функція повертає модифіковану внутрішню швидкість обороту засобів для ряду періодичних надходжень і виплат змінної величини. При цьому враховується як вартість інвестиції, так і доход, одержуваний від реінвестування. Формула для обчислення функції МВСД має вигляд:

де п – загальне число виплат і надходжень;

value_і ^p – додатні значення (надходження);

value_і ^m – від'ємні значення (виплати);

r – норма прибутку, виплачуваного за гроші, що знаходяться в обороті;

f – норма прибутку, одержуваного за гроші в обороті при реінвестуванні.

Синтаксис МВСД(значения, финансовая_норма, реинвест_норма).

Аргумент значення повинен містити принаймні одне додатнє й одне від'ємне значення для того, щоб можна було обчислити модифіковану внутрішню швидкість обороту. У противному випадку функція МВСД повертає значення помилки #ДЕЛ/0!

 

Приклади.

Задача 1.

Припустимо, що п'ять років тому була узята позичка в розмірі 1 млрд. грн. під 10 % річних для фінансування проекту, прибуток по якому за ці роки склав відповідно: 100, 270, 450, 340 і 300 млн. грн. Ці гроші були реінвестовані під
12 % річних. Знайти модифіковану внутрішню швидкість обороту інвестиції.

Розв'язання.

Нехай на робочому листі позика введена як –1000 в клітинці В1, і в клітинки В2:В6 введені значення прибутку за кожний рік. Тоді модифікована внутрішня швидкість обороту за п'ять років обчислюється в такий спосіб:

МВСД(В1:В6, 10 %, 12 %) = 12.25 %.

Модифікована внутрішня швидкість обороту за п'ять років, якби ставка реінвестування складала 14 % річних, обчислюється в такий спосіб

МВСД(В1:В6, 10 %, 14 %) = 12.99 %.

 

⇐ Предыдущая1234567Следующая ⇒

Поделиться: