Показатель внутренней нормы доходности excel

Пример формулы расчета внутренней ставки доходности в Excel

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

Функция ВСД для расчета внутренней ставки доходности IRR проекта в Excel

Для вычисления внутренней ставки нормы доходности будущих вложений Excel предлагает функцию ВСД (Внутренняя Ставка Доходности). Данная функция очень тесно связанна с функцией для вычисления текущей стоимости инвестиций ЧПС. Функция ВСД служит для расчета внутренней ставки доходности в Excel, при которой для тех самых регулярных будущих взносов функция ЧПС буде возвращать свой итоговый результат вычисления равный нулю (0).

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

Ниже на рисунке представлен хронологический график дивидендных выплат. В нем учитывается начальная сумма инвестиций для того, чтобы функция ВСД возвращала правильный итоговый результат вычисления. В начале таблицы графика находится строка содержащая значение суммы инвестиционного вложения равной 300 000$. Ниже приведенная формула отображает внутреннюю ставку доходности равной 10,53%:

Первый аргумент функции ВСД содержит диапазон ячеек с платежами на протяжении всего инвестиционного периода:

Во втором аргументе указана внутренняя процентная ставка доходности инвестиций. Если второй аргумент не указан, Excel по умолчанию присевает значение 10%.

Принцип действия функции ВСД основан на вычислениях значений текущей стоимости суммы для каждого платежа при процентной ставке доходности инвестиций. Если в результате вычислений текущая стоимость суммы очередного платежа больше чем >0, функция уменьшает процентную ставку и выполняет то же вычисление еще раз. Excel повторяет эти же операции изменяя размер процентной ставки и суммируя текущие значения пока сумма не будет равна =0. Тогда функция ВСД возвращает в своем итоговом результате вычисления найденную подходящую процентную ставку.

Расчет внутренней ставки доходности с нерегулярными платежами по ЧИСТВНДОХ

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

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

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

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

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

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

Источник

Функция ВСД в Excel и пример как посчитать IRR

Для расчета внутренней ставки доходности (внутренней нормы доходности, IRR) в Excel используется функция ВСД. Ее особенности, синтаксис, примеры рассмотрим в статье.

Особенности и синтаксис функции ВСД

Один из методов оценки инвестиционных проектов – внутренняя норма доходности. Расчет в автоматическом режиме можно произвести с помощью функции ВСД в Excel. Она находит внутреннюю ставку доходности для ряда потоков денежных средств. Финансовые показатели должны быть представлены числовыми значениями.

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

Внутренняя ставка доходности (IRR, внутренняя норма доходности) – процентная ставка инвестиционного проекта, при которой приведенная стоимость денежных потоков равняется нулю. При данной ставке инвестор вернет вложенные первоначально средства. Инвестиции состоят из платежей (суммы со знаком «–») и доходов (со знаком «+»), которые происходят в одинаковые по продолжительности временные промежутки.

Аргументы функции ВСД в Excel:

1. Значения. Диапазон ячеек, в которых содержатся числовые выражения денежных средств. Для данных сумм нужно посчитать внутреннюю норму доходности.
2. Предположение. Цифра, которая предположительно близка к результату. Аргумент необязательный.

Секреты работы функции ВСД (IRR):

1. В диапазоне с денежными суммами должно содержаться хотя бы одно положительное и одно отрицательное значение.
2. Для функции ВСД важен порядок выплат или поступлений. То есть денежные потоки должны вводится в таблицу в соответствии со временем их возникновения.
3. Текстовые или логические значения, пустые ячейки при расчете игнорируются.
4. В программе Excel для подсчета внутренней ставки доходности используется метод итераций (подбора). Формула производит циклические вычисления с того значения, которое указано в аргументе «Предположение». Если аргумент опущен, со значения 0,1 (10%).

При расчете ВСД в Excel может возникнуть ошибка #ЧИСЛО!. Почему? Используя метод итераций при расчете, функция находит результат с точностью 0,00001%. Если после 20 попыток не удается получить результат, ВСД вернет значение ошибки.

Когда функция показывает ошибку #ЧИСЛО!, повторите расчет с другим значением аргумента «Предположение».

Примеры функции ВСД в Excel

Расчет внутренней нормы рентабельности рассмотрим на элементарном примере. Имеются следующие входные данные:

Сумма первоначальной инвестиции – 7000. В течение анализируемого периода было еще две инвестиции – 5040 и 10.

Заходим на вкладку «Формулы». В категории «Финансовые» находим функцию ВСД. Заполняем аргументы.

Значения – диапазон с суммами денежных потоков, по которым необходимо рассчитать внутреннюю норму рентабельности. Предположение – опустим.

Искомая IRR (внутренняя норма доходности) анализируемого проекта – значение 0,209040417. Если перевести десятичное выражение величины в проценты, то получим ставку 20,90%.

В нашем примере расчет ВСД произведен для ежегодных потоков. Если нужно найти IRR для ежемесячных потоков сразу за несколько лет, лучше ввести аргумент «Предположение». Программа может не справиться с расчетом за 20 попыток – появится ошибка #ЧИСЛО!.

Еще один показатель эффективности инвестиционного проекта – NPV (чистый дисконтированный доход). NPV и IRR связаны: IRR определяет ставку дисконтирования, при которой NPV = 0 (то есть затраты на проект равны доходам).

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

На основании полученных данных построим график изменения NPV.

Пересечение графика с осью Х (когда чистый дисконтированный доход проекта равняется нулю) дает показатель IRR для данного проекта. Графический метод показал результат ВСД, аналогичный найденному в Excel.

Как пользоваться показателем ВСД:

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

То есть если ставка кредита меньше внутренней нормы рентабельности, то заемные средства принесут прибыль. Так как в при реализации проекта мы получим больший процент дохода, чем величина капитала.

Вернемся к нашему примеру. Допустим, для запуска проекта брался кредит в банке под 15% годовых. Расчет показал, что внутренняя норма доходности составила 20,9%. На таком проекте можно заработать.

Источник

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

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

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

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

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

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

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

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

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

Часто 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 для разных ставок дисконтирования:

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

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

Источник