Чистнз в excel

Чистая приведенная стоимость NPV (ЧПС) и внутренняя ставка доходности IRR (ВСД) в EXCEL

Рассчитаем Чистую приведенную стоимость и Внутреннюю норму доходности с помощью формул MS EXCEL.

Начнем с определения, точнее с определений.

Чистой приведённой стоимостью (Net present value, NPV) называют сумму дисконтированных значений потока платежей, приведённых к сегодняшнему дню (взято из Википедии). Или так: Чистая приведенная стоимость – это Текущая стоимость будущих денежных потоков инвестиционного проекта, рассчитанная с учетом дисконтирования, за вычетом инвестиций (сайт cfin. ru) Или так: Текущая стоимость ценной бумаги или инвестиционного проекта, определенная путем учета всех текущих и будущих поступлений и расходов при соответствующей ставке процента. (Экономика . Толковыйсловарь . — М . : « ИНФРА — М «, Издательство « ВесьМир «. Дж . Блэк .)

Примечание1 . Чистую приведённую стоимость также часто называют Чистой текущей стоимостью, Чистым дисконтированным доходом (ЧДД). Но, т.к. соответствующая функция MS EXCEL называется ЧПС() , то и мы будем придерживаться этой терминологии. Кроме того, термин Чистая Приведённая Стоимость (ЧПС) явно указывает на связь с Приведенной стоимостью .

Для наших целей (расчет в MS EXCEL) определим NPV так: Чистая приведённая стоимость — это сумма Приведенных стоимостей денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через равные промежутки времени.

Совет : при первом знакомстве с понятием Чистой приведённой стоимости имеет смысл познакомиться с материалами статьи Приведенная стоимость .

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

Как было сказано, в MS EXCEL для вычисления Чистой приведённой стоимости используется функция ЧПС() (английский вариант — NPV()). В ее основе используется формула:

CFn – это денежный поток (денежная сумма) в период n. Всего количество периодов – N. Чтобы показать, является ли денежный поток доходом или расходом (инвестицией), он записывается с определенным знаком (+ для доходов, минус – для расходов). Величина денежного потока в определенные периоды может быть =0, что эквивалентно отсутствию денежного потока в определенный период (см. примечание2 ниже). i – это ставка дисконтирования за период (если задана годовая процентная ставка (пусть 10%), а период равен месяцу, то i = 10%/12).

Примечание2 . Т.к. денежный поток может присутствовать не в каждый период, то определение NPV можно уточнить: Чистая приведённая стоимость — это Приведенная стоимость денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через промежутки времени, кратные определенному периоду (месяц, квартал или год) . Например, начальные инвестиции были сделаны в 1-м и 2-м квартале (указываются со знаком минус), в 3-м, 4-м и 7-м квартале денежных потоков не было, а в 5-6 и 9-м квартале поступила выручка по проекту (указываются со знаком плюс). Для этого случая NPV считается точно также, как и для регулярных платежей (суммы в 3-м, 4-м и 7-м квартале нужно указать =0).

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

Функция ЧИСТВНДОХ

В этой статье описаны синтаксис формулы и использование функции ЧИСТВНДОХ в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции ЧИСТВНДОХ описаны ниже.

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

Даты Обязательный. График дат платежей, который соответствует платежам для денежных потоков. Даты могут быть в любом порядке. Дата должна быть введена с использованием функции ДАТА либо как результат других формул или функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем. .

Предп Необязательный. Величина, предположительно близкая к результату ЧИСТВНДОХ.

Замечания

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

Числа в аргументе «даты» усекаются до целых.

В ЧИСТВНДОХ ожидается хотя бы одна положительная денежная движение и одна отрицательная денежная движение. в противном случае ЧИСТВНДОХ Возвращает #NUM! значение ошибки #ЗНАЧ!.

Если какое-либо число в поле даты не является допустимой датой, то функция ЧИСТВНДОХ возвращает #VALUE! значение ошибки #ЗНАЧ!.

Если какое – либо число в датах предшествует дате начала, функция ЧИСТВНДОХ Возвращает #NUM! значение ошибки #ЗНАЧ!.

Если значения и даты содержат различное количество значений, функция ЧИСТВНДОХ Возвращает #NUM! значение ошибки #ЗНАЧ!.

В большинстве случаев задавать аргумент «предп» для функции ЧИСТВНДОХ не требуется. Если этот аргумент опущен, то он полагается равным 0,1 (10 процентов).

Функция ЧИСТВНДОХ тесно связана с функцией ЧИСТНЗ. Ставка доходности, вычисляемая функцией ЧИСТВНДОХ — это процентная ставка, соответствующая ЧИСТНЗ = 0.

В Excel используется итеративный метод для вычисления ЧИСТВНДОХ. Используя частоту изменения (начиная с предположения), ЧИСТВНДОХ просматривает вычисление, пока результат не станет точным в 0,000001%. Если функции ЧИСТВНДОХ не удается найти результаты, которые работают после попыток 100, #NUM! возвращено значение ошибки. Частота меняется до:

di = дата i-й (последней) выплаты;

d1 = дата 0-й выплаты (начальная дата);

Pi = сумма i-й (последней) выплаты.

Пример

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

Функция ЧИСТНЗ;

Функция ЧИСТНЗвозвращает чистую текущую стоимость инвестиции, вычисляемую на основе нормы скидки и ряда поступлений наличных, которые не обязательно периодические. В случае периодических поступлений следует пользоваться функцией НПЗ.

Функция ЧИСТНЗ позволяет рассчитывать чистую текущую стоимость нерегулярных переменных расходов п доходов. Для расчета используется формула:

XNPV — чистая текущая стоимость нерегулярных переменных выплат и поступлений;

r — ставка процента (норма дисконтирования);

d_i— дата i-ой операции;

do — дата 0-ой операции (начальная дата);

value_i— сумма i-ой операции;

Синтаксис ЧИСТНЗ(ставка; значения; даты)

ставка— это норма скидки, применяемая к операциям с наличными.

значения— это ряд поступлений наличных <сумма0; сумма1;..; суммan>, которые соответствуют расписанию в аргументе даты. Первая выплата не является обязательной, она соответствует выплате в начале инвестиции. На все последующие выплаты делается скидка на основе 365-дневного года.

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

Указанные даты операции должны соответствовать суммам выплат и поступлений. Расчет производится на дату, когда осуществляется первая операция, т.е. на дату дата0. Первая сумма (сумма0), таким образом, не дисконтируется (см. формулу (3.11)). Если требуется сделать расчет на дату, предшествующую дате первой операции, то следует задать аргумент сумма0 равным 0. Если предполагается несколько операций (ожидаемых поступлений и расходов), то можно указать ссылки на ячейки, содержащие даты и суммы операций в обычном формате.

Задача 11. Рассмотрим инвестицию размером

10 000 руб. 1 января 2000 года,

которая принесет доходы:

2 750 руб. 1 марта 2000 года.

4 250 руб. 30 октября 2000 года,

3 250 руб. 15 февраля 2001 года,

2 750 руб. 1 апреля 2001 года.

Ставка процента 9%. Определить чистую текущую стоимость инвестиции.

Пусть в ячейки А1:Е1 введены даты выплат и поступлений. а в А2:Е2 — суммы операций.

Начальный платеж должен быть включен в число аргументов со знаком “-“ (в ячейке А2 число -10000).

Чистая текущая стоимость составит:

ЧИСТНЗ(ставка; значения; даты)

ЧИСТНЗ(0.09; А2:Е2; А1:Е1) = 2086.6 руб.

При нулевых начальных затратах (ячейка А2=0) текущая стоимость будущих доходов на 1.01.2000 составит 12 086.6 руб.

Talkin go money

ЧПС ЧИСТНЗ (Апрель 2020).

Table of Contents:

Сумма денег не совпадает с периодом времени. Например, если вы выиграли 500 долларов в лотерее 50 лет назад, вы были бы богаче, чем если бы вы выиграли ее вчера. Это правило отражает силу накопленного интереса.

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

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

Чтобы взять предыдущий пример, если вы выиграли 500 долларов 50 лет назад, и вы поместили все эти деньги в инвестиционный автомобиль с годовым возвратом в 5%, вы бы взяли 5 долларов. 733 сегодня, или $ 500 * (1 + 5%) ^ 50.

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

Принцип NPV

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

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

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

И если мы примем 10% -ную ставку дисконтирования и срок службы машины 15 лет, это то, что мы получим:

Формула чистой цены в Excel:

NPV = — $ 232 000 + $ 38, 800 (1 + 0, 10) -1 + $ 38, 800 (1 + 0, 10) -2 + $ 38, 800 (1 + 0, 10) > -3 + … + $ 38, 800 (1 + 0, 10) -15 NPV

= $ 63, 116 Это означает, что, выбрав сегодня $ 232 000 замените машину, компания испытает рост на 63 636 долл. США.

Денежные потоки в КОНЕЦ периода

В случае, когда денежные потоки всегда собираются на ту же дату каждый год — конец периода — вы можете просто использовать базовую функцию NPV Excel.Эта функция, как показано ниже, требует двух параметров: первая — это ставка дисконтирования, а вторая — диапазон денежных потоков. (Подробнее см. Ниже:

Улучшите свое инвестирование с помощью Excel .)

Движение денежных средств в СНВ периода

В случае, когда денежные потоки всегда удерживаются на той же дате каждый год, но раньше, просто умножайте NPV на (1 + rate) , Действительно, основная функция Excel предполагает, что денежные потоки получены в конце периода. Аналогичным образом, в таком сценарии первый поток следует рассматривать в момент времени 0, поэтому мы могли бы просто исключить функцию NPV и добавить ее в NPV из трех других потоков, которые затем будут учитываться на конце периода денежного потока, но с отставание года (см. пример ниже).

Потоки в разные моменты времени

Наконец, если вы попытаетесь рассчитать NPV проекта, который генерирует денежные потоки в разные моменты времени, вы должны использовать функцию XNPV, которая включает в себя три параметры: первая — ставка дисконтирования, вторая — серия денежных потоков, а третья — диапазон дат, когда денежные потоки получены вовремя.

Excel делает вычисление NPV быстрым и (относительно) простым.

Функция ЧИСТНЗ

Определение текущей стоимости вклада (займа)

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

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

Известны три типа инвестиций, для расчета инвестиции каждого типа в Ехсеl используется отдельная функция:

1. денежные потоки равной величины через равные промежутки времени (ПС);

2. денежные потоки произвольной величины через равные промежутки времени (ЧПС);

3. денежные потоки произвольной величины через произвольные промежутки времени (ЧИСТНЗ).

2.2.1. Функция ПС («приведенная стоимость»)

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

Этот расчет является обратным к определению будущей стоимости при помощи функции БС.

ПС(Ставка;Кпер;Плт;Бс;Тип)

1. Ставка – процентная ставка за период.

Например, если получена ссуда на автомобиль под 10% годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0,83%;

2. Кпер – общее количество периодов выплат или начисления процентов.

Например, если получена ссуда на 4 года и делаются ежемесячные платежи, то ссуда имеет периодов выплат;

3. Плт – это выплата, производимая в каждый период и не меняющаяся за все время срока ссуды, включает основные платежи и проценты.

Например, месячная выплата по четырехгодичному займу в 10 000 рублей под 12% годовых составит 203,33 рубля, в качестве аргумента функции нужно ввести -203,33;

4. Бс – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0.

Например, если предпогается накопить 50000 рублей для оплаты специального проекта в течение 18 лет, 50 000 рублей – это и есть будущая стоимость;

5. Тип – число 0 или 1, обозначающее, когда должна производиться выплата: 0 или пропущено – в конце периода, 1 – в начале периода.

Для расчета единой суммы вклада используется функция ПС с аргументами Ставка, Кпер, Бс:

ПС(Ставка;Кпер;;БС)

Пример 4.Фирме потребуется 5 млн. рублей через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5 000 000 рублей. Определить необходимую сумму текущего вклада, если ставка процента по нему составляет 12% годовых.

Решение в MS Excel:

1. Внести исходные данные:

ü годовая процентная ставка 12%

ü Срок вклада 12 лет

ü Будущая стоимость 5000000 рублей

2. В ячейке D2 вызвать функцию ПС и заполнить ее аргументы:

Функция имеет вид: = ПС(A2;B2;;C2) = -1 283 375,46 рублей.

2.2.2. Функция ЧПС («чистая приведенная стоимость»)

Функция ЧПС предназначена для расчета чистой приведенной (текущей) стоимости периодических платежей переменной величины.

ЧПС(Ставка;Значение1;Значение2; . ;ЗначениеN)

1. Ставка – процентная ставка за период;

2. Значение1, Значение2,…,ЗначениеN – от 1 до 254 аргументов, представляющих собой доходы и расходы; эти доходы и расходы должны быть равномерно распределены по времени; выплаты должны осуществляться в конце каждого периода.

Основное разлитие между функциями ПС и ЧПС заключается в том, что,

ü в функции ПС периодические выплаты предполагаются одинаковыми, а в функции ЧПС они могут быть различными,

ü в функции ПС платежи и поступления происходят как в конце, так и в начале периода, а в функции ЧПС предполагается, что все выплаты производятся равномерно и всегда в конце периода.

Из последнего вывода следует:

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

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

Пример 5.Инвестиции в проект к концу первого года его реализации составят 10 000 рублей. В последующие три года ожидаются годовые доходы по проекту 3000 рублей, 4200 рублей и 6800 рублей. Издержки привлечения капитала (процентная ставка) – 10%. Рассчитать текущую стоимость проекта.

Решение в MS Excel:

1. Внести исходные данные:

ü Годовая процентная ставка 10%

ü Инвестиция к концу первого года 10000 рублей

ü Доходы по проекту 3000, 4200, 6800 рублей во второй, третий и четвертый годы соответственно

2. В ячейке B7 вызвать функцию ЧПС и заполнить ее аргументы. Так как по условию задачи инвестиция в сумме 10 000 рублей вносится к концу первого года, то это значение следует включить в список аргументов функции, причем со знаком «минус». Остальные денежные потоки представляют собой доходы, поэтому при вычислениях укажем их со знаком «плюс».

Формула имеет вид: =ЧПС(B1;B3:B6)=1 188,44 рублей.

Пример 6. Инвестор с целью инвестирования рассматривает 2 проекта, рассчитанных на 5 лет. Определить, какой проект является наиболее привлекательным для инвестора при годовой процентной ставке 15%. Проекты характеризуются следующими данными:

ü по первому проекту – начальные инвестиции составляют 550 тыс. руб., ожидаемые доходы за 5 лет соответственно 100, 190, 270, 300 и 350 тыс. рублей

ü по второму проекту – начальные инвестиции составляют 650 тыс. руб., ожидаемые доходы за 5 лет соответственно 150, 230, 470, 180 и 320 тыс. рублей

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

Решение в MS Excel:

1. Внести исходные данные:

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

Формула в ячейке B10 имеет вид: =ЧПС(B2;B5:B9)-B3=203,69 тыс.рублей

Формула в ячейке С10 имеет вид: =ЧПС(C2;C5:C9)-C3=225,39 тыс.рублей

Таким образом, второй проект является для инвестора наиболее привлекательным (доход по этому проекту больше).

Функция ЧИСТНЗ предназначена для расчета чистой приведенной (текущей) стоимости для графика денежных потоков произвольной величины через произвольные промежутки времени.

ЧИСТНЗ(Ставка;Значения;Даты)

1. Ставка – ставка дисконтирования, применяемая к денежным потокам;

2. Значения – ряд денежных потоков, соответствующий графику платежей, приведенному в аргументе «Даты»

3. Даты – расписание дат платежей, соответствующее ряду денежных потоков.

Расчет производится на дату, когда осуществляется первая операция.

Пример 7.Рассмотрим инвестицию 10 000 рублей на 1 июля 2013 г. Она принесет следующие доходы:

ü 2 750 рублей 1 сентября 2013 г.,

ü 4 250 рублей 1 октября 2013 г.,

ü 5 250 рублей 10 октября 2013 г.

Норма дисконтирования – 9% годовых. Определить чистую текущую стоимость инвестиции:

ü на 1 июля 2012 г.

ü на 1 июля 2013 г.

Решение в MS Excel:

1. Внести исходные данные:

Т.к. на 01.07.2012 года не было вкладов (доходов), то сумма, соответствующая данному числу равна 0.

2. В ячейке А6 (В6) вызвать функцию ЧИСТНЗ и заполнить ее аргументы:

Чистая текущая стоимость инвестиции на 1 июля 2012 года составит:

Чистая текущая стоимость инвестиции на 1 июля 2013 года составит: