Формула в excel чиствндох

ЧИСТВНДОХ (функция ЧИСТВНДОХ)

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

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

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

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

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

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

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

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

В функции ЧИСТВНДОХ предполагаются по крайней мере один положительный и один отрицательный денежный поток; в противном случае эта функция возвращает значение ошибки #ЧИСЛО!.

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

Если хотя бы одно из чисел в аргументе «даты» предшествует начальной дате, то функция ЧИСТВНДОХ возвращает значение ошибки #ЧИСЛО!.

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

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

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

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

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

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

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

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

IRR: как рассчитать в Excel

Статьи по теме

Компания столкнется с внутренней нормой доходности (IRR), когда будет составлять бизнес-план для себя или банка. Показатель отражает возможность инвестиционного проекта приносить прибыль. Узнайте, как сделать расчет IRR в Excel и скачайте бесплатно пример вычисления.

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

Рассчитать IRR по формуле в Excel гораздо проще, чем делать это вручную. К тому же, сама формула для вычисления показателя выглядит не очень легкой. Поэтому сейчас финансисты редко обращаются к ней.

Чтобы сделать вычисления вручную, нужно знать следующую информацию:

  • за какой временной промежуток фирма выполнит расчет (t);
  • какой денежный поток был у компании за этот период (CFt);
  • какими были вложения в проект на этапе запуска (IC).

Тогда можно будет применить формулу:

Расчёт IRR в Excel

Очевидно, что ручные вычисления займут много времени. Гораздо проще найти IRR c помощью специальной формулы в Excel. Точнее, с помощью формул, ведь есть несколько путей, как вычислить доходность.

Сделать расчет IRR в Excel можно несколькими способами. Приведем три из них.

Читать еще:  Формула отнять процент от числа в excel формула

Способ 1. Функция «ВСД»

Во-первых, показатель можно вычислить с помощью функции ВСД (внутренняя ставка дисконта). Она возвращает ставку для ряда потоков денежных средств. В первоначальном виде формула для вычисления IRR выглядит так:

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

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

Еще один способ как рассчитать IRR в Excel – применить функцию «ЧИСТВНДОХ». Ее принцип аналогичен принципу опции «ВСД», но эта функция подходит для несистематических платежей. Общая формула:

  • значения – денежный поток за конкретную дату;
  • даты – движение денежных потоков в течение месяца (года и т.п.);
  • предположение – предположительно близкая к результату «ЧИСТВНДОХ» величина, заполнять необязательно.

Способ 3. Функция «МВСД»

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

Подготовлено по материалам
«Системы Финансовый директор»

Расчет IRR в Excel: пример

Разберем на конкретных примерах, как вычислить нужный показатель.

Пример 1. Расчет по формуле «ВСД»

Составим таблицу, которая состоит из двух столбцов: «Период» и «Денежный поток». Предположим, что расчет нужно сделать за год. Зададим период от 0 до 12. Известно, что первоначальные траты составили 100 тыс. руб., поэтому их мы запишем в таблицу в нулевом периоде и со знаком «минус». Вычислим IRR. Для нашего примера формула выглядит следующим образом:

В2-В14 – показатели по денежному потоку за все периоды, в том числе и за нулевой.

Пример 2. Расчет по формуле «ЧИСТВНДОХ»

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

Здесь поля B2-B14 отражают информацию по денежным потокам, а A2-F14 – сведения по датам.

Пример 3. Расчет по формуле «МВСД»

Образец таблицы можно взять из предыдущих примеров. Рассмотрим, в чем будет кардинальное отличие от предыдущих ситуаций. Для функции МВСД необходимо знать ставку финансирования и реинвестирования. Допустим, норма дисконта составляет 12 процентов. А реинвестировать доход можно по ставке 10 процентов годовых. Получаем формулу для Excel:

Все примеры расчета IRR в Excel можно бесплатно скачать в этом материале.

Гость, уже успели прочесть в свежем номере?

Как оценить результат

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

1. Если показатель доходности окажется выше капитала, проект стоит рассмотреть.

2. Когда ВНД меньше капитала, лучше искать другие возможности.

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

Иногда IRR сравнивают с процентами по банковскому депозиту. Неудачный вариант, если проценты по депозиту оказались выше. Значит, компании лучше вложиться во что-то другое.

Cрок окупаемости проекта: формула расчета

Термин «внутренняя норма доходности» обычно идет в соседстве с таким показателям как «срок окупаемости». Если компания хочет сделать полноценный анализ перспектив инвестиционного проекта, ей стоит узнать, за какой период он окупится. Посмотрим, какую формулу расчета срока окупаемости проекта можно использовать в Excel.

Вычислить срок окупаемости в Excel можно в одно действие. Для этого необходимо сумму первоначальных инвестиций разделить на сумму ежемесячных поступлений. Например, для нашей ситуации (см. образец в Excel на стр. 3) срок окупаемости составит четыре месяца:

290000 / 67000 = 4 (результат округленный).

Расчет ставки доходности денежных потоков в Excel ЧИСТВНДОХ

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

Читать еще:  Формула еслиошибка в excel примеры

Расчет рентабельности инвестиций по денежным потокам в Excel

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

Пример 1. Инвестор получил кредит в размере 100000 руб. под 13% годовых и вложил средства в проект, финансовые потоки которого занесены в таблицу Excel. Определить, является ли его вложение прибыльным.

Вид таблицы данных:

Рассчитаем внутреннюю ставку доходности по формуле:

  1. B3:B12 – диапазон ячеек со значениями финансовых выплат. Первое значение соответствует затратам (вложение 100000 рублей в инвестиционный проект), поэтому указано в виде отрицательного числа.
  2. A3:A12 – диапазон ячеек с датами, соответствующие моментам совершения выплат (введены с использованием функции ДАТА).

Данный инвестиционный проект является убыточным, поскольку кредит был получен по ставке 13%.

Расчет внутренней ставки доходности облигаций в Excel

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

Вид таблицы данных:

Для определения прибыли используем формулу:

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

Для определения ставки доходности используем формулу:

На практике подобные расчеты имеют смысл для достаточно больших периодов времени, поскольку на рынке зачастую возникают кризисные явления. Оптимальный период расчета доходности инвестиционного портфеля с использованием функции ЧИСТВНДОХ – от 5 лет и более.

Особенности расчета внутренней ставки доходности по функции ЧИСТВНДОХ в Excel

Функция ЧИСТВНДОХ имеет следующую синтаксическую запись:

=ЧИСТВНДОХ( значения;даты; [предположение])

  • значения – обязательный для заполнения, принимает ссылку на диапазон ячеек, содержащих записи о ряде денежных потоков, соответствующих графику, который задан аргументов даты. Для получения корректного результата ряд значений должен содержать хотя бы одно отрицательное и положительные числа соответственно. Первый платеж может быть равен нулю. Он соответствует начальной выплате или понесенным затратам;
  • даты — обязательный для заполнения, принимает ссылку на диапазон ячеек с датами проведения платежей (график денежных потоков). В отличие от функции ВСД, даты можно указывать в произвольном порядке. Для указания дат следует использовать функцию ДАТА. В противном случае результатом работы функции ЧИСТВНДОХ может быть код ошибки;
  • [предположение] – необязательный для заполнения, принимает числовое значение, близкое к результату вычисления функции.
  1. Даты в Excel при проведении вычислений преобразуются в числовые значения. Нецелые числа, переданные в качестве параметра даты, усекаются до целых значений.
  2. Функция ЧИСТВНДОХ генерирует код ошибки #ЧИСЛО!, если хотя бы одна дата из диапазона даты является недопустимым значением (например, текст или данные логического типа).
  3. Если один из последующих платежей был произведен ранее, чем первый платеж (ошибка в последовательности указания дат), рассматриваемая функция вернет код ошибки #ЧИСЛО!
  4. Если в качестве параметров значения и даты были переданы ссылки на диапазоны с различным числом элементов, результатом вычислений будет код ошибки #ЧИСЛО!
  5. Последний необязательный аргумент в большинстве расчетов не используют. Если он явно не указан, считается, что его значение равно 0,1 (10%).
  6. Функции ЧИСТВНДОХ и ЧИСТНЗ являются взаимозависимыми. Вычисляемая первой функцией ставка соответствует нулевому чистому дисконтируемому доходу (то есть, при данной ставке результат вычислений ЧИСТНЗ равен нулю).

ЧИСТВНДОХ (XIRR)

Вычисляет внутренний уровень доходности на основании ряда нерегулярных выплат.

Пример использования

ЧИСТВНДОХ(<-4000; 200; 250; 300>; ДАТА(2012; 01; 01); ДАТА(2012; 06; 23); ДАТА(2013; 05; 12); ДАТА(2014; 02; 09)>; 0,09)

ЧИСТВНДОХ(денежные_потоки; даты_выплат; [примерная_ставка])

денежные_потоки – массив или диапазон, содержащий доход или платежи, связанные с инвестициями.

  • денежные_потоки – параметр должен содержать хотя бы один отрицательный и один положительный денежный поток для расчета нормы доходности.

даты_выплат – массив или диапазон, содержащий даты, которые соответствуют денежным потокам в массиве денежные_потоки .

примерная_ставка – [ НЕОБЯЗАТЕЛЬНО – 0,1 по умолчанию ] – приблизительная оценка значения будущей внутренней доходности.

Примечания

Если дни, указанные в параметре даты_выплат , расположены с регулярным интервалом, используйте функцию ВСД .

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

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

Читать еще:  Функция если в excel вложенная функция

ЧИСТНЗ : Чистая приведенная стоимость инвестиций. Вычисляется на основании нерегулярных выплат и ставки дисконтирования.

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

ЧПС : Вычисляет чистую приведенную стоимость инвестиций с учетом дисконтированных доходов и затрат.

МВСД : Вычисляет модифицированную внутреннюю норму доходности для ряда денежных потоков. Учитывает разницу между затратами на привлечение инвестиций и суммой, получаемой от реинвестирования денежных средств.

ВСД : Вычисляет внутреннюю ставку окупаемости капиталовложений на основании ряда денежных потоков.

Чистая приведенная стоимость NPV (ЧПС) и внутренняя ставка доходности IRR (ВСД) в MS 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 Похожие задачи

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector