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

При оцінці та аналізі варіантів інвестицій часто потрібно одержати кінцеві значення для різних наборів початкових даних. Однією з переваг Excel є можливість швидкого розв'язування подібних задач і автоматич­ного перерахунку результатів при зміні початкових даних. Наприклад, можна побудувати фінансову модель для різних значень процентних ста­вок і періодичних виплат і вибрати оптимальний розв'язок. Для розв'я­зування подібних задач в Excel використовується Таблиця підстановки, яка містить результати підстановки різних значень у формулу. Принцип використання цього засобу полягає в наступному.

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

128

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

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

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

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

Для розв'язування задачі доцільно використати Таблицю підстанов­ки Excel. Попередньо потрібно підготувати початкові дані на робочому аркуші Excel, як це показано на рис. 3.18.

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

Рис. 3.18

1. ввести в комірку D7 формулу для розрахунку періодичних сталих витрат з позички за умови, що вона повністю погашається протя­гом строку позички, =ППЛАТ (С4/12;СЗ*12;С2);

2. виділити діапазон комірок, який містить початкові значення процентних ставок і формулу для розрахунку — C7:D13. По­чаткові дані в нашому прикладі розміщені в стовпчикові С8: С13, тому результати підстановки також будуть розміщеними в стовпчикові D8:D13;

129

Зацеркляний М. М., Мельников О. ф.

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

Рис. 3.19

3) в меню Дані виконується ко­ манда Таблиця підстанов­ ки. На екрані з'являється ді­ алогове вікно Таблиця під­ становки (рис. 3.19). Це вікно використовується для задания робочої комірки,

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

4) при натисканні кнопки OK Excel заповнює стовпчик результатів, як показано на рис. 3.20.

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

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

В нашому прикладі для розрахунку платежів за процентами за перший період для кожного значення процентної ставки в комірку Е7 потрібно ввести формулу =ПЛПРОЦ (С4/12;1;СЗ*12;С2) і повторити всі кроки, описані вище. Результати розрахунку приведені на рис. 3.21.

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

Одержана таблиця автоматично перераховується при зміні суми і строку позички, тобто при внесенні змін в комірки С2 і СЗ.

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

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

130

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

Рис. 3.21

Для створення Таблиці підстановки для такої задачі потрібно ви­конати такі дії:

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

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

3. ввести формулу для розрахунку на перетині рядка і стовпчика, які містять дві множини початкових значень, тобто в комірку В7. Якщо початкові дані введені на робочому аркуші Excel так,

9*

131

Рис. 3.22

Рис. 3.23

Рис. 3.24

як показано на рис. 3.22, то формула для розрахунку сталих пе­ріодичних виплат для позички при повному її погашенні протягом строку позички має такий вигляд: =ППЛАТ (С4/12;СЗ*12;С2).

132

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

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

5. виконується в меню Дані команда Таблиця підстановки і заповню­ється діалогове вікно, як показано на рис. 3.23;

6. при натисканні кнопки OK Excel виконує розрахунок таблиці під­становки. Результат розрахунку приведений на рис. 3.24.

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

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

Оскільки розрахунок чистої поточної вартості зв'язаний із дис­контуванням, то найбільш важливим моментом тут є вибір ставки про­цента, за яким виконується дисконтування. Тому при оцінці ефектив­ності капіталовкладень важко аналізувати вплив різних процентних ставок на чисту поточну вартість проекту. В Excel такий розрахунок можна здійснити за допомогою Таблиці підстановки і функції НПЗ. Розглянемо такий приклад.

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

На робочому аркуші Excel початкові дані можна подати так, як на рис. 3.25. В комірку D3 розміщена передбачувана величина початкових витрат із проекту (1280 млн гривень) зі знаком «мінус», оскільки цей по­тік рухається протилежно очікуваним доходам. Це значення необхідно включити в список аргументів функції НПЗ, оскільки чиста поточна вар­тість розраховується на початок року, а капіталовкладення, за умовою

133

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

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

Рис. 3.25

Рис. 3.26

Рис. 3.27

задачі, будуть здійснюватися в кінці року. В комірки C9:F9 розміщуються різні об'єми капіталовкладень, як показано на рис. 3.25. Для розрахун­ку чистої поточної вартості візьмемо значення процентних ставок 13 %, 13,8 %, 15 %. В комірку В9 за допомогою Майстра функцій розміщуєть­ся формула: =НПЗ (D2;D3;D4;D5;D6:D7).

134

Для побудови Таблиці підстановки необхідно виділити діапазон ко­мірок В9: F12, в меню Дані виконати команду Таблиці підстановки, а ді­алогове вікно заповнити таким чином, як показано на рис. 3.26.

Результати розрахунку приведені на рис. 3.27.

Очевидно, що максимальна величина чистої поточної вартості досягається при мінімальних капіталовкладеннях і мінімальній ставці дис­контування. Аналізуючи одержані результати, потрібно відмітити, що деякі варіанти дають практично однакову величину чистої поточної вартості, на­приклад, при капіталовкладеннях 1310 млн гривень досягається та ж вели­чина NPV, що і при інвестиціях величиною 1270 млн гривень і ставці 15 %.

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