3.1. Технологія використання вбудованих функцій excel для фінансових розрахунків

Розглянемо технологію використання деяких засобів середовища моделювання MICROSOFT EXCEL для здійснення фінансового аналізу. До цих засобів належать:

• фінансові функції EXCEL;

• підбір параметра;

• диспетчер сценаріїв; таблиця підстановки.

Фінансові функції EXCEL призначені для обчислення базових ве­личин, необхідних для проведення складних фінансових розрахунків.

106

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Методика використання фінансових функцій EXCEL потребує дотри­мання певної технології:

1. на робочому аркуші в окремих комірках здійснюється підготовка значень основних аргументів функції;

2. для розрахунку результату фінансової функції EXCEL курсор встановлюється в нову комірку для введення формули, яка ви­користовує вбудовану фінансову функцію; якщо фінансова функ­ція викликається як продовження введення іншої формули, даний пункт опускається;

3. здійснюється виклик Майстра функції за допомогою команди Вставка/Функція або натисканням однойменної кнопки на панелі інструментів Стандартна;

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

Рис. 3.1

Екран виклику Майстра функції, крок 1

107

Зацеркляний М. М., Мельников О. Ф. _*лА\-

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГИ У ФІНАНСОВО-КРЩИТНИХУСТАНОВАХ

	Норма| 3J -
Число„периодов J ^J »
Выплата j JVj <=
Hsj ^«
" Тип| _ 3J- . j
Возвращает будущее значение вклада с периодическими постогэнныни выплатами и посточнным
процентом.
Норма норма прибыли за период.
11	Значение: ОК | Отмена |,

Рис. 3.2

Д/'алогове вікно введення аргументів функції

Кнопка Готово переносить у рядок формули синтаксичну кон­струкцію вибраної вбудованої функції. При натисканні на кнопку Далі здійснюється перехід до роботи з діалоговим вікном вибраної функції;

5. виконується вибір у списку необхідної фінансової функції, в результаті вибору з'являється діалогове вікно для введення ар­гументів (рис. 3.2). Для кожної фінансової функції існує регламен­тований за складом і форматом значень перелік аргументів;

6. у поля введення діалогового вікна можна вводити як посилання на адреси комірок, що містять значення аргументів, так і значення аргументів;

7. якщо аргумент є результатом розрахунку іншої вбудованої функ­ції EXCEL, можна організувати обчислення вкладеної вбудованої функції шляхом виклику Майстра функції однойменною кноп­кою, розташованою перед полем введення аргументу;

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

9. для відмови від роботи із вбудованою функцією натискується кнопка Скасування;

108

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Рис. 3.3

Діалогове вікно для редагування

10) завершення введення аргументів і завантаження розрахунку зна­чення вбудованої функції виконується натисканням кнопки Готово. В разі необхідності коригування значень аргументів функції (змі­на посилань, сталих значень і т. п.) необхідно встановити курсор в ко­мірку, яка містить формулу, і викликати Майстер функцій. При цьому з'являється вікно для редагування (рис. 3.3.)

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

Формула розпочинається зі знака =. Далі вказується ім'я функції, а в круглих дужках вказуються її аргументи в послідовності, яка відпові­дає синтаксису функції. За роздільник аргументів використовується ви­браний при налаштуванні Windows роздільник, як правило, це крапка з комою (;) або кома (,). Окремі аргументи функції можуть бути як кон­стантами, так і посиланнями на адреси комірок.

Розглянемо специфіку задания значень аргументів фінансових функцій:

1) всі аргументи, які означають витрати коштів (наприклад, що­річні платежі), вважаються від'ємними числами, а аргументи, які

109

Рис. 3.4

Діалогове вікно для редагування

означають надходження (наприклад, дивіденди), вважаються до­датними числами;

2. всі дати як аргументи функції мають числовий формат подання, наприклад, дата 1 січня 2001 року подається числом 34 700. Якщо значення аргументу типу дата береться з комірки, то дата в комірку може записуватися у звичайному вигляді, наприклад, як 1.01.01. При введенні аргументу типу дата безпосередньо в поле введен­ня Майстра функції можна скористатися вбудованою функцією ДАТА, яка здійснює перетворення рядка символів у дату. Для цьо­го натискується кнопка виклику Майстра функцій, яка знаходиться перед полем, і вибирається функція категорії Дата і час — ДАТА. Далі заповнюється екран введення (рис. 3.4). Кнопка Скасування дозволяє відмовитися від використання викликаної функції;

3. для аргументів типу логічне можливе безпосереднє введення кон­стант типу ІСТИНА або НЕПРАВДА, або використання вбудова­них функцій аналогічної назви категорії Логічні;

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

ПО

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

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

Розглянемо декілька прикладів на використання вбудованих функ­цій для розв'язування фінансових задач.

1. Прості і складні відсотки

Розглянемо схему однократного надання деякої суми Р в кредит на час t. За використання кредиту потрібно платити. Повернути потрібно S = Р + І, де / — плата за кредит. В найпростішому випадку вважають, що / = it, де і — процентна ставка.

Величина / вимірюється в грошових одиницях. Час t виміряєть­ся в роках, причому кількість років може бути не цілим числом, тоді використовуються долі року. Розмір процентної ставки і — грошових одиниць на рік.

Величина нарощеної суми визначається за формулою S = Р (1 + it). Відношення ■%■ називається коефіцієнтом нарощення.

Як же проводяться обчислення? Початкова сума Р задана, задана ставка процента і (причому потрібно слідкувати за коректністю розмі­ру: ставка повинна бути віднесеною до року), час потрібно визначити в долях року. Зауважимо, що день видачі позички і день погашення вва­жаються ОДНИМ ДНЄМ. ДОЛЯ року обчИСЛЮЄТЬСЯ За формуЛОЮ П = -JT, ДЄ

t — число днів позички; К — число днів у році (часова база).

Задача 1

Позичка в розмірі 1 млн. гривень видана 20 січня до 5 жовтнявключно під 18 % річних. Яку суму повинен заплатити боржник в кінці періоду?

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

Формулювання задачі потребує уточнення: в якому році ви­конувалася операція: у звичайному чи високосному. Проте функція ДОЛЯГОДА працює без урахування цієї обставини. Розглянемо три ва­ріанти (рис. 3.5).

Звернемося до фінансових функцій.

При використанні фінансових функцій потрібно враховувати зна­ки грошових сум, пам'ятаючи, з чиєї точки зору розглядається фінансова

111

Зацеркляний М. М., Мельников О. Ф. _^/*Зк^_

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Рис. 3.6

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

Потрібно розрахувати майбутнє значення початкової суми. Скористаємося функцією БЗ {норма, число периодов, выплата, началь­ное значение, тип).

Перед тим, як скористатися цією функцією, потрібно виконати деякі розрахунки. Число періодів для простих відсотків, як уже було сказано, дорівнює 1. Відсотки є простими. Тому попередньо обчис­лимо процентну ставку за вказаний в умові задачі період. Початкові дані внесемо в комірки робочого аркушу в діапазон ВЗ.В6. В діапазоні АЗ:А6 розмістимо назви кожного параметра. Комірці ВИ призначимо грошовий формат (рис. 3.6).

Результат, як і треба було очікувати, виявився від'ємним.

З'ясуємо третій (пропущений) аргумент функції БЗ. Під виплатами тут розуміється проміжні рівні виплати на початку (тип = 1) чи в кінці (тип = 0) періоду. В даній задачі виплат немає.

Функція БЗ має коротку назву, яка легко запам'ятовується, і багато аргументів, порядок і призначення яких запам'ятати важко. Аби відра­зу розпочати роботу з другим діалоговим вікном Майстра функцій, по­ступають так: в комірку ВИ вводиться =БЗ (і натискується комбінація клавіш Ctrl+A. З'являється діалогове вікно з полями для введення ар­гументів.

При розв'язанні задач рекомендується заносити початкові дані в ко­мірки робочого аркуша, а не в формули; в сусідніх комірках — давати

*™ 113

*Зоі юпі/пяиим КЛ КА Мапіиііііі'ла D ҐІ~>

a

ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ У ФІНАНСОВО-КРЕДИТНИХ УСТАНОВАХ ЩЩ

назви даних. Можна зробити формули набагато зрозумілішими, якщо замість адрес комірок дати їм назви. Виділимо діапазон АЗ:В10. В меню виконаємо Вставляння/Ім'я/Створити. З'являється діалогове вікно, в якому Excel пропонує вибрати імена з лівої колонки. Підтверджується цей вибір і знову викликається меню Вставляння/Ім'я/Застосувати. Формула в комірці ВИ прийме вигляд:

=БЗ (ставка_для_периода, 1„ суммакредита).

Розмістимо на робочому аркуші не тільки результати, а й форму­ли для розрахунку як текст. Для цього додамо на початок формул апо­строф — формули перетворюються в текст.

Приведемо формулу для розв'язування задачі, коли всі початкові дані розміщуються як аргументи у функцію БЗ. Хоча це є протиріччя «хорошому стилю» оформлення робочого аркушу, формула має само­стійний інтерес:

=БЗ(120%*("15/3/99"-"13/1/99")/365„1000000)

Зверніть увагу, що дати заключені в подвійні лапки. їх формат по­винен відповідати міжнародним налаштуванням Windows.

Перейдемо до схеми складних відсотків. У договорах вказуються річна ставка / і кількість нарахувань відсотків т на протязі року. Це озна­чає, що базовий період складає рік, поділений на т, а ставка складних відсотків для періоду дорівнює ііт. Формула для складних відсотків при­ймає вигляд:

Задача З

Позичка в 20 000 гривень дана на півтора року під ставку 28 % річ­них із щоквартальним нарахуванням. Визначити суму кінцевого платежу.

114

Розділ З МОДЕЛІ І МЕТОДИ ФІНАНСОВО-ЕКОНОМІЧНИХ РОЗРАХУНКІВ

Розе 'язування

Тут базовий період — квартал. Термін позички складає 6 періо­дів (4 квартали на рік, термін півтора року), за період нараховується 7 % = 28 %/4. Тоді формула, яка дає розв'язок задачі, має вигляд:

=БЗ(28%/4,4*1.5„20000).

Вона повертає результат — 30014.61.

Задача 4

Розрахувати майбутнє значення вкладу 1000 гривень через 0,1,2,3, 4, 5 років при річних ставках 10 %, 20 %,..., 50 %. Додаткові надходжен­ня і виплати відсутні.

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

В комірку В1 розмістимо величину початкового значення вкладу. В комірки B2:G2 розмістимо числа 0, 1,..., 5, в комірки АЗ:А7 величини 10, 20,..., 50 % (ці числа заносяться з використанням прийомів, які дозволя­ють генерувати арифметичні прогресії). Отже, потрібно табулювати функ-ціїо двох змінних (процентна ставка і кількість рокш), яка залежить від параметра — початкового вкладу. Вводиться в комірку ВЗ формула =БЗ ($АЗ,В$2„ — $В$1). Формула копіїоється в комірки інтервалу B3:G7.