Считаем накопленный денежный поток и находим первый неотрицательный период. Проверяем нулевой итог, отсутствие окупаемости и повторное падение потока.
В проект вложили деньги, затем начали получать поступления. Чтобы определить период окупаемости, нужно найти момент, когда накопленный денежный поток впервые стал неотрицательным. У этого простого определения есть несколько граничных случаев, которые стоит проверить в файле.
Поток может попасть ровно в ноль. Проект может не окупиться за выбранный горизонт. После первого выхода в плюс могут возникнуть новые расходы и вернуть накопленный результат в минус. Формула должна давать понятный ответ в каждой такой ситуации.
Сначала собираем накопленный поток
Возьмём учебный проект. Период 0 содержит первоначальные инвестиции. Все суммы указаны в одинаковых условных единицах, периоды имеют одинаковую длительность.
| Период | 0 | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|---|
| Денежный поток | −100 | 30 | 35 | 45 | −20 | 40 |
| Накопленный поток | −100 | −70 | −35 | 10 | −10 | 30 |
Первый неотрицательный итог появляется в периоде 3. В периоде 4 он снова становится отрицательным. Оба наблюдения нужны для интерпретации проекта.
В Excel разместите номера периодов в B2:G2, денежные потоки — в B3:G3. В B4 введите накопительную сумму и протяните вправо.
=СУММ($B3:B3)
Начало диапазона закреплено по столбцу B. Его конец расширяется при копировании. В G4 получится сумма всех потоков от B3 до G3. Начальные инвестиции участвуют в каждом накопленном результате.
Добавляем явную проверку
В B5 запишите условие и скопируйте его до G5.
=B4>=0
В строке появится последовательность ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ИСТИНА. Теперь задача поиска сформулирована точно — найти первое ИСТИНА.
=ИНДЕКС(B2:G2;1;ПОИСКПОЗ(ИСТИНА;B5:G5;0))
Последний аргумент ПОИСКПОЗ равен нулю и задаёт точное совпадение. Функция возвращает позицию первого подходящего значения, а ИНДЕКС берёт из строки 2 соответствующий номер периода. В нашем примере ответ равен 3. Правила точного и приближённого поиска приведены в документации Microsoft по ПОИСКПОЗ.
Вспомогательная строка делает логику видимой. По ней можно сразу проверить, включено ли равенство нулю и где впервые выполнилось условие. Она также позволяет обойтись без составной формулы массива для сравнения всего диапазона.
Если окупаемость не найдена
При потоках −100, 10, 10 и 10 накопленный итог останется отрицательным. ПОИСКПОЗ не найдёт ИСТИНА и вернёт ошибку отсутствующего совпадения. После проверки исходных данных можно оформить понятный результат.
=ЕСЛИОШИБКА(ИНДЕКС(B2:G2;1;ПОИСКПОЗ(ИСТИНА;B5:G5;0));"Нет в горизонте")
У ЕСЛИОШИБКА широкое действие — она перехватывает разные ошибки формулы. Поэтому перед её использованием убедитесь, что в исходных потоках, накопительных суммах и логических проверках нет ошибок. Иначе техническая неисправность может превратиться в сообщение об отсутствии окупаемости.
Фраза «Нет в горизонте» относится к показанным периодам. Она ничего не доказывает о движении денег за пределами построенной модели. Если горизонт продлевается, нужно расширить и формулы, и диапазон поиска.
Какие случаи проверить отдельно
| Проверка | Учебные потоки | Ожидаемый ответ |
|---|---|---|
| Попадание ровно в ноль | −100, 50, 50, 20 | Период 2 |
| Быстрый возврат инвестиций | −100, 120, 0, 0 | Период 1 |
| Нет возврата за четыре периода | −100, 10, 10, 10 | Нет в горизонте |
| Повторное падение | −100, 150, −200, 200 | Первый выход в периоде 1 |
В последнем случае найденный период описывает первый переход через ноль. Для оценки устойчивого возврата средств нужно дополнительно проверить последующие потоки. Условие такой проверки определяется задачей модели.
При нулевых начальных инвестициях первый неотрицательный итог может находиться уже в периоде 0. Это корректный результат принятого алгоритма. Его экономический смысл зависит от того, действительно ли у проекта нет первоначальных затрат.
Дисконтированная окупаемость
Если задача требует учесть стоимость денег во времени, сначала рассчитывают дисконтированные потоки, затем накапливают их и ищут первое неотрицательное значение тем же способом. Для одинаковых периодов поток периода t делят на (1+r)^t, где r — ставка за один такой период.
Нужно согласовать длительность периода и ставку. Годовая ставка непосредственно применяется к годовым шагам. Переход к месячному расчёту требует отдельного определения месячной ставки и временной структуры потоков. Этот пример не выбирает ставку за пользователя.
Полученный ответ — номер периода. Дробный срок окупаемости требует дополнительного предположения о том, как деньги поступают внутри периода. Его нельзя получить простым переименованием найденного года в точную дату.
Разбор таких проверок входит в обучение финансовым расчётам в Excel. Полезный результат упражнения — формула, поведение которой понятно при изменении исходных данных.
Практика на корпоративном обучении
Финансовые модели в Excel для экономистов и финансистов. Программа включает учебные задачи, самостоятельный расчёт и сверку результатов.