Розрахунок NPV в Excel. Розрахунок IRR в Excel

1. Як розрахувати NPV в Excel
2. Розрахунок NPV за допомогою формули ЧПС
3. Як оцінити ефективність інвестиційного проекту в Excel за допомогою IRR
4. Формула IRR в Excel
5. ★ Топ-6 найважливіших статей для фінансового директора:
6. Як перевіряти банки на чесність, розрахувавши NPV і IRR в Excel

Знаєте, як розрахувати NPV Excel? А IRR? У статті розберемо ці найважливіші для фінансиста формули і розберемо приклад розрахунку NPV і IRR інвестиційного проекту.

Фінансових формул в Excel багато. Розберемо найважливіші формули, які дозволять розрахувати:

Також розглянемо деякі нюанси і хитрощі використання цих формул. Всі розрахунки можна знайти в доданому файлі. Основний акцент зроблений на функції Excel.

Як розрахувати NPV в Excel

Розглянемо умовний приклад: є проект, який щорічно протягом 5 років буде приносити 250 000 рублів. На його реалізацію потрібен 1 000 000 рублів. Ставка дисконтування - 10%.
Якщо грошові потоки, наведені до поточного періоду, більше інвестованих грошей (NPV> 0), то проект вигідний. В іншому випадку - ні.

Формула NPV виглядає так:

Якщо грошові потоки, наведені до поточного періоду, більше інвестованих грошей (NPV> 0), то проект вигідний. В іншому випадку немає.

Щоб розрахувати NPV, нам буде потрібно зробити в Excel наступне:

Додамо порядкові номери років: 0 - стартовий рік, до нього наводяться потоки.
1, 2, 3 і т.д. - це роки реалізації проекту. У формулі на малюнку виконані дії, які прописані вище після знака суми (Σ).

Грошовий потік за період ділимо на суму 1 і ставку дисконтування, зведену в ступінь відповідного року. Розрахована рядок являє собою дисконтований грошовий потік. Щоб отримати значення NPV, досить знайти загальну суму всього рядка.
Виходить «-52 303". Проект невигідний.

Розрахунок NPV за допомогою формули ЧПС

Щоб провести розрахунок NPV в Excel, необов'язково готувати таку таблицю. Досить скористатися формулою ЧПС в Excel. Де ЧПС - це ставка дисконтування; діапазон дисконтируемой значень. Тобто досить вказати клітинку з відсотком і з грошовими потоками. Але при використанні цієї формули з незвички фінансисти часто припускаються помилки:

Взагалі-то результати повинні збігатися. Чому ж тут різні значення? Справа в тому, що ЧПС в Excel починає дисконтувати з першого ж значення. Тобто, вона шукає наведену вартість. А стартові інвестиції потрібно забирати після. Правильний запис формули в нашому випадку буде мати такий вигляд:

Стартові інвестиції «виведені» за межі дисконтируемой діапазону (червоний колір) і додані (взагалі-то, відняті: так як стартові інвестиції вже йдуть з мінусом, то D8 потрібно додавати) окремо. Тепер результат такий же.

Як оцінити ефективність інвестиційного проекту в Excel за допомогою IRR

IRR - це ще один показник для оцінки інвестиційних проектів. IRR дає відповідь на питання: а яка повинна бути ставка, щоб NPV стала = 0?

Формула IRR в Excel

Якщо ставка дисконтування <IRR, то проект варто прийняти, якщо немає - відмовитися. Як розрахувати IRR в Excel? Дуже просто: Підставляємо в функцію ВСД підсумковий грошовий потік.

IRR виявився менше ставки прибутковості. Проект невигідний (той же висновок, що і при NPV).

NPV і IRR по праву вважаються головними економічними критеріями. Їх використовують і для інвестиційної оцінки проектів, і для оцінки вартості існуючого бізнесу. В тому числі, показник EVA (Economic Value Added) вважається хорошим критерієм, оскільки при правильному розрахунку він дорівнює NPV.

★ Топ-6 найважливіших статей для фінансового директора:

Де ще стане в нагоді розрахунок NPV і IRR в Excel

NPV і IRR фінансові фахівці можуть використовувати в більш прикладних справах. Наприклад, у вирішенні питання з банками про реальну кредитній ставці. Справа в тому, що при видачі кредиту банки розраховують суму ануїтету або, іншими словами, рівномірного платежу. Щоб спланувати виплати по кредиту важливо розуміти, як проводиться розрахунок ануїтету.
Припустимо, ви збираєтеся взяти кредит 1 000 000 рублів на 5 років під 10% річних. Платити будете раз на рік рівними платежами. Формулу розрахунку ануїтету з підручника з фінансового менеджменту тут наводити не будемо. Наведемо формулу Excel:

ПЛТ - ставка дисконтування; кількість періодів; сума кредиту яку ви берете.

У формулі є ще два необов'язкових пункту. Сума, яка повинна залишитися (за замовчуванням нуль) і як розрахувати суму: на початок місяця - ставлять 1, на кінець місяця - нуль. У більшості випадків ці пункти не потрібні, тому їх можна не ставити взагалі. Разом сума ануїтету визначається так:

Сума щорічного платежу виходить відразу з мінусом. Цю суму потрібно щороку платити банку. У ній містяться дві частини:

1. Платіж по кредиту - беремо 10% (відсоток по кредиту) від суми заборгованості на початок періоду.
2. Тіло кредиту - різниця між щорічним платежем і платежем за відсотками (в Excel можна знайти формули, які розрахують вам і ці платежі).

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

Річну ставку ми б розділили на 12 (привели до щомісячного показника) і взяли не 5 періодів, а 5 • 12 = 60 місяців. Отримуємо щомісячний платіж, який дорівнює 21 247 рублів.

Як перевіряти банки на чесність, розрахувавши NPV і IRR в Excel

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

Коли кредит і платежі по ньому розраховані правильно, то NPV, взятий по тій же процентній ставці, дорівнює нулю. А IRR показує ту саму ставку. Коли банк робить нам пропозицію, від якої неможливо відмовитися, і яке збільшить кредитну ставку «всього» на скільки-то відсотків - не вірте і перераховуйте. Наведу приклад, як розрахувати IRR і зрозуміти реальний відсоток по кредиту.

Банк запропонував страховку «всього» 2% від суми кредиту в рік. Думаєте це приріст всього в 2%? Ні. Справа в тому, що справжній кредит на початку кожного року зменшується:

В результаті видно, що NPV не дорівнює нулю. А реальний відсоток не 10, а 12,9%. Зверніть увагу, тут же виросла сума переплати. Якщо вас збентежить це, вам можуть запропонувати «ще більш вигідні умови» - заплатити переплату зараз, а решту потім, меншими платежами, або як в нашому прикладі, просто заплатити більше, а потім менше. Сума переплати при цьому не зміниться, а от відсоток - значно.

Що тут зроблено? З кожного наступного платежу взята сума 43 797 рублів і додана до першого ж платежу. Якщо для реального сектора фінансова математика про «гроші вчора - гроші завтра» здається дещо віддаленій від життя, для банків - це реальний прибуток. Тому вони навантажують вже перший платіж. А ви за допомогою простих формул це зможете відстежити і підготувати основу для подальших переговорів.

PS Не забудьте, якщо мова йде про щомісячні платежі, множте на 12.