Posobie_1_chast2_Excel

випадками обчислення поточної вартості очікуваних доходів і витрат, що у загальному випадку можуть бути змінними величинами і відбуватися в різні періоди часу. Розрахунок за допомогою функції ПС вимагає грошових потоків рівної величини і рівних інтервалів між операціями. Функція ЧПС допускає грошові потоки змінної величини через рівні періоди часу. Найбільш загальний розрахунок можна здійснити за допомогою функції ЧИСТНЗ, що дозволяє обчислювати чисту поточну вартість перемінних грошових потоків, що є нерегулярними.

Функція ПС

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

Задача 4.

6.Допустимо, що у вас просять у борг 10000 грн. і обіцяють повертати по 2000 грн. протягом 5 років. Чи буде вигідна ця угода при річній ставці 5%? Розрахувати ефективність капіталовкладень.

=ПС(B4;B2;-B3)

Розрахунок періодичних платежів

Функції EXCEL дозволяють обчислювати наступні величини, зв'язані з періодичними виплатами:

1.періодичні платежі, що здійснюються на основі постійної процентної ставки і не змінюються за увесь час розрахунку (функція ПЛТ);

2.платежі відсотка за конкретний період (функція ПРПЛТ);

3.суму платежів по відсотках за кілька періодів, що йдуть підряд (функція ОБЩПЛАТ);

4.основні платежі по позиці (за винятком відсотків) за конкретний період (функція ОСПЛТ);

110

5. суму основних платежів за кілька періодів, що йдуть підряд (функція ОБШДОХОД).

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

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

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

Оцінка інвестицій на основі Таблицы подстановки

При оцінці й аналізі варіантів інвестицій часто потрібно одержати кінцеві значення для різних наборів вихідних даних. Одним з достоїнств ЕХСЕL є можливість швидкого рішення подібних задач і автоматичного перерахування результатів при зміні вихідних даних. Наприклад, можна побудувати фінансову модель для різних значень процентних ставок і періодичних виплат і вибрати оптимальне рішення. Для рішення подібних задач

вЕХСЕL служить Таблица подстановки, що містить результати підстановки різних значень у формулу. Принцип використання цього засобу ЕХСЕL полягає

внаступному.

Можливі значення одного або двох аргументів функції необхідно представити у виді списку або таблиці. Для одного аргументу список вихідних значень задається у виді рядка або стовпця таблиці. ЕХСЕL дставляє ці значення у формулу (функцію), задану користувачем, а потім вибудовує результати відповідно в рядок або стовпець.

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

Таким чином, команда Таблица подстановки з меню Данные дозволяє створювати два типи таблиць даних: таблицю для однієї перемінної, котра

111

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

Далі технологія побудови і використання Таблицы подстановки ЕХСЕL описана більш докладно.

Побудова Таблиці підстановки для однієї перемінної

Задача 5.

Припустимо, що потрібно визначити, які щомісячні виплати необхідно вносити по позичці розміром 200 млн. руб., виданої на 3 роки, при різних процентних ставках.

Для рішення задачі доцільно скористатися Таблицею подстанлвки ЕХСЕL.

Для заповнення таблиці необхідно виконати наступну послідовність дій:

1.Ввести в комірку D7 формулу для розрахунку періодичних постійних виплат по позиці за умови, що вона цілком погашається протягом терміну позики,

=ПЛТ(С4/12,С3*12,С2).

2.Виділити діапазон комірок, що містить вихідні значення процентних ставок і формулу для розрахунку – С8:D13. Вихідні дані в нашому прикладі розташовані в стовпці С8:С13, тому результати підстановки також будуть розташовані в стовпці (D8:D13).

3.У меню Данные виберіть команду Таблица подстановки. На екрані з'явиться діалогове вікно Таблица подстановки.

Це вікно використовується для завдання робочої комірки, на яку посилається формула розрахунку. У нашому прикладі це комірка С4, яку необхідно вказати в поле Подставлять значения по строкам в діалогового вікна в абсолютних координатах.

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

112

При натисканні кнопки ОК ЕХСЕL заповніть стовпець результатів, як показано на малюнку.

Щомісячні виплати по позиці для різних процентних ставок.

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

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

Наприклад, у нашому прикладі для розрахунку платежів по відсотках за перший період для кожного значення процентної ставки в комірку Е7 необхідно увести формулу

=ПРПЛТ(С4/12,1,С3 * 12,С2).

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

Для розрахунку виплат по відсотках для інших періодів (з 2 по 36) необхідно підставити формули в наступні комірки праворуч від останньої. Отримана таблиця буде автоматично перелічена при зміні суми і терміну позики, тобто при внесенні змін в комірки С2 і СЗ.

Побудова Таблиці підстановки для двох перемінної

Для аналізу даних в ЕХСЕL можна побудувати таблицю, що обчислює результат підстановки двох змінних в одну формулу.

Задача 6.

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

Для того, щоб створити Таблицю підстановки для цієї задачі, необхідно

113

виконати наступні дії.

1.Увести першу множину вхідних значень (процентні ставки) у стовпець, наприклад, в комірки C8:C13.

2.Увести другу множину вхідних значень (терміни погашення) у рядок, розташований вище і правіше на одину комірку від початку першого діапазону. У нашому прикладі це діапазон D7:G7.

3.Увести формулу для розрахунку на перетині рядка і стовпця, що містять дві множини вхідних значень, тобто в комірку В7. Якщо вихідні дані введені на робочому листі ЕХСЕL так, як показано на мал., то формула для розрахунку постійних періодичних виплат по позиці при повному його погашенні

протягом терміну позики виглядає в такий спосіб: =ПЛТ (С4/12,С3 * 12,С2).

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

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

4.Далі необхідно виділити діапазон таблиці даних, що включає усі вхідні значення і формулу розрахунку, тобто В7: F13.

5.Вибрати в меню Данные команду

Таблица подстановки і заповнити діалогове вікно, як показано на рисунку.

При натисканні кнопки ОК ЕХСЕL виконає розрахунок таблиці підстановки. Результати розрахунку приведені на рисунку.

114

При зміні суми позики ЕХСЕL автоматично перерахує всю таблицю.

Оцінка ефективності інвестицій на основі Таблиці підстановки і функції ЧПС

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

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

Задача 7.

Припустимо, що наприкінці року капіталовкладення по проекті складуть близько 1280 млн. руб. Очікується, що за наступні 3 роки проект принесе наступні доходи: 420, 490, 550, 590 млн. руб. Розрахуємо чисту поточну вартість проекту для різних норм дисконтирования й обсягів капіталовкладень.

В комірку DЗ помістимо передбачувану величину початкових витрат по проекті (1280 млн. руб.) зі знаком «мінус», оскільки цей потік рухається протилежно очікуваним доходам. Це значення необхідно ввімкнути в список аргументів функції ЧПС, тому що чиста поточна вартість розраховується на початок року, а капіталовкладення, за умовою задачі, будуть здійснені наприкінці року. В комірки С10:F10 помістимо різні обсяги капіталовкладень, як показано на рисунку. Для розрахунку чистої поточної вартості візьмемо значення процентних ставок 13%, 13,8%, 15%. В комірку В10 за допомогою Майстра функцій помістимо формулу для розрахунку:

= ЧПС (D2,D3,D4,D5,D6,D7).

На робочому листі ЕХСЕL вихідні дані представимо в такий спосіб.

115

Задача 8.

Самостійно заповните за допомогою Таблицы подстановки дану таблицю. Результати розрахунку приведені на рисунку.

Отримані значення представте у вигляді Лінійчатої діаграми. Задайте атрибути діаграми (назва – Чиста поточна вартість проекту для різних обсягів капіталовкладень і процентних ставок, підписи осей та даних, легенду).

Фінансові розрахунки по цінних паперах.

На практиці застосовуються різні варіанти розрахунку тривалості періодів. Особливістю розрахунків по цінних паперах з використанням вбудованих функцій ЕХСЕL є можливість обліку тривалості фінансової операції з точністю до днів. Вбудовані фінансові функції розбиті для вивчення на групи відповідно до специфіки розрахунків по цінних паперах, наведені формули,

використовувані ЕХСЕL, і коментарі до них.

Таблиця 2. Варіанти значення тимчасового базису у фінансових розрахунках

	Тип	Позначення	Коментар
	базису
	0 чи	US (NASD) 30/360	Американський стандарт, місяць дорівнює 30
	опущено		дням, рік – 360 дням
	1	Фактический/	Фактична довжина місяця і року (у тому числі
			116

	фактический	– високосний рік дорівнює 366 дням);
2	Фактический/360	Фактична довжина місяця, рік дорівнює 360
		дням
3	Фактический/365	Фактична довжина місяця, рік дорівнює 365
		дням
4	Европейский 30/360	Європейський стандарт, місяць дорівнює 30
		дням, рік – 360 дням

Функції розрахунку тимчасових параметрів операцій з періодичною виплатою відсотків

При розрахунках по цінних паперах з періодичними виплатами купонного доходу потрібно визначити тривалість інтервалів купонних виплат, а також їхні календарні дати.

Часовий інтервал купона розбивається на дні, що передують моменту придбання цінного папера, і дні, що випливають до чергової оплати купона після придбання цінного папера.

Функція ДНЕЙКУПОН

Дата угоди не може перевищувати дату вступу в силу. Періодичність задається як число:

1 - 1 раз у рік (щорічна виплата); 2 - 2 рази в рік (піврічна виплата);

4 - 4 рази в рік (щоквартальна виплата). Інших значень періодичності не допускається.

Базис задається як число в діапазоні 0 – 4 відповідно до таблиці 2.

Задача 9.

1.Обчисліть число днів у

періоді купона для цінних паперів.

Облігації випущені на таких умовах: дата придбання облігації - 6.09.96; дата закінчення дії облігації - 12.09.98; періодичність виплат купонного доходу – 2 рази в рік; прийнятий базис розрахунків − 1 (тут і далі).

117

=ДНЕЙКУПОН(B2;B3;2;1)

або

=ДНЕЙКУПОН("06.09.2006";"12.09.2008";2;1)

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

Якщо значення дати вводиться безпосередньо в поле Мастера функций, дата запам'ятовується у форматі рядка символів, і формула містить явну вказівку на значення дати.

Задача 10. Самостійно зробіть розрахунки у наступних задачах:

11.1.Клієнт відкриває рахунок у банку, кладе 3000 грн. на 5% і докладатиме в кінці кожного місяця 200 грн. Яка сума буде на рахунку черев 12 місяців?

11.2.Чи вигідно 5000 грн. інвестувати в бізнес на три місяці, якщо пропонуються ставки доходу 7%, 5% і 4% ?

11.3.Підприємець бере позику 5000 грн. у банку під 6% місячних терміном на 6 місяців. Визначити щомісячну виплату і її складові у першому і другому місяцях.

11.4.Підприємець бере позику 4000 грн. у банку під 6% місячних терміном на 4 місяців. Визначити щомісячну виплату і її складові у всіх місяцях.

11.5.Який термін потрібний, щоб повернути банку кредит 3000 грн., взятий під

% за умови повертання в кінці кожного місяця 500 грн.

11.6.Деякий бізнес буде приносити щомісяця дохід (ренту) 500грн. протягом шести місяців. Яка сьогоднішня вартість ренти?

11.7.Підприємець планує отримувати ренту протягом 4 місяців: 500, 700, 900 і 1000 грн. Яка сьогоднішня вартість ренти?

Примітка: У всі листки файлів вставте верхній колонтитул:

РС№

Прізвище студента

Шифр групи

118

Контрольні питання

1.Яке призначення функції БС, БЗРАСПИС, ПЛТ, ПРПЛТ?

2.Яке призначення функції ОСПЛТ, КПЕР, СТАВКА, ПС, НПЗ?

3.Які функції використовуються для обчислення теперішньої вартості майбутніх інвестицій?

4.Які функції використовуються для обчислення майбутньої вартості теперішніх інвестицій?

5.Що означає принцип дисконтування?

6.Яка різниця між рентою і інвестицією, кредитом і депозитом?

7.Які є фінансові функції для виконання розрахункових операцій за кредитами і позиками? Охарактеризуйте їх.

8.Дайте характеристику фінансових функцій для визначення швидкості обороту інвестицій?

9.Наведіть приклади фінансових функцій для розрахунку амортизації. Дайте їх загальний опис.

10.Назвіть і охарактеризуйте фінансові функції для розрахунку операцій за цінними паперами.

11.Опишіть функції посилань і масивів.

Література: [1], [4], [5], [6], [7], [8], [11], [12], [13], [16], [19], [21], [22], [23], [25], [26].

Практичне заняття № 11 – тестування

Студенти повинні вміти: використовувати логічні функції (ЕСЛИ, И, ИЛИ, СЧЕТЕСЛИ), проводити обчислення математичних виразів, будувати зведені таблиці, змінювати структуру зведеної таблиці, підводити підсумки у зведених таблицях, виконувати операції групування та розгрупування даних у зведеної таблиці, представляти дані зведеної таблиці у вигляді діаграми, захищати робочу книгу, захищати комірки, формули, робочі листи, використовувати таблиці підстановки з одним вхідним параметром, таблиці підстановки з двома вхідними параметрами, створювати сценарії, створювати звіти по сценаріях, використовувати фунуції підбору параметра, надбудову Пошук рішення, фінансові функції.

119