Как рассчитать показатели NPV и IRR в Excel? Финансовый анализ инвестиционного портфеля — Блог SF Education

Posted on by admin

Как рассчитать показатели NPV и IRR в Excel? Финансовый анализ инвестиционного портфеля

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

  • ЧДД или чистый дисконтированный доход от инвестиционного проекта;
  • Внутренняя норма доходности.

Рассмотрим эти два показателя подробнее и рассчитаем пример работы с ними в Excel.

Что такое NPV

Net Present Value (NPV, чистый дисконтированный доход) – один из самых распространенных показателей эффективности инвестиционного проекта.

Это разность между дисконтированными по времени поступлениями от проекта и инвестиционными затратами на него.

Метод расчета NPV:

  1. Определяем текущую стоимость затрат (инвестиции в проект)
  2. Производим расчет текущей стоимости денежных поступлений от проекта, для этого доходы за каждый отчетный период приводятся к текущей дате

  • CF – денежный поток;
  • r – ставка дисконта.

3. Сравниваем текущую стоимость инвестиций (наши затраты) в проект (Io) с текущей стоимостью доходов (PV). Разница между ними будет чистый дисконтированный доход – NPV.

NPV=PV-Io (1)

NPV показывает инвестору доход или убыток от вложений средств в проект по сравнению с доходом от хранения денег в банке.

Если NPV больше 0, то инвестиции принесут больше дохода, нежели чем аналогичный вклад в банке.

Формула 1 модифицируется если инвестиционные вложения в проект осуществляются в несколько этапов (периодов).

CF – денежный поток;
I — сумма инвестиционных вложений в проект в t-ом периоде;
r — ставка дисконтирования;
n — количество периодов.

Internal Rate of Return (Внутренняя норма доходности, IRR) определяет ставку дисконтирования при которой инвестиции равны 0 (NPV=0), или другими словами затраты на проект равны его доходам.

IRR = r, при которой NPV = f(r) = 0, находим из формулы:

  • CF – денежный поток;
  • I — сумма инвестиционных вложений в проект в t-ом периоде;
  • n — количество периодов.

Этот показатель показывает норму доходности или возможные затраты при вложении денежных средств в проект (в процентах).

Пример расчета NPV в Excel

В MS Excel 2010 для расчета NPV используется функция =ЧПС().

Найдем чистый дисконтированный доход (NPV) проекта, требующего вложений инвестиций на 90 тыс. руб., и денежный поток которого распределен по времени рис 1. , и ставка дисконта равна 10%.

Рассчитаем показатель NPV по формуле Excel:

=ЧПС(D3;C3;C4:C11)

D3 – ставка дисконта;
C3 – вложения в 0 периоде (наши инвестиционные затраты в проект);
C4:C11 – денежный поток проекта за 8 периодов.

В итоге показатель чистого дисконтированного дохода равен 51,07 >0, это говорит о том, что в проект стоит инвестировать.

Расчет IRR в Excel

Для определения IRR в Excel используется встроенная функция

=ЧИСТВНДОХ()

Но так как у нас в примере данные поступали в равные интервалы времени можно использовать функцию =ВСД(C3:C11)

Доходность вложения в проект равна 38%.

В завершение картинка финансового анализа проекта целиком.

Узнай о других возможностях Excel на курсе «Excel Academy» от SF Education!

]]>
Новая версия Excel-таблицы
«Расчет инвестиционных проектов» Расчет всех показателей
Анализ чувствительности
Сравнение инвестиционных проектов

версия 7.1 от 22 мая 2019

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

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

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

  1. Cтавку дисконтирования методом WACC
  2. Все показатели инвестиционного проекта: NPV, IRR, ROI, PI, Срок окупаемости инвестиций, Дисконтированный срок окупаемости
  3. Новое: Min и Max границы объема инвестиций. Если планируемый объем инвестиций выходит за эти границы — скорее всего допущена ошибка при расчете потребности в инвестициях.
  4. Чувствительность к отклонениям в статьях доходов и расходов
  5. Запас прочности по статьям доходов и расходов. Т.е. на сколько процентов должна увеличиться или уменьшиться статья чтобы проект перестал приносить доход (NPV = 0)

Иными словами, в пунктах 3 и 4 таблица анализирует риски инвестиционного проекта, насколько он устойчив к изменению условий.

Сравнивает до 7 инвестиционных проектов

  1. Качественно (визуально)
  2. Количественно
Строит графики
  1. Доли инвестиций
  2. Стоимость и взвешенная стоимость инвестиций
  3. Доход, расход, чистый доход и чистый дисконтированный доход по шагам инвестиционного проекта
  4. Чистый доход и чистый дисконтированный доход нарастающим итогом. Переход этих двух графиков через 0 соответствует сроку окупаемости и дисконтированному сроку окупаемости.
  5. Сводные графики для сравнения показателей окупаемости 7 проектов.

Если проигрывает с остановками — нажмите на паузу и дайте файлу загрузиться.

Ключевой момент при разработке бизнес-плана — расчет показателей эффективности инвестиционного проекта. Это трудоемкое занятие, требующее большого объема сложных вычислений. Более того, один из главных показателей — внутренюю норму доходности IRR нельзя рассчитать по формуле, т.к. ее просто нет. Для вычисления IRR используют метод последовательных приближений — многократно пересчитывают чистый дисконтированный доход с разными ставками дисконтирования.

Но, расчет показателей доходности — только первый шаг.

Дело в том, что невозможно точно предсказать доходы и расходы инвестиционного проекта. Чтобы оценить как изменится доходность надо сделать расчеты для нескольких вариантов денежных потоков, а еще лучше — определить чувствительность к изменениям в доходах и расходах. На всякий случай скажу, что чувствительность будет разная у разных показателей и разная по отношению к разным статьям. Таким образом, приходится повторять расчеты много раз, пересчитывая все показатели заново.

Другой способ решения проблемы неопреденности будущих доходов и расходов — определить запасы прочности базового варианта инвестпроекта по различным статьям. Другими словами, на сколько процентов должна измениться статья доходов или расходов, чтобы проект перестал приносить доход (NPV = 0). Но и этот способ требует огромного количества расчетов, т.к. запас прочности будет разным для разных статей доходов/расходов.

Задача еще больше усложняется, если возможны варианты в ходе выполнения проекта или надо выбрать один инвестиционный проект из нескольких. Обычно рекомендации по выбору формулируются следующим образом: «При прочих равных условиях предпочтительнее инвестпроект у которого …». На самом деле «прочие равные условия» встречаются довольно редко и приходится сравнивать по многим параметрам, а это сложно.

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

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

Я предлагаю Вам мощную Excel-таблицу для расчета всех показателей инвестиционного проекта, анализа устойчивости и сравнения нескольких инвестиционных проектов. Люди которые пользовались таблицей, пишут что она удобная и наглядная. Это уже седьмой вариант, включающий в себя множество полезных возможностей, добавленных по просьбам пользователей таблицы.

Новое в этой версии:

Таблица работает под Mac и Windows.

Новое в предыдущих версиях:

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

Поэтому в таблицу добавлен расчет границ (min и max объема инвестиций), выход за которые указывает на явную ошибку. Расчет выполняется по потокам платежей на шагах инвестиционного проекта.

Подробные пояснения включены в таблицу, в том числе в демо-версию.

В таблицу можно вводить данные семи инвестиционных проектов одновременно, для каждого предназначен отдельный лист (листы «Проект 1», … «Проект 7»).

Для каждого проекта можно выполнять анализ устойчивости, результаты выводятся на листы «Анализ 1», … «Анализ 7» соответственно.

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

Чтобы решить эту задачу таблица выполняет количественное сравнение, вычисляя для каждого проекта обобщающий числовой показатель (суммарный вес). Лучшим будет инвестиционный проект с максимальным суммарным весом.

Как работает таблица «Расчет инвестиционных проектов»

Вы вводите

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

Используя введенные данные таблица делает следующее:

Рассчитывает все показатели инвестиционного проекта

  1. Ставку дистонтирования по методике WACC — средневзвешенной стоимости капитала
  2. Чистый доход
  3. Чистый дисконтированный доход (NPV)
  4. Внутреннюю норму доходности (IRR)
  5. Индекс прибыльности (PI)
  6. Коэффициент окупаемости инвестиций (ROI)
  7. Срок окупаемости (PBP или PP)
  8. Дисконтированный срок окупаемости (DPBP или DPP)

Выполняет анализ устойчивости инвестиционного проекта

  1. Рассчитывает Чувствительность показателей инвестпроекта к изменениям указанных статей доходов и расходов.
  2. Рассчитывает Запас прочности показателей чистый доход и чистый дисконтированный доход (NPV) по указанным статьям доходов и расходов.

Сравнивает до семи инвестиционных проектов

  1. Качественно (визуально) по всем показателям.
  2. Количественно по указанным показателям.

Если Вы хотите сделать расчет окупаемости инвестиционного проекта, исследовать его сильные и слабые стороны и при этом не утонуть в расчетах, а сосредоточиться на самом проекте — Вам поможет моя таблица «Расчет инвестиционных проектов».

Таблица строит графики, которые понятнее сухих чисел, графики можно скопировать и вставить в свой бизнес-план. Графики хорошо смотрятся и придают солидность вашему бизнес-плану (или диплому).

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Как говорится, лучше один раз увидеть — Посмотрите подробнее в Демо-версии

Посмотрите скриншоты (снимки экрана), ниже показаны все листы с расчетами и графиками. Таблица содержит еще 3 листа с подробными инструкциями. Чтобы посмотреть всю таблицу двигайте ползунки во фрейме (окошке) ниже.

Листы защищены от случайных изменений и порчи формул. Ячейки для ввода (не защищенные) помечены фоном . При желании Вы можете снять защиту, как это сделать написано на первом листе с инструкциями.

Вам будет интересно  Оператор платежного сервиса Alipay разместит акции на $17,5 млрд

Если нужно, вот подробное описание.

Подробное описание

  • Сумму инвестиций и стоимость привлеченных ресурсов в процентах годовых. Если инвестиции привлечены из разных источников с разной стоимостью, вводите каждую часть отдельно. Таблица рассчитывает общую сумму инвестиций и средневзвешенную стоимость капитала (WACC). Значение WACC рекомендуется взять в качестве ставки дисконтирования.
  • Можно рассчитывать окупаемость инвестиций с переменной ставкой дисконтирования. Для длительных инвестиционных проектов ставка дисконтирования может увеличиваться с течением времени, отражая увеличение неопределенности и рисков.
  • Шаг инвестиционного проекта. Это период, по итогам которого осуществляется промежуточное подведение итогов произведенных затрат и полученных доходов. Длина шага может быть любой. Число шагов проекта неограничено.
  • Суммы доходов и расходов по статьям на каждом шаге инвестпроекта. В исходном состоянии таблица содержит 15 статей (столбцов) доходов и 15 статей (столбцов) расходов. Если этого мало, можно вставить нужное количество столбцов для новых статей доходов и расходов.
  • Отклонения от базового варианта в статьях для расчета чувствительности проекта. Можно задать отклонения для первых 10 статей доходов и первых 10 статей расходов. На всякий случай поясню: Вы задаете денежные потоки (доходы и расходы) только для одного базового варианта. Потом для нужных статей задаете отклонение от базового варианта в процентах. Сами денежные потоки с учетом заданного отклонения пересчитывает таблица и соответственно пересчитывает показатели проекта.

Для всего инвестиционного проекта таблица рассчитывает:

  1. Ставку дистонтирования по методике WACC — средневзвешенной стоимости капитала
  2. Чистый доход — сумму всех доходов и расходов
  3. NPV — Чистый дисконтированный доход
  4. IRR — Внутреннюю норму доходности в годовом исчислении
  5. IRR — Внутреннюю норму доходности в пересчете на шаг
  6. PI — Индекс прибыльности
  7. ROI — Коэффициент окупаемости инвестиций
  8. PBP — Срок окупаемости в годах
  9. PBP — Срок окупаемости в шагах.
  10. DPBP — Дисконтированнный срок окупаемости в годах
  11. DPBP — Дисконтированнный срок окупаемости в шагах
  12. Чувствительность показателей к изменениям статей доходов/расходов
  13. Запас прочности чистого дохода и чистого дисконтированного дохода (NPV) по статьям доходов и расходов

На каждом шаге проекта таблица рассчитывает:

  1. Доход суммарно по всем статьям
  2. Расход суммарно по всем статьям
  3. Чистый доход (доход минус расход)
  4. Дисконтированный чистый доход
  5. Чистый доход нарастающим итогом
  6. Дисконтированный чистый доход нарастающим итогом

Анализ устойчивости проекта

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

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

На приведенном рисунке зарплата увеличилась на 10%, нижняя граница инвестиций увеличилась на 2%, верхняя на 2.39%, чистый доход уменьшился на 10%, NPV уменьшился почти на 20%.

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

При ручном режиме можно сравнивать не только базовый вариант с текущим. Можно записать любой текущий вариант кнопкой Записать показатели текущего варианта и сравнивать с любым другим вариантом. Например, можно сравнивать самый благоприятный вариант не с базовым, а с самым неблагоприятным. При этом рекомендую записывать краткие пояснения к вариантам в ячейках Пояснения к варианту.

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

В автоматическом режиме расчеты показателей выполняются поочередно по тем статьям для которых заданы отклонения. Берется базовый вариант, изменяется одна статья и делается расчет показателей. Потом опять берется базовый вариант, изменяется другая статья, делается расчет показателей. И так далее. При этом вычисляются изменения показателей проекта, чувствительность и запас прочности по каждой статье. Расчет выполняется после нажатия на кнопку «Анализ», результаты печатаются на листах «Анализ 1», «Анализ 2» и т.д.

Пример расчета показан выше на сриншотах (образах экрана) с таблицей. Для простоты не учтены налоговые платежи.

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

Как получить таблицу

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

Поэтому таблица продается в нескольких вариантах.

  • Вариант Полный. Расчет показателей, анализ устойчивости и сравнение до семи проектов — 2990 руб.
  • Вариант Анализ. Расчет показателей и анализ устойчивости одного инвестпроекта — 1890 руб.
  • Вариант Окупаемость. Расчет показателей одного инвестиционного проекта — 1490 руб.

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

Олеся [22.05.2019 в 06:02]
Добрый день уважаемый Владимир! Спасибо Вам большое за таблицы по расчету инвест.проектов! Первое время конечно нужно немного разобраться, но в целом, таблица очень удобная для быстрого расчета окупаемости инвест.вложений и наглядного анализа. Очень удобно считать новые проекты.

Александр [Кемерово] [04.12.2017 в 10:40]
Здравствуйте Владимир.
При разработки бизнес-плана строительства крупного обьекта в Европе, использовали таблицу ОКУПАЕМОСТИ ИНВЕСТИЦИЙ. Бизнес план прошел контроль по ЮНИДО, таблица соответствовала всем международным требованиям. Спасибо.

Евгения [25.07.2015 в 19:39]
Большое спасибо за таблицу «Инвестиции. Вариант АНАЛИЗ». Очень помогла, удобна в обращение, намного быстрее считать, чем в ручную Спасибо, очень довольны

Михаил [23.06.2014 в 07:45]
Владимир, спасибо за таблицу! Расчет окупаемости инвестиций.Сначала было немного непонятно, но с Вашей помощью быстро разобрался, директор по инвестициям проверил- оценил скорость и правильность расчетов.

Ирина [Москва] [02.05.2012 в 12:19]
Спасибо Вам Владимир, Ваша таблица «Окупаемость инвестиций» очень выручила. За день надо было проверить цифры по строительству жилого многоквартирного дома. 1 мая 2012г. нашла сайт этот в интернете и решила сразу оплатить, рискнула и не ошибласть. К вечеру 1 мая получила таблицу и быстро все посчитала , очень благодарна автору. Сегодня приобрела «Объединенную таблицу». Спасибо Вам Владимир, так как, если отдавать на просчет специалистам, по времени долго и деньги другие. Очень рекомендую.

Гапликов Алексей [Санкт-Петербург] [16.04.2012 в 14:58]
www.doctor911.ru Большое спасибо за таблицу! Очень пригодилась!Спасибо за сэкономленное время. Использовали при составлении бизнес-плана по строительству коммерческого дома престарелых.

Вячеслав [18.02.2012 в 18:34]
Спасибо! Очень нужна была таблица окупаемости инвестиций для бизнес-плана. Получил в течение 2-3-х часов после оплаты. Все выполнено понятно и доступно, никаких трудностей для заполнения. Ожидания были полностью оправданы. До этого пытался сам написать программку, но выходило очень примитивно. Здесь охвачен широкий спектр параметров. Хорошая наглядность. Особенно понравились графики.

Лия [14.02.2012 в 20:42]
Спасибо огромное! Второй раз купили таблицу «Окупаемость инвестиций» (новую версию со скидкой — В.П.). Нарадоваться не могу. Очень сильно облегчает расчеты. Качество, оперативность, доходчивость выше всяких похвал.

Яна [30.08.2011 в 10:07]
Работаем с инвестиционной таблицей, проверили все данные в аналогичных программах, результатом остались довольны, спасибо)))))

  • Доставка в течение от нескольких минут до нескольких часов после прихода платежа. Сроки прохождения платежа для различных способов оплаты см. ниже.
  • Все таблицы заполнены данными, которые служат примером и помогают понять логику работы.
  • Таблицы снабжены подробными пояснениями по заполнению и работе.
  • Отвечаю на любые вопросы по работе с таблицами. Конечно, предполагается что вы знакомы с предметной областью и имеете базовые навыки работы с Excel.

При выходе новой версии:

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

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

Сравнение вариантов и цены

Таблица
«Расчет инвестиционных проектов»
версия 7.0 для Windows, 7.1 для MAC и Windows

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Вариант
«Окупаемость»

Рассчитывает для одного проекта:

Показатели окупаемости:

Вариант
«Анализ»

Рассчитывает для одного проекта:

Показатели окупаемости:

  1. Ставку дисконтирования методом WACC
  2. NPV
  3. IRR
  4. PI
  5. ROI
  6. PBP — Срок окупаемости
  7. DPBP — Дисконтированный срок окупаемости

Учет рисков и анализ устойчивости:

Вариант
«Полный»

Рассчитывает для семи проектов:

Показатели окупаемости:

  1. Ставку дисконтирования методом WACC
  2. NPV
  3. IRR
  4. PI
  5. ROI
  6. PBP — Срок окупаемости
  7. DPBP — Дисконтированный срок окупаемости

Учет рисков и анализ устойчивости:

  1. Отклонения показателей проектов от базовых значений при изменениях статей доходов и расходов
  2. Чувствительность показателей проектов к изменениям статей
  3. Запас прочности чистого дохода и NPV для каждого проекта

Сравнивает качественно и количественно семь инвестиционных проектов:

  1. Формирует сводную таблицу показателей
  2. Строит сводные графики сравнения показателей
  3. Позволяет выбрать критерии (показатели проектов) для сравнения
  4. Позволяет задать вес (важность) каждого критерия
  5. Вычисляет суммарные веса проектов с учетом весов критериев

Рекомендую для упрощенных формальных расчетов (дипломы, курсовые)

Рекомендую для расчета реальных проектов

Рекомендую для расчета реальных проектов при наличии вариантов

Windows 1490 руб.
Mac и Windows 1788 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Windows 1890 руб.
Mac и Windows 2268 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Windows 2990 руб.
Mac и Windows 3588 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)

Способы оплаты:

  1. Robokassa
    • Карты Visa, MasterCard, Maestro, Мир
    • Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
    • Терминалы
    • Салоны связи Евросеть, Связной
    • Электронные деньги Яндекс.Деньги, QIWI, WebMoney, Wallet One (W1), Элекснет
  2. Банк
    на расчетный счет Прохоров Владимир Викторович (ИП), ИНН 645400330452,
    р/с 40802810711010053426, Филиал «Бизнес» ПАО «Совкомбанк» г. Москва,
    к/с 30101810045250000058, БИК 044525058.

Обратите внимание, что указание формы собственнсти (ИП) является обязательным.
После оформления заказа можно распечатать счет для юр. лиц
или квитанцию по форме ПД-4 для физ. лиц.

Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).

В назначении платежа, пожалуйста, указывайте номер счета, например:
По счету № . За электронные таблицы «Расчет инвестиционных проектов». Без НДС.

При платежах не из России в начале назначения платежа укажите код валютной операции
Например, назначение платежа:
По счету № . За электронные таблицы «Расчет инвестиционных проектов». Без НДС.
Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.

Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Если письма долго нет, проверьте папку СПАМ.

Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты. Сервис Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег 1-2 рабочих дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.

Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.

Дальнейшая переписка по поводу полученных таблиц

Дальнейшая переписка по поводу полученных таблиц (вопросы по работе с таблицами, обновления и т.п.) происходит через адрес, на который были отправлены таблицы. При переписке по эл.почте сохраняйте, пожалуйста, историю переписки, т.е. включайте в письмо все предыдущие письма и ответы на них. При этом помещайте Ваше сообщение вверху (перед историей).

Оформить заказ
Windows
Вариант ОКУПАЕМОСТЬ

Оформить заказ
Mac и Windows
Вариант ОКУПАЕМОСТЬ

Оформить заказ
Windows
Вариант АНАЛИЗ

]]>
Оценка инвестиционных проектов в excel скачать

Инвестиционные показатели NPV, IRR: Excel на службе у финансового директора

Как рассчитать NPV и IRR, оценить эффективность инвестиционных проектов, рассчитать сумму аннуитета и проверить банк на честность. Финансовых формул в Excel много. Часть из них предназначена для расчета амортизации разными способами. Другие – для определения стоимости ценных бумаг. Третьи для чего-то еще. Здесь мы разберем самые главные и «животрепещущие» (на мой взгляд).

Это формулы, которые позволят рассчитать:
— NPV (Net Present Value) — чистую приведенную стоимость.
— IRR (Internal Rate of Return) — внутреннюю ставку доходности.
— Аннуитеты – равномерные платежи.

Также рассмотрим некоторые нюансы использования этих формул. Все расчеты можно найти в приложенном файле. Основной акцент сделан на функции Excel, поэтому саму финансовую математику разбирать особо не будем.


Оценка целесообразности проекта с помощью NPV

Есть проект, который ежегодно в течении 5 лет будет приносить 250 000 руб. Нужно потратить 1 000 000 руб. Предположим, что ставка дисконтирования равна 10%.

Оцениваем NPV проекта. Напомню формулу этого показателя:

Если денежные потоки, приведенные к текущему периоду, больше инвестированных денег (NPV > 0), то проект выгодный. В противном случае – нет. Другими словами, нам потребуется сделать в Excel следующее:

Добавить порядковые номера лет: 0 – стартовый год, к нему приводятся потоки. 1, 2, 3 и т.д. – это годы реализации проекта. В формуле на рисунке выполнены действия, которые прописаны выше после знака суммы (Σ): денежный поток за период делится на сумму 1 и ставки дисконтирования, возведенную в степень соответствующего года.

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

Получается «-52 303». Проект невыгоден.

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

ЧПС(Ставка дисконтирования; Диапазон дисконтируемых значений)

То есть достаточно указать ячейку с процентом и с денежными потоками. Но при использовании этой формулы с непривычки финансисты часто допускают ошибку:

Вообще-то дисконтированный поток и расчет по ЧПС должны совпадать. Почему же здесь разные значения? Дело в том, что ЧПС начинает дисконтировать с первого же значения. Т.е. она на самом деле ищет приведенную стоимость. А стартовые инвестиции нужно отнимать после. Правильная запись формулы в нашем случае будет иметь следующий вид:

Стартовые инвестиции «выведены» за пределы дисконтируемого диапазона и вычтены: т.к. стартовые инвестиции уже идут с минусом, то D8 нужно прибавлять. Теперь результаты одинаковые.


Оценка целесообразности проекта с помощью IRR

Как еще можно оценить проект? Можно посмотреть на него с точки зрения ставки дисконтирования. Задать вопрос: а какая должна быть ставка, чтобы NPV стала = 0? Вот этой ставкой как раз и является IRR. Если Ставка дисконтирования
Аннуитеты – любимая банковская цифра

Сначала поговорим о волнующем вопросе – как банки рассчитывают сумму равномерного платежа, как их проверить и как это понимать. Допустим, вы собираетесь взять кредит 1 000 000 руб. на 5 лет под 10% годовых. Платить будете раз в год равными платежами. Формулу из учебника по финансовому менеджменту здесь приводить не будем. Приведем формулу Excel:

ПЛТ(Ставка дисконтир; Количество периодов; Сумма кредита которую вы берете)

В формуле есть еще два необязательных пункта: сумма, которая должна остаться (по умолчанию ноль), и как высчитывать сумму – на начало месяца, и тогда ставят 1, или на конец – ставят ноль. В 90% случаев эти пункты не нужны, поэтому их можно не ставить вообще. Итого аннуитет определяется так:

Сумма ежегодного платежа получается сразу с минусом. Эту сумму нужно каждый год платить банку.

В ней содержатся две части: 1) платеж по кредиту, 2) тело кредита.

Ниже они показаны. Платеж по кредиту берется как 10% (процент по кредиту) от суммы задолженности на начало периода. Тело – как разность между ежегодным платежом и платежом по процентам (в Excel можно найти формулы, которые рассчитают вам и эти платежи). Задолженность на конец рассчитывается как разность между Задолженностью на начало и платежом по телу кредита.

Если платежи не ежегодные, а ежемесячные или ежеквартальные, то нужно ставку и период приводить к этим значениям. Так если бы у нас платеж был каждый месяц, формула выглядела бы так:

Мы бы годовую ставку разделили на 12 (привели к ежемесячному), и взяли не 5 периодов, а 5 • 12 = 60 месяцев. И получили ежемесячный платеж в 21 247 руб.


Нюансы и тонкости

А теперь обсудим, как проверять банки на честность. Любой поток платежей по кредиту подразумевает под собой, что все выбытия денег приведены к поступлениям на ставку кредитования. Теперь по-русски: если мы построим денежный поток из полученного нами кредита и последующих наших аннуитетных платежей, то затем мы можем посчитать по ним NPV и IRR. NPV при этом должно принять нулевое значение, а IRR, что интереснее, — показать нам реальную процентную ставку.

Когда кредит и платежи по нему рассчитаны правильно, то NPV, взятый по той же процентной ставке, равен нулю. А IRR показывает ставку. Когда банк делает предложение, от которого невозможно отказаться и которое увеличит кредитную ставку «всего» на несколько процентов – не верьте и пересчитывайте! Например, в нашем случае банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%? Нет! Дело в том, что настоящий кредит в начале каждого года уменьшается:

В результате видно, что NPV не равен нулю. А реальный процент не 10, а 12,9%! Обратите внимание: здесь же выросла сумма переплаты. Если вас это смутит, вам могут предложить «еще более выгодные условия» — заплатить переплату сейчас, а остальное потом, меньшими платежами, или в нашем примере просто заплатить больше, а потом меньше. Сумма переплаты не изменится, а вот процент…

Что здесь сделано? Из каждого последующего платежа взята сумма 43 797 руб. и добавлена к первому же платежу (а бывает выкручивают сумму в момент выдачи кредита). Если для реального сектора финансовая математика «деньги вчера – деньги завтра» кажется несколько отдаленной от жизни, для банков это реальная прибыль. Поэтому всеми силами нагружают первый платеж. А вы с помощью простых формул сможете подготовить основу для дальнейших переговоров.

Да, не забудьте, если речь идет про ежемесячные платежи, умножать на 12.

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;» —>

Анализ инвестиционного проекта в Excel скачать

Любая инвестиция нуждается в тщательных расчетах. Иначе инвестор рискует потерять вложенные средства.

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

Расчет инвестиционного проекта в Excel

Инвестор вкладывает деньги в готовое предприятие. Тогда ему необходимо оценить эффективность работы (доходность, надежность). Либо в новое дело – все расчеты проводятся на основе данных, полученных в ходе изучения рынка (инфраструктуры, доходов населения, уровня инфляции и т.д.).

Рассмотрим создание бизнеса с нуля. Рассчитаем прибыльность предприятия с помощью формул Excel. Для примера будем брать условные товары и цифры. Важно понять принцип, а подставить можно любые данные.

Итак, у нас есть идея открыть небольшой магазин. Определимся с затратами. Они бывают

  • постоянными (нельзя рассчитать на единицу товара);
  • переменными (можно рассчитать на единицу товара).

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

Составляем таблицу с постоянными затратами:

* Статьи расходов индивидуальны. Но принцип составления — понятен.

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

Для нахождения цены продажи использовали формулу: =B4*(1+C4/100).

Следующий этап – прогнозируем объем продаж, выручку и прибыль. Это самый ответственный этап при составлении инвестиционного проекта.

Объем продаж условный. В реальной жизни эти цифры – результат анализа доходов населения, востребованности товаров, уровня инфляции, сезона, места нахождения торговой точки и т.д.

Для подсчета выручки использовалась формула: =СУММПРОИЗВ(B3:B6;Лист2!$D$4:$D$7). Где первый массив – объемы продаж; второй массив – цены реализации.

Выручка минус переменные затраты: =B7-СУММПРОИЗВ(B3:B6;Лист2!$B$4:$B$7).

Прибыль до уплаты налогов: =B8-Лист1!$B$14 (выручка без переменных и постоянных затрат).

Налоги ЕНВД: =Лист1!A10*1800*0,15*3 (1800 – базовая доходность по виду деятельности, 3 – количество месяцев, С12 – площадь помещения).

Чистая прибыль: прибыль – налоги.

Оценка инвестиционного проекта в Excel

Рассчитывают 4 основных показателя:

  • чистый приведенный эффект (ЧПЭ, NPV);
  • индекс рентабельности инвестиций (ИРИ, PI);
  • внутреннюю норму доходности (ВНД, IRR);
  • дисконтированный срок окупаемости (ДСО, DPP).

Для примера возьмем следующий вариант инвестиций:

Сначала дисконтируем каждый положительный элемент денежного потока.

Создадим новый столбец. Введем формулу вида: = положительный элемент денежного потока / (1 + ставка дисконтирования)^ степень, равная периоду.

Теперь рассчитаем чистый приведенный эффект:

  1. С помощью функции СУММ.
  2. С помощью встроенной функции ЧПС.
    Чтобы получить чистый приведенный эффект, складываем результат функции с суммой инвестиций.
    Цифры совпали:

Найдем индекс рентабельности инвестиций. Для этого нужно разделить чистую приведенную стоимость (ЧПС) на объем инвестированных средств (со знаком «+»):

Посчитаем IRR инвестиционного проекта в Excel. Напомним формулу:

ВНД = ΣДПt/ (1 + ВНР) t = И.

ДПt– положительные элементы денежного потока, которые нужно продисконтировать по такой ставке, чтобы чистый приведенный эффект равнялся нулю. Внутренняя норма доходности – такая ставка дисконтирования, при которой выпадает равенство вида:

ΣДПt / (1 + ВНР) t – И = 0,

Воспользуемся инструментом «Анализ «Что-Если»»:

  1. Ставим курсор в ячейку со значением чистого приведенного эффекта. Выбираем «Данные»-«Анализ Что-Если»-«Подбор параметра».
  2. В открывшемся окне в строке «Значение» вводим 0 (чистый приведенный эффект должен равняться 0). В поле «Изменяя значение ячейки» ссылаемся на ставку дисконтирования. Нужно изменить ее так, чтобы соблюдалось приведенное выше равенство.
  3. Нажимаем ОК.

Ставка дисконтирования равняется 0,41. Следовательно, внутренняя норма доходности составила 41%.

Моделирование рисков инвестиционных проектов в Excel

Используем метод имитационного моделирования Монте-Карло. Задача – воспроизвести развитие бизнеса на основе результатов анализа известных элементов и взаимосвязей между ними.

Продемонстрируем моделирование рисков на простейшем примере. Составим условный шаблон с данными:

Ячейки, которые содержат формулы ниже подписаны своими значениями соответственно.

Прогнозируемые показатели – цена услуги и количество пользователей. Под этими данными делаем запись «Результаты имитации». На вкладке «Данные» нажимаем «Анализ данных» (если там нет инструмента придется подключить настройку). В открывшемся окне выбираем «Генерация случайных чисел».

Заполняем параметры следующим образом:

Нам нужно смоделировать ситуацию на основе распределений разного типа.

Для генерации количества пользователей воспользуемся функцией СЛУЧМЕЖДУ. Нижняя граница (при самом плохом варианте событий) – 1 пользователь. Верхняя граница (при самом хорошем варианте развития бизнеса) – 50 покупателей услуги.

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

Чтобы оценить риски, нужно сделать экономико-статистический анализ. Снова воспользуемся инструментом «Анализ данных». Выбираем «Описательная статистика».

Программа выдает результат (по столбцу «Коэффициент эффективности»):

Можно делать выводы и принимать окончательное решение.

Использование Excel для оценки эффективности проекта

Расчет чистого дисконтированного дохода NPV , также называемого ЧДД, несложен, но трудоемок, если считать его вручную.

Мы уже рассматривали пример расчета NPV и IRR по формулам. Там же были приведены ф ормулы всех перечисленных показателей и их расчеты ручным методом .

Теперь поговорим, как рассчитать ЧДД, ВНД (ИРР), срок окупаемости простой и дисконтированный без особых усилий с помощью таблиц Ms Excel . Итак, можно прописать формулы в таблице в экселе для расчета NPV. Что мы и сделаем.

Здесь вы можете бесплатно скачать таблицу Excel для расчета NPV, внутренней нормы доходности ( IRR), сроков окупаемости простого и дисконтированного. Мы приведем таблицу для расчета NPV за 25 лет или меньший срок, в таблицу только стоит вставить значения предполагаемого размера инвестиций, размер ставки дисконтирования и величину годовых денежных потоков. И NPV рассчитается автоматически.

Вот эта таблица . Пароль к файлу : goodstudents.ru

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

Теперь давайте поговорим, как воспользоваться данной таблицей для расчета ЧДД, ВНД, срока окупаемости . В ней уже приведен пример расчета NPV.

Если вам нужно рассчитать NPV за 5 лет. Вам известна ставка дисконтирования 30% (т.е. 0,3). Известны денежные потоки по годам:

Размер инвестиций 500 т.р.

В таблице экселя исправим значение ставки дисконтирования на 0,3 (2я строка сверху), исправим значение инвестиций (5я строка, 3й столбец) на 500.

Сотрем денежные потоки и их итог за 25 лет. (также сотрем строки чистых денежных потоков с 6го по 25й год и значение NPV для лишних лет). Вставим известные нам значения за 5 лет. Получим следующие данные.

Годы

Сумма инвестиций, тыс. руб

Денежные потоки, тыс. руб(CF)

Чистые денежные потоки, тыс. руб.

Чистый дисконтировнный доход, тыс. руб. (NPV)

Итого

500,00

1350,00

562,09

62,09

Как видите нам не пришлось считать NPV самостоятельно, таблица эксель посчитала данный показатель за нас.

Теперь давайте разберемся как посчитать IRR с помощью экселя на конкретном примере. В Ms Excel есть функция, которая называется «подбор параметра». В 2003 экселе эта функция расположена в сервис- > подбор параметра.

Мы уже говорили ранее, что IRR – это такая ставка дисконтирования, при которой NPV равен нулю.

Нажимаем в экселе сервис- > подбор параметра, открывается окошко,

Мы знаем, что ЧДД =0, выбираем значение ячейки с ЧДД за 5й год, присваиваем ему значение 0, изменяя значение ячейки, в которой расположена ставка дисконтирования. После расчета получим.

Итак, NPV равен нулю при ставке дисконтирования равной 35,02%. Т.е. ВНД внутренняя норма доходности ( IRR ) =35,02%.

Теперь рассчитаем значение срока окупаемости простого и дисконтированного с помощью данной таблицы Эксель.

Срок окупаемости простой:

Мы видим по таблице, что у нас инвестиции 500 т.р. За 2 года мы получим доход 300 т.р. За 3 года получим 600 т.р. Значит срок окупаемости простой будет более 2 и менее 3х лет.

В ячейке F32 (32 строка файла экселя) нажимаем F2 и исправляем, вместо «1+» у нас будет «2+», меняем 1 на 2, и преобразуем формулу следующим образом, вместо « =1+(-(D5-C5)/D6)» у нас будет «=2+(-((D5+D6)-C5)/D7)», другими словами, мы к 2м полным годам прибавили долг по инвестициям на конец второго года, деленный на денежный поток за третий год. Получим 2,66 года.

Срок окупаемости дисконтированный пример расчета:

NPV переходит с минуса на плюс с 4го на 5й год, значит срок окупаемости с учетом дисконтирования будет более 4х и менее 5 лет.

В ячейке F3 3 (33 строка файла экселя) нажимаем F2 и исправляем, вместо «2+» у нас будет «4+», меняем 2 на 4, и преобразуем формулу следующим образом, вместо «=2+(-F6/E7)» у нас будет «=4+(-F8/E9))», другими словами, мы к четырем полным годам прибавили отношение последнего отрицательного NPV к чистому денежному потоку в следующем году ( 4+-( -45,64 /107,73) .

Получим 4 , 42 года – срок окупаемости с учетом дисконта.

Поэтому если необходимо рассчитать показатели по формулам, то можно посмотреть примеры по ссылке приведенной выше, а здесь мы подробно остановились на расчетах ЧДД, дисконтированных денежных потоков, ВНД, сроков окупаемости с помощью таблиц Excel, что гораздо проще и эффективнее.

Данный пример предназначен для практических занятий. к.э.н., доцент Одинцова Е.В.

Вам будет интересно  Инвестиции в недвижимость в Краснодарском крае