Завантажте зразок робочої книги
Цей підручник з Excel демонструє, як користуватися Функція Excel XIRR в Excel для розрахунку внутрішньої норми прибутку набору грошових потоків із прикладами формул.
Опис функції XIRR:
Функція XIRR Розраховує внутрішню норму прибутку для ряду грошових потоків. Грошові потоки не повинні бути періодичними.
Щоб використовувати функцію робочого аркуша Excel XIRR, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис та входи функції XIRR:
| 1 | = XIRR (значення, дати, припущення) |
цінності - Аргумент значень складається з масиву або посилання на діапазон комірок, що представляють ряд грошових потоків. Аргумент значень повинен містити принаймні одне негативне та одне позитивне значення для обчислення.
дати - Цей аргумент стосується масиву дат, на які відбуваються грошові потоки. Його довжина повинна бути такою ж, як довжина масиву значень. Першою датою повинна бути дата початкової інвестиції/позики, а наступні дати подальших платежів мають відбутися пізніше першої дати.
відгадати - НЕОБОВ’ЯЗКОВО: це оцінка того, якою повинна бути внутрішня норма прибутку. Якщо цей аргумент пропущено, він приймає значення за замовчуванням 0,1 або 10%.
Що таке функція Excel XIRR?
XIRR означає розширена внутрішня норма прибутку. Функція Excel XIRR використовується для розрахунку рентабельності інвестицій, в якій серія грошових потоків відбувається на визначені дати. Функція XIRR завжди повертає річне значення IRR незалежно від інтервалів грошових потоків.
Формула XIRR
Значення XIRR обчислюється як значення ставка що задовольняє такому рівнянню:
Де:
n = кількість періодів часу
j = номер періоду
ставка = ставка дисконтування або процентна ставка
dj = j -й платіж або останній платіж
d1 = 0го оплата
Pj = j -й платіж або останній платіж
Розрахувати внутрішню норму прибутку інвестиції
Врахуйте, що ви зробили інвестиції в 20 000 доларів у 2022 році і очікуєте отримати прибуток у найближчі п'ять років. Грошові потоки та відповідні їм дати згадуються у таблиці вище.
Тут початкові інвестиції вводяться з від’ємним знаком, оскільки це вихідний платіж та прибуток представлені з позитивними значеннями. Аргумент припущення був опущений, тому функція XIRR працювала за значенням за замовчуванням 0,1 або 10%.
Формула, яка використовується для розрахунку процентної ставки або внутрішньої норми прибутку:
| 1 | = XIRR (B4: B9, C4: C9) |
Внутрішня норма прибутку для цієї інвестиції становить
XIRR = 3.84%
Розрахувати внутрішню норму прибутку пайового фонду
Припустимо, ви почали інвестувати у пайовий фонд з початковими інвестиціями 5000 доларів у перший рік. Побачивши хороші результати щодо ваших початкових інвестицій, ви збільшували свої інвестиції щороку на 5% протягом наступних дев’яти років. Через дев’ять років ви отримали загальний прибуток від інвестицій у розмірі 90 000 доларів США.
У наведеній вище таблиці перші дев’ять значень аргументу «Значення» є від’ємними, оскільки він показує, що платежі надходять з нашого рахунку у пайові фонди.
Для обчислення використовується формула:
| 1 | = XIRR (B4: B13, C4: C13) |
Після розрахунку внутрішня норма прибутку, яку ви отримали від ваших початкових та додаткових інвестицій, становить
XIRR = 9,15%
XIRR у Google Таблицях
Усі наведені вище приклади працюють точно так само в Google Таблицях, як і в Excel.
додаткові нотатки
Використовуйте функцію XIRR для розрахунку внутрішньої норми прибутку інвестицій.
Грошові потоки не повинні бути періодичними. Натомість потрібно ввести відповідні дати разом із грошовими потоками.
Результат функції XIRR може виглядати як десятковий знак. Змініть форматування номера комірки на Відсоток, щоб відобразити результат функції у відсотках:
Приклади XIRR у VBA
Ви також можете використовувати функцію XIRR у VBA. Тип:application.worksheetfunction.xirr (значення, дати, припущення)
Для аргументів функції (значення тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Повернення до списку всіх функцій Excel
