Excel для финансиста
Поиск на сайте
Финансовая модель инвестиционного проекта в excel
В планировании деятельности компании часто возникает задача оценки эффективности от долгосрочных (более 2 лет) инвестиций. Необходимо ответить на ряд вопросов: окупятся ли инвестиции вообще, если да — то насколько быстро, какова эффективность инвестиционного проекта по сравнению с другими управленческими решениями.
Показатели инвестиционного проекта
Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:
- срок окупаемости проекта (обычно в месяцах)
- чистая приведённая стоимость (net present value, NPV)
- внутренняя норма доходности (IRR).
Срок окупаемости проекта — промежуток времени, который показывает, как долго будут возмещаться вложения в проект с учетом оплаты всех сопутствующих операционных затрат. Чем меньше этот срок, тем выше привлекательность проекта для инвестора.
Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени. Дисконтирование производится путём умножения значений будущих потоков на понижающий коэффициент:
Кд = 1 / (1 + Ставка дисконтирования)^Номер периода
Ставка дисконтирования – это процентная ставка, используемая для перерасчета будущих потоков доходов в единую величину текущей стоимости. Выбор ставки дисконтирования обуславливается:
- стоимостью привлекаемого капитала инвестора;
- прогнозной инфляцией;
- премией за риск проекта.
Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).
Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.
Пример расчёта инвестиционного проекта в 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;» —>
]]>
Анализ чувствительности инвестиционного проекта
Определение
В широком смысле, анализ чувствительности оценивает степень изменчивости выходного параметра к изменению одного из входных параметров при условии, что остальные входные параметры остаются неизменными. При анализе чувствительности инвестиционного проекта, как правило, оценивается воздействие изменения объема продаж, переменных затрат, постоянных затрат, ставки дисконтирования, ставки налога на прибыль и т.п. на его чистую приведенную стоимость (англ. Net Present Value, NPV). Помимо чистой приведенной стоимости проекта в качестве выходного параметра могут также использоваться внутренняя норма доходности (англ. Internal Rate of Return, IRR), дисконтированный срок окупаемости (англ. Discounted Payback Period) и т.п. Проведения этого вида анализа позволяет оценить устойчивость проекта к факторам риска.
Формула
При проведении анализа чувствительности инвестиционного проекта следует придерживаться следующего алгоритма.
- Рассчитывается значение выходного параметра (например, чистая приведенная стоимость) при базовом сценарии.
- Рассчитывается значение выходного параметра при изменении одного из входных параметров (остальные входные параметры остаются неизменными).
- Рассчитывается процентное изменение входного и выходного параметра относительно базового сценария.
- Рассчитывается степень чувствительности выходного параметра к изменению входного параметра путем деления процентного изменения выходного параметра на процентное изменение входного параметра.
В общем виде формулу оценки чувствительности можно записать следующим образом.
Такие расчеты проводятся для всех входных параметров. Это позволяет выделить факторы риска, которые представляют наибольшую уязвимость для инвестиционного проекта.
Пример расчета
Менеджмент компании «ХимТех ЛТД» проводит анализ чувствительности инвестиционного проекта, предполагающего запуск новой производственной линии. Реализация этого проекта предполагает осуществление первоначальной инвестиции в размере 500 000 у.е., посленалоговая стоимость привлекаемого капитала составляет 16%, а ставка налога на прибыль 30%. Основные показатели проекта для базового сценария представлены в таблице.
Амортизация оборудования включена в постоянные затраты и составляет 40 000 у.е. в год.
Проведем анализ чувствительности чистой приведенной стоимости инвестиционного проекта к изменению:
- постоянных затрат;
- объема продаж;
- цены единицы продукции;
- переменных затрат на единицу продукции.
Поэтапный расчет дисконтированного чистого денежного потока по годам приведен в таблице.
Рассчитаем чистую приведенную стоимость проекта для базового сценария.
NPV = -500 000 + 131 034 + 136 891 + 160 164 + 137 686 + 111 030 = 176 805 у.е.
Предположим, что постоянные затраты по годам окажутся выше на 5%. При условии неизменности остальных параметров проекта дисконтированный чистый денежный поток по годам будет выглядеть следующим образом.
NPV = -500 000 + 128 017 + 134 238 + 157 810 + 135 579 + 109 113 = 164 757 у.е.
Процентное изменение NPV = (164 757 — 176 805) ÷ 176 805 × 100% = -6,81%
Таким образом, чувствительность NPV к изменению постоянных затрат составит -1,362.
Чувствительность NPV = -6,81% ÷ 5% = -1,362
Это означает, что при увеличении постоянных затрат на 1% чистая приведенная стоимость проекта будет уменьшаться на 1,362%, и наоборот, при снижении постоянных затрат на 1% чистая приведенная стоимость проекта будет увеличиваться на 1,362%.
Предположим, что объем продаж по годам окажется выше на 5%, чем предполагает базовый сценарий. При условии неизменности остальных параметров проекта дисконтированный чистый денежный поток по годам будет выглядеть следующим образом.
NPV = -500 000 + 138 879 + 144 902 + 169 246 + 145 573 + 117 545 = 216 145 у.е.
Процентное изменение NPV = (216 145 — 176 805) ÷ 176 805 × 100% = 22,25%
Чувствительность NPV = 22,25% ÷ 5% = 4,450
Таким образом, при росте объема продаж на 1% NPV проекта будет увеличиваться на 4,450%, и наоборот, при снижении объема продаж на 1% NPV проекта будет снижаться на 4,450%.
Предположим, что цена единицы продукции по годам окажется выше на 5%, чем предполагает базовый сценарий. При условии неизменности остальных параметров проекта дисконтированный чистый денежный поток по годам будет выглядеть следующим образом.
NPV = -500 000 + 152 155 + 157 491 + 183 170 + 157 896 + 128 277 = 278 989 у.е.
Процентное изменение NPV = (278 989 — 176 805) ÷ 176 805 × 100% = 57,79%
Чувствительность NPV = 57,79% ÷ 5% = 11,558
При росте цены единицы продукции на 1% NPV проекта будет увеличиваться на 11,558%, и наоборот, при снижении цены единицы продукции на 1% NPV проекта будет снижаться на 11,558%.
Предположим, что переменные затраты на единицу продукции по годам окажутся выше на 5%, чем предполагает базовый сценарий. При условии неизменности остальных параметров проекта дисконтированный чистый денежный поток по годам будет выглядеть следующим образом.
NPV = -500 000 + 117 759 + 124 301 + 146 240 + 125 363 + 100 298 = 113 961 у.е.
Процентное изменение NPV = (113 961 — 176 805) ÷ 176 805 × 100% = -35,54%
Чувствительность NPV = -35,54% ÷ 5% = -7,109
При росте переменных затрат на единицу продукции на 1% NPV проекта будет уменьшаться на 7,109%, и наоборот, при снижении переменных затрат на единицу продукции на 1% NPV проекта будет увеличиваться на 7,109%.
Проведенный анализ чувствительности инвестиционного проекта показал, что его чистая приведенная стоимость наиболее чувствительна к изменению цены продукции и наименее чувствительна к изменению постоянных затрат.
График
Результаты анализа чувствительности инвестиционного проекта из приведенного выше примера можно визуализировать следующим образом.
На графике по оси X отложены процентные изменения входных параметров проекта, а по оси Y – соответствующее значение выходного параметра NPV, выраженное в у.е. Данный тип графика позволяет визуально оценить риски и определить критические точки проекта.