Как сделать инвестиционный проект в эксель
В планировании деятельности компании часто возникает задача оценки эффективности от долгосрочных (более 2 лет) инвестиций. Необходимо ответить на ряд вопросов: окупятся ли инвестиции вообще, если да — то насколько быстро, какова эффективность инвестиционного проекта по сравнению с другими управленческими решениями.
Показатели инвестиционного проекта
Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:
- срок окупаемости проекта (обычно в месяцах)
- чистая приведённая стоимость (net present value, NPV)
- внутренняя норма доходности (IRR).
Срок окупаемости проекта — промежуток времени, который показывает, как долго будут возмещаться вложения в проект с учетом оплаты всех сопутствующих операционных затрат. Чем меньше этот срок, тем выше привлекательность проекта для инвестора.
Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени. Дисконтирование производится путём умножения значений будущих потоков на понижающий коэффициент:
Кд = 1 / (1 + Ставка дисконтирования)^Номер периода
Ставка дисконтирования – это процентная ставка, используемая для перерасчета будущих потоков доходов в единую величину текущей стоимости. Выбор ставки дисконтирования обуславливается:
- стоимостью привлекаемого капитала инвестора;
- прогнозной инфляцией;
- премией за риск проекта.
Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).
Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.
Пример расчёта инвестиционного проекта в Excel
Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.
Прогнозирование денежного потока в Excel
Заполните таблицу «Денежные потоки»:
Теперь рассчитаны денежные потоки, в том числе нарастающим итогом.
Срок окупаемости в Excel: пример расчёта
Дисконтирование в 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 должна кроме расчёта финансовых потоков и показателей содержать анализ чувствительности проекта: модель должна давать ответ на вопросы, как изменятся показатели в зависимости от изменения входных данных (курсы валют, спрос на рынке, себестоимость сырья и т.п.). Это тема для отдельной статьи.
Для привлечения и вложения средств в какое-либо дело инвестору необходимо тщательно изучить внешний и внутренний рынок.
На основании полученных данных составить смету проекта, инвестиционный план, спрогнозировать выручку, сформировать отчет о движении денежных средств. Наиболее полно всю нужную информацию можно представить в виде финансовой модели.
Финансовая модель инвестиционного проекта в Excel
Составляется на прогнозируемый период окупаемости.
- описание макроэкономического окружения (темпы инфляции, проценты по налогам и сборам, требуемая норма доходности);
- прогнозируемый объем продаж;
- прогнозируемые затраты на привлечение и обучение персонала, аренду площадей, закупку сырья и материалов и т.п.;
- анализ оборотного капитала, активов и основных средств;
- источники финансирования;
- анализ рисков;
- прогнозные отчеты (окупаемость, ликвидность, платежеспособность, финансовая устойчивость и т.д.).
Чтобы проект вызывал доверие, все данные должны быть подтверждены. Если у предприятия несколько статей доходов, то прогноз составляется отдельно по каждой.
Финансовая модель – это план снижения рисков при инвестировании. Детализация и реалистичность – обязательные условия. При составлении проекта в программе Microsoft Excel соблюдают правила:
- исходные данные, расчеты и результаты находятся на разных листах;
- структура расчетов логичная и «прозрачная» (никаких скрытых формул, ячеек, цикличных ссылок, ограниченное количество имен массивов);
- столбцы соответствуют друг другу;
- в одной строке – однотипные формулы.
Расчет экономической эффективности инвестиционного проекта в Excel
Для оценки эффективности инвестиций применяются две группы методов:
- статистические (PP, ARR);
- динамические (NPV, IRR, PI, DPP).
Коэффициент PP (период окупаемости) показывает временной отрезок, за который окупятся первоначальные вложения в проект (когда вернутся инвестированные деньги).
Экономическая формула расчета срока окупаемости:
где IC – первоначальные вложения инвестора (все издержки),
CF – денежный поток, или чистая прибыль (за определенный период).
Расчет окупаемости инвестиционного проекта в Excel:
- Составим таблицу с исходными данными. Стоимость первоначальных инвестиций – 160000 рублей. Ежемесячно поступает 56000 рублей. Для расчета денежного потока нарастающим итогом была использована формула: =C4+$C$2.
- Рассчитаем срок окупаемости инвестированных средств. Использовали формулу: =B4/C2 (сумма первоначальных инвестиций / сумма ежемесячных поступлений).
Так как у нас дискретный период, то срок окупаемости составит 3 месяца.
Данная формула позволяет быстро найти показатель срока окупаемости проекта. Но использовать ее крайне сложно, т.к. ежемесячные денежные поступления в реальной жизни редко являются равными суммами. Более того, не учитывается инфляция. Поэтому показатель применяется вкупе с другими критериями оценки эффективности.
Рентабельность инвестиций
ARR, ROI – коэффициенты рентабельности, показывающие прибыльность проекта без учета дисконтирования.
где CFср. – средний показатель чистой прибыли за определенный период;
IC – первоначальные вложения инвестора.
Пример расчета в Excel:
- Изменим входные данные. Первоначальные вложения в размере 160 000 рублей вносятся только один раз, на старте проекта. Ежемесячные платежи – разные суммы.
- Рассчитаем средние поступления по месяцам и найдем рентабельность проекта. Используем формулу: =СРЗНАЧ(C23:C32)/B23. Формат ячейки с результатом процентный.
Чем выше коэффициент рентабельности, тем привлекательнее проект. Главный недостаток данной формулы – сложно спрогнозировать будущие поступления. Поэтому показатель часто применяется для анализа существующего предприятия.
Примеры инвестиционне6ого проекта с расчетами в Excel:
Статистические методы не учитывают дисконтирование. Зато позволяют быстро и просто найти необходимые показатели.
IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.
Экономический смысл показателя
Другие наименования: внутренняя норма рентабельности (прибыли, дисконта), внутренний коэффициент окупаемости (эффективности), внутренняя норма.
Коэффициент IRR показывает минимальный уровень доходности инвестиционного проекта. По-другому: это процентная ставка, при которой чистый дисконтированный доход равен нулю.
Формула для расчета показателя вручную:
- CFt – денежный поток за определенный промежуток времени t;
- IC – вложения в проект на этапе вступления (запуска);
- t – временной период.
На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:
- ВНД выше – следует внимательно рассмотреть данный проект.
- ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
- Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).
Часто IRR сравнивают в процентами по банковскому депозиту. Если проценты по вкладу выше, то лучше поискать другой инвестиционный проект.
Пример расчета IRR в Excel
Быстро рассчитать IRR можно с помощью встроенной функции ВСД. Синтаксис:
- диапазон значений – ссылка на ячейки с числовыми аргументами, для которых нужно посчитать внутреннюю ставку доходности (хотя бы один денежный поток должен иметь отрицательное значение);
- предположение – величина, которая предположительно близка к значению ВСД (аргумент необязательный; но если функция выдает ошибку, аргумент нужно задать).
Возьмем условные цифры:
Первоначальные затраты составили 150 000, поэтому это числовое значение вошло в таблицу со знаком «минус». Теперь найдем IRR. Формула расчета в Excel:
Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%. Для дальнейшего анализа значение сравнивается с процентной ставкой банковского вклада, или стоимостью капитала данного проекта, или ВНД другого инвестиционного проекта.
Мы рассчитали ВНД для регулярных поступлений денежных средств. При несистематических поступлениях использовать функцию ВСД невозможно, т.к. ставка дисконтирования для каждого денежного потока будет меняться. Решим задачу с помощью функции ЧИСТВНДОХ.
Модифицируем таблицу с исходными данными для примера:
Обязательные аргументы функции ЧИСТВНДОХ:
- значения – денежные потоки;
- даты – массив дат в соответствующем формате.
Формула расчета IRR для несистематических платежей:
Существенный недостаток двух предыдущих функций – нереалистичное предположение о ставке реинвестирования. Для корректного учета предположения о реинвестировании рекомендуется использовать функцию МВСД.
- значения – платежи;
- ставка финансирования – проценты, выплачиваемые за средства в обороте;
- ставка реинвестирования.
Предположим, что норма дисконта – 10%. Имеется возможность реинвестирования получаемых доходов по ставке 7% годовых. Рассчитаем модифицированную внутреннюю норму доходности:
Полученная норма прибыли в три раза меньше предыдущего результата. И ниже ставки финансирования. Поэтому прибыльность данного проекта сомнительна.
Графический метод расчета IRR в Excel
Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости (NPV) от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков.
Для примера возьмем проект со следующей структурой денежных потоков:
Для расчета NPV в Excel можно использовать функцию ЧПС:
Так как первый денежный поток происходил в нулевом периоде, то в массив значений он не должен войти. Первоначальную инвестицию нужно прибавить к значению, рассчитанному функцией ЧПС.
Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% (0,10). При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей. В частности, от стоимости капитала (ставки дисконта).
Рассчитаем NPV для разных ставок дисконтирования:
Посмотрим результаты на графике:
Напомним, что IRR – это ставка дисконтирования, при которой NPV анализируемого проекта равняется нулю. Следовательно, точка пересечения графика NPV с осью абсцисс и есть внутренняя доходность предприятия.
На предыдущей странице мы может быть и не в полной мере подробно познакомили Вас с такими понятиями как инвестиционный анализ, дисконтирование, приведенная стоимость и т.д. Но о применении в реальной практике этих знаний мы еще не раз поговорим на страницах нашего сайта, особенно в части такого важнейшего понятия как рыночная оценка стоимости чистых активов, а также ответа на самый распространенный вопрос собственников предприятий: «Где деньги?»
При этом мы выложили там же на предыдущей странице весьма схематичную финансовую модель для расчетов основных показателей эффективности инвестиционных проектов, которая представляет собой, наверное, больше некий инвестиционный калькулятор для того, чтобы «поиграться», чем полноценную финансовую модель инвестиционного проекта. Этот недочет мы устраним здесь, предоставив читателю возможность погрузиться в методологию инвестиционного проектирования.
Как обычно, наш экскурс сопровождается примером реально действующей инвестиционной модели, которую сразу в начале раздела выкладываем для скачивания в виде следующего EXCEL-файла:
СКАЧАТЬ
Финмодель инвестпроекта, версия 2.0
Здесь в качестве примера и для лучшего понимания финмодель выложена с заполненными данными, а незаполненный экземпляр этого же EXCEL-файла Вы, как обычно, найдете в конце настоящей страницы.
Опишем сначала кратко основные характеристики, возможности и недостатки представленной инвестмодели. Понимание недостатков позволит нам определиться с направлением доработок при создании очередной версии модели.
Горизонт моделирования увеличен в два раза до двадцати лет.
Капитальные затраты теперь ранжируются на четыре типа, а именно на покупку земли, строительные работы, покупку и ввод в эксплуатацию оборудования, покупку транспорта. Для каждого типа, а всего их в финмодели 30-ть с учетом строительства вышеупомянутых трех цехов, можно задавать свой период амортизации. Недостатком является тот факт, что не учитывается специфика каждого из указанных типов капзатрат, а также мы пока что не стали рассматривать арендные или лизинговые операции. В версии 3.0 планируется устранить все эти недостатки.
Капитальные затраты (например, график строительных работ и ввод объектов в эксплуатацию) и их оплата теперь отделены друг от друга, что соответствует реальной практике. Для каждого объекта капитальных затрат пользователь может выбрать одну из трех предложенных схем оплаты:
- «один-в-один с работами», т.е. когда график оплат в точности совпадает с графиком затрат;
- «аванс плюс оплата», т.е. когда в начале проекта оплачивается заданный процент предоплаты, после чего по факту завершения каждого этапа графика капитальных затрат оплачивается остаток, соответствующий доле затрат текущего этапа;
- «вручную, доли%», т.е. задание произвольного графика оплаты.
Важно отметить, что в версии 2.0 инвестиционной модели мы исключили возможность автоматического масштабирования производственных модулей при росте плана продаж в единицах готовой продукции. Теперь для каждого цеха задается объем максимальной выработки в месяц и соответственно итоговый план продаж автоматически корректируется.
Но при этом добавлена возможность задания вручную года запуска строительства каждого из трех цехов, либо вообще можно исключать строительство того или иного цеха, тогда и продукция такого цеха вместе с расходами будут исключены из учета финмодели.
Под формирование себестоимости готовой продукции в EXCEL-файле с моделью выделен отдельный лист, где для каждого цеха можно задать до 30-ти общих ингредиентов и от 5-ти до 10-ти отдельных специфических себестоимостных составляющих на каждую отдельную номенклатурную позицию готовой продукции. Сформированная таким образом себестоимость, выраженная в деньгах, для каждого наименования производимой продукции считается, как себестоимость на начало периода инвестиционного проекта, после чего пользователь может в разрезе каждого цеха задать уровни инфляции, согласно которым финмодель пересчитывает себестоимость продукции в будущих периодах.
Классические инвестиционные модели предусматривают такую логику формирования рентабельности продаж, при которой сначала рассчитывается себестоимость производства продукции, после чего задается, возможно в результате предварительного маркетингового исследования, «средний чек» продаж. Сравнивая маркетинговое виденье ценовых уровней продаж и уровней их себестоимости предполагается, что пользователь, получая таким образом расчет рентабельности, может принимать решение о целесообразности вообще такого производства.
Мы же закладываем здесь несколько иной подход к формированию бюджета продаж. Ожидайте, скоро продолжим
Рассмотрим такой инвестиционный показатель как срок окупаемости инвестиций, его модификации, примеры и формулы расчета.
Срок окупаемости инвестиций (англ. PP, payback period) – это минимальный период времени возврата вложенных средств в инвестиционный проект, бизнес или любую другую инвестицию. Срок окупаемости является ключевым показателем оценки инвестиционной привлекательности бизнес плана, проекта и любого другого объекта инвестирования. Рассмотрим различные показатели срока окупаемости используемые на практике:
Инфографика: Срок окупаемости инвестиций (PP, DPP, BO DPP)
Данный показатель позволяет сравнивать между собой различные проекты по степени их эффективности возврата капитала.
Оценка стоимости бизнеса | Финансовый анализ по МСФО | Финансовый анализ по РСБУ |
Расчет NPV, IRR в Excel | Оценка акций и облигаций |
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.
Для расчета денежного потока необходимо воспользоваться следующими формулами:
где:
А (Amortization) – амортизация, вид денежного потока, который не является затратами;
NP (Net Profit) – чистая прибыль инвестиционного проекта.
Cрок окупаемости инвестиций (PP). Пример расчета в Excel
Рассчитаем срок окупаемости инвестиций в проект с помощью программы Excel. Для этого необходимо определить первоначальные затраты, которые в нашем примере составили 100000 руб., далее необходимо спрогнозировать будущие денежные поступления (CF) и определить с какого периода сумма денежного потока превысит первоначальные инвестиционные затраты. На рисунке ниже показан расчет срока окупаемости проекта. Формула расчета денежного потока нарастающим итогом следующая:
Денежный поток нарастающим итогом (CF) =C6+D5
Пример расчета срока окупаемости инвестиций в Excel
На пятом месяце сумма денежных поступлений окупит первоначальные затраты, поэтому срок окупаемости составит 5 месяцев.
Основные недостатки использования данного показателя в оценке инвестиций заключаются:
- Отсутствие дисконтирования денежных потоков бизнес проекта.
- Не рассматриваются денежные поступления за пределами срока окупаемости.
Дисконтированный срок окупаемости (англ. DPP, Discounted Payback Period) – период возврата денежных средств с учетом временной стоимости денег (ставки дисконта). Главное отличие от простой формулы срока окупаемости – это дисконтирования денежных потоков и приведение будущих денежных поступлений к текущему времени.
DPP (Discounted Payback Period) – дисконтированный срок окупаемости инвестиций;
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
CF (Cash Flow) – денежный поток, создаваемый инвестицией;
r – ставка дисконтирования;
n – срок реализации проекта.
Расчета дисконтированного срока окупаемости инвестиций в Excel
Рассмотрим пример оценки дисконтированного срока окупаемости инвестиций для бизнес-плана. Первоначальные инвестиции составили 100000 руб., денежный поток изменялся ежемесячно и отражен в столбце «С». Ставка дисконтирования была взята равной 10%. Для расчета дисконтированного денежного потока воспользуемся следующей формулой:
Дисконтированный денежный поток =C7/(1+$C$3)^A7
Денежные поступления нарастающим итогом =E7+D8
Пример расчета дисконтированного срока окупаемости инвестиции в Excel
Проект окупится на 5 месяц, в котором денежные поступления составят 100860 руб.
Мастер-класс: «Как рассчитать срок окупаемости для бизнес плана: инструкция»
Срок окупаемости с учетом ликвидационной стоимости (англ. Bail-Out Payback Period) – представляет собой период возврата денежных средств с учетом остаточной стоимости активов, созданных в инвестиционном проекте. При осуществлении инвестиционного проекта могут создаваться активы, которые могут быть проданы (ликвидированы) в результате этого срок окупаемости проекта существенно сокращается.
где:
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
RV (Residual Value)– ликвидационная стоимость активов проекта;
CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.
Ликвидационная стоимость может, как увеличиться в результате создания новых активов, так и уменьшаться за счет износа.
Расчет срока окупаемости инвестиции с учетом ликвидационной стоимости в Excel
На рисунке ниже показан расчет периода окупаемости проекта с учетом ликвидационной стоимости. Формула в Excel достаточно простая и имеет вид:
Денежные поступления с ликвидационной стоимостью =C6+E5+D6
Пример оценки срока окупаемости с учетом ликвидационной стоимости в Excel
В итоге, срок окупаемости с учетом ликвидационной стоимости составит ~4 лет. Данный способ оценки целесообразно применять при высокой ликвидности создаваемых активов. Как можно заметить, в данном варианте расчета срока окупаемости тоже может быть использована ставка дисконтирования.
Кроме срока окупаемости инвестиций существуют другие показатели оценки эффективности, позволяющие более точно провести анализ проекта. Более подробно о них вы можете узнать в статье: «6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI«.
Резюме
Срок окупаемости является важнейшим показателем инвестиционного анализа проектов и бизнеса. Он позволяет определить целесообразность вложения в тот или иной проект. Использование дисконтирования денежных потоков и ликвидационной стоимости активов позволяет инвестору более точно оценить период возврата капитала. Помимо данного коэффициента необходим анализ через другие показатели эффективности: чистой приведенной стоимости (NPV), внутренней нормы доходности (IRR) индекса прибыльности (PI). Кроме точечной оценки необходим анализ динамики денежных потоков и их равномерность.
Читайте также: