Використання функцій ПЛТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД

Лабораторна робота № 6

Розрахунок періодичних платежів, пов'язаних з погашенням позик.

Використання функцій ПЛТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД.

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

Розрахунок періодичних платежів, пов'язаних з погашенням позик

Серед фінансових функцій Excel виділяються функції, пов'язані з періодичними виплатами:

ПЛТ (ставка; кпер; пс; бс; тип)
ПРПЛТ (ставка; період; кпер; пс; бс; тип)
ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период; тип)
ОСПЛТ (ставка; період; кпер; пс; бс; тип)
ОБЩДОХОД (ставка; кол_пер; нз; нач_период; кон_период; тип)

Функція ПЛТ розраховує суму періодичного платежа для ануїтету на основі постійності сум платежів і процентної ставки.

Платежі за процентами за заданий період на основі періодичних постійних виплат і постійної процентної ставки розраховуються за допомогою функції Excel ПРПЛТ

Сума основного платежу за позикою (виплата заборгованості), який погашається рівними платежами в кінці або на початку кожного розрахункового періоду, на вказаний період розраховується за допомогою функції Excel ОСПЛТ.

Накопичений дохід з позики (суму платежів по процентах), яка погашається рівними платежами в кінці або на початку кожного розрахункового періоду, між двома періодами виплат розраховує в Excel функція ОБЩПЛАТ.

Функція ОБЩДОХОД розраховує кумулятивну (наростаючим підсумком) суму, яка виплачується в погашення основної суми позики в проміжку між двома періодами:

Завдання 1. Клієнтові банку необхідно накопичити 400 000 грн. за 4 роки. Клієнт зобов'язується вносити на початку кожного місяця постійну суму під 9% річних. Якою повинна бути ця сума?

Розв’язання Для визначення щомісячних виплат застосовується функція ПЛТ з аргументами: Ставка = 9%/12; Кпер = 4*12 = 24; Бс = 400; Тип = 1, оскільки внески пренумерандо. Тоді величина щомісячних виплат дорівнює:

= ПЛТ (9%/12; 48;; 400; 1) = - 6,902 тис. грн(див. рис.1).

Рис. 1. Застосування функції ПЛТ

Завдання 2. Клієнт банку здійснює позику у розмірі 5000 грн. під 6% річних на 6 місяців. Визначити щомісячні платежі клієнта. Платежі здійснюються в кінці місяця.

Розв’язання Для визначення щомісячних платежів клієнта скористаємося функцією ПЛТ

= ПЛТ (6%/12; 6; -5000) = 847,98 грн.

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

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

Розв’язання Для визначення платежу по відсотках за перший місяць заданого періоду застосуємо функцію ПРПЛТ з наступними аргументами:

Ставка = 10%/12; Період = 1; Кпер = 3*12 = 36; Пс = 100 000.

Тоді платежі по відсотках за перший місяць

= ПРПЛТ (10%/12; 1; 36; 100000) = - 833,33 грн(див. рис.2).

Знак «мінус» означає, що платіж по відсотках необхідно внести.

Рис. 2. Фрагмент вікна з використанням функції ПРПЛТ

Завдання 4. Клієнт щорічно протягом 5 років вносив гроші на свій рахунок в банку і накопичив 40 000 грн. Визначити, який дохід отримав клієнт банку за останній рік, якщо річна ставка склала 13,5%.

Розв’язання Дохід за останній п'ятий рік є сумою відсотків, нарахованих на накопичену суму вкладень. Для обчислень скористаємося функцією ПРПЛТ:

= ПРПЛТ(13,5%; 5; 5;; 40000)= 4030,77 грн.

Відмітимо, що при розв’язанні даної задачі значення аргументів функції ПРПЛТ Бс і Тип не вказуються (вважаються рівними 0).

Завдання 5. Визначити значення основного платежу для першого місяця дворічної позики в 60000 грн. під 12% річних.

Розв’язання Сума основного платежу по позиці обчислюється за допомогою функції ОСПЛТ:

= ОСПЛТ (12%/12; 1; 24; 60000) = - -2 224,41руб.

Розв’язання задачі за допомогою функції ОСПЛТ представлено на рис. 3.

Рис. 3. Фрагмент вікна з використанням функції ОСПЛТ

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

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

Наприклад, для раніше наведеного завдання 2 щомісячна виплата клієнта складає: = ПЛТ (6%/12; 6; -5000) = 847,98 грн.

Розмір основного платежу: = ОСПЛТ (6%/12; 1; 6; -5000) = 822,98 руб

Розмір платежу по відсотках: = ПРПЛТ (6%/12; 1; 6; -5000) = 25,00 грн.

Завдання 6. Організація взяла позику в банку в розмірі 500 тис. грн. на 10 років під 10,5% річних; відсотки нараховуються щомісячно.

Визначити суму виплат по відсотках за перший місяць і за третій рік періоду.

Розв’язання Для обчислення суми платежів по відсотках за необхідні періоди скористаємося функцією ОБЩПЛАТ (рис. 4).

Аргументи функції: Кол_пер = 10*12 = 120 місяців; Ставка = 10,5%/12; Нз = 500000; Тип = 0; для виплати відсотків за 1-й місяць Нач_период = 1 і Кон_период = 1. Для виплати відсотків за 3-й рік Нач_период = 25 і Кон_период = 36.

Виплата за перший місяць складе:

= ОБЩПЛАТ(10,5%/12; 120; 500; 1; 1; 0)= - 4,375 тис. грн.
Сума виплат по відсотках за третій рік періоду складе:
= ОБЩПЛАТ (10,5%/12; 120; 500; 25; 36; 0) = - 44,143 тис. грн.

Рис. 4. Фрагмент вікна з використанням функції ОБЩПЛАТ

Завдання 7. Позика розміром 1 млн. грн. видана під 13% річних строком на 3 роки; відсотки нараховуються щокварталу. Визначити величину загальних виплат по позиці за другий рік.

Розв’язання Припустимо, що позика погашається рівними платежами в кінці кожного розрахункового періоду. Тоді для розрахунку суми виплати заборгованості за другий рік застосуємо функцію ОБЩДОХОД.

Аргументи функції: Кол_пер = 3*4 = 12 кварталів; Ставка = 13%/4;

Нз = 1000000; Нач_период = 5 і Кон_период = 8; Тип = 0.

= ОБЩДОХОД (13%/4; 12; 1000000; 5; 8; 0) = - 331522,23 грн.

Розв’язання задачі представлено на рис..5.

Рис. 5. Фрагмент вікна з використанням функції ОБЩДОХОД

Завдання 8. Банком виданий кредит в 500 тис. грн. під 10% річних строком на 3 роки. Кредит повинен бути погашений рівними долями, що виплачуються в кінці кожного року. Розробити план погашення кредиту, представивши його у вигляді наступної таблиці:

Розв’язання Розрахунок числових значень здійснимо за допомогою функцій Excel. Вкажемо у формулах абсолютні посилання на початкові дані та скористаємося копіюванням формул. Розв’язання задачі для 3-го періоду представлено на рис..6.

Наведемо формули, що були застосовані при обчисленні для 1-го періоду:

розмір щорічного платежу: = ПЛТ (0,1; 3; -500000) = 201 057,40 грн.;
основний борг: =ОСПЛТ (0,1;1;3;-500000) = 151 057,40 грн.;
відсотки: =ПРПЛТ (0,1; 1; 3; -500000) =;50 000 грн.;
накопичений борг: =-ОБЩДОХОД (0,1; 3; 500000; 1; 1; 0) = 151 057,40 грн.;
накопичений відсоток: =-ОБЩПЛАТ (0,1; 3; 500000; 1; 1; 0) = 50 000 грн.;
баланс на кінець періоду: = Кредит – Накопичений борг = 348 942,60 грн.

Рис. 6. Фрагмент вікна з таблицею плану погашення кредиту

Завдання 9. З метою покупки нерухомості інвестор узяв в банку кредит у сумі 12млн. грн. Визначити щомісячні виплати по кредиту для різних процентних ставок і термінів погашення кредиту.

Розв’язання Щомісячні виплати по позиці розраховуються з використанням функції ПЛТ. Проте аргументи даної функції – процентна ставка і термін погашення кредиту – за умовою можуть приймати різні значення. Тому розглянемо вплив цих параметрів на задану функцію. Скористаємося механізмом Таблица подстановки з меню команди Данные. Виконаємо наступну послідовність дій.

1. У комірку С3 введемо числове значення суми кредиту (12 000 000).

2. У комірки С4, С5 введемо довільні (умовні) значення процентної ставки (наприклад, 5%) і терміну погашення кредиту в роках (наприклад, 1), які нам знадобляться при побудові Таблиці підстановки.

3. В діапазон комірок В9:В22 введемо різні значення процентних ставок. В діапазон комірок С9:К9 - можливі терміни погашення.

4. В комірку В9 введемо формулу для розрахунку щомісячних виплат по позиці:
= ПЛТ (5%/12; 1*12; 12 000 000).

Відмітимо, що аргументи функції можна вводити як адреси комірок, так і конкретні початкові значення.

5. Виділимо інтервал для таблиці даних, що включає формулу і всі початкові дані, - В9:К22

6. Виберемо команду Данные -> Таблица подстановки У діалоговому вікні (рис. 22), що з'явилося, заповнимо відповідні поля. Оскільки наша таблиця залежить від двох параметрів, то в полі “Подставлять значения по столбцам в:” введемо посилання на комірку С5 (термін погашення), а в полі “Подставлять значения по строкам в:” - посилання на комірку С4 (ставка).

Рис. 7. Вікно задання параметрів таблиці постановки

7. Підтвердимо введення натисненням клавіші [Enter] або кнопкою ОК.

Таблиця щомісячних виплат по кредиту за допомогою таблиці підстановки буде сформована (рис..8).

Рис. 8. Фрагмент вікна з таблицею щомісячних виплат по кредиту

Поиск по сайту: