Методичні рекомендації та завдання
Для проведення лабораторних робіт з курсу
«Новітні програмні технології у фінансах»
Лабороторна робота №1
Технологія використання вбудованих функцій MS EXCEL для фінансових розрахунків
Мета: Оволодіти практикою застосування вбудованих функцій табличного процесора MS Excel для розрахунку ефективності капіталовкладень та амортизації обладнання.
Табличний редактор Excel є найбільш простим та гнучким інструментом здійснення різноманітних фінансових розрахунків, пов’язаних з оцінкою ефективності проведення кредитних операцій, операцій з цінними паперами, визначення та нарахування амортизації, оцінки ефективності проведення інвестиційних операцій, тощо. Тому питання опанування цього табличного редактора є важливим елементом навчального процесу та розуміння суті автоматизації фінансових розрахунків студентами під час вивчення курсу «Новітні програмні технології у фінансах».
Розглянемо технологію використання деяких засобів середовища моделювання MICROSOFT EXCEL для здійснення фінансового аналізу. До цих засобів належать:
|
|
• фінансові функції EXCEL;
• підбір параметра;
• диспетчер сценаріїв.
Фінансові функції.
Фінансові функції EXCEL призначені для обчислення базових величин, необхідних для проведення складних фінансових розрахунків.
Методика використання фінансових функцій EXCEL потребує дотримання певної технології:
1) на робочому аркуші в окремих комірках здійснюється підготовка значень основних аргументів функції;
2) для розрахунку результату фінансової функції EXCEL курсор встановлюється в нову комірку для введення формули, яка використовує вбудовану фінансову функцію; якщо фінансова функція викликається як продовження введення іншої формули, даний пункт опускається;
3) здійснюється виклик Майстра функції за допомогою команди Вставка/Функція або натисканням однойменної кнопки на панелі інструментів Стандартна;
4) виконується вибір категорії Фінансові (рис.1). У списку Функція міститься повний перелік доступних функцій вибраної категорії. Пошук функції здійснюється шляхом послідовного переглядання списку. У нижній частині вікна приводяться стислий синтаксис і довідка про призначення вибраної функції. Кнопка Довідка викликає екран довідки для вбудованої функції, на якій встановлений курсор. Кнопка Скасування припиняє роботу Майстра функцій.
Рис. 1 Екран виклику Майстра функції, крок 1
Кнопка Готово переносить у рядок формули синтаксичну конструкцію вибраної вбудованої функції. При натисканні на кнопку Далі здійснюється перехід до роботи з діалоговим вікном вибраної функції;
|
|
5) виконується вибір у списку необхідної фінансової функції, в результаті вибору з'являється діалогове вікно для введення аргументів. Для кожної фінансової функції існує регламентований за складом і форматом значень перелік аргументів;
6) у поля введення діалогового вікна можна вводити як посилання на адреси комірок, що містять значення аргументів, так і значення аргументів;
7) якщо аргумент є результатом розрахунку іншої вбудованої функції EXCEL, можна організувати обчислення вкладеної вбудованої функції шляхом виклику Майстра функції однойменною кнопкою, розташованою перед полем введення аргументу;
8) можлива робота з екраном довідки, яка пояснює призначення і правила встановлення аргументів функції. Виклик довідки здійснюється шляхом натискання кнопки Довідка. Для відмови від роботи із вбудованою функцією натискується кнопка Скасування;
9) Формула розпочинається зі знака =. Далі вказується ім'я функції, а в круглих дужках вказуються її аргументи в послідовності, яка відповідає синтаксису функції. За роздільник аргументів використовується вибраний при налаштуванні Windows роздільник, як правило, це крапка з комою (;) або кома (,). Окремі аргументи функції можуть бути як константами, так і посиланнями на адреси комірок.
Розглянемо специфіку заданих значень аргументів фінансових функцій:
1) всі аргументи, які означають витрати коштів (наприклад, щорічні платежі), вважаються від'ємними числами, а аргументи, які означають надходження (наприклад, дивіденди), вважаються додатними числами;
2) всі дати як аргументи функції мають числовий формат подання, наприклад, дата 1 січня 2001 року подається числом 34 700. Якщо значення аргументу типу дата береться з комірки, то дата в комірку може записуватися у звичайному вигляді, наприклад, як 1.01.01. При введенні аргументу типу дата безпосередньо в поле введення Майстра функції можна скористатися вбудованою функцією ДАТА, яка здійснює перетворення рядка символів у дату. Для цього натискується кнопка виклику Майстра функцій, яка знаходиться перед полем, і вибирається функція категорії Дата і час — ДАТА;
3) для аргументів типу логічне можливе безпосереднє введення констант типу ІСТИНА або НЕПРАВДА, або використання вбудованих функцій аналогічної назви категорії Логічні;
4) при безпосередньому введенні формули в комірку необхідно стежити за тим, щоб кожний аргумент знаходився на своєму місці. Якщо певні аргументи не використовуються, то необхідно поставити відповідне число розділових знаків. Якщо не використовується останній аргумент або декілька останніх аргументів, які розміщуються послідовно, то відповідні розділові знаки можна опустити (у більшості випадків це зауваження відноситься до аргументів тип і базис).
Базові теоретичні положення фінансових розрахунків
Прості відсотки
Введемо позначення
I — проценти за весь період позики;
Р — початкова сума боргу;
S — нарощена (майбутня сума) позики;
i — ставка нарощення (десятковий дріб);
п — термін позики (в роках);
t — число днів позики;
К — число днів в році.
I = Р*п* I (1)
S = P + I = P + P* n*i = P* (1 + n* i) (2)
(3)
Звичайно прості відсотки використовують при короткострокових позиках (до одного року), або при виплаті відсотків періодично позичальнику.
Як же проводяться обчислення? Початкова сума Р задана, задана ставка процента і (причому потрібно слідкувати за коректністю розміру: ставка повинна бути віднесеною до року), час потрібно визначити в долях року. Зауважимо, що день видачі позички і день погашення вважаються одним днем.
При видачі споживчого кредиту для визначення разової величини виплати по кредитній позиці R використовують формулу
|
|
(4)
де, m – кількість виплат по кредиту в рік.
Методології простих відсотків використовується для здійснення операцій по простому дисконтуванню, а саме при здійсненні операцій банківського дисконтування(обліку векселів).
Для цього використовують облікову ставку – d. Облікова ставка це така ставка відсотка, яка застосовується для майбутньої вартості при визначенні величини дисконту D (різниці між майбутньою та поточною вартістю векселя).
(5)
Приклад 1.
Вексель виданий (дата угоди) - 6.09.96 на суму (інвестиція) - 125000, оплачений (дата набрання чинності)- 12.09.98 з обліковою ставкою (Знижка) - 7%. Необхідно визначити суму до отримання за векселем (Його номінал).
Розв’язок:
Для визначення номінальної вартості векселя використаємо формулу:
Для нашого прикладу отримаємо:
= 145513.3
Для розв’язку задачі використаємо вбудовану фінансову функцію Excel ПОЛУЧЕНО, яка обчислює нарощену суму, одержувану в термін вступу в силу цінних паперів при використанні облікової (дисконтної ставки). Отримаємо наступний результат:
ПОЛУЧЕНО("6.09.96"; "12.09.98"; 125000; 0.07; 1) = 145513.7
Приклад 2.
Безкупонні облігації на суму 125 000 грн., 06.09.93 з погашенням 12.09.96 за ціною 175 000. Знайти річну ставку додаткового доходу (нарощення).
Розв’язок:
Річну ставку можна визначити з формули
Використовуючи функцію ИНОРМА, яка розраховує річну ставку додаткового доходу для цінних паперів без періодичної виплати відсотків, отримаємо такий самий результат:
ИНОРМА ("06.09.93", "12.09.96", 125000, 175000, 1) = 13,26%.
Приклад 3.
Визначте величину облікової ставки, якщо вексель був виданий 1.01.97 на три місяці на суму 870 000 рублів з погашенням суми боргу в 1000000 рублів через три місяці.
Розв’язок:
Для визначення дисконтної (облікової) ставки використовується формула:
Аналогічний результат покаже фінансова функція СКИДКА
СКИДКА("1.01.97";"1.04.97";870000; 1000000;1) = 52.72%
Складні відсотки
|
|
В середньострокових і довгострокових фінансово-кредитних операціях, якщо відсотки не виплачуються відразу ж після їх нарахування, а приєднуються до суми боргу, для нарощення застосовуються складні відсотки. База для нарахування складних відсотків збільшується з кожним періодом виплат. Приєднання нарахованих відсотків до суми боргу, яка служить базою для їх нарахування, називають капіталізацією відсотків.
Формула для розрахунку нарощеної суми в кінці n-го року при умови, що відсотки нараховуються один раз на рік, має вигляд:
(6)
Необхідно відзначити, що основна формула складних відсотків (6) передбачає постійну процентну ставку протягом усього терміну нарахування відсотків. Однак часто використовують плаваючі або змінні відсоткові ставки. Тоді нарощена сума розраховується так:
(7)
Відсотки капіталізуються зазвичай кілька разів на рік. Якщо річна номінальна ставка j, число періодів капіталізації на рік рівне m, а загальна кількість періодів нарахування рівно N = n*m, то кожен раз відсотки нараховуються за ставкою j/m. Тоді нарощена сума S визначається так:
(8)
Ефективна ставка - це річна ставка складних відсотків, що дає той же результат, що і m - разове нарахування відсотків за ставкою j/m. Якщо позначити ефективну ставку через i, то вона визначається наступним чином:
(9)
Дисконтування по ставці складних відсотків, коли відсотки нараховуються m раз у році, здійснюється наступним чином:
(10)
Величина Р в цьому випадку називається сучасною вартістю S, а величина D - дисконтом.
Розглянемо декілька прикладів вирішення задач на складні відсотки.
Приклад 1.
Розрахувати, яка сума виявиться на рахунку, якщо 27 000 грн. покладені на 33 роки під 13,5% річних. Відсотки нараховуються кожні півроку.
Розв’язок
Нарощену суму знайдемо за формулою (8). У нашому випадку:
Для вирішення даної задачі використовуємо функцію БС. Негативне число означає вкладення грошей:
БС (13,5% / 2,33 * 2,. -27000) = 2012074,6 грн.
Приклад 2.
За облігації номіналом 100 000 грн., випущеної на 6 років, передбачений наступний порядок нарахування відсотків: в перший рік - 10%, в два наступних роки - 20%, в решту три роки - 25%. Розрахувати майбутню (нарощену) вартість облігації за складною відсотковою ставкою.
Рішення
Майбутню вартість облігації розраховує за формулою (7).
Цю ж формулу використовує функція БЗРАСПИС, яка розраховує майбутнє значення інвестиції після нарахування складних відсотків:
БЗРАСПИС (100000, 10%, 20%, 20%, 25%, 25%, 25%) = 309 375 грн.
Приклад 3.
Розрахувати теперішню вартість вкладу, який через три роки складе 15000000 грн. при нарахуванні 20% в рік.
Рішення
Це завдання можна вирішити за допомогою формули (10).
На це ж питання відповідає функція ПС. Вона розраховує, яку суму необхідно покласти на рахунок сьогодні, щоб завтра отримати задане значення.
ПС (20%, 3, 15000000) = 8680556 грн..
Для покращення розуміння використання фінансових функцій при здійсненні обчислень по кредитних операцій доцільно використовувати кредитний калькулятор який працює за такою схемою:
Таблиця 1.
Схема роботи кредитного калькулятора в Excel
Показник | Функції MS Excel | |||
ПЛТ | КПЕР | СТАВКА | ПС | |
Сума кредиту | 1000,00 грн. | 1000,00 грн | 1000,00 грн | 903,23 грн. |
Річний відсоток | 15% | 15% | 20% | 12% |
Термін кредиту (місяці) | 36 | 79 | 36 | 36 |
Щомісячна сплата | -34,67 грн. | -20,00 грн. | -20,00грн. | -30,00 грн. |
Скільки прийдеться виплачувати в місяць якщо я візьму кредит? | За який термін я віддам кредит? | Який відсоток банка для кредиту з такими умовами? | Яку суму кредиту я можу взяти? |
Фінансові функції ПЛТ, КПЕР, СТАВКА і ПС дозволяють розрахувати параметри кредиту з усіх можливих точок зору.
Функція ПЛТ (РМТ) - обчислює суму періодичної (наприклад, щомісячної) виплати по взятому кредиту.
Функція КПЕР (КПЕР) - обчислює термін, за який Ви розплатитеся з кредитом при заданих сумах кредиту, щомісячних виплат і відсотку банку.
Функція СТАВКА (RATE) - обчислює, який повинен бути відсоток банку для кредиту з заданими сумою, терміном та щомісячною виплатою.
Функція ПС (PV) - обчислює, яку суму Ви можете взяти в кредит у цьому банку, якщо готові виплачувати щомісячно не більше... грн.
Для визначення величини погашення основної суми боргу та відсотків за певні періоди при рентних платежах використовують фінансові функції
ПРПЛТ - служить для розрахунку суми відсоткових платежів за кредитом за даний період при постійних сумах періодичних платежів і постійної відсоткової ставки
ОСПЛТ - повертає величину платежу в погашення основної суми за кредитом за даний період при постійних сумах періодичних платежів і постійної відсоткової ставки