Как считать irr в excel: Расчет IRR в Excel с помощью функций и графика
Рассчитать IRR в Excel — voxt.ru
у приложение
Расчет IRR в Excel
IRR или внутренняя норма доходности используется для расчета внутренней прибыли для некоторых инвестиций в финансовую отрасль, IRR — это встроенная функция в Excel, которая используется для ее расчета, и это также финансовая формула, эта функция принимает диапазон значений в качестве входные данные, для которых нам нужно рассчитать внутреннюю норму прибыли и предполагаемое значение в качестве второго входа.
IRR в excel означает явнутренний рсъел рэтурн. IRR в Excel — одна из встроенных функций, доступных в Microsoft Excel. IRR в Excel подпадает под категорию финансовых функций.
Внутренняя норма прибыли — это процентная ставка, полученная за сделанное вложение. IRR в Excel состоит из выполненных платежей, представленных отрицательными значениями, и полученного дохода, обозначенного положительными значениями, которые происходят через регулярный интервал времени.
Функция IRR в Excel возвращает внутреннюю норму прибыли для серии денежных потоков, представленных положительными и отрицательными числами. Эти денежные потоки не обязательно должны быть последовательными, как в случае аннуитета. Однако денежные потоки должен происходят через определенные промежутки времени, например ежемесячно, ежеквартально или ежегодно. Сумма наличных может варьироваться для каждого интервала.
Формула, используемая для расчета IRR в Excel
Формула IRR Excel выглядит следующим образом:
Где,
- спектр = Это обязательный параметр. Диапазон ячеек, обозначающий ряд денежных потоков, для которых будет рассчитана внутренняя норма прибыли.
- угадать = Это необязательный параметр. Он обозначает число, которое, по вашему мнению, близко к результату внутренней нормы прибыли. Если значение не указано, используется значение по умолчанию 0,1 или 10%.
- «[ ]” обозначает необязательные параметры.
Функция возвращает числовое значение. Это могло быть положительно или отрицательно.
Как рассчитать IRR в Excel? (с примерами)
Вы можете скачать этот шаблон Excel для функции IRR здесь — Шаблон Excel для функции IRR
Расчет IRR в Excel может быть выполнен двумя способами, т. Е. С помощью функции рабочего листа (WS) или функции VBA. Как функцию WS, ее можно ввести как часть формулы в ячейку рабочего листа. В качестве функции VBA его можно использовать в коде макросов, который вводится через редактор Microsoft Visual Basic, интегрированный в MS Excel. Обратитесь к расчету IRR в примере Excel, приведенном ниже, чтобы лучше понять.
Пример # 1 — Расчет IRR в листе Excel
В этом примере расчета IRR -3000 обозначает сумму инвестированного капитала. Последующие числа 2000 и 5000 обозначают доход. Функция IRR в Excel рассчитывается для значений -3000, 2000 и 5000. Результат составляет 67%.
Пример №2
В этом примере расчета IRR #NUM! отображается в результате, потому что в наборе значений нет единственного отрицательного значения.
Итак, нет значения, указывающего на оплату, но все это доход.
Пример №3
В этом примере расчета IRR все значения отрицательны. Это указывает на отсутствие дохода. Следовательно, сгенерированный результат — # ЧИСЛО!
Пример №4
В этом примере расчета IRR всего пять значений. -10000 обозначает произведенную оплату. Следующие значения относятся к годам, т. Е. Доход, полученный в год 1 — 2000, год 2 — 5000, год 3 — 4000, год 5 — 6000.
Чтобы рассчитать доход, полученный в конце трех лет, примените формулу IRR к ячейкам от B2 до B4. Результат составляет -19%, так как сумма выплаты в первый год больше, чем доход, полученный в последующие годы.
Чтобы рассчитать доход, полученный в конце пяти лет, примените формулу IRR к ячейкам от B2: B6. Результат 22%. Результат положительный, так как доход, полученный в последующие годы, превышает сумму платежа, инвестированного в первый год.
Пример # 2 — Расчет IRR в Excel VBA
Функцию IRR можно использовать в коде VBA в Microsoft Excel следующим образом:
Формула IRR в Excel VBA:
Формула Irr в VBA выглядит следующим образом.
Где,
Значения = Денежный поток
IRRVal = Внутренняя норма прибыли
IRR в примере Excel VBA
Давайте рассмотрим пример для Irr в VBA.
В этом примере расчета IRR IRRVal — это переменная типа double. Он содержит результат, возвращаемый функцией IRR. Значения представлены в виде массива типа данных double, в котором начальное значение -10000 указывает на платеж, произведенный в первый год. Последующие значения — 2000, 5000, 4000 и 6000, что указывает на годовой доход в течение следующих четырех лет.
То, что нужно запомнить- Массив значений (спектр) должен содержать как минимум одно положительное и одно отрицательное число.
- Значения должны быть в последовательном порядке. IRR использует порядок значений для интерпретации порядка денежных потоков. Позаботьтесь о вводе значений выплат и доходов в той последовательности, в которой они произошли или вы хотите, чтобы это произошло.
- Если массив ссылочного аргумента содержит текст, логические значения или пустые ячейки, эти значения игнорируются.
- MS Excel использует итеративный метод расчета IRR с помощью Excel. Начиная с предположения, IRR циклически выполняет расчет до тех пор, пока результат не станет точным в пределах 0,00001 процента; если IRR не может найти результат, который работает после 20 попыток, #NUM! Возвращается значение ошибки.
- Если функция IRR в excel возвращает #NUM! или если результат не такой, как вы ожидали, попробуйте другое значение для
- В большинстве случаев вам не нужно указывать предполагаемое значение для расчета IRR в Excel. Если параметр предположения пропущен, программное обеспечение принимает его равным 0,1, то есть 10%.
- IRR тесно связана с NPV, то есть функцией чистой приведенной стоимости. Норма прибыли, рассчитанная IRR с помощью Excel, представляет собой процентную ставку, соответствующую нулевой чистой приведенной стоимости.
IRR в видео Excel
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 917
Похожие записи
Прокрутить вверхРабота с денежными потоками: вычисление показателей ЧПС и ВСД в Excel
Вы теряете спящий режим и знаете, как максимально повысить прибыльность и свести к минимуму риски, связанные с бизнес-инвестициями? Остановить переключение и поворот.
Наличные, то есть. Взгляните на движение денежных средств, а также на то, что выходит за рамки вашей компании. Положительный денежный поток — это показатель получения денежных средств (продажи, проценты, проблемы с акциями и так далее), тогда как отрицательный денежный поток — это показатель выходных денежных средств (покупки, выплаты, налоги и так далее). Чистое движение денежных средств — это разница между положительным и отрицательным денежными потоками и ответы на наиболее фундаментальные деловые вопросы о том, сколько денег осталось в хлеве?
Для развития бизнеса необходимо принимать ключевые решения о том, куда вкладывать деньги в долгосрочной перспективе. Microsoft Excel сравните параметры и сделайте правильный выбор, чтобы можно было отдохнуть как днем, так и ночью.
Вопросы о проектов по capital investment
Если вы хотите взять деньги с себя, сделать его рабочим и вкладывать их в проекты, которые составляют вашу бизнес, вам нужно задать несколько вопросов об этих проектах:
-
Лучше ли вкладывать деньги в другой проект?
-
Следует ли вкладывать средства в текущий проект еще больше или стоит ли сократить потери?
org/ListItem»>
Будет ли новый долгосрочный проект прибыльным? Когда?
Теперь рассмотрим каждый из этих проектов подробнее и спросите:
В итоге вам действительно нужны номера нижней строки, которые можно использовать для сравнения вариантов проектов. Но для этого необходимо включить в анализ значения времени денежных средств.
Мой гогол как-то сказал мне: «»Юный, лучше получить деньги как можно скорее и удерживать на нем как можно больше времени». Позже я узнал, почему. Вы можете вкладывать эти деньги по сложным процентным ставкам, что означает, что ваши деньги могут заработать вам больше денег , а затем некоторые из них.
Другими словами, при выходе или выходе денежных средств так же важно, как и то, сколько денег выходит или поступает.
Ответы на вопросы с помощью ЧПС и IRR
Существует два финансовых способа, которые помогут вам ответить на все эти вопросы: чистая стоимость (ЧПС) и внутренняя ставка прибыли (IRR). Как ЧПС, так и IRR называются дисконтируемыми методами движения денежных средств, так как они применяют значение времени к оценке проектов по capital investment. Как ЧПС, так и IRR основаны на рядах будущих платежей (отрицательный денежный поток), доходов (положительный денежный поток), потерь (отрицательный денежный поток) или «нулевых денежных потоков».
Npv
ЧПС возвращает чистую стоимость денежных потоков, представленных в современных рублях. Из-за денежной стоимости каждый доллар сегодня стоит больше, чем завтра. ЧПС вычисляет обтекаемую стоимость для каждого ряда денежных потоков и объединяет их, чтобы получить чистую стоимость.
Где n — количество денежных потоков, а i — процентная или скидка.
Irr
IRR основан на ЧПС. Это можно представить в особом случае ЧПС, где вычисляемая ставка прибыли представляет процентную ставку, соответствующую нулевой (нулевой) чистой стоимости.
NPV(IRR(values),values) = 0
Если все отрицательные денежные потоки происходят раньше всех положительных или когда последовательность денежных потоков проекта содержит только один отрицательный денежный поток, IRR возвращает уникальное значение. Большинство проектов по инвестиции в основном начинаются с больших отрицательных денежных потоков (в начале инвестиции), за которыми следуют положительные денежные потоки, и, следовательно, имеют уникальный IRR.
Сравнение проектов
ЧПС определяет, должен ли проект получить больше или меньше желаемой ставки прибыли (уровень сложности), и хорошо определяет, будет ли проект прибыльным. IRR на один шаг дальше, чем ЧПС, чтобы определить определенную ставку прибыли для проекта. Как ЧПС, так и IRR — это числа, которые можно использовать для сравнения проектов и выбора оптимальных вариантов для вашей компании.
Выбор соответствующей Excel функции
hich Office Excel функции, которые можно использовать для вычисления ЧПС и IRR? Существует пять: функция ЧПС,функция ЧПС,функция IRR,ФУНКЦИЯ XIRRи МВСД. Выбор зависит от предпочитаемого финансового метода, от того, происходят ли денежные потоки через определенные интервалы времени и являются ли они периодическими.
Примечание: Денежные потоки заданы как отрицательные, положительные или нулевые значения.
При использовании этих функций обратите особое внимание на то, как вы обрабатываете мгновенные денежные потоки, которые происходят в начале первого периода, и все остальные денежные потоки, которые происходят в конце периодов.
|
Синтаксис функции |
Используйте, когда нужно |
Примечания |
|
Функция ЧПС (ставка; значение1; [значение2]; …) |
Определите чистую стоимость на основе денежных потоков, которые происходят регулярно, например ежемесячно или ежегодно. |
Каждый денежный поток, заданный как значение,происходит в конце периода. Если в начале первого периода имеется дополнительный денежный поток, он должен быть добавлен к значению, возвращаемом функцией ЧПС. См. пример 2 в разделе справки по функции ЧПС. |
|
Функция ЧПС (ставка, значения, даты) |
Определите чистую стоимость на основе денежных потоков, которые возникают через нерегулярные интервалы. |
Каждый денежный поток, указанный как значение, происходит в запланированную дату платежа. |
|
Функция IRR (значения, [предположение]) |
Определите внутреннюю ставку прибыли с использованием денежных потоков, которые происходят регулярно, например ежемесячно или ежегодно. |
Каждый денежный поток, заданный как значение,происходит в конце периода. IRR вычисляется с помощью итеративной процедуры поиска, которая начинается с оценки для IRR , указанной в качестве прогноза, и затем несколько раз изменяет это значение до тех пор, пока не будет достигнут правильный IRR. Указать аргумент «предположение» необязательно. Excel в качестве значения по умолчанию используется значение 10%. Если имеется несколько допустимых ответов, функция IRR возвращает только первый из них. Если IRR не находит ответ, возвращается #NUM! значение ошибки #ЗНАЧ!. Используйте другое значение для предположения, если вы получаете ошибку или результат отличается от ожидаемого.
Примечание. Другой вариант может возвращать другой результат, если имеется более одной внутренней ставки прибыли. |
|
ФУНКЦИЯ XIRR (значения, даты, [предположение]) |
Определите внутреннюю ставку прибыли с помощью денежных потоков, которые возникают через нерегулярные интервалы. |
Каждый денежный поток, указанный как значение, происходит в запланированную дату платежа. XIRR вычисляется с помощью итеративной процедуры поиска, которая начинается с оценки IRR , указанной в качестве прогноза, и затем несколько раз изменяет это значение до тех пор, пока не будет достигнут правильный XIRR. Указать аргумент «предположение» необязательно. Excel в качестве значения по умолчанию используется значение 10%. Если имеется несколько допустимых ответов, функция XIRR возвращает только первый из них. Примечание. Другой вариант может возвращать другой результат, если имеется более одной внутренней ставки прибыли. |
|
Функция MIRR (значения, finance_rate, reinvest_rate) |
Определите модифицированную внутреннюю ставку прибыли с использованием денежных потоков, которые происходят регулярно, например ежемесячно или ежегодно, и учитывайте как стоимость инвестиций, так и проценты, полученные от реинвестирования денежных средств. |
Каждый денежный поток, заданный как значение,происходит в конце периода, за исключением первого денежного потока, который определяет значение в начале периода. Процентная ставка, которую вы платите с денежных средств, используемых в денежных потоках, указана в finance_rate. Процентная ставка, получаемая для денежных потоков при реинвестирования, указывается в reinvest_rate. |
Если xiRR не находит ответ, возвращается #NUM! значение ошибки #ЗНАЧ!. Используйте другое значение для предположения, если вы получаете ошибку или результат отличается от ожидаемого.
Дополнительные сведения
Дополнительные сведения об использовании ЧПС и IRR см. в разделах Главы 8 «Оценка инвестиций с использованием критериев чистой стоимости» и Глава 9 ,»Внутренняя ставка прибыли» в Microsoft Excel Анализ данных и бизнес-моделирование Уин Л. Уинстон. чтобы узнать больше об этой книге.
К началу страницы
Microsoft Excel: 3 способа расчета внутренней нормы прибыли в Excel | Новости
Excel предлагает три функции для расчета внутренней нормы прибыли, и я рекомендую вам использовать все три.
В.
Я подготовил прогнозы для предложенного проекта и хочу рассчитать внутреннюю норму прибыли. Должен ли я использовать простые математические формулы вместо использования функции IRR в Excel, чтобы другие могли следить за моими расчетами?
A. Excel предлагает три функции для расчета внутренней нормы прибыли, и я рекомендую вам использовать все три. Проблема с использованием математики для расчета внутренней нормы прибыли заключается в том, что необходимые расчеты сложны и требуют много времени. По сути, математическое решение включает в себя расчет чистой приведенной стоимости (NPV) для каждой суммы денежного потока (в серии денежных потоков) с использованием различных предполагаемых процентных ставок на основе проб и ошибок, а затем эти NPV складываются вместе. . Этот процесс повторяется с использованием различных процентных ставок, пока вы не найдете/не наткнетесь на точную процентную ставку, при которой сумма чистой приведенной стоимости равна нулю. Процентная ставка, которая дает NPV с нулевой суммой, затем объявляется внутренней нормой доходности.
Чтобы упростить этот процесс, Excel предлагает три функции для расчета внутренней нормы прибыли, каждая из которых представляет собой лучший вариант, чем использование подхода на основе математических формул. Этими функциями Excel являются IRR, XIRR и MIRR. Объяснения и примеры для этих функций представлены ниже. Вы можете загрузить пример рабочей тетради по адресу carltoncollins.com/irr.xlsx.
1. Функция Excel IRR. Функция IRR Excel вычисляет внутреннюю норму доходности для ряда денежных потоков, предполагая периоды платежей одинакового размера. Используя пример данных, показанный выше, формула IRR будет =IRR(D2:D14,.1)*12, что дает внутреннюю норму доходности 12,22%. Однако, поскольку в одних месяцах 31 день, а в других — 30 или меньше, месячные периоды имеют разную продолжительность, поэтому IRR всегда будет возвращать немного ошибочный результат, когда задействовано несколько месячных периодов.
2. Функция ЧССНП в Excel. Функция ЧИСТВНР в Excel вычисляет более точную внутреннюю норму доходности, поскольку она учитывает периоды времени разного размера.
Чтобы использовать эту функцию, вы должны указать как суммы денежных потоков, так и конкретные даты, когда эти денежные потоки выплачиваются. В примере, изображенном ниже слева, формула ЧИСТВНРД будет = ЧИСТВНРД(D2:D14,B2:B14,.1), что дает внутреннюю норму доходности 12,97%.
3. Функция Excel MIRR. Функция MIRR Excel (модифицированная внутренняя норма доходности) работает аналогично функции IRR, за исключением того, что она также учитывает стоимость заимствования первоначальных инвестиционных средств, а также сложные проценты, полученные путем реинвестирования каждого денежного потока. Функция MIRR достаточно гибкая, чтобы учитывать отдельные процентные ставки для заимствования и инвестирования денежных средств. Поскольку функция MIRR рассчитывает сложные проценты на доходы проекта или дефицит денежных средств, результирующая внутренняя норма прибыли обычно значительно отличается от внутренней нормы прибыли, полученной с помощью функций IRR или XIRR. В примере слева формула MIRR будет =MIRR(D2:D14,D16,D17)*12, что дает внутреннюю норму доходности 17,68%.
Примечание: Некоторые CPA утверждают, что результаты функции MIRR менее достоверны, поскольку денежные потоки проекта редко полностью реинвестируются. Однако умные CPA могут компенсировать частичные уровни инвестиций, просто регулируя процентную ставку в соответствии с ожидаемыми уровнями реинвестирования. Например, если предполагается, что реинвестированные денежные потоки принесут 3,0%, но ожидается, что будет реинвестирована только половина денежных потоков, то CPA может использовать процентную ставку 1,5% (половину от 3,0%) в качестве процентной ставки для компенсировать частичное инвестирование денежных потоков.
Вместо того, чтобы беспокоиться о том, какой метод дает более точный результат, я считаю, что лучше всего включить все три расчета (IRR, XIRR и MIRR), чтобы читатель-финансист мог рассмотреть их все. Далее следует несколько комментариев по поводу этих расчетов.
1. Требуются отрицательные и положительные значения денежного потока. Все три функции требуют хотя бы одного отрицательного и хотя бы одного положительного денежного потока для завершения расчета.
Первое число в ряду денежных потоков обычно является отрицательным числом, которое считается первоначальным вложением в проект.
2. Месячная и годовая доходность. При расчете IRR или MIRR ежемесячных денежных потоков результаты должны быть умножены на 12, чтобы получить годовой доход; однако функция ЧВНПГ автоматически дает годовой результат, который не нужно умножать. При расчете IRR, XIRR или MIRR годовых денежных потоков результаты не нужно умножать. (Поскольку функция ЧВНПГ включает диапазоны дат, она автоматически пересчитывает результаты в год.)
3. Угадай. Функции IRR и XIRR позволяют вам ввести предположение в качестве начальной скорости, когда функция начинает вычисление с приращением, до 20 циклов для функции IRR и 100 циклов для функции XIRR, пока не будет найден ответ в пределах 0,00001%. Если ответ не определен в течение отведенного количества циклов, то #ЧИСЛО! возвращается сообщение об ошибке.
4. #ЧИСЛО! ошибка. Если функция IRR возвращает значение #ЧИСЛО! значение ошибки или если результат не близок к тому, что вы ожидали, файлы справки Excel предлагают вам повторить попытку с другим значением для вашего предположения.
5. Если вы не введете предположение. Если вы не введете предположение для функции IRR или XIRR, Excel примет 0,1 или 10% в качестве начального предположения.
6. Финики. Даты, которые вы вводите, должны быть введены как значения дат, а не как текст, чтобы функция ЧИСТНДОХД могла точно использовать эти даты.
ФункцияIRR — служба поддержки Майкрософт
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Больше…Меньше
В этой статье описаны синтаксис формулы и использование функции IRR в Microsoft Excel.
Описание
Возвращает внутреннюю норму прибыли для ряда денежных потоков, представленных числами в values. Эти денежные потоки не обязательно должны быть четными, как в случае с аннуитетом.
Однако денежные потоки должны происходить через регулярные промежутки времени, например, ежемесячно или ежегодно. Внутренняя норма доходности — это процентная ставка, полученная за инвестиции, состоящая из платежей (отрицательные значения) и дохода (положительные значения), которые происходят через регулярные периоды.
Синтаксис
IRR(значения, [предположение])
Синтаксис функции IRR имеет следующие аргументы:
Значения Обязательно. Массив или ссылка на ячейки, содержащие числа, для которых вы хотите рассчитать внутреннюю норму доходности.
Значения должны содержать по крайней мере одно положительное значение и одно отрицательное значение для расчета внутренней нормы прибыли.
IRR использует порядок значений для интерпретации порядка денежных потоков. Не забудьте ввести значения платежа и дохода в нужной вам последовательности.
Если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки, эти значения игнорируются.
Угадай Необязательно. Угаданное вами число близко к результату IRR.
Microsoft Excel использует итеративный метод для расчета внутренней нормы доходности.
Начиная с предположения, IRR циклически повторяет расчет, пока результат не будет точным в пределах 0,00001 процента. Если IRR не может найти результат, который работает после 20 попыток, #ЧИСЛО! возвращается значение ошибки.В большинстве случаев вам не нужно предоставлять предположение для расчета IRR. Если предположение опущено, предполагается, что оно равно 0,1 (10 процентов).
Если IRR дает #ЧИСЛО! значение ошибки, или если результат не близок к тому, что вы ожидали, попробуйте еще раз с другим значением для предположения.
Начиная с предположения, IRR циклически повторяет расчет, пока результат не будет точным в пределах 0,00001 процента. Если IRR не может найти результат, который работает после 20 попыток, #ЧИСЛО! возвращается значение ошибки.Примечания
IRR тесно связан с NPV, функцией чистой приведенной стоимости. Норма прибыли, рассчитанная по IRR, представляет собой процентную ставку, соответствующую 0 (нулю) чистой приведенной стоимости.
Следующая формула показывает, как связаны NPV и IRR:
NPV(IRR(A2:A7),A2:A7) равно 1,79E-09 [В пределах точности расчета IRR значение фактически равно 0 (нулю).]
Пример
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.
Об авторе