Як розрахувати IRR в Excel (проста формула)

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

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

Пояснюється внутрішня норма рентабельності (IRR)

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

IRR відображається у відсотках і може бути використаний для вирішення того, чи є проект (інвестиція) прибутковим для компанії чи ні.

Дозвольте мені пояснити IRR простим прикладом.

Припустимо, ви плануєте придбати компанію за 50 000 доларів, яка буде приносити 10 000 доларів щороку протягом наступних 10 років. Ви можете використати ці дані для розрахунку IRR цього проекту, тобто норма прибутку, яку ви отримуєте від інвестицій у розмірі 50000 доларів США.

У наведеному вище прикладі IRR становить 15% (ми побачимо, як це розрахувати пізніше у підручнику). Це означає, що це еквівалентно тому, що ви вкладете свої гроші за ставкою 15% або повернетесь протягом 10 років.

Отримавши значення IRR, ви можете використовувати його для прийняття рішень. Отже, якщо у вас є інший проект, де IRR становить більше 15%, вам слід замість цього інвестувати у цей проект.

Або, якщо ви плануєте взяти позику або залучити капітал і придбати цей проект за 50000 доларів, переконайтеся, що вартість капіталу менша за 15% (інакше ви сплачуєте більше за вартість капіталу, ніж робите з проект).

Функція IRR в Excel - Синтаксис

Excel дозволяє розрахувати внутрішню норму прибутку за допомогою функції IRR. Ця функція має такі параметри:

= IRR (значення, [здогадка])

• цінності - масив клітинок, що містить числа, для яких потрібно розрахувати внутрішню норму прибутку.
• відгадати - число, яке, на вашу думку, близьке до результату IRR (воно не є обов’язковим і за замовчуванням становить 0,1 - 10%). Це використовується, коли є можливість отримати кілька результатів, і в цьому випадку функція повертає результат, найближчий до значення аргументу вгадки.

Ось деякі важливі передумови для використання функції:

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

Якщо ви хочете розрахувати значення IRR, коли грошовий потік надходить через різні проміжки часу, вам слід скористатися Функція XIRR в Excel, що також дозволяє вказати дати для кожного грошового потоку. Приклад цього розглянуто пізніше в підручнику

Тепер давайте подивимося на деякий приклад, щоб краще зрозуміти, як використовувати функцію IRR в Excel.

Розрахунок IRR для змінних грошових потоків

Припустимо, у вас є набір даних, як показано нижче, де ми маємо початкові інвестиції у розмірі 30 000 доларів США, а потім змінюємо грошовий потік/дохід від нього протягом наступних шести років.

Для цих даних нам потрібно розрахувати IRR, що можна зробити за формулою нижче:

= IRR (D2: D8)

Результат функції такий 8.22%, що є IRR грошового потоку через шість років.

Примітка: Якщо функція повертає a #NUM! помилку, вам слід заповнити параметр "здогадка" у формулі. Це трапляється, коли формула вважає, що декілька значень можуть бути правильними, і їй потрібно мати значення припущення, щоб повернути IRR, найближчу до передбаченої нами. У більшості випадків вам не доведеться користуватися цим

Дізнайтеся, коли інвестиції приносять позитивну IRR

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

Припустимо, у нас є набір даних нижче, де я маю всі грошові потоки, перераховані у стовпці C.

Ідея полягає в тому, щоб з'ясувати рік, у якому IRR цієї інвестиції стає позитивним (вказуючи, коли проект вирівнює та стає прибутковим).

Для цього замість розрахунку IRR для всього проекту ми дізнаємось IRR за кожен рік.

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

= IRR ($ C $ 2: C3)

Як бачите, IRR після 1 року (значення D2: D3) становить -80%, після 2 року (D2: D4) -52%тощо.

Цей огляд показує нам, що інвестиції в розмірі 30 000 доларів США з урахуванням грошового потоку мають позитивну IRR після п’ятого року.

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

Зауважте, що у наведеній вище формулі посилання на діапазон є змішаним, тобто перша посилання на клітинку ($ C $ 2) блокується знаками доларів перед номером рядка та літерою стовпця, а друга посилання (C3) - не заблоковані.

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

Використання функції IRR для порівняння кількох проектів

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

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

Щоб отримати найкращий проект (який має найвищу IRR, нам доведеться розрахувати IRR для кожного проекту, використовуючи просту формулу IRR:

= IRR (C2: C8)

Наведена вище формула дасть IRR для проекту 1. Аналогічно, ви також можете розрахувати IRR для інших двох проектів.

Як ви можете бачити:

• Проект 1 має IRR 5.60%
• Проект 2 має IRR 1.75%
• Проект 3 має IRR 14.71%.

Якщо припустити, що вартість капіталу становить 4,50%, ми можемо зробити висновок, що інвестиції 2 не є прийнятними (оскільки це призведе до збитків), тоді як інвестиції 3 є найбільш прибутковими з найвищою внутрішньою нормою прибутку.

Отже, якщо вам доведеться прийняти рішення інвестувати лише в один проект, вам слід перейти до проекту 3, а якщо ви зможете інвестувати у декілька проектів, ви можете інвестувати у проекти 1 та 3.

Визначення: Якщо вам цікаво, скільки коштує капітал, це гроші, які вам доведеться заплатити, щоб отримати доступ до грошей. Наприклад, якщо ви берете 100 000 доларів США в кредит під 4,5% на рік, ваша вартість капіталу складає 4,5%. Аналогічно, якщо ви випускаєте привілейовані акції, що обіцяють a 5% прибутку, щоб отримати 100 тис., Ваша вартість капіталу складе 5%. У реальних життєвих сценаріях компанія зазвичай збирає гроші з різних джерел, і вартість капіталу - це середньозважена величина всіх цих джерел капіталу.

Розрахунок IRR для нерегулярних грошових потоків

Одним з обмежень функції IRR в Excel є те, що грошові потоки повинні бути періодичними з однаковим інтервалом між ними.

Але в реальному житті можуть бути випадки, коли ваші проекти окупаються через нерівні проміжки часу.

Наприклад, нижче набір даних, де грошові потоки відбуваються через нерегулярні проміжки часу (див. Дати у стовпці А).

У цьому прикладі ми не можемо використовувати звичайну функцію IRR, але є ще одна функція, яка може це зробити - Функція XIRR.

Функція XIRR враховує грошові потоки, а також дати, що дозволяє йому враховувати нерегулярні грошові потоки та надавати коригувальні IRR.

У цьому прикладі IRR можна розрахувати за формулою нижче:

= XIRR (B2: B8, A2: A8)

У наведеній вище формулі грошові потоки вказуються як перший аргумент, а дати - як другий аргумент.

Якщо ця формула повертає #NUM! помилку, вам слід ввести третій аргумент із приблизним IRR, який ви очікуєте. Не хвилюйтесь, це не повинно бути точним або навіть дуже близьким, це лише наближення того, що IRR, на вашу думку, це дасть. Це допомагає формулі краще повторюватись і дає результат.

IRR проти NPV - що краще?

Що стосується оцінки проектів, використовуються як NPV, так і IRR, але NPV - більш надійний метод.

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

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

З іншого боку, коли ви розраховуєте IRR для проекту, він повідомляє вам, якою буде норма повернення майбутніх грошових потоків, щоб ви отримали суму, еквівалентну поточному відтоку. Наприклад, якщо сьогодні ви витрачаєте 100 тисяч доларів на проект, який має IRR або 10%, це говорить про те, що якщо ви знижите всі майбутні грошові потоки за 10 -процентною ставкою дисконту, ви отримаєте 100 тисяч доларів.

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

У такому випадку найкраще піти з рекомендацією, яку ви отримаєте, використовуючи метод NPV.

Загалом, метод IRR має деякі недоліки, які роблять метод NPV більш надійним:

• Вищий або метод передбачає, що всі майбутні грошові потоки, генеровані від проекту, будуть реінвестовані з тією ж ставкою прибутку (тобто IRR проекту). в більшості випадків це є необґрунтованим припущенням, оскільки більшість грошових потоків буде реінвестовано в інші проекти, які можуть мати інший ІР або незахищеність, наприклад, облігації, які мали б набагато нижчу норму прибутку.
• Якщо у вас є кілька відтоків та припливів у проекті, для цього проекту буде декілька IRR. Це знову ускладнює порівняння.

Незважаючи на свої недоліки, IRR є хорошим способом оцінки проекту і може використовуватися разом із методом NPV, коли ви вирішуєте, який проект (проекти) обрати.

У цьому уроці я показав вам, як користуватися Функція IRR в Excel. Я також розповів, як розрахувати IRR у разі, якщо у вас нерегулярні грошові потоки за допомогою функції XIRR.

Сподіваюся, вам цей підручник був корисним!