Срок окупаемости в Excel: расчет и формулы
Срок окупаемости показывает, через сколько месяцев или лет вложения вернутся за счет прибыли проекта. Показатель нужен и владельцу бизнеса, который покупает оборудование, и инвестору, который сравнивает два предложения, и банку, который решает, выдавать ли кредит. В Excel расчет сводится к нескольким столбцам и паре формул. Ниже разберем оба метода — простой и дисконтированный, покажем числовые примеры с готовыми формулами и дадим шаблон, который можно скачать и заполнить своими данными.
Что такое срок окупаемости и зачем его считать
Срок окупаемости (Payback Period, PP) — это минимальный период, за который суммарный чистый денежный поток от проекта покрывает первоначальные инвестиции. Показатель не измеряет прибыльность, его задача — оценить риск. Чем короче период, тем быстрее инвестор выходит в плюс и тем меньше зависит от того, что случится на рынке через три-пять лет.
Расчет нужен в нескольких ситуациях: при запуске нового направления или точки продаж, при закупке оборудования, при обосновании кредита перед банком, при выборе между несколькими проектами с ограниченным бюджетом. Банки, как правило, требуют, чтобы срок окупаемости укладывался в период кредитования, иначе заявку отклонят.
телефоне
Какой срок считается нормальным
Универсальной нормы нет, все зависит от отрасли и типа вложений. В розничной торговле оборудование для нового отдела обычно окупают за 1-2 сезона. Кофейня или небольшое производство — за 1,5-3 года. IT-стартап может окупаться 3-5 лет, и это нормально для венчурных инвестиций. Солнечные панели или капитальный ремонт здания — 7-10 лет.
Главное правило: срок окупаемости должен быть короче срока жизни актива или проекта. Если станок морально устареет через 4 года, а вложения вернутся через 5, проект не имеет смысла даже при хорошей расчетной прибыли.
-
87%
видят результат в первый месяц -
31%
среднее сокращение издержек -
42%рост скорости операций
-
18%увеличение оборота
Исходные данные для расчета
Прежде чем заполнять Excel, нужно собрать три вещи.
Первое — сумма начальных инвестиций. Сюда входит все, что тратится до запуска: покупка оборудования, ремонт помещения, закупка первой партии товара, лицензии, маркетинг на старте. Важно не занизить: забытая статья расходов сдвинет реальный срок окупаемости на месяцы.
Второе — чистый денежный поток по периодам. Это разница между притоком (выручка, поступления) и оттоком (аренда, зарплата, закупки, налоги, прочие расходы) за каждый месяц или год. Используйте именно денежный поток, а не бухгалтерскую прибыль: прибыль включает амортизацию и неденежные статьи, а для окупаемости важно реальное движение денег.
Третье — ставка дисконтирования (для дисконтированного метода). За основу берут ключевую ставку ЦБ, доходность ОФЗ или средневзвешенную стоимость капитала компании (WACC). Для малого бизнеса часто используют ставку по кредиту, которым финансируется проект.
Метод 1. Простой срок окупаемости (PP)
Подходит для быстрой оценки или проектов с коротким горизонтом. Не учитывает изменение стоимости денег во времени.
Если денежный поток равномерный
Когда ежемесячная или ежегодная прибыль примерно одинаковая, формула элементарна:
PP = Инвестиции / Среднегодовой чистый денежный поток
Пример: вложили 900 000 руб. в пекарню, чистый денежный поток после всех расходов — 75 000 руб./мес, или 900 000 руб./год. PP = 900 000 / 900 000 = 1 год. В Excel: =B2/C2, где B2 — инвестиции, C2 — годовой поток.
Если денежный поток неравномерный
На практике доходы по месяцам отличаются: первые месяцы слабее, потом выходят на плато, бывают сезонные провалы. Здесь нужен накопительный итог. Рассмотрим на примере.
Условие: предприниматель вкладывает 1 200 000 руб. в открытие точки продаж. Прогнозные денежные потоки по полугодиям неравномерны.
| Период | Денежный поток, руб. | Накопительный итог, руб. | Формула накопит. итога |
|---|---|---|---|
| 0 (старт) | -1 200 000 | -1 200 000 | =B2 |
| 1 полугодие | 180 000 | -1 020 000 | =C2+B3 |
| 2 полугодие | 320 000 | -700 000 | =C3+B4 |
| 3 полугодие | 350 000 | -350 000 | =C4+B5 |
| 4 полугодие | 400 000 | +50 000 | =C5+B6 |
| 5 полугодие | 420 000 | +470 000 | =C6+B7 |
Накопительный итог переходит из минуса в плюс между 3-м и 4-м полугодием. Точный момент считается линейной интерполяцией:
PP = 3 + 350 000 / 400 000 = 3,875 полугодия, или примерно 1 год и 11 месяцев
Формула в Excel для точного значения: =3+(-C5/B6), где C5 — накопительный итог перед переходом (отрицательный), B6 — поток периода, в котором произошел переход.
Метод 2. Дисконтированный срок окупаемости (DPP)
Простой метод не учитывает, что 400 000 руб. через два года стоят дешевле, чем 400 000 руб. сегодня: инфляция, упущенная доходность альтернативных вложений. Дисконтированный расчет приводит будущие потоки к сегодняшней стоимости. Результат всегда длиннее простого срока, иногда на несколько периодов.
Формула дисконтирования для одного периода:
Дисконтированный поток = Поток / (1 + Ставка)^N
где N — номер периода (полугодия, года), ставка — в долях за тот же период.
Возьмем те же данные. Ставка дисконтирования — 10% годовых, то есть 5% за полугодие (0,05).
| Период | Поток, руб. | Коэфф. дисконтир. | Дисконтир. поток, руб. | Накопит. итог, руб. | Формулы (столбцы D и E) |
|---|---|---|---|---|---|
| 0 | -1 200 000 | 1,0000 | -1 200 000 | -1 200 000 | D: =B2/(1+$G$1)^A2 E: =D2 |
| 1 | 180 000 | 0,9524 | 171 429 | -1 028 571 | D: =B3/(1+$G$1)^A3 E: =E2+D3 |
| 2 | 320 000 | 0,9070 | 290 249 | -738 322 | D: =B4/(1+$G$1)^A4 E: =E3+D4 |
| 3 | 350 000 | 0,8638 | 302 341 | -435 981 | D: =B5/(1+$G$1)^A5 E: =E4+D5 |
| 4 | 400 000 | 0,8227 | 329 080 | -106 901 | D: =B6/(1+$G$1)^A6 E: =E5+D6 |
| 5 | 420 000 | 0,7835 | 329 082 | +222 181 | D: =B7/(1+$G$1)^A7 E: =E6+D7 |
В ячейке G1 хранится ставка за полугодие (0,05). Накопительный итог переходит в плюс между 4-м и 5-м полугодием:
DPP = 4 + 106 901 / 329 082 = 4,32 полугодия, или 2 года и 2 месяца
Для сравнения: простой срок окупаемости того же проекта — 1 год 11 месяцев, дисконтированный — 2 года 2 месяца. Разница — 3 месяца. На проектах с горизонтом 5-10 лет и высокой ставкой разрыв будет заметно больше.
Автоматический поиск точки окупаемости в Excel
Когда периодов много, искать переход вручную неудобно. Можно автоматизировать формулой. В пустой ячейке напишите:
=ПОИСКПОЗ(ИСТИНА;ИНДЕКС(E2:E20>0;0);0)-1+(-ИНДЕКС(E2:E20;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(E2:E20>0;0);0)-1)/ИНДЕКС(D2:D20;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(E2:E20>0;0);0)))
Формула находит первый период, где накопительный итог (столбец E) стал положительным, берет предыдущее отрицательное значение и делит на поток текущего периода (столбец D). На выходе — точный срок окупаемости с дробной частью.
Если формула кажется громоздкой, проще разбить на вспомогательные ячейки: в одной найти номер периода перехода через ПОИСКПОЗ, в другой — посчитать дробную часть.
Связка с другими показателями
Срок окупаемости — необходимый, но недостаточный критерий. Проект может окупиться быстро, но принести мало прибыли за весь жизненный цикл. Поэтому его всегда смотрят вместе с двумя другими показателями.
NPV (чистая приведенная стоимость) — сумма всех дисконтированных потоков за весь срок проекта. Если NPV положительный, проект генерирует стоимость сверх требуемой доходности. В Excel считается функцией ЧПС: =ЧПС(ставка;B3:B12)+B2, где B2 — инвестиции (отрицательное число), B3:B12 — потоки.
IRR (внутренняя норма доходности) — ставка, при которой NPV равен нулю. Показывает, какую годовую доходность дает проект. В Excel: =ВСД(B2:B12). Если IRR выше вашей ставки дисконтирования, проект выгоден.
Хорошая практика — считать все три показателя на одном листе. Так видна полная картина: окупаемость отвечает на вопрос «когда вернутся деньги», NPV — «сколько заработаю сверх минимума», IRR — «какая доходность в процентах».
Типичные ошибки при расчете
Несколько вещей, которые часто портят результат на практике.
Путаница между прибылью и денежным потоком. Бухгалтерская прибыль включает амортизацию и начисленные, но не полученные доходы. Для окупаемости нужен именно кэш — реальные поступления минус реальные выплаты.
Забытые статьи инвестиций. Люди считают стоимость оборудования, но забывают доставку, монтаж, обучение персонала, первый закуп расходников. Каждая пропущенная статья занижает знаменатель и делает расчет оптимистичнее, чем реальность.
Неверный период ставки дисконтирования. Если потоки помесячные, а ставка годовая, ее нужно пересчитать. Простое деление на 12 дает приблизительный результат. Точная формула: =(1+годовая_ставка)^(1/12)-1.
Игнорирование сезонности. Средний поток по году может выглядеть привлекательно, но если зимой бизнес стоит, накопительный итог проседает и реальная окупаемость сдвигается. Считайте помесячно, если доходы неравномерны.
Практические рекомендации
- Считайте минимум два сценария: реалистичный и пессимистичный (потоки на 20-30% ниже плана). Если даже пессимистичный вариант окупается в приемлемый срок, проект устойчив.
- Используйте дисконтированный метод для любого проекта длиннее года. Простой метод годится только для быстрой прикидки.
- Сохраняйте копии файла с датой перед каждым обновлением прогноза. История версий покажет, как менялись ожидания.
- Если рядом с окупаемостью нужно считать себестоимость продукции, загляните в руководство по калькуляции себестоимости в Excel — там разобрана структура таблицы с нормами, ценами и формулами.
- Когда проект завязан на складские запасы и движение товара, данные о потоках удобно брать из журналов прихода и расхода. Как их организовать, описано в статье учет прихода и расхода товаров в Excel.
- Если объем данных вырос и Excel тормозит или над файлом работают несколько человек, стоит посмотреть на признаки того, что таблиц перестало хватать, и подумать об учетной программе.
