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

При установленной надстройке становятся доступны функции расчета простого и дисконтированного срока окупаемости (PBP, DPBP), Profitability Index, CAGR, налога на прибыль и другие. Эти функции позволяют сэкономить время, отказавшись от написания сложных формул, и избежать в моделях ячеек с промежуточными техническими расчетами.

Использование надстройки не требует внедрения макросов в сами файлы моделей.

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

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

Чтобы установить надстройку достаточно скачать готовую надстройку “InvestmentFunctions.xlam” и подключить ее в настройках Excel. Детальная инструкция в конце этой заметки.

Список функций и как их использовать

Disclaimer:

• Сигнатура функции описывает, какие аргументы принимает функция. Названия аргументов подобраны так, чтобы было понятно, что они означают. Для аргументов и особенной использования даны пояснения.
• Реальные примеры использования функций можно также посмотреть в файле-примере на листах “Examples”.

fCAGR - расчет CAGR по вектору значений

Рассчитывает совокупный среднегодовой темп роста (CAGR) по диапазону ячеек.

Сигнатура функции: fCAGR(rValuesRange As Range) As Double

Особенности:

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

fCAGR2 - расчет CAGR по начальному и конечному значению

Рассчитывает совокупный среднегодовой темп роста (CAGR) по начальному и конечному значению показателя с указанием периодов.

Сигнатура функции: fCAGR2(dStartValue As Double, dEndValue As Double, dStartYear As Double, dEndYear As Double) As Double

fDepreciation - расчет амортизации

Рассчитывает амортизацию в текущем периоде на базе диапазона предыдущих капитальных вложений и среднего срока амортизации активов.

Сигнатура функции: fDepreciation(rCapex As Range, dDepreciationPeriod As Double) As Double

Особенности:

• “rCapex” - диапазон ячеек с суммами капитальных вложений в предыдущие периоды. Порядок ячеек слева - направо.

fExtendAP - продлить ряд значений как арифметическую прогрессию

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

Сигнатура функции: fExtendAP(vInput As Variant, iPeriods As Variant, Optional dGrowthRate As Variant) As Variant

Особенности:

• “vInput” - может быть диапазоном ячеек или одним числом
• “iPeriods” - на какое количество периодов продлевать ряд
• Опциональный показатель “dGrowthRate” - шаг или разность арифметической прогрессии. По умолчанию - ноль.

fExtendGP - продлить ряд значений как геометрическую прогрессию

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

Сигнатура функции: fExtendGP(vInput As Variant, iPeriods As Variant, Optional dGrowthRate As Variant) As Variant

Особенности:

• “vInput” - может быть диапазоном ячеек или одним числом
• “iPeriods” - на какое количество периодов продлевать ряд
• Опциональный показатель “dGrowthRate” - знаменатель геометрической прогрессии. По умолчанию - 1.

fIncomeTax - расчет налога на прибыль

Рассчитывает налог на прибыль в текущем периоде с учетом прибыли и убытков прошлых периодов.

Сигнатура функции: fIncomeTax(rProfitRange As Range, dTaxRate As Double, Optional dPriorProfitsAndLosses As Double) As Double

Особенности:

• “rProfitRange” - диапазон ячеек с суммами прибыли до налога в предыдущие периоды, включая текущий период. Порядок ячеек слева - направо.
• “dTaxRate” - налоговая ставка в процентах. Например, “20% или “0,2”.
• Опциональный показатель “dPriorProfitsAndLosses” - входящая накопленная прибыль/убыток до первого периода. Значение убытка указывается с минусом.

fLimit - ограничение значения параметра в целевом диапазоне

Берет входящее значение и ограничивает сверху и снизу его в целевом диапазоне. Это продвинутый МИН(МАКС()), в котором не надо думать о порядке аргументов.

Сигнатура функции: fLimit(varInput, varLimit1, varLimit2)

Особенности:

• “varInput” - входящий параметр, значение которого мы хотим ограничить
• “varLimit1”, “varLimit2” - границы диапазона. Порядок указания (нижняя, верхняя границы) не важен.

fPayout - расчет свободного денежного потока с учетом будущих поступлений и платежей

Формула рассчитывает, сколько денег можно безопасно извлечь из денежного потока текущего периода, чтобы в будущем деньги на счетах не снижались ниже заданного лимита.

Сигнатура функции: fPayout(dPayoutBase As Double, rFutureCashFlow As Range, Optional dWACC As Double, Optional dMinCashLimit As Double) As Double

Особенности:

• “dPayoutBase” - денежный поток текущего периода, из которого мы берем деньги на дивиденды
• “rFutureCashFlow” - диапазон ячеек с денежными потокам в будущие периоды, не включая текущий период
• Опциональный показатель “dWACC” - стоимость капитала. Подразумевается, что неиспользованные деньги на счетах каждый период приносят эту доходность. По умолчанию - ноль.
• Опциональный показатель “dMinCashLimit” - неснижаемый денежный остаток. По умолчанию - ноль.

fPBP - расчет простого и дисконтированного срока окупаемости

Формула рассчитывает срок окупаемости для денежного потока с возможностью продления потока за период прогнозирования. (Wikipedia: Простой срок окупаемости, Дисконтированный срок окупаемости)

Сигнатура функции: fPBP(varCashFlowRange As Range, Optional varDiscountRate As Variant, Optional varGrowthRate As Variant) As Variant

Особенности:

• “varCashFlowRange” - диапазон ячеек с денежными потоками. Порядок ячеек слева-направо. Первая ячейка в диапазоне учитывается как нулевой период.
• Опциональный показатель “varDiscountRate” - ставка дисконтирования в процентах. Например, “20% или “0,2”. Если параметр не указан, то, по умолчанию, берется нулевое значение и формула считает простой срок окупаемости.
• Опциональный показатель “varGrowthRate” - темп роста в постпрогнозный период. Например, “1.05”. По умолчанию значение ноль. Если указать значение, то формула возьмет денежный поток последнего периода и будет продлевать его с заданным темпом. Максимальное количество периодов, на которое формула продляет денежный поток - 360.

fPI - расчет Profitability Index (PI)

Формула рассчитывает Profitability Index (“Индекс рентабельности””) денежного потока. (Wikipedia: Индекс рентабельности (PI))

Сигнатура функции: fPI(varCashFlowRange As Range, Optional varDiscountRate As Variant) As Variant

Особенности:

• “varCashFlowRange” - диапазон ячеек с денежными потоками. Порядок ячеек слева-направо.
• Опциональный параметр “varDiscountRate” - ставка дисконтирования в процентах. Например, “20% или “0,2”. Если параметр не указан, то, по умолчанию, берется нулевое значение.

Инструкция по установке

Все материалы доступны в репозитории на GitHub.

1. Скачать файл “InvestmentFunctions.xlam” и сохранить, где будет удобно
2. В Microsoft Excel войти в меню “Файл”->“Параметры”
3. Далее перейти в раздел “Надстройки”. В поле внизу экрана выбрать “Надстройки Excel” и нажать “Перейти”.
4. В появишемся окне нажать “Обзор” и указать путь к ранее сохраненному файлу “InvestmentFunctions.xlam”. В списке надстроек слева появится надстройка “Investmentfunctions”.
5. Если все прошло успешно, то в Excel станут доступны новые функции.

Обратная связь

Вопросы, информацию о багах или предложения по развитию пишите здесь в комментариях или в Issues на GitHub.