Как рассчитать 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 есть три встроенные функции расчета нормы прибыли. Узнайте об этих функциях здесь:

Синтаксис IRR в Excel рассчитывает процентную ставку инвестиций в бизнес-проект, взаимный фонд или пенсионный фонд. Инвестиции должны включать как минимум одно положительное и отрицательное значение. Также поступление денежных средств в фонд должно происходить через регулярные промежутки времени. Вот как выглядит формула:

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

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

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

=ЧИСТВНДОХ(значения или диапазоны ячеек, даты, [аргумент предположения])

MIRR дает очень надежные значения IRR по сравнению с простой функцией IRR. IRR не учитывает стоимость капитала, который вы инвестируете. Следовательно, окончательное значение IRR не является точным. Вместо этого вы можете использовать функцию MIRR, чтобы включить стоимость капитала в расчет IRR. Следовательно, синтаксис становится таким, как показано ниже:

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

• Ценности — это отчет о движении денежных средств, состоящий как минимум из одного положительного и отрицательного денежных потоков.
• Finance_rate — это проценты, которые вы платите кредитору за деньги, взятые взаймы для бизнес-проекта.
• Reinvest_rate — это процентная ставка, которую ваш бизнес или инвестиционный фонд получает от денежных потоков при реинвестировании.

Также читайте:Как использовать формулу вычитания в Excel

Прежде чем вы сможете приступить к расчету IRR для нескольких инвестиционных планов или проектов, вы должны упорядочить данные в Excel. Вот как это делается:

• Откройте рабочий лист Excel и создайте три заголовка столбца: Период, Описание, и Денежные потоки.
• Заполните данные, которые вы хотите рассчитать, под соответствующими заголовками столбцов.

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

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

=ВНР(C2:C8)

• Ударять Входить чтобы получить рентабельность потенциальных инвестиций.

Этот расчет IRR основан на аргументе Guess по умолчанию, равном 10%. Допустим, вы ищете 12% прибыли. Вот как вам нужно ввести предполагаемое значение:

= IRR (C2: C8, 30%)

• В приведенной выше таблице вы видите, что последний денежный поток отрицательный. Таким образом, денежный поток должен измениться с положительного на отрицательное или с отрицательного на положительное.

• В этом сценарии используйте Предполагать чтобы найти точный результат, так как IRR даст два результата.

• Предполагать аргументом должна быть ожидаемая рентабельность инвестиций.

Когда вы получаете доход в разные даты после первоначальных инвестиций, использование синтаксиса IRR даст ошибочные результаты. В этом сценарии необходимо использовать синтаксис ЧИСТРДН. Вот шаги, которые вы можете попробовать на своем собственном листе Excel:

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

• После этого используйте следующее Синтаксис ЧИСТРРР в любой ячейке, чтобы получить внутреннюю норму доходности инвестиций при нерегулярном графике возвратных платежей:

=ЧИСТРЯД(C2:C8;D2:D8)

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

• На текущем листе добавьте две строки информации, как показано на рисунке.

• Эти Стоимость капитала и Ставка реинвестирования.

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

=MIRR(C2:C8,G2,G3)

IRR и его модифицированные формулы выполняют 20 итерационных вычислений перед отображением результата. Если вам нужна более высокая точность, вы можете использовать функцию Excel Goal Seek для расчета IRR. В этом методе Excel выполняет до 32 000 итерационных вычислений, прежде чем сгенерировать возвращаемое значение. Вот инструкции по использованию поиска цели и расчету IRR в Excel:

• Установите IRR на 11% или что-нибудь еще вручную.

• Рассчитайте NPV ниже IRR, используя эту формулу:

=ЧПС(С9,С3:С8)+С2

• Теперь нажмите на Данные вкладку, а затем выберите Анализ «что, если» в Прогноз группа команд в меню ленты Excel.
• Выбирать поиск цели из всплывающего контекстного меню.
• Здесь Установить ячейку должно быть значением NPV. Затем Оценивать должно быть 0.

• Набор Сменив ячейку к значению IRR.
• Вам просто нужно ввести ссылки на ячейки, как показано на изображении.

• Теперь нажмите ХОРОШО для расчета IRR через поиск цели.
• Согласно текущему набору данных, чистая приведенная стоимость будет равна 0,00, если IRR составляет 12%.

В методе поиска цели вам фактически не нужно рассчитывать IRR на основе денежного потока и суммы инвестиций. Вместо этого вы устанавливаете ожидаемую внутреннюю норму доходности в ячейке внутренней нормы доходности и используете ее для расчета чистой приведенной стоимости с использованием денежного потока и суммы инвестиций. Затем вы используете итеративные вычислительные возможности Excel, чтобы предсказать, когда NPV станет равным 0,00, при каком значении IRR.

Помните эти советы и факты, когда будете рассчитывать IRR в Excel, чтобы получить надежные проценты IRR:

• Аргумент для части Values ​​должен содержать доход (положительный денежный поток) и расход (отрицательный денежный поток), чтобы функция работала точно.
• Функция IRR будет обрабатывать только числа в аргументе Values. Он будет игнорировать любые другие элементы, такие как логические значения, пустые ячейки и тексты.
• Должны быть регулярные интервалы движения денежных средств, такие как еженедельно, ежемесячно, ежеквартально, ежегодно и т. д. Не обязательно, чтобы денежные потоки были равны в долларовом выражении.
• Убедитесь, что вы подготовили таблицу денежных потоков в хронологическом порядке. Например, если вы платите 100 долларов каждый месяц, укажите значения денежных потоков и их даты за январь, февраль, март и т. д.
• В простых расчетах IRR не обязательно вводить аргумент Guess.
• При использовании значения Guess формула IRR покажет значение, наиболее близкое к аргументу Guess. Если он показывает #ЧИСЛО! Ошибка, измените значение аргумента Guess.

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

• IRR — это выражение ROI в процентах. Он не учитывает абсолютное значение. Таким образом, он может показать более высокий процент отдачи для проекта с низкой стоимостью в долларах. Однако венчурные капиталисты и индивидуальные инвесторы ищут проекты с более высокой стоимостью в долларах, хотя процент рентабельности инвестиций меньше, чем у небольших проектов.
• Формула IRR возвращает значение, учитывая, что проект будет реинвестировать свой капитал или денежный поток по той же ставке, что и рассчитанная IRR. Это не всегда возможно, потому что ставки доходности сильно колеблются на рынке капитала.
• Если инвестиционный план или проект предполагает чередование положительных и отрицательных денежных потоков, вы можете получить более одного IRR для одного и того же проекта.

Теперь вы знаете, как рассчитать IRR в Excel, используя различные специальные синтаксисы IRR. Кроме того, вы узнали, как использовать Excel Goal Seek для расчета IRR в Excel. Если я пропустил какой-либо другой метод, не забудьте упомянуть об этом в поле для комментариев ниже.

Следующий, как использовать формулу Excel ЕСЛИ-ТО.