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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=IRR(C2:C8)

• удар Введіть щоб отримати рентабельність інвестицій від потенційних інвестицій.

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

=IRR(C2:C8;30%)

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

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

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

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

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

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

=XIRR(C2:C8;D2:D8)

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

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

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

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

=MIRR(C2:C8;G2;G3)

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

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

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

=NPV(C9;C3:C8)+C2

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

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

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

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

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

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

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

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

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

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