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

41494
Салостей Станислав
автор и руководитель проекта Finalytics.PRO, бывший финансовый директор
Финансовых формул в Excel много. Разберем самые важные для финансиста формулы, которые подскажут, как рассчитать: IRR и NPV инвестиционного проекта.

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

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

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

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

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

Скачайте полезные документы

Word 2Регламент оценки и анализа инвестиционных проектов

Word 3Инструкция по оценке инвестиционных проектов компании

Word 4Методика инвестиционных расчетов девелоперской компании

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

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

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

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

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

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

Расчет в Excel

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

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

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

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

Значение NPV

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

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

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

Таблица для расчета NPV в Excel

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

Ставка дисконтирования

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

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

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

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

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

Функция ВСД

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

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

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

Аннуитеты

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

Допустим, вы собираетесь взять кредит 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 показывает ту самую ставку. Когда банк делает нам предложение, от которого невозможно отказаться, и которое увеличит кредитную ставку «всего» на сколько-то процентов – не верьте и пересчитывайте. Приведу пример, как рассчитать IRR и понять реальный процент по кредиту.

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

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

Учет страховки банка

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

Расчет по условиям банка

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

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

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

Методические рекомендации по управлению финансами компании



Ваша персональная подборка

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

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

    Рекомендации по теме

    Школа

    Школа

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

    Записаться

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

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

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

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

    А еще...




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

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

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

    
    • Мы в соцсетях
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

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