Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Виконання обчислень у середовищі ТП.

Практична робота до предмету Інформатика 11 клас.

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

Виконання обчислень у середовищі ТП.

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

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

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

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

Час виконання :   90 хвилин  

                                                  Після виконання лабораторної роботи студент повинен:

Знати призначення і параметри фінансових функцій ПЛТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД; використання засобу Подбор параметра
Вміти: здійснювати подбор параметра при розв’язанні економічних задач, застосовувати відповідні фінансові функції. При потребі використовувати засіб Поиск решения, Таблицы подстановки.

                                                                  Основні теоретичні положення.

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

Серед фінансових функцій 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. Фрагмент вікна з таблицею щомісячних виплат по кредиту
 
                                                               Порядок виконання роботи.

I.    Ознайомитися з теорією та прикладами розв’язання економічних задач за допомогою фінансових функцій MS EXCEL ПЛТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД – завдання 1 – 9.

II.    Виписати в зошит для кожної функції: її призначення, формат та 1 приклад з розв’язанням.

III.    За допомогою відповідних фінансових функцій MS EXCEL розв'язати наведені нижче завдання. Результати роботи зберегти в книзі Фінансові функції_4  в своїй теці.

Завдання для самостійної роботи.

Функція ПЛТ

1.    Розробіть план погашення кредиту, отриманого на наступних умовах:

а) 700 тис. грн. терміном на 6 років під 9% річних при виплаті один раз в кінці року.(-93043,85 грн)
б) 900 тис. грн. терміном  на 9 років під 7% річних при виплаті один раз в  квартал.( -18157,56 грн)
в) 500 тис. грн. терміном на 4 роки  під 11% річних при виплаті один раз в місяць.(-8339,43 грн)

2.    Необхідно накопичити 4000 грн за 3 роки, відкладаючи постійну суму в кінці кожного місяця. Якою повинна бути ця сума, якщо норма процента за вкладом становить 12 % річних ? (- 92,86 грн.)

3.    Банк видав позику 200 тис. грн на 4 роки під 18 % річних. Позика видана на початку року, а погашення починається в кінці року однаковими платежами. Потрібно визначити величину щорічного погашення позики. (74,35 тис. грн)

Функція ПРПЛТ

4.    Розрахувати платежі за процентами за перший місяць від трьохрічної позики у 800 тис. грн, виходячи з розрахунку 18 % річних.(- 12 тис. грн.)

5.    За рахунок щорічних відрахувань протягом 6 років був сформований фонд в 500 тис. грн. Треба розрахувати, який дохід приносили вкладення власнику за останній рік, якщо річна ставка становила 17,5 %.(Дохід за останній рік (6 періодів) склав: 66,48110268 тис. грн; щорічно відраховувалось 53,627 тис. грн.)

Функція  ОСПЛТ

6.    Визначте суму основного платежу з дворічної позики 50 000 грн за перший місяць з розрахунку 18 % річних. Нарахування процентів щомісяця.(- 1746,21 грн)

7.    Споживач отримує позику на покупку автомобіля 20 000$ під 8% річних строком на три роки при щомісячних виплатах. Яка буде сума по відсотках і основний платіж за перший і останній місяці виплат?

Відповідь: за 1-ий місяць сума по відсотках - 133,33 $; основний платіж -493,93 $
за 3-ий місяць сума по відсотках – 4,15 $; основний платіж -622,58 $

Функції  ОБЩПЛАТ, ОБЩДОХОД

8.    Видана позика під купівлю нерухомості розміром 400 000 грн строком на 30 років під 9 % річних, проценти нараховуються щомісяця. Визначте величину основних виплат за процентами за другий рік.(-35 632,74 грн)

9.    Позика розміром 2,5 млн. грн. видана під 21% річних строком на 5 років; відсотки нараховуються щокварталу. Визначити величину загальних виплат по позиці за третій рік.(-479 666,47 грн)

10.    На 4 роки видана позика 200 000 грн під 17% річних. Проценти нараховуються щоквартально. Визначити розмір основних виплат за весь 2 рік.        (-45219,3 грн)

IV.    Оформити звіт по роботі.

Алгоритм розв'язання завдання № (за вибором викладача) представити в документі Word з відповідними поясненнями, фрагментами вікна MS EXCEL та діалоговими вікнами. Зберегти документ з назвою Пояснення_4 в своїй теці.

Література: [ 1, 2, 3, 7]

Надіслала вчитель інформатики Деснянського економіко – правового технікуму при МАУП м. Києва Рябець Людмила Іванівна

_{Збірка конспектів уроків з інформатики скачати, календарно-тематичне планування, підручники по всім предметам онлайн}