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

Содержание

Расчет доходности портфеля методом XIRR. Инструкция для инвесторов

про Инвестиции

В этой статье расскажем об одном из простых и универсальных, но эффективных способов расчета доходности по формуле XIRR.

Расчет доходности портфеля — обязательное условие грамотного инвестирования. Если просто покупать и продавать активы по принципу «тут заработал, там потерял», вы не будете понимать, правильно ли выбрали стратегию, приближает ли она к поставленным целям. Мы расскажем об одном из простых и универсальных, но эффективных способов расчета доходности по формуле XIRR (расширенная внутренняя норма доходности).

Зачем рассчитывать доходность?

1) Чтобы быть уверенным, что доход инвестиционного портфеля выше инфляции. В 2020 году официальный показатель инфляции в России — 4,9%. Реальный еще выше. Если инвестиции принесли вам меньше, чем 4,9%, то большого смысла в них нет — по факту, вы просто остались в нуле, а не сохранили деньги.

2) Чтобы видеть, что инвестирование менее рискованный инструментов вложения средств. Например, в вашем банке можно открыть депозит под 5% годовых. Инвестиции приносят вам столько же. В этом случае выгоднее хранить свободные средства на вкладе — это безопаснее.

Что такое XIRR?

XIRR — функция для расчета доходности. Ее используют в электронных таблицах — Microsoft Office Excel, Google Таблицах. Это расширенный вариант функции IRR, которая рассчитывает внутреннюю норму доходности с учетом ставок дисконтирования за определенный период времени.

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

Как рассчитывать доход по формуле XIRR?

Для расчета вам нужно заполнить Excel или Google таблицу, где указаны все денежные потоки по вашему инвестиционному счету за нужный период, например, год. Необходимо поставить, когда были совершены пополнения и снятия средств.

Расходные операции должны быть со знаком минус. В конце проставляется стоимость инвестиционного портфеля на конец периода. Здесь нужно использовать сумму с минусом, как будто вы снимаете все деньги со счета, иначе функция сработает неверно.

Если выплачиваются дивиденды, то их тоже нужно указать — со знаком минус, если они приходят на карту или сторонний счет, и со знаком плюс, если на инвестиционный.

Вот как должна выглядеть итоговая таблица. Лучше вести ее и вписывать операции по мере их совершения, чтобы потом ничего не упустить.

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

= XIRR (B2: B5; C2: C5) * 100 — для Google
ЧИСТВНДОХ (B2: B5; C2: C5) * 100 — для Excel

В нашем примере получается процент доходности 14%.

Учтите, что доходность нужно считать в процентах годовых . Если вы инвестировали не весь год, то нужно произвести корректировку результата. Например, вы инвестировали полгода. Полученный на прошлом шаге процент доходности 14% нужно скорректировать по формуле:

доходность × количество дней в году / количество дней, когда вы инвестировали средства

Соответственно получаем: 14 × 366 / 180 = 28

28% — реальная годовая доходность вашей стратегии инвестирования.

Конечно, брокерские компании и предоставляют отчеты, где можно отследить и движения средств по счету, и показатель доходности. Но грамотный инвестор никогда не упускает возможность использовать все инструменты для проверки своей стратегии. Тем более, когда метод расчета простой, универсальный и занимает всего несколько минут.

Функция XIRR — формула, примеры, руководство по использованию XIRR

Функция XIRR относится к финансовым функциям Excel. Он рассчитает внутреннюю норму доходности (IRR) Внутренняя норма доходности (IRR) Внутренняя норма доходности (IRR) — это ставка дисконтирования, которая делает чистую приведенную стоимость (NPV) проекта равной нулю. Другими словами, это ожидаемая совокупная годовая норма прибыли, которая будет получена от проекта или инвестиций. для серии денежных потоков, которые могут быть непериодическими. Это достигается путем присвоения конкретных дат каждому отдельному денежному потоку. Основное преимущество использования функции XIRR Excel состоит в том, что такие неравномерные по времени денежные потоки можно точно смоделировать. Чтобы узнать больше, прочтите, почему всегда использовать XIRR вместо IRR XIRR против IRR Зачем использовать XIRR против IRR. XIRR назначает конкретные даты каждому отдельному денежному потоку, что делает его более точным, чем IRR, при построении финансовой модели в Excel. в моделировании Excel.

В финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем его создавать. функция XIRR полезна для определения стоимости инвестиций или понимания осуществимости проекта, который не имеет регулярных периодических денежных потоков. Это помогает нам понять норму прибыли от инвестиций. Следовательно, он обычно используется при оценке и выборе между двумя или более инвестициями.

Формула

= XIRR (значения, даты; [предположение])

В формуле используются следующие аргументы:

Значения (обязательный аргумент) — это массив значений, представляющих серию денежных потоков. Вместо массива это может быть ссылка на диапазон ячеек, содержащих значения.
Даты (обязательный аргумент) — это серия дат, соответствующих заданным значениям. Последующие даты должны быть позже первой даты, так как первая дата — это дата начала, а последующие даты — это будущие даты исходящих платежей или доходов.
[предположение] (необязательный аргумент) — это первоначальное предположение или оценка того, какой будет IRR. Если он не указан, Excel принимает значение по умолчанию 10%.

Excel использует итеративный метод расчета XIRR. Используя скорость изменения (начиная с [предположить]), XIRR циклически перебирает вычисления до тех пор, пока результат не будет точным в пределах 0,000001%.

Как использовать функцию XIRR в Excel?

Чтобы понять использование функции XIRR, давайте рассмотрим несколько примеров:

Пример XIRR

Предположим, что проект стартовал 1 января 2018 года. Проект дает нам денежные потоки в середине первого года, через 6 месяцев, затем через 1,5 года, 2 года, 3,5 года и затем ежегодно. Приведенные данные показаны ниже:

Используемая формула будет следующей:

Мы оставим предположение пустым, поэтому Excel примет значение по умолчанию 10%.

Получаем результат ниже:

Что нужно помнить о функции XIRR

Числа в датах округляются до целых.
XNPV и XIRR тесно связаны. Ставка доходности, рассчитываемая XIRR, — это процентная ставка, соответствующая XNPV = 0.
Даты следует вводить как ссылки на ячейки, содержащие даты или значения, полученные из формул Excel.
# ЧИСЛО! ошибка — возникает, если:
Массивы значений и дат имеют разную длину;
Данные массивы не содержат хотя бы одного отрицательного и хотя бы одного положительного значения;
Любая из указанных дат предшествует первой указанной дате;
Расчет не сходится после 100 итераций.
#ЦЕННОСТЬ! ошибка — возникает, когда одна из указанных дат не может быть распознана Excel как допустимые даты.
Щелкните здесь, чтобы загрузить образец файла Excel
Дополнительные ресурсы
Спасибо за то, что прочитали финансовое руководство по функции Excel XIRR. Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными финансовыми ресурсами:
Функции Excel для финансов Excel для финансов Это руководство по Excel для финансов расскажет о 10 основных формулах и функциях, которые вы должны знать, чтобы стать отличным финансовым аналитиком в Excel. В этом руководстве есть примеры, скриншоты и пошаговые инструкции. В конце скачайте бесплатный шаблон Excel, который включает в себя все финансовые функции, описанные в руководстве.
Продвинутый курс Excel
Расширенные формулы Excel, которые вы должны знать Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Вы должны знать дополнительные функции Excel. Изучите 10 лучших формул Excel, которые регулярно использует каждый финансовый аналитик мирового уровня. Эти навыки улучшат вашу работу с таблицами в любой карьере.
Ярлыки Excel для ПК и Mac Ярлыки Excel для ПК Mac Ярлыки Excel — список наиболее важных и распространенных ярлыков MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения.
Простой расчет внутренней нормы прибыли в Excel
Вы ищете простой способ рассчитать внутреннюю норму прибыли? Excel — это мощный инструмент, который поможет вам быстро и точно рассчитать внутреннюю норму прибыли. Всего за несколько кликов вы сможете быстро получить необходимую информацию. Кроме того, он невероятно удобен для пользователя, поэтому даже если у вас нет большого опыта работы с электронными таблицами, этот метод все равно будет работать для вас.
Знание внутренней нормы прибыли необходимо для принятия разумных финансовых решений. Это позволяет вам сравнивать различные инвестиции и делать осознанный выбор о том, куда вложить свои деньги. С Excel вычисление этого числа никогда не было проще и точнее!
Прочтите запись в нашем блоге и узнайте, как легко рассчитать внутреннюю норму прибыли в Excel!
Что такое внутренняя норма доходности?
Внутренняя норма прибыли (или IRR) — это способ сравнения нескольких проектов друг с другом. IRR обеспечивает годовую норму прибыли, предполагая, что чистая приведенная стоимость проекта (NPV) равна нулю. Это еще один из строительных блоков финансов, поскольку он обеспечивает вторую точку сравнения для оценки проектов.
В отличие от NPV, которая представляет стоимость проекта в долларах, IRR предоставляет вам ставку дисконтирования. Затем ставка дисконтирования IRR сравнивается со стоимостью капитала компании и другими проектами. Если IRR превышает стоимость капитала компании, то, скорее всего, она обеспечит благоприятный доход. Проект с самым высоким IRR обычно является лучшим вложением, если у вас несколько проектов.
Вычисление IRR вручную чрезвычайно сложно и требует серии проб и ошибок, чтобы найти формулу. К счастью, Excel может быстро запускать сценарии и выполнять вычисления за нас.
Как рассчитывается IRR?
Excel сделает всю тяжелую работу за вас, поэтому не стесняйтесь переходить к следующему разделу, если формулы и расчеты вам не по душе. Однако сначала приведем краткий обзор для тех, кому интересно, как рассчитывается IRR.
Формула расчета внутренней нормы доходности: 92 + 1000 долл. США * (1+r) + 1000 долл. США
Excel значительно упрощает расчет внутренней нормы прибыли с помощью встроенной формулы. Все, что вам нужно сделать, это ввести денежные потоки за каждый период в электронную таблицу Excel, а затем ввести следующую функцию: = IRR (значения).
Компоненты внутренней нормы доходности
Для расчета внутренней нормы доходности в Excel вам понадобятся два основных компонента: количество периодов и денежные потоки.
Количество периодов
Необходимо определить, какие периоды (годы, месяцы, недели, дни) вы хотите оценить. К счастью, гибкая формула автоматически подстраивается под введенное количество периодов. Тем не менее, IRR обычно представляет собой годовую сумму. Чтобы настроить IRR на более короткий период времени, сделайте следующее:
-Годовой: без изменений
-Ежеквартальный: IRR*4
-Ежемесячный: IRR*12
-Еженедельный: IRR*52
-Ежедневный: IRR*365
Денежный поток
Вам понадобится серия денежных потоков за периоды, которые вы хотите оценить. Это может быть связано непосредственно с финансовыми отчетами, или вы можете построить таблицу для расчета денежных потоков только по формуле.
Если у вас есть два элемента, перечисленных выше, вы можете рассчитать IRR в Excel.
Где найти функцию IRR?
Вариант 1:
Вы можете найти функцию IRR на вкладке Формулы Excel в разделе Финансы
Вариант 2:
Вы можете использовать Построитель формул , чтобы шаг за шагом пройтись по формуле.
Вариант 3:
Формулу можно ввести вручную в любую ячейку.
Учебное пособие. Расчет внутренней нормы прибыли в Excel
Книга Excel
2_IRR_ExcelWorkbookDownload
Учебное пособие IRR
Шаг 1. Создайте таблицу денежных потоков для каждого периода, который вы хотите проанализировать. Вы должны определить, должны ли периоды быть годовыми или какими-то другими, и соответствующим образом скорректировать.
Шаг 2. Введите функцию =IRR() и позвольте Excel сделать свое волшебство.
Часто задаваемые вопросы=
В: В чем разница между NPV и IRR?
A: Чистая приведенная стоимость (NPV) измеряет стоимость ряда денежных потоков по мере их возникновения во времени. Внутренняя норма доходности (IRR) — это то, как эти денежные потоки будут дисконтированы для достижения текущей стоимости.
В: Как Excel вычисляет IRR?
О: Excel использует метод проб и ошибок для определения внутренней нормы прибыли. Он начинает с первоначальной оценки ставки, затем изменяет ее до тех пор, пока не найдет ставку, при которой чистая приведенная стоимость равна 0.
В: Могу ли я использовать IRR для любого типа инвестиций?
A: Нет. IRR не подходит для инвестиций, денежные потоки которых не следуют периодической схеме, например, одноразовые инвестиции или инвестиции с отрицательными денежными потоками. В таких случаях больше подходят другие меры, такие как чистая приведенная стоимость. Кроме того, IRR предполагает, что денежные потоки могут быть реинвестированы по той же ставке, что верно не во всех случаях.
В: Есть ли в Excel функция расчета внутренней нормы доходности?
О: Да, в Excel есть встроенная функция =IRR(), которую можно использовать для расчета внутренней нормы прибыли. Все, что вам нужно сделать, это ввести денежные потоки за каждый период, а затем войти в функцию. Он автоматически рассчитает внутреннюю норму прибыли ваших инвестиций. Дополнительную информацию о том, как использовать =IRR(), можно найти в разделе справки Excel.
В: Что такое хорошая IRR?
О: Ответ зависит от инвестиций, которые вы делаете, и вашей личной терпимости к риску. Вообще говоря, инвестиции с IRR 8% или выше считаются хорошими инвестициями. Однако это не следует считать жестким правилом. Важно учитывать риски, связанные с любыми потенциальными инвестициями, и следить за тем, чтобы ожидаемая прибыль превышала риск, на который вы идете.
Есть вопросы по внутренней норме прибыли (IRR) в Excel? Есть ли другие темы, которые вы хотели бы, чтобы мы осветили? Оставьте комментарий ниже и дайте нам знать! И подпишитесь на нашу рассылку, чтобы получать эксклюзивные финансовые новости в свой почтовый ящик.
Как рассчитать внутреннюю норму доходности (плюс определение и ограничения)
CPE
6 апреля 2023 г.
Уэйн Уинстон, доктор философии
Независимо от того, являетесь ли вы штатным бухгалтером или работаете бухгалтером-консультантом, вы должны иметь возможность поддерживать своих клиентов с финансовой жизнеспособностью проектов. Обычно мы оцениваем проекты по критерию чистой приведенной стоимости (NPV).

Согласно Harvard Business Review, чистая приведенная стоимость представляет собой текущую стоимость денежных потоков при требуемой норме прибыли вашего проекта по сравнению с вашими первоначальными инвестициями. Это простой способ рассчитать рентабельность инвестиций. Еще одним способом измерения финансового успеха проекта является внутренняя норма прибыли (IRR). IRR определяется как метрика, используемая для измерения прибыльности инвестиций, которая учитывает временную стоимость денег.
В этой статье мы расскажем, как рассчитать внутреннюю норму прибыли и чистую текущую стоимость, а также как выполнять эти расчеты в Microsoft Excel. Следуйте инструкциям на листе IRR рабочей книги IRR.xlsx. В этой электронной таблице показаны годовые денежные потоки от двух проектов, как показано на рис. 1.

Рисунок 1 Оценка двух проектов по NPV и IRR

Расчет NPV в Excel денежные потоки проекта. Проект с NPV>0 помогает компании.
Проект с NPV<0 вредит компании.
Формула =NPV(0,2,C2:I2) в ячейке J2 вычисляет стоимость Проекта 1 в сегодняшних долларах с использованием годовой ставки дисконтирования 20%. Мы находим NPV, равную 266,54 доллара, при условии, что 1 доллар через год рассчитывается по формуле = 1 доллар/1,20.
Имейте в виду: ячейки J1:K2 учитывают ставку дисконтирования 20%. При такой ставке дисконтирования проект 2 имеет большую NPV. Если мы используем ставку дисконтирования 2%, Проект 1 имеет большую NPV. Часто трудно договориться о ставке дисконтирования. Это когда IRR становится полезным.
IRR последовательности денежных потоков — это ставка дисконтирования, при которой чистая приведенная стоимость денежных потоков равна 0. С IRR может возникнуть несколько проблем, которые мы обсудим позже, но в большинстве случаев IRR говорит вам о доходность, которую вы зарабатываете на своих инвестициях.

Как рассчитать внутреннюю норму прибыли в Excel
Синтаксис функции IRR: IRR(диапазон денежных потоков, [предположение]). Аргумент предположения является необязательным и должен представлять собой «догадку» о том, что, по вашему мнению, представляет собой IRR. В большинстве ситуаций вам не нужно вводить аргумент предположения, но иногда это может быть важно.
Чтобы быть в безопасности, вы должны ввести диапазон предположений. Копирование из B8 в B9:B17 формулы =IRR($C$2:$I$2,A8) показывает, что IRR для проекта 1 составляет 47,5%. Точно так же формулы в C8:C17 показывают, что для всех предположений наша расчетная IRR составляет 80,1%. По сути, это означает, что наши инвестиции в Проект 1 приносят годовую доходность 47,5%, а наши инвестиции в Проект 2 приносят годовую доходность 80,1% 9.0003
Как рассчитать внутреннюю норму доходности с денежными потоками
Как рассчитать внутреннюю норму доходности, если денежные потоки происходят нерегулярно? На помощь приходит замечательная функция Excel ЧИСТВНДОХ. Просто введите даты и значения денежных потоков, и Excel вернет годовую норму прибыли инвестиций. Например, в нашем листе XIRR мы обнаруживаем, что денежные потоки, происходящие в отображаемые даты, дают годовую внутреннюю норму доходности 18,3%.
Рис. 2 Пример функции ЧВНПД

Каждая ли инвестиция имеет уникальный IRR?
Трехпериодный проект, показанный на листе No IRR нашей электронной таблицы, не имеет IRR. Это означает, что не существует такой ставки дисконтирования, которая сделала бы NPV этого проекта равной 0.
Рис. 3. Проект без внутренней нормы доходности

: -9,6% и 216,1%. Мы нашли их IRR, варьируя наши предположения относительно IRR проекта. Обе эти ставки дисконтирования дают NPV, равную 0. Практической интерпретации IRR в этой ситуации нет.
Если последовательность денежных потоков начинается с отрицательного денежного потока, за которым следуют все неотрицательные денежные потоки, то вам гарантируется уникальная IRR. Многие проекты соответствуют этому требованию, потому что они начинаются с отрицательного денежного потока, а затем денежные потоки представляют собой положительную отдачу от инвестиций.

Рисунок 4: Проект с двумя значениями IRR

Как связаны NPV и IRR?
Последовательность денежных потоков имеет положительную чистую приведенную стоимость тогда и только тогда, когда ее IRR больше ставки дисконтирования. Например, проект с 9% IRR будет иметь положительное значение NPV при ставке дисконтирования менее 9% и отрицательное значение NPV при ставке дисконтирования 9%.

Следует ли всегда выбирать проект с большей IRR?
Рассмотрим две последовательности денежных потоков на листе «Какой проект». Проект 2 имеет внутреннюю норму доходности 50%, потому что 1 доллар вырастает за год на 50%. Точно так же проект 1 имеет внутреннюю норму доходности 40%. Если бы нам было позволено выбрать только один проект, наше решение, основанное на внутренней норме доходности, привело бы к выбору проекта 2. Однако это было бы глупо, потому что проект 1 поставил бы нас в гораздо более выгодное положение с денежными средствами в момент времени 1, чем проект 2.