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

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

Тема «Використання функцій БС, БЗРАСПИС в економічних розрахунках».

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

                                                                                             Лабораторна робота № 1.

                                           Визначення майбутньої вартості на основі постійної та змінної процентної ставки.

                                                        Використання функцій БС, БЗРАСПИС в економічних розрахунках.

Мета роботи: Знайомство з фінансовими функціями БС, БЗРАС.

Набуття практичних навичок по використанню функцій БС, БЗРАСПИС в економічних розрахунках.

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

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

Знати призначення і параметри фінансових функцій БС, БЗРАСПИС , застосовувати засіб Подбор параметра
Вміти: застосовувати відповідні фінансові функції для визначення майбутньої вартості; здійснювати подбор параметра при розв’язанні економічних задач.

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

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

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

Перед вирішенням вказаних завдань слід визначити:

1. Хто є власником грошових коштів? Наприклад, в завданні накопичення — вкладник або банк? У завданні позики — боржник або кредитор? При обчисленні вартості ряду майбутніх виплат — покупець(виплата за придбаний товар) або продавець (отримання виплат за проданий товар)?

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

Завдання 1.

На банківський рахунок під 16% річних внесли 40000 грн Визначити розмір внеску після закінчення 3 років, якщо відсотки нараховуються кожні півроку.

Розв’язання

Оскільки необхідно розрахувати єдину суму внеску на основі постійної процентної ставки, то використовуємо функцію

БС (ставка; кпер; плт; пс; тип).

У зв'язку з тим, що відсотки нараховуються кожні півроку, аргумент ставка рівний 16%/2. Загальне число періодів нарахування рівне 3*2 (аргумент кпер). Якщо вирішувати дану задачу з погляду вкладника, то аргумент пс (початкова вартість внеску) рівний 40 000 грн, задається у вигляді негативної величини (- 40 000), тому що для вкладника це відтік його грошових коштів(вкладення засобів).

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

Аргумент плт відсутній, оскільки внесок не поповнюється. Аргумент тип рівний 0, оскільки в подібних операціях відсотки нараховуються в кінці кожного періоду(задається за умовчанням). Тоді до кінця 3-го року на банківському рахунку маємо:

= БС (16%/2;3*2;;-40 000) = 63474,97 грн, з погляду вкладника це дохід
= БС (16%/2;3*2;;40 000) = - 63474,97 грн, з погляду банку це витрата, тобто повернення грошей банком вкладникові.

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

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

Завдання 2.

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

Розв’язання

Скористаємося функцією БС з наступними аргументами:

= БС(19%;5;-20000;;1)= 173 659,04 грн

Якби внески здійснювалися в кінці кожного року, результат був би:    = БС(19%;5;-20000)= 145 931,96 грн

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

Завдання 3.

Чи досить покласти на рахунок 75 000 грн для придбання через 5 років легкового автомобіля вартістю 210000 грн? Банк нараховує відсотки щокварталу, річна ставка 18%. Провести розрахунки при різних варіантах процентної ставки.

Розв’язання

Оскільки потрібно знайти майбутнє значення суми внеску через 5 років, для вирішення поставленого завдання скористаємося функцією БС.  Отримаємо:  =БС(18%/4;5*4;;-75000; 0)= 180878,55 грн

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

1 варіант.

Для визначення необхідної початкової суми скористаємося засобом Подбор параметра з меню команди Сервіс(див. рис. 1 )

 
Рис. 1. Фрагмент вікна Excel із заповненими полями підбору параметрів

Після підтвердження введених даних в комірці В7 встановиться значення 210000,00 грн, а в комірці B3 відобразиться результат – 87075,00 грн

2 варіант.

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

Для цього додатково побудуємо таблицю підстановки: вкажемо найменування стовпців; в діапазоні D9:D16 введемо можливі процентні ставки, а в комірку Е8 введемо формулу розрахунку майбутнього значення єдиної суми внеску. Потім виконаємо необхідні дії з ініціалізації засобу Таблиця підстановки(Сервис - > Надстройка і встановити прапорець Таблица подстановки) і внесення до відповідного поля підстановки по рядках значення адреси діапазону з процентною ставкою.

Використання засобу Таблица подстановки та отримані результати представлені на рис. 2.



Рис. 2. Приклад використання функції БС та засобу Таблица подстановки

Після підтвердження в діалоговому вікні таблиці підстановки заданих параметрів в діапазоні комірок Е9:Е16 автоматично з'являються результати.

З результатів виходить, що річні ставки, що менші за 21,5% не забезпечують зростання внеску до необхідної величини, рівної  210 000 грн

При ставці 21,5% значення внеску виросте до 213704,18 грн, а ставка 22% забезпечує зростання внеску до 218831,81 грн

Завдання 4.Страхова компанія, що уклала договір з підприємством на 3 роки, щорічні страхові внески — 500 грн розміщує в банк під 15 % річних із нарахуванням процентів кожного півріччя. Визначте суму, отриману страховою компанією за цим контрактом у кінці строку контракту.

Розв’язання

Сума, отримана страховою компанією, являє собою нарощену суму постійної фінансової ренти (ануїтету), її можна обчислити як суму функцій БС

БС(15 % / 2; 2;; - 500) = 577,8125;

БС(15 % / 2; 2; ; -577,8125)= 667,73457;

S= 500 + БС(15 % / 2; 2;; -500) + БС(15 % / 2; 2;; -577,8125) = 500 + 577,8125 + 667,73457 = 1745,547 грн

Завдання 5.Страхова компанія приймає річний внесок 500 грн двічі на рік, кожного півріччя, в розмірі 250 грн протягом 3 років. Банк, який обслуговує страхову компанію, нараховує їй проценти з розрахунку 15 % річних (складні проценти) один раз на рік. Визначте суму, отриману страховою компанією в кінці строку договору.

Розв’язання

Сума, отримана страховою компанією, являє собою нарощену вартість - термінової ренти, її можна обчислити як суму відповідних функцій

БС(15 %; 1/2;; - 250) = 268,0951324;

БС(15 %; 2/2; ; - 250) = 287,5; БС(15 %; 3/2;; - 250) = 308,3094022;

                                                       Визначення майбутньої вартості на основі змінної процентної ставки

Завдання 6.

По облігації номіналом 10000 грн, випущеною на 6 років, передбачений наступний порядок нарахування відсотків: у перший рік – 10%, в наступні два роки – 20%, в три роки, що залишилися, – 25%.

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

Розв’язання

Оскільки процентна ставка міняється з часом, але є постійною впродовж кожного з періодів однакової тривалості, то для розрахунку майбутнього значення інвестиції по складній процентній ставці слід скористатися функцією БЗРАСПІС (первинне; план).

Ілюстрація розв’язання задачі представлена на рис. 3 .

 
Рис. 3. Вікно функції БЗРАСПІС з даними про майбутню вартість облігації

Результат рішення задачі – 30937,5 грн може бути знайдений і при явному записі функції БЗРАСПІС. Масив процентних ставок в цьому випадку слід ввести у фігурних дужках:

=БЗРАСПИС(10 000; {0,1; 0,2; 0,2; 0,25; 0,25; 0,25}) = 30937,5

Завдання 7.

По облігації, випущеній на 6 років, передбачений порядок нарахування відсотків, приведений в завданні 1. Розрахувати номінал облігації, якщо відомо, що її майбутня вартість склала 185625 грн

Розв’язання

Для вирішення запропонованого завдання скористаємося засобом Подбор параметра з меню команди Сервіс.

Хай початкові дані завдання введені у відповідність з рис. :  в діапазоні комірок  С5:С10 набрані процентні ставки; комірка С4 призначена для зберігання значення номінала облігації; в комірку С11 введена формула =БЗРАСПИС(С4;С5:С10).

Виділяємо комірку С11, яка містить формулу обчислення майбутньої вартості облігації. Застосовуємо засіб Подбор параметра меню Сервис (див рис 4)

 
Рис. 4. Дані для визначення номінала
Після підтвердження введення даних в результаті підбору параметра в комірці С4 отримаємо значення номінала облігації – 60 000 грн(див Рис. 5.)

 
Рис. 5.  Отримані результати

                                                                    Порядок виконання роботи.

I.    Ознайомитися з теорією та прикладами розв’язання економічних задач за допомогою фінансових функцій MS EXCEL – завдання 1 – 7.

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

III.    За допомогою фінансових функцій MS EXCEL БС, БЗРАСПИС самостійно розв'язати представлені нижче завдання.(Відповіді для перевірки наведені в круглих дужках). Результати роботи зберегти в книзі Фінансові функції_1 в своїй теці.

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

Функція БС

1.    У банк на депозит внесена сума 10 тис. грн Термін депозиту 2 року, річна ставка – 18%. Нарахування відсотків проводиться щокварталу. Визначити величину депозиту в кінці терміну.(14 221, 01 грн)

2.    Існує два варіанти грошових внесків по 50 тис. грн протягом трьох років: на початку кожного року під 19% річних або в кінці кожного року під 23% річних. Визначити найбільш переважний варіант.(214 562,95 грн; 187 145 грн)

3.     Два клієнти банку протягом декількох років вносять однакові фіксовані грошові суми під 17% річних. Один клієнт робить внесок на початку кожного кварталу, інший – в кінці кожного місяця. Визначити розміри накопичених клієнтами до кінця п'ятого року сум, якщо загальна сума внесків кожного з них за рік дорівнювала 12 тис. грн(95 584,2234 грн; 93 581,18159 грн)

4.     Визначити накопичену суму, якщо 7 тис. грн розміщено  в банк під 16% річних на 28 місяців, а відсотки нараховуються щокварталу.(9 211,52 грн)

5.    Якщо Ви займаєте 30 000 грн  на два роки під 8% річних, то скільки всього грошей Ви повинні повернути?(34 992 грн)

6.     Якщо початковий баланс на рахунку 6 000 грн і щомісячний внесок  500 грн (в кінці кожного місяця), то скільки можна накопичити за три роки при ставці 0,75% в місяць?(28 428,23 грн)

7.    Корпорація планує щокварталу протягом 8-ми років робити відрахування по 2 000 грн для створення фонду викупу своїх облігацій. Засоби поміщаються в банк під 10% річних. Яка сума буде накопичена до кінця терміну операції?(963 000,555 грн)

Функція БЗРАСПИС

8.     Розрахувати майбутню вартість облігації номіналом 100 тис. грн, випущеною на 4 роки, якщо передбачений наступний порядок нарахування відсотків: у перший рік – 12,5%, в наступні два роки – 14%, в останній рік – 17% річних.(171 059,85 грн)

9.    Очікується, що майбутня вартість інвестиції розміром 150 тис. грн до кінця четвертого року складе 300 тис. грн При цьому за перший рік прибуток складає 15%, за другий – 17%, за четвертий – 23%. Розрахувати прибутковість інвестиції за третій рік, використовуючи апарат підбору параметра.(21%)

10.     Ставка банку по валютних внесках на початок року складає 10% річних, що нараховуються раз в  квартал. Первинна сума внеску 500 у.о. Протягом року, на початку подальших кварталів, очікується зниження ставки від первинного розміру на 2, 3 і 5 відсотків відповідно. Визначити величину внеску на початок наступного року(538,5547 грн).  

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

Алгоритм розв'язання завдання № (за вибором викладача) представити в документі Word з відповідними поясненнями, фрагментами вікна MS EXCEL та діалоговими вікнами функції. Зберегти документ з назвою Пояснення_1 в своїй теці.
Література: [ 1, 2, 3, 7]

¹Вказівка.Потрібно здійснити перерахування річних процентних ставок з прив’язкою до кварталу або під час введення плану ставок (річних) у вікні функції БЗРАСПИС виконати одночасне ділення значень діапазону комірок на 4 за допомогою натискання комбінації клавіш Ctrl + Shift +Enter.

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

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