Расчет окупаемости инвестиционного проекта Мощная Excel-таблица. Все показатели: WACC, NPV, IRR, PBP, DPBP, PI, ROI, чувствительность

Финансовая модель инвестиционного проекта в Excel: практический пример расчёта эффективности с показателями – срок окупаемости, ЧПС (NPV), ВНД (IRR)

Инфографика: Срок окупаемости инвестиций (PP, DPP, BO DPP)

srok-okupaemosti

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

Пройдите наш авторский курс по выбору акций на фондовом рынке →

обучающий курс

Формулы для динамического расчета срока окупаемости проекта в Excel

Если вы формируете модели окупаемости инвестиций в Excel, попробуйте мастер формул в наших надстройках PowerFin или PowerQuick.

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

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

1.  Округленный срок окупаемости (т.е. на какой год накопленный поток перешагнет через ноль): =СЧЁТЕСЛИ(C7:I7;”<0″)+1

2. Точный срок окупаемости — то же самое, но с указанием десятичных знаков:

=СЧЁТЕСЛИ(C7:I7;”<0″)+ИНДЕКС(C7:I7;1;СЧЁТЕСЛИ(C7:I7;”<0″)) / ((ИНДЕКС(C7:I7;1;СЧЁТЕСЛИ(C7:I7;”<0″))*-1+ИНДЕКС(C7:I7;1;СЧЁТЕСЛИ(C7:I7;”<0″)+1)))*-1

Модель денежных потоков с показателями срока окупаемости:

Срок окупаемости в Excel 2

Кстати, вы можете не писать эти формулы вручную! Просто установите нашу бесплатную надстройку для Excel PowerFin, чтобы этот и другие финансовые инструменты были под рукой в любую минуту!

Надстройка PowerFin

А если вы хотите больше функционала в вашем Excel – попробуйте нашу мощную универсальную надстройку PowerQuick!

Вы вводите

  1. Суммы инвестиций
  2. Все расходы по статьям на каждом шаге инвестиционного проекта, в том числе налоги и другие платежи
  3. Все доходы по статьям на каждом шаге проекта

Как рассчитать простой срок окупаемости в Excel

Приведенные выше примеры достаточно просто рассчитать при помощи обычного калькулятора и листа бумаги. Если же данные сложнее – пригодятся таблицы в Excel.

Расчет примера №1

Расчет простого срока окупаемости выглядит следующим образом:

Таблица 1: формулы расчета.

Пример расчета простого срока окупаемости в Excel

Таблица 2: результаты вычислений:

Пример расчета простого срока окупаемости в Excel - результаты вычислений

Расчет примера №2

Для более сложного варианта расчета простого срока окупаемости расчет в Excel делается следующим образом:

Таблица 1: формулы расчета.

Пример расчета простого срока окупаемости в Excel - формулы расчета

Таблица 2: результаты вычислений:

Пример расчета простого срока окупаемости в Excel - результаты вычислений

Аналогичная методика вычислений используется и для расчета дисконтированного срока окупаемости, речь о котором пойдет в следующей главе.

Расчет NPV в Excel (пример табличный)

Этот же пример мы можем решить, организовав соответствующие данные в форме таблицы Excel.

Выглядеть это должно примерно так:

расчет npv в excel пример, расчет npv в excel, формула npv в excel, как рассчитать npv в excel, npv в эксель

Рисунок 2. Расположение данных примера на листе Excel

Для того чтобы получить нужный результат, мы должны соответствующие ячейки заполнить нужными формулами.

Ячейка Формула
E4 =1/СТЕПЕНЬ(1+$F$2/100;B4)
E5 =1/СТЕПЕНЬ(1+$F$2/100;B5)
E6 =1/СТЕПЕНЬ(1+$F$2/100;B6)
E7 =1/СТЕПЕНЬ(1+$F$2/100;B7)
E8 =1/СТЕПЕНЬ(1+$F$2/100;B8)
E9 =1/СТЕПЕНЬ(1+$F$2/100;B9)
E10 =1/СТЕПЕНЬ(1+$F$2/100;B10)
E11 =1/СТЕПЕНЬ(1+$F$2/100;B11)
E12 =1/СТЕПЕНЬ(1+$F$2/100;B12)
E13 =1/СТЕПЕНЬ(1+$F$2/100;B13)
E14 =1/СТЕПЕНЬ(1+$F$2/100;B14)
F4 =(C4-D4)*E4
F5 =(C5-D5)*E5
F6 =(C6-D6)*E6
F7 =(C7-D7)*E7
F8 =(C8-D8)*E8
F9 =(C9-D9)*E9
F10 =(C10-D10)*E10
F11 =(C11-D11)*E11
F12 =(C12-D12)*E12
F13 =(C13-D13)*E13
F14 =(C14-D14)*E14
F15 =СУММ(F4:F14)

В результате в ячейке F15 мы получим искомое значение NPV, равное 352,1738.

Чтобы создать такую таблицу нужно затратить 3-4 минуты. Excel позволяет найти нужное значение NPV быстрее.

Пример расчёта инвестиционного проекта в Excel

Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.

Прогнозирование денежного потока в Excel

Заполните таблицу «Денежные потоки»:

  • в ячейку В9 введите значение первоначальных инвестиций,
  • в ячейку В10 — формулу «=B8-B9»
  • в ячейку С8 введите сумму поступлений в первый год,
  • в D8 – формулу «=C8*1,3»,
  • в С9 — «=C8*0,8»,
  • протяните формулу из ячейки D8 вправо до 2019 года, рассчитайте итоговое значение;
  • протяните вправо формулы из ячеек С9 и В10,
  • протяните формулу из ячейки G8 на две ячейки вниз.
  • В ячейку В11 формулу «=B10», в ячейку С11 формулу =B11+C10, протяните ячейку С11 вправо до F11, сверьте значение в ячейке F11 cо значением в G10.

Теперь рассчитаны денежные потоки, в том числе нарастающим итогом.

Срок окупаемости в Excel: пример расчёта

Для расчёта срока окупаемости в примере Excel введите в ячейку В17 формулу «=СЧЁТЕСЛ?(B11:G11;»<1″)+1». Смысл этой формулы:  подсчёт всех отрицательных значений и прибавление единицы даст номер года, в котором совокупный денежный поток станет неотрицательным.

Дисконтирование в Excel денежного потока: продолжение примера

На листе «инвестиционный проект» заполните строчку Дисконтированный денежный поток: в ячейку В13 введите «=B10/СТЕПЕНЬ(1+$B$5;B12)», протяните вправо. Обратите внимание на нумерацию периодов инвестирования: текущий год имеет номер 0, так как для него денежный поток не дисконтируется. Второй год (2016) имеет номер 1, денежный поток дисконтируется с коэффициентом 0,82 (=1/(1+22%)), с каждым следующим годом коэффициент дисконтирования уменьшается. Рассчитайте суммарный дисконтированный денежный поток в ячейке G13. Как видим, значение NPV существенно отличается от значения совокупного недисконтированного денежного потока (ячейка F11) за счёт достаточно большой ставки дисконтирования. Поскольку значение положительное, проект может быть признан привлекательным для инвестирования.

Расчёт чистой приведённой стоимости (NPV) в Excel: продолжение примера

В Excel есть встроенная функция для расчёта NPVЧПС(ставка.дисконтирования; диапазон_денежных_потоков). Этой функцией пользоваться удобнее (не нужно рассчитывать дисконтированный поток), но у неё есть особенность: первый год проекта считается как будущий (с номером 1) и его денежный поток уже дисконтируется. Если проект планируется, начиная с текущего года, необходимо до применения функции ЧПС скорректировать значения денежных потоков каждого года, умножив их на коэффициент (1+ставка дисконтирования), покажем на примере.

На листе «инвестиционный проект» заполните строчку «Скорректированный денежный поток»: в ячейку В15 введите формулу «=B10*(1+$B$5)» и протяните вправо. Теперь в ячейку В18 введите формулу «=ЧПС(B5;B15:F15)», сравните с рассчитанным «вручную» значением в ячейке G13.

Расчёт внутренней нормы доходности (IRR) в Excel: окончание примера

Сделаем расчёт IRR в Excel двумя способами: вручную и автоматически.

На листе «инвестиционный проект», вручную изменяя ставку дисконтирования, примерно подберите такое значение ставки, чтобы значение в ячейке G13 было близко к нулю.

В Excel есть функция для расчёта IRR — ВСД, которая работает с той же особенностью дисконтирования с первого года. В ячейке В19 введите функцию «=ВСД(B15:F15)», сравните с подобранным вручную.

Таким образом, рассмотрен пример расчёта инвестиционного проекта в Excel, включающий составление таблицы дисконтированного денежного потока  с расчётом показателей инвестиционного проекта.

Скачать файл с примером инвестиционного проекта Excel можно по ссылке: pokazateli-investproekta.

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

Задача на нахождение NPV

Пример. Первоначальные инвестиции в проект A составляют 10000 рублей. Ежегодная процентная ставка – 10 %. Динамика поступлений с 1-го по 10-ый годы представлена в нижеследующей таблице:

Период Притоки Оттоки
10000
1 1100
2 1200
3 1300
4 1450
5 1600
6 1720
7 1860
8 2200
9 2500
10 3600

Для наглядности cответствующие данные можно представить графически:

Рисунок 1. Графическое представление исходных данных для расчета NPV

Необходимо рассчитать показатель NPV.

Стандартное решение. Для решения задачи будем использовать уже известную нам формулу NPV:

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

NPV = -10000/1,1 + 1100/1,11 + 1200/1,12 + 1300/1,13 + 1450/1,14 + 1600/1,15 + 1720/1,16 + 1860/1,17 + 2200/1,18 + 2500/1,19 + 3600/1,110 = 352,1738 рублей.

Отличия между дисконтированным и простым сроком окупаемости

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

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

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

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

Вы можете посмотреть как рассчитывается простой и дисконтированный срок окупаемости применительно к интересующему вас бизнесу в конкретных бизнес-планах, для этого вы можете воспользоваться формой поиска или перейти на соответствующий документ по ссылке ниже:

Расчет срока окупаемости инвестиции с учетом ликвидационной стоимости в Excel

На рисунке ниже показан расчет периода окупаемости проекта с учетом ликвидационной стоимости. Формула в Excel достаточно простая и имеет вид:

Денежные поступления с ликвидационной стоимостью =C6+E5+D6

Оценка срока окупаемости с учетом ликвидационной стоимости в Excel

Пример оценки срока окупаемости с учетом ликвидационной стоимости в Excel

В итоге, срок окупаемости с учетом ликвидационной стоимости составит ~4 лет. Данный способ оценки целесообразно применять при высокой ликвидности создаваемых активов. Как можно заметить, в данном варианте расчета срока окупаемости тоже может быть использована ставка дисконтирования.

Кроме срока окупаемости инвестиций существуют другие показатели оценки эффективности, позволяющие более точно провести анализ проекта. Более подробно о них вы можете узнать в статье: «6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI«.

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

Резюме

Срок окупаемости является важнейшим показателем инвестиционного анализа проектов и бизнеса. Он позволяет определить целесообразность вложения в тот или иной проект. Использование дисконтирования денежных потоков и ликвидационной стоимости активов позволяет инвестору более точно оценить период возврата капитала. Помимо данного коэффициента необходим анализ через другие показатели эффективности: чистой приведенной стоимости (NPV), внутренней нормы доходности (IRR) индекса прибыльности (PI). Кроме точечной оценки необходим анализ динамики денежных потоков и их равномерность.

Автор: к.э.н. Жданов Иван Юрьевич

Оцените качество статьи. Нам важно ваше мнение:

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...