Как рассчитать irr в excel

Расчет IRR для нерегулярных денежных потоков

Для того, чтобы рассчитать IRR (внутренняя норма доходности) проекта или инвестиции, в EXCEL есть несколько функций, позволяющих это осуществить. Наиболее универсальным методом является расчет с использованием функции ЧИСТНВДОХ (XIRR). Данная функция позволяет рассчитывать IRR по нерегулярным денежным потокам и учитывает их реинвестирование.

Для расчета, выполняем следующее:

В выбранную ячейку вставляем формулу =ЧИСТНВДОХ(), нажимаем fx.

2. В поле аргумента «Значения» указываем диапазон, содержащий значения денежных потоков.

3. В поле аргумента «Даты» указываем диапазон, содержащий даты поступления потоков.

4. В поле аргумента «Предп» можно указать предполагаемое значение результата, но это делать необязательно. Можно оставить пустым.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Расчет IRR в Excel с помощью функций и графика

IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.

Экономический смысл показателя

Другие наименования: внутренняя норма рентабельности (прибыли, дисконта), внутренний коэффициент окупаемости (эффективности), внутренняя норма.

Коэффициент IRR показывает минимальный уровень доходности инвестиционного проекта. По-другому: это процентная ставка, при которой чистый дисконтированный доход равен нулю.

Формула для расчета показателя вручную:

Формула.

CFt – денежный поток за определенный промежуток времени t;
IC – вложения в проект на этапе вступления (запуска);
t – временной период.

На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:

ВНД выше – следует внимательно рассмотреть данный проект.
ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).

Часто IRR сравнивают в процентами по банковскому депозиту. Если проценты по вкладу выше, то лучше поискать другой инвестиционный проект.

Пример расчета IRR в Excel

Быстро рассчитать IRR можно с помощью встроенной функции ВСД. Синтаксис:

диапазон значений – ссылка на ячейки с числовыми аргументами, для которых нужно посчитать внутреннюю ставку доходности (хотя бы один денежный поток должен иметь отрицательное значение);
предположение – величина, которая предположительно близка к значению ВСД (аргумент необязательный; но если функция выдает ошибку, аргумент нужно задать).

Возьмем условные цифры:

Условные цифры.

Первоначальные затраты составили 150 000, поэтому это числовое значение вошло в таблицу со знаком «минус». Теперь найдем IRR. Формула расчета в Excel:

ВСД.

Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%. Для дальнейшего анализа значение сравнивается с процентной ставкой банковского вклада, или стоимостью капитала данного проекта, или ВНД другого инвестиционного проекта.

Мы рассчитали ВНД для регулярных поступлений денежных средств. При несистематических поступлениях использовать функцию ВСД невозможно, т.к. ставка дисконтирования для каждого денежного потока будет меняться. Решим задачу с помощью функции ЧИСТВНДОХ.

Модифицируем таблицу с исходными данными для примера:

Пример.

Обязательные аргументы функции ЧИСТВНДОХ:

значения – денежные потоки;
даты – массив дат в соответствующем формате.

Формула расчета IRR для несистематических платежей:

ЧИСТВНДОХ.

Существенный недостаток двух предыдущих функций – нереалистичное предположение о ставке реинвестирования. Для корректного учета предположения о реинвестировании рекомендуется использовать функцию МВСД.

значения – платежи;
ставка финансирования – проценты, выплачиваемые за средства в обороте;
ставка реинвестирования.

Предположим, что норма дисконта – 10%. Имеется возможность реинвестирования получаемых доходов по ставке 7% годовых. Рассчитаем модифицированную внутреннюю норму доходности:

МВСД.

Полученная норма прибыли в три раза меньше предыдущего результата. И ниже ставки финансирования. Поэтому прибыльность данного проекта сомнительна.

Графический метод расчета IRR в Excel

Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости (NPV) от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков.

Для примера возьмем проект со следующей структурой денежных потоков:

Денежный поток.

Для расчета NPV в Excel можно использовать функцию ЧПС:

ЧПС.

Так как первый денежный поток происходил в нулевом периоде, то в массив значений он не должен войти. Первоначальную инвестицию нужно прибавить к значению, рассчитанному функцией ЧПС.

Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% (0,10). При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей. В частности, от стоимости капитала (ставки дисконта).

Рассчитаем NPV для разных ставок дисконтирования:

NPV.

Посмотрим результаты на графике:

Пример на графике.

Напомним, что IRR – это ставка дисконтирования, при которой NPV анализируемого проекта равняется нулю. Следовательно, точка пересечения графика NPV с осью абсцисс и есть внутренняя доходность предприятия.

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры

покупка

IRR, внутренняя норма прибыли, является важным фактором для оценки осуществимости проекта. Обычно мы можем установить NPV (чистую приведенную стоимость) равной 0 и вычислить ставку дисконтирования методом проб и ошибок. Теперь вы можете применить функцию IRR, чтобы легко вычислить IRR в Excel.

Синтаксис и аргументы функции

IRR (значения; [предположить])

(1) Наши ценности: Необходимые. Это значения денежных потоков, для которых вы рассчитаете IRR.

Это может быть массив, например или ссылку на ячейки, например B3: G3.
Значения должны состоять из наличных денег (отрицательные значения) и полученных денег (положительные значения).

(2) Guess: Необязательный. Это приблизительное число, близкое к результату IRR. Если опущено, будет принято значение 0.1 (10%).

Возвращаемое значение

Функция IRR возвращает внутреннюю норму прибыли для ряда денежных потоков, которые происходят через регулярные промежутки времени, например ежемесячно или ежегодно.

Примечания по использованию

(1) Функция IRR будет игнорировать текстовые значения, логические значения или пустые ячейки в аргументе значений.

(2) Если функция ВСД возвращает # ЧИСЛО! значение ошибки, или результат расчета далек от ожидаемой процентной ставки, вы можете добавить или изменить аргумент предположения.

Читать:
Создать эмодзи онлайн как на айфоне
(3) Функция IRR возвращает интервал доходности за период. Вы можете использовать формулу = (1 + г) ^ п -1 для преобразования IRR в годовую внутреннюю норму доходности.

Для ежемесячного потока денежных потоков функция IRR будет возвращать месячную интервальную ставку доходности (говорит 0.5%), и вы можете преобразовать его в годовую ставку 6.17% (формула: = (1 + 0.5%) ^ 12-1);

Для квартального потока денежных потоков функция IRR вернет квартальную интервальную норму доходности (говорит 1.5%), и вы можете преобразовать его в годовую ставку 6.14% (формула: = (1 + 1.5%) ^ 4-1);

Если вы перечисляете свои денежные потоки каждые полгода, функция IRR будет возвращать полугодовой интервал доходности (говорит 3%), и вы можете преобразовать его в годовую ставку 6.09% (формула: = (1 + 3%) ^ 2-1)

Примеры формул

Пример 1. Расчет IRR с ежемесячными денежными потоками в Excel

Предположим, вы потратите $5,940 получить газонокосилку в начале этого года, а потом сдать ее в аренду. Имея прогноз аренды на каждый месяц, вы можете легко вычислить IRR (внутреннюю норму доходности) и оценить осуществимость этого проекта.

Для расчета IRR через 6 месяцев значения представляют собой денежные потоки в диапазоне C4: C10, поэтому вы можете применить одну из следующих формул:

= ВНД (C4: C10)

Для расчета IRR через 12 месяцев значения представляют собой денежные потоки в диапазоне C4: C16, поэтому вы можете применить одну из следующих формул:

= ВНД (C4: C16)

Tips:
1. В ячейке C4 начальная стоимость должна быть введена как отрицательное число: -5940.00, так как это сумма, которую вы выплачиваете.
2. В этом примере денежные потоки перечислены по месяцам, поэтому функция IRR возвращает ежемесячную внутреннюю норму прибыли. Если вам нужно вернуть годовую ставку, вы можете использовать эту формулу:

=POWER((IRR(C4:C16)+1),12)-1

Пример 2: Использование функции IRR для расчета ставки доходности, взвешенной по деньгам, в Excel

Допустим, вы купили 5 акции компании А по цене $100 за акцию на 2017/1/1. А потом ты купил 8 акции Компании А по цене $125 на акцию снова на 2018/1/1. Вы получили дивиденды на акцию в размере $8 on 2018/1/1 тоже. Наконец, вы решили продать все акции Компании А по цене $120 за акцию на 2019/1/1. В этом случае вы можете легко вычислить взвешенную по деньгам норму прибыли с помощью функции IRR в Excel.

В этом случае денежные потоки за 3 года можно рассчитать следующим образом:
(1) В 2017 году вы заплатили 500 долларов (= -100 * 5);
(2) В 2018 году вы получили дивиденды по акциям в размере 40 долларов США (= 5 * 8) и заплатил 1,000 долларов (= -125 * 8) на покупку 8 акций, поэтому общий денежный поток составит — 960 долларов (= -1000 + 40);
(3) В 2019 году вы получили 1560 долларов (= 120 * (5 + 8));

Поэтому вы можете применить одну из формул ниже для расчета нормы прибыли, взвешенной по денежным средствам.

= IRR (G4: G6)

Как рассчитать показатели NPV и IRR в Excel? Финансовый анализ инвестиционного портфеля

Научитесь использовать все прикладные инструменты из функционала MS Excel.

В статье будет приведен расчет показателей эффективности npv irr проекта, с учетом дисконтирования.

ЧДД или чистый дисконтированный доход от инвестиционного проекта;

Внутренняя норма доходности.

Рассмотрим эти два показателя подробнее и рассчитаем пример работы с ними в Excel. Еще больше о возможностях Excel можно узнать на нашем открытом курсе «Аналитика в Excel».

Что такое NPV

Net Present Value (NPV, чистый дисконтированный доход) — один из самых распространенных показателей эффективности инвестиционного проекта.

Это разность между дисконтированными по времени поступлениями от проекта и инвестиционными затратами на него.

Метод расчета NPV:

Определяем текущую стоимость затрат (инвестиции в проект)

Производим расчет текущей стоимости денежных поступлений от проекта, для этого доходы за каждый отчетный период приводятся к текущей дате

CF – денежный поток;
r – ставка дисконта.

3. Сравниваем текущую стоимость инвестиций (наши затраты) в проект (Io) с текущей стоимостью доходов (PV). Разница между ними будет чистый дисконтированный доход — NPV.

NPV показывает инвестору доход или убыток от вложений средств в проект по сравнению с доходом от хранения денег в банке.

Если NPV больше 0, то инвестиции принесут больше дохода, нежели чем аналогичный вклад в банке.

Формула 1 модифицируется если инвестиционные вложения в проект осуществляются в несколько этапов (периодов).

CF – денежный поток;
I – сумма инвестиционных вложений в проект в t-ом периоде;
r – ставка дисконтирования;
n – количество периодов.

Internal Rate of Return (Внутренняя норма доходности, IRR) определяет ставку дисконтирования при которой инвестиции равны 0 (NPV=0), или другими словами затраты на проект равны его доходам.

IRR = r, при которой NPV = f(r) = 0, находим из формулы:

CF – денежный поток;
I – сумма инвестиционных вложений в проект в t-ом периоде;
n – количество периодов.

Этот показатель показывает норму доходности или возможные затраты при вложении денежных средств в проект (в процентах).

Пример расчета NPV в Excel

В MS Excel 2010 для расчета NPV используется функция =ЧПС().

Найдем чистый дисконтированный доход (NPV) проекта, требующего вложений инвестиций на 90 тыс. руб., и денежный поток которого распределен по времени рис 1. , и ставка дисконта равна 10%.

Рассчитаем показатель NPV по формуле Excel:

D3 – ставка дисконта;
C3 – вложения в 0 периоде (наши инвестиционные затраты в проект);
C4:C11 – денежный поток проекта за 8 периодов.

В итоге показатель чистого дисконтированного дохода равен 51,07 >0, это говорит о том, что в проект стоит инвестировать.

Расчет IRR в Excel

Для определения IRR в Excel используется встроенная функция

Но так как у нас в примере данные поступали в равные интервалы времени можно использовать функцию

Доходность вложения в проект равна 38%.

В завершение картинка финансового анализа проекта целиком.

Научитесь использовать все прикладные инструменты из функционала MS Excel.