Як розрахувати IRR в Excel: 4 найкращі методи у 2023 році

Якщо ви хочете приймати обґрунтовані рішення щодо систематичних інвестиційних планів (SIP) або інвестувати в бізнес-проекти як венчурний капіталіст, вам слід навчитися розраховувати IRR в Excel.

Існує багато способів обчислення рентабельності інвестицій (ROI) для інвестицій, зроблених у фінансові або бізнес-проекти. Хоча обчислення на основі фіксованої ставки є досить простим, це не завжди вибір інструменту для розрахунку рентабельності інвестицій у великому бізнесі чи інвестиційному банківському бізнесі.

Припустимо, ви інвестуєте суму грошей в інвестиційний план на наступні 20 років. У плані сказано, що ви отримуватимете прибуток щокварталу. Тепер, чи розумно інвестувати в такий план, можна визначити за значенням IRR SIP. Чим вище IRR, тим прибутковіший інвестиційний план. Те саме стосується всіх інших капіталомістких інвестицій у підприємства, банківські ощадні рахунки, пайові фонди, 401(k) тощо.

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

Що таке внутрішня норма прибутку (IRR)?

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

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

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

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

Читайте також: Найкращі шаблони бюджету Excel

Які функції IRR у Microsoft Excel?

Завдяки Microsoft вам не потрібно створювати численні формули або виконувати складні розрахунки, щоб отримати IRR інвестиційного плану. Excel має три спеціальні внутрішні функції розрахунку норми прибутку. Дізнайтеся про ці функції тут:

1. IRR для простої внутрішньої норми прибутку

Синтаксис внутрішньої ставки доходу в Excel обчислює відсоткову ставку інвестицій у бізнес-проект, взаємний фонд чи фонд пенсійного планування. Інвестиція повинна містити принаймні одне позитивне та негативне значення. Крім того, грошовий потік у фонд має відбуватися через рівні проміжки часу. Ось як виглядає формула:

=IRR(значення або діапазони клітинок, [припущення аргументу])

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

2. XIRR для нерівного часу повернення

Якщо вам потрібно врахувати графік грошових потоків, то ви повинні використовувати функцію XIRR. Синтаксис такий:

=XIRR(значення або діапазони клітинок, дати, [припущення аргументу])

3. MIRR для реінвестування проектних коштів

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

=MIRR(значення або діапазони клітинок, фінансова_ставка, реінвестована_ставка)

• Значення – це звіт про рух грошових коштів, який складається принаймні з одного позитивного та негативного грошових потоків.
• Finance_rate – це відсотки, які ви сплачуєте позикодавцеві за гроші, позичені на бізнес-проект.
• Reinvest_rate – це процентна ставка, яку ваш бізнес або інвестиційний фонд отримує від грошових потоків після реінвестування.

Читайте також: Як використовувати формулу віднімання Excel

Як обчислити IRR в Excel: за допомогою синтаксису IRR

Перш ніж почати розраховувати IRR для кількох інвестиційних планів або проектів, необхідно впорядкувати дані в Excel. Ось як це робиться:

• Відкрийте робочий аркуш Excel і створіть три заголовки стовпців: Період , Опис і Грошові потоки .
• Заповніть дані, які потрібно обчислити, у відповідних заголовках стовпців.

Використання формули IRR в Excel

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

Дізнайтеся, як розрахувати IRR в Excel за допомогою синтаксису IRR

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

=IRR(C2:C8)

• Натисніть Enter , щоб отримати рентабельність потенційних інвестицій.

Цей розрахунок IRR базується на стандартному аргументі Guess 10%. Припустімо, ви шукаєте 12% прибутку. Ось як вам потрібно ввести припущене значення:

=IRR(C2:C8;30%)

• У таблиці вище ви бачите, що останній грошовий потік є від’ємним. Таким чином, грошовий потік повинен змінитися з позитивного на негативний або з негативного на позитивний.

Подивіться, як розрахувати IRR в Excel за допомогою припущення IRR

• У цьому сценарії використовуйте припущення , щоб знайти точний результат, оскільки IRR дасть два результати.

Як розрахувати IRR в Excel при використанні від’ємного значення Guess

• Аргумент Guess має бути рентабельністю інвестицій, яку ви очікуєте.

Як обчислити IRR в Excel: за допомогою синтаксису XIRR

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

• Створіть заголовок стовпця для дати повернення праворуч від стовпця «Грошові потоки» .
• Тепер введіть дату інвестування вгорі, а потім введіть очікувану дату відсотка або повернення кредитів.

Дізнайтеся, як обчислити IRR в Excel за допомогою синтаксису XIRR

• Після цього використовуйте наведений нижче синтаксис XIRR у будь-якій клітинці, щоб отримати внутрішню норму прибутку для інвестицій із нерегулярним графіком повернення прибутку:

=XIRR(C2:C8;D2:D8)

Як розрахувати IRR в Excel: за допомогою синтаксису MIRR

Щоб зробити значення IRR більш реалістичним, можна скористатися формулою MIRR. Сюди можна включити два обов'язкових чинника: вартість капіталу та норму реінвестування. Нижче наведено кроки для використання MIRR в Excel:

• На поточному аркуші додайте два рядки інформації, як показано на зображенні.

Використання функції MIRR в Excel

• Це вартість капіталу та норма реінвестування .

Дізнайтеся, як розрахувати IRR в Excel за допомогою синтаксису MIRR

• У відповідні клітинки також введіть значення.
• Тепер скористайтеся такою формулою, щоб отримати реалістичну внутрішню норму прибутку:

=MIRR(C2:C8;G2;G3)

Як розрахувати IRR в Excel: за допомогою Goal Seek Excel

IRR і його модифіковані формули виконують 20 ітераційних обчислень перед відображенням результату. Якщо вам потрібна більша точність, ніж ця, ви можете скористатися функцією Excel Goal Seek для обчислення IRR. У цьому методі Excel виконує до 32 000 ітераційних обчислень, перш ніж генерувати повернуте значення. Ось інструкції щодо використання Goal Seek і розрахунку IRR в Excel:

• Вручну встановіть IRR на 11% або щось інше.

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

• Обчисліть NPV нижче IRR за цією формулою:

=NPV(C9;C3:C8)+C2

• Тепер клацніть вкладку «Дані» , а потім виберіть « Аналіз що-якщо» в групі команд «Прогноз» у меню стрічки Excel.
• У контекстному меню, що з’явиться, виберіть «Пошук цілі» .
• Тут у клітинці Set має бути значення NPV. Тоді значення To має бути 0.

Виконайте Goal Seek, щоб обчислити IRR в Excel

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

Результати Goal Seek для розрахунку IRR в Excel

• Тепер натисніть OK , щоб обчислити IRR за допомогою Goal Seek.
• Згідно з поточним набором даних, NPV становитиме 0,00, якщо IRR становить 12%.

У методі Goal Seek вам насправді не потрібно обчислювати IRR на основі грошового потоку та суми інвестицій. Натомість ви встановлюєте очікуваний IRR у комірці IRR і використовуєте його для розрахунку NPV за допомогою грошового потоку та суми інвестицій. Потім ви використовуєте можливість ітераційного обчислення Excel, щоб передбачити, коли NPV стане 0,00, за якого значення IRR.

Поради щодо правильного розрахунку IRR в Excel

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

• Аргумент для частини значень має містити дохід (позитивний грошовий потік) і витрати (негативний грошовий потік), щоб функція працювала точно.
• Функція IRR оброблятиме лише числа в аргументі Values. Він ігноруватиме будь-які інші елементи, як-от логічні значення, порожні клітинки та тексти.
• Мають бути регулярні інтервали грошових потоків, як-от щотижня, щомісяця, щокварталу, щороку тощо. Не обов’язково, щоб грошові потоки були однаковими в доларах.
• Переконайтеся, що ви підготували таблицю грошових потоків у хронологічному порядку. Наприклад, якщо ви платите 100 доларів США щомісяця, упорядкуйте значення грошових потоків і їх дати з січня, лютого, березня тощо.
• У простих розрахунках IRR необов’язково вводити аргумент Guess.
• Якщо використовується припущене значення, формула IRR показуватиме значення, найближче до аргументу припущення. Якщо він показує #NUM! Помилка, змініть значення аргументу Guess.

Які обмеження IRR у Excel?

Перш ніж застосовувати значення IRR, розраховане в Excel, у реальних ситуаціях, ви також повинні знати внутрішні обмеження формули внутрішньої норми прибутку в Excel:

• IRR – це вираз ROI у відсотках. Він не враховує абсолютне значення. Таким чином, він може продемонструвати вищий відсоток прибутку для проекту низької вартості в доларах. Однак венчурні капіталісти та окремі інвестори шукають проекти з вищою вартістю в доларах, хоча відсоток рентабельності інвестицій нижчий, ніж у невеликих проектів.
• Формула IRR повертає значення, враховуючи, що проект реінвестує свій капітал або грошовий потік за тією самою ставкою, що й обчислена IRR. Це не завжди можливо, оскільки ставки прибутку сильно коливаються на ринку капіталу.
• Якщо інвестиційний план або проект передбачає чергування позитивних і негативних грошових потоків, ви можете отримати більше однієї IRR для одного проекту.

Висновок

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

Далі розглянемо,  як використовувати формулу Excel IF-THEN .