Пример формулы расчета внутренней ставки доходности в Excel
При вычислении текущей стоимости инвестиций на основе показателей, прогнозируемых будущих регулярных взносов с разными суммами полученное значение, сравнивается с начальной суммой необходимого объема инвестиций. Если текущая стоимость инвестиций больше чем необходимая сумма вложения финансовых средств, значит процент возврата вложенных средств выше чем ожидается. Но в таком случае возникает вопрос: какая же реальная фактическая процентная ставка доходности инвестиций при данных условиях?
Функция ВСД для расчета внутренней ставки доходности IRR проекта в Excel
Для вычисления внутренней ставки нормы доходности будущих вложений Excel предлагает функцию ВСД (Внутренняя Ставка Доходности). Данная функция очень тесно связанна с функцией для вычисления текущей стоимости инвестиций ЧПС. Функция ВСД служит для расчета внутренней ставки доходности в Excel, при которой для тех самых регулярных будущих взносов функция ЧПС буде возвращать свой итоговый результат вычисления равный нулю (0).
Чтобы использовать функцию ВСД необходимо подготовить входные данные. В диапазоне значений должно находится минимум одно отрицательное и одно положительное число суммы будущих платежей. Если же все суммы будут положительными – это будет значит, что ничего не инвестируется, а только возвращается платежами доходности от инвестиций, например, дивиденды, прибыль с выручки и т.п. Это положительный сценарий, но мало вероятный на практике. Обычно платежи инвестирования идут в начале периода реализации инвестиционного проекта, а платежи доходов от инвестиций идут под его конец. Не всегда так бывает, но всегда будет минимум 1 платеж с отрицательным числом (на расход) и минимум 1 платеж с положительным платежом (на приход).
Ниже на рисунке представлен хронологический график дивидендных выплат. В нем учитывается начальная сумма инвестиций для того, чтобы функция ВСД возвращала правильный итоговый результат вычисления. В начале таблицы графика находится строка содержащая значение суммы инвестиционного вложения равной 300 000$. Ниже приведенная формула отображает внутреннюю ставку доходности равной 10,53%:
Первый аргумент функции ВСД содержит диапазон ячеек с платежами на протяжении всего инвестиционного периода:
Во втором аргументе указана внутренняя процентная ставка доходности инвестиций. Если второй аргумент не указан, Excel по умолчанию присевает значение 10%.
Принцип действия функции ВСД основан на вычислениях значений текущей стоимости суммы для каждого платежа при процентной ставке доходности инвестиций. Если в результате вычислений текущая стоимость суммы очередного платежа больше чем >0, функция уменьшает процентную ставку и выполняет то же вычисление еще раз. Excel повторяет эти же операции изменяя размер процентной ставки и суммируя текущие значения пока сумма не будет равна =0. Тогда функция ВСД возвращает в своем итоговом результате вычисления найденную подходящую процентную ставку.
Расчет внутренней ставки доходности с нерегулярными платежами по ЧИСТВНДОХ
Одинаково как для функции ЧПС так и для функции ВСД принято условия, что все будущие платежи будут регулярными и проведенными в одном и том же промежутке времени. Однако на практике так бывает далеко не всегда. На случай нерегулярных инвестиционных платежей программа Excel предлагает функцию ЧИСТВНДОХ:
В отличии от функции ВСД функция ЧИСТВНДОХ содержит дополнительный обязательный для заполнения аргумент в своем синтаксисе, в которой нужно указать все даты для всех платежей:
Для функции ВСД – даты не нужны, так как обязательным условием считается, что все платежи будут выполнены регулярно и между ними проходит один и тот же период времени. Будит ли это одни день или месяц, или год – для функции ВСД нет никакого значения. Вычисленная процентная ставка с помощью ВСД будет правильной для всех указанных платежей. Это значит, что при ежегодных платежах будет вычисленная процентная ставка доходности – годовых. Если же платежи выполняются ежеквартально – будет получена ставка в ежеквартальной процентной ставке внутренней нормы доходности.
Примечание. Функция ЧИСТВНДОХ имеет родственную функцию ЧИСТНЗ для вычисления текущей стоимости инвестиций при нерегулярных платежах. Данная функция также требует указать даты нерегулярных платежей.
Ниже на рисунке представлен хронологический график с нерегулярными платежами инвестиционных взносов и дивидендных выплат. Иногда инвестор меняет свою инвестиционную стратегию и делает дополнительные вложения на протяжении всего периода реализации инвестиционного проекта при различных условиях: непредвиденные убытки или новые возможности. Учитывая все нерегулярные платежи, инвестиция достигает внутреннюю ставку доходности равную 10,14%. Для ее вычисления была использована функция ЧИСТВНДОХ.
Внутренний принцип действия функции ЧИСТВНДОХ практически идентичный с функционированием ВСД. ЧИСТВНДОХ вычисляет текущую стоимость для отдельно каждого платежа и повторяет результат, изменяя внутреннюю ставку доходности пока вычисляемый результат не будет равен нулю. При вычислении текущей стоимости учитывается количество дней между датами актуального платежа и его предыдущего предшественника. В итоговом результате вычисления функцией ЧИСТВНДОХ будет возвращена реальная внутренняя ставка доходности для всех приходных и расходных нерегулярных инвестиционных платежей.
