Скидка на подписку 20% + 4 месяца бесплатно | 8(800)505-89-86
Журнал "Финансовый директор"

Расчет NPV и IRR в Excel

  • 22 августа 2018
  • 52057
автор и руководитель проекта Finalytics.PRO, бывший финансовый директор
Расчет NPV и IRR в Excel

Знаете, как рассчитать NPV Excel? А IRR? В статье разберем эти самые важные для финансиста формулы и разберем пример расчета NPV и IRR инвестиционного проекта.


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

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

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

Рассмотрим условный пример: есть проект, который ежегодно в течение 5 лет будет приносить 250 000 рублей. На его реализацию нужен 1 000 000 рублей. Ставка дисконтирования – 10%.
Если денежные потоки, приведенные к текущему периоду, больше инвестированных денег (NPV>0), то проект выгодный. В противном случае – нет.

Расчет NPV и IRR в Excel

Формула NPV выглядит так:

Формла рассчета NPV

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

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

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

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

ИконкаЧитайте также:

ИконкаКак не ошибиться при оценке проектов

Кому пригодится: допустим, ваша компания планирует инвестировать в перспективный на первый взгляд проект. У него хорошая чистая приведенная стоимость (NPV), неплохая внутренняя норма рентабельности (IRR). Эти показатели кажутся совершенно стандартными и общепринятыми. Но в их расчетах есть много тонкостей, которые влияют на итоговую цифру, а иногда и на принимаемое решение. Читайте статью, чтобы разобраться в особенностях оценки проекта, проанализировав его с применением разных методик.

ИконкаКонцепция реальных опционов: как оценить инвестпроект

Чем поможет: рассчитать стоимость инвестпроекта, оценить его ценность с учетом всех факторов, получить возможность в дальнейшем выгодно продать актив.

Расчет NPV с помощью формулы ЧПС

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

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

Расчет NPV и IRR в Excel

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

ИконкаЕще по теме:

ИконкаКак использовать показатель модифицированной внутренней нормы доходности при оценке инвестиционного проекта

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

ИконкаКак сопоставить эффективность двух разных инвестиционных проектов

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

Как оценить эффективность инвестиционного проекта в Excel с помощью IRR

IRR – это еще один показатель для оценки инвестиционных проектов. IRR дает ответ на вопрос: а какая должна быть ставка, чтобы NPV стала = 0?

Формула IRR в Excel

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

Функция ВСД

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

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


Где еще пригодится расчет NPV и IRR в Excel

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

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

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

Сумма аннуитета

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

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

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

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

Как проверять банки на честность, рассчитав NPV и IRR в Excel


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

Особенности расчета NPV и IRR

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

Банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%? Нет. Дело в том, что настоящий кредит в начале каждого года уменьшается:
Учет страховки банка

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

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

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

logo
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

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

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

"Финансовый директор" - профессиональный ресурс по управлению финансами компании. Авторские материалы подготовлены финансовыми директорами и экспертами находятся в закрытом доступе. Зарегистрируйтесь или войдите через соцсеть, чтобы прочитать эту статью бесплатно

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

Мы подобрали для вас книги:

Как решать нерешаемые задачи, посмотрев проблему с другой стороны Управление финансовой службой по KPI Как не потерять на налогах: проверенные способы
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.