Расчёт NPV в Excel: основы работы и подводные камни

Наверняка, многие пользователи Microsoft Office знают, что в Excel присутствует блок функций, называющийся "финансовые". В число них также входит функция для расчёта чистой приведённой стоимости (NPV). С ней я и предлагаю познакомиться. В принципе, смоделировать формулу в Excel без особых сложностей можно и самому, так зачем в таком случае изучать эту функцию?



Причин в действительности две. Первая: знание работы этой функции может сэкономить время. Вместо того, чтобы забивать формулу и затем протягивать её каждый раз можно использовать встроенную функцию (в дальнейшем мы убедимся, что сфера её применения в действительности очень узка и ограничивается лишь простейшими расчётами). Вторая (и более важная): нельзя исключать, что при работе над листами, где участвовали руки другого человека, она не была использована. Без знания механики работы формулы придётся немного повозиться, что понять принцип её логику, а время, как известно - ценный ресурс. Мучиться с несчастной формулой, когда горит квартальный или годовой отчёт удовольствие ниже среднего.

Автор надеется, что убедил: представление о работе функции иметь надо (пусть и для общего развития). В таком случае, приступим к вскрытию пациента!

Предыстория. Бедный студент Вильям

Представим себе простую картину. Конец девятнадцатого века. Соединённые Штаты Америки. Зима. Студент Вильям грустным взглядом охватывает Йельский университет - ему не хватает денег на последний год обучения. Слёзы наворачиваются на глазах, но ничего не поделать - работы нет. Боль, печаль, тоска. Однако, стоило Вильяму войти в комнату общежития в последний раз, как жизнь бедолаги приняла совсем другой оборот. В конверте, который почтальон аккуратно подбросил под дверь, он нашёл письмо от юриста покойного дяди Вильяма. Старик завещал бедному студенту старую нефтяную вышку.

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

Внезапно вспомнив вырезку из газеты, сообщавшей, что доходность акций нефтяного спрута Рокфеллера Standard Oil составляет сорок процентов в год, Вильям стал лихорадочно прикидывать, хватит ли ему этих денег на продолжение учёбы. Схватив ручку и листок он начал судорожно проводить расчёты. Хватит ли Вильяму денег на то, чтобы оплатить свою учёбу? Последний год обучения должен был обойтись ему без малого в три с половиной тысячи долларов.

Простейшее использование формулы NPV

Функция расчёта NPV называется в русскоязычной версии Excel "ЧПС" (Чистая приведённая стоимость). Альтернативный вариант поиска: Функции - Финансовые - ЧПС (Functions - Financial - NPV в англ. версии). Исходные условия выглядят так: денежные потоки составляют 2 000 долл. (амортизацию игнорируем, поскольку стоимость вышки уже оплатила предыдущая сторона сделки и вышка досталась нам в дар). Альтернативные издержки составляют 40% в год со сложным начислением. Именно таким темпом растёт стоимость акций аналогичного вложения в бизнес Standard Oil. Запись условия в Excel будет выглядеть следующим образом:

Значения ячеек голубого цвета вбивались вручную

Далее используем функцию NPV для расчёта стоимости вложений:

Вид окна функции

Excel проводит расчёты, и в конечном счёте мы получаем следующий результат:

Результат расчётов

Если сравнить расчёт с "ручной" процедурой расчёта NPV решение окажется верным. И для подобных элементарных случаев функция ЧПС (NPV) в Excel годится (ежегодное и равноинтервальное поступление денежных средств и единая ставка дисконтирования).

Функция NPV: подводные камни

Однако функция довольно капризна и страдает рядом недостатков: как фундаментальных, так и технических. Во-первых, функция сразу же дисконтирует денежный поток первой ячейки. Стоимость обучения составляет 3 500 долларов в год. В таком случае для правильного расчёта при наличии денежного потока в "нулевой" период придётся модифицировать формулу расчёта подобным образом:

В4 добавляется к расчёты ЧПС отдельно

Результат в ячейке B7 отражает итог включения "нулевого" периода в формулу NPV. Если мысленно вычесть: 4 070,3 - 3 500 = 570,3. Результат отличается от итога в ячейке B7. Всё из-за "лишнего" дисконтирования начального периода. Читателя может смутить, почему же, если мы дисконтируем начальный отрицательный денежный поток, правильный результат стал меньше, а не наоборот (отрицательный денежный поток за счёт дисконтирования ведь сократился). Но ведь и последующие (положительные) ячейки тоже дисконтировались каждая на один период больше. А поскольку функция степенная, то каждый последующий денежный поток терял в своей ценности всё больше. Отсюда и этот эффект уменьшения итоговой стоимости. Поэтому не дайте себя обмануть (с)! Добавляйте стартовый денежный поток отдельно от формулы, чтобы не занизить поступления как начального, так и последующих периодов (см. ячейка В8).

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

В ячейке В7 используется формула NPV. Из-за разрыва, в ряде (Е4), периоды 5 и 6 дисконтируются по степеням 4 и 5 соответственно.

Ситуацию легко исправить, прописав в пропущенной ячейке значение 0,0:

Поставив в ячейку Е4 значение "0", расчёт через функцию NPV принял верное значение

Тем не менее, подобную особенность функции нужно принимать в расчёт, если вы имеете с ней дело. Но, если бы беды ограничивались исключительно подобным вопросом. Беда ещё и в том, что при применении более сложных задач, её использование попросту нерационально. Предположим, что на обратной стороне листа дядя Вильяма указывал, что добыча на нефтяной вышке падает ежегодно в среднем на 4 процента в год. Единого значения процентной ставки здесь получить невозможно, потому что в первый год, (темп падения будет равен 0) ставка дисконтирования будет равна (1/1,4), а во второй и последующие: [(1-0,04)/(1+0,4)]^t, где t - год денежного потока.

В подобном случае можно, конечно, попытаться вычислить среднюю процентную ставку (effective rate, которую придётся постоянно пересчитывать) и проделать несколько вычислений сбоку или же посчитать отдельно для первой ячейки по обычной ставке дисконтирования (1,4), а в последующие, по той, что указаны выше. Но зачем городить огород, если можно записать формулу расчёта для денежного потока для каждого из периодов и заносить результат в отдельную ячейку?

Для расчётов приведённой стоимости проектов с ростом, лучше писать функцию расчёта вручную

Пусть это и выглядит несколько громоздко, но свою функцию, данная формула выполняет. Для лиц, знакомых с формулой расчёт приведённой стоимости с функцией роста, разобраться с ней не составит труда. Денежный поток B10 перемножается на темп роста функции (1-В12) в степени t - 1. Полученный результат дисконтируется на (1+В11) в степени t. Дисконтируется каждый период, сумма значений которых и составляет приведённую стоимость проекта: В17 = SUM(B15:F15).

Резюме

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

Надеюсь, что статья была полезна и пригодится в дальнейшем при работе в Excel. Если Вы знаете иные хитрости данной функции - делитесь в комментариях!