Как в Excel рассчитать IRR и NPV инвестиционного проекта

23301
автор и руководитель проекта Finalytics.PRO, бывший финансовый директор
Как рассчитать NPV и IRR, оценить эффективность инвестиционных проектов, рассчитать сумму аннуитета и проверить банк на честность – в этой статье.

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

NPV инвестиционного проекта (Net Present Value) или чистую приведенную стоимость

IRR инвестиционного проекта (Internal Rate of Return) или внутреннюю ставку доходности

Аннуитеты – равномерные платежи.

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

Оценка эффективности инвестиционных проектов с помощью NPV

Рассмотрим условный пример: есть проект, который ежегодно в течение 5 лет будет приносить 250 000 рублей. На его реализацию нужен 1 000 000 рублей. Ставка дисконтирования - 10%.
Оценка эффективности инвестиционных проектов с помощью NPV

Формула NPV следующая:

Формула npv

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

Другими словами, нам потребуется сделать в Excel следующее:

NPV инвестиционного проекта - чистая приведенная стоимость

Добавить порядковые номера лет: 0 – стартовый год, к нему приводятся потоки.

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

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

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

NPV инвестиционного проекта - чистая приведенная стоимость

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

Как рассчитать NPV

Чтобы провести расчет npv в excel, необязательно готовить такую таблицу. Достаточно воспользоваться формулой ЧПС в Excel. Где ЧПС - ставка дисконтирования; диапазон дисконтируемых значений. То есть, достаточно указать ячейку с процентом и с денежными потоками. Но при использовании этой формулы с непривычки финансисты часто допускают ошибку:

Как рассчитать NPV

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

чистая приведенная стоимость

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

Оценка эффективности инвестиционных проектов с помощью IRR

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

Как рассчитать  IRR

Если ставка дисконтирования < IRR, то проект стоит принять, если нет – отказаться. Сделать расчет IRR в excel очень просто: Подставляем в функцию ВСД итоговый денежный поток.

Как рассчитать  IRR

IRR оказался меньше ставки доходности. Проект невыгодный (тот же вывод, что и при NPV).

NPV и IRR по праву считаются главными экономическими критериями. Их используют и для инвестиционной оценки проектов, и для оценки стоимости существующего бизнеса. В том числе, показатель EVA (Economic Value Added) считается хорошим критерием, поскольку при правильном расчете он равен NPV.

NPV и IRR финансовые специалисты могут использовать в более прикладных делах. Например, в решении вопроса с банками о реальной кредитной ставке.

Аннуитеты

При выдаче кредита банки рассчитывают сумму аннуитета или, другими словами, равномерного платежа. Чтобы спланировать выплаты по кредиту важно понимать, как проводится расчет аннуитета.

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

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

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

Аннуитеты

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

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

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

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

формула расчета аннуитета

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

Хитрые тонкости в расчете NPV и IRR или как проверять банки на честность

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

NPV при этом должно принять нулевое значение, а IRR - показать нам реальную процентную ставку.

расчет NPV и IRR

Когда кредит и платежи по нему рассчитаны правильно, то NPV, взятый по той же процентной ставке, равен нулю. А IRR показывает ту самую ставку. Когда банк делает нам предложение, от которого невозможно отказаться, и которое увеличит кредитную ставку «всего» на сколько-то процентов – не верьте и пересчитывайте. Приведу пример.

Банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%?

Нет. Дело в том, что настоящий кредит в начале каждого года уменьшается:

страховка от суммы кредита

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

NPY и IRR

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

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

Другие блоги автора:



Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Живое общение с редакцией

А еще...


Опрос

Финансовый директор это мужская профессия или женская?

  • Конечно, мужская 16.77%
  • Женская, разумеется 2.48%
  • Пол не имеет значения, был бы специалист хороший 80.75%
результаты

Рассылка





© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
  • Смотреть вебинары. Регистрация на ближайший уже открыта, приходите (раздел «Вебинары»)
  • Прочитать книгу «Я – финансовый директор. Секреты профессии» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
×

Доставка новостей

Подпишитесь на рассылку и получайте решения, которые пригодятся Вам в работе. Бесплатно.