Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

В этой статье описаны синтаксис формулы и использование функции ЧИСТВНДОХ в 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, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Внутренняя норма доходности на excel

В данной статье мы рассмотрим, что такое внутренняя норма доходности, какой экономический смысл она имеет, как и по какой формуле рассчитать внутреннюю норму доходности, рассмотрим некоторые примеры расчёта, в том числе при помощи формул MS Exel.

Что такое внутренняя норма доходности?

Внутренняя норма доходности (IRR — Internal Rate of Return) — один из основных критериев оценки инвестиционных проектов (доходности единицы вложенного капитала): ставка дисконта, при которой выполняется равенство суммы дисконтированных доходов по проекту (положительного денежного потока) дисконтированной сумме инвестиций (отрицательному денежному потоку, приведенному объему инвестиций), т.е. когда чистая текущая стоимость (NPV) равна нулю.

В финансово-экономической литературе довольно часто можно встретить синонимы внутренней ставки доходности:

  • внутренняя ставка доходности;
  • внутренняя ставка отдачи;
  • внутренняя норма прибыли;
  • внутренняя норма рентабельности;
  • внутренняя норма возврата инвестиций.

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

Читать еще:  Формула подсчета символов в ячейке excel

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

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

Формула расчёта внутренней нормы доходности

Внутренняя норма доходности рассчитывается по следующей формуле:

где
NPVIRR (Net Present Value) — чистая текущая стоимость, рассчитанная по ставке IRR;
CFt (Cash Flow) – денежный поток в период времени t;
IC (Invest Capital) – инвестиционные затраты на проект в первоначальном периоде (тоже являются денежным потоком CF = IC).
t – период времени.

или же данную формулу можно представить в виде:

Практическое применение внутренней нормы доходности

Внутренняя норма доходности применяется для оценки инвестиционной привлекательности проекта или для сопоставительного анализа с другими проектами. Для этого IRR сравнивают с эффективной ставкой дисконтирования, то есть с требуемым уровнем доходности проекта (r). За такой уровень на практике зачастую используют средневзвешенную стоимость капитала (Weight Average Cost of Capital, WACC).

Значение IRRКомментарии
IRR>WACCУ инвестиционного проекта внутренняя норма доходности выше чем затраты на собственный и заемный капитал, т.е. данный проект имеет инвестиционную привлекательность
IRRИнвестиционный проект имеет внутреннюю норму доходности ниже чем затраты на капитал, это свидетельствует о нецелесообразности вложения в него
IRR=WACCВнутренняя норма доходность проекта равна средневзвешенной стоимости капитала, т.е. данный проект находится на минимально допустимом уровне доходности, поэтому следует произвести корректировки движения денежных средств и увеличить денежные потоки
IRR1>IRR2Инвестиционный проект №1 имеет больший потенциал для вложения чем проект №2

Следует отметить, что вместо критерия сравнения WACC может быть использована любая другая норма доходности, например, ставка доходности по государственным облигациям, ставка по банковскому депозиту и т.п. Так, если процентная ставка по депозиту составляет 17%, а IRR инвестиционного проекта составляет 22%, то, очевидно, что деньги следует вкладывать в инвестиционный проект, а не размещать на депозит в банк.

Узнайте о других решениях

Лучшие 9 инструментов HR аналитики

Аналитика для коммунальных услуг

Аналитика в науках о жизни

17 метрик рекрутинга, о которых вам нужно знать

Индустрия 4.0

Графический метод поиска внутренней ставки доходности

Предположим, что мы собираемся инвестировать 10 тыс. денежных единиц, и у нас есть варианты их инвестирования в 3 проекта каждый из которых, как предполагается, будет формировать определённые денежные потоки на протяжении 5 лет.

Период, летПроект №1Проект №2Проект №3
-10000-10000-10000
1100010004000
24 00015003000
3200030002000
4400040001000
5200030001000

Продисконтируем вышеуказанные денежные потоки по 3-м проектам по разным процентным ставкам (от 0 до 14%) и на основе полученных результатов построим график.

На графике прослеживается чёткая взаимосвязь между ставкой дисконтирования и чистой текущей стоимостью: чем выше ставка дисконтирования, тем ниже дисконтированная стоимость.

Внутренняя норма доходности, как это следует из определения указанного в начале данной статьи, — это тот уровень ставки дисконта, при которой NPV=0. В нашем примере внутренняя норма доходности определяется в точках пересечения кривых с осью Х. В частности, для проекта №1 IRR составляет 8,9%, для проекта №2 IRR=6,6% и для проекта №3 IRR=4,4%.

Расчёт внутренней нормы доходности (IRR) при помощи MS Exel

Внутреннюю норму доходности можно довольно легко рассчитать при помощи встроенной финансовой функции ВСД (IRR) в MS Exel.

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

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

Синтаксис функции ВСД:

где
Значения — это массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности, учитывая требования указанные выше;
Предположение — это величина, о которой предполагается, что она близка к результату ВСД:

  • Microsoft Excel использует метод итераций для вычисления ВСД. Начиная со значения Предположение, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001 процента. Если функция ВСД не может получить результат после 20 попыток, то выдается значение ошибки #ЧИСЛО!.
  • В большинстве случаев нет необходимости задавать Предположение для вычислений с помощью функции ВСД. Если Предположение опущено, то оно полагается равным 0,1 (10 процентов).
  • Если ВСД возвращает значение ошибки #ЧИСЛО! или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента Предположение.

Пример расчёта внутренней ставки доходности (на основе данных о денежных потоках по трём проектам, которые рассматривались выше):

Расчет ставки доходности денежных потоков в 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. Функции ЧИСТВНДОХ и ЧИСТНЗ являются взаимозависимыми. Вычисляемая первой функцией ставка соответствует нулевому чистому дисконтируемому доходу (то есть, при данной ставке результат вычислений ЧИСТНЗ равен нулю).

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

gling,
Не похоже. На 3-х месячном отрезке (01.01.2018 — 01.04.2018, 90 дней) времени разница получается еще больше. Сумма %-ого дохода получается: 1.000.000*10%*90/365=24.657,53. Если это число подставить для расчета ставки с помощью функции ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.04.2018 : -1.024.657,53), то получится ставка 10,38%.

365/4 = 91,25. Но если считать процентный доход обычным способом, то даже ровно за 91 день по ставке 10,38% получим: 1.000.000*10,38%*91/365=25.878,9 что заметно больше, чем 24.657,53.

Да и потом, разве функция ЧИСТВНДОХ не считает точное кол-во дней с дробным числом между датами?

gling,
Не похоже. На 3-х месячном отрезке (01.01.2018 — 01.04.2018, 90 дней) времени разница получается еще больше. Сумма %-ого дохода получается: 1.000.000*10%*90/365=24.657,53. Если это число подставить для расчета ставки с помощью функции ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.04.2018 : -1.024.657,53), то получится ставка 10,38%.

365/4 = 91,25. Но если считать процентный доход обычным способом, то даже ровно за 91 день по ставке 10,38% получим: 1.000.000*10,38%*91/365=25.878,9 что заметно больше, чем 24.657,53.

Да и потом, разве функция ЧИСТВНДОХ не считает точное кол-во дней с дробным числом между датами? pavel2018

Сообщение gling,
Не похоже. На 3-х месячном отрезке (01.01.2018 — 01.04.2018, 90 дней) времени разница получается еще больше. Сумма %-ого дохода получается: 1.000.000*10%*90/365=24.657,53. Если это число подставить для расчета ставки с помощью функции ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.04.2018 : -1.024.657,53), то получится ставка 10,38%.

365/4 = 91,25. Но если считать процентный доход обычным способом, то даже ровно за 91 день по ставке 10,38% получим: 1.000.000*10,38%*91/365=25.878,9 что заметно больше, чем 24.657,53.

Да и потом, разве функция ЧИСТВНДОХ не считает точное кол-во дней с дробным числом между датами? Автор — pavel2018
Дата добавления — 28.10.2018 в 15:11

pavel2018Дата: Воскресенье, 28.10.2018, 15:27 | Сообщение № 4
pavel2018Дата: Воскресенье, 28.10.2018, 15:40 | Сообщение № 5

Т.е. функция ЧИСТВНДОХ, возможно, предполагает капитализацию процентного дохода.
Если вернуться к 1-му примеру: 01.01.2018 — 01.07.2018 (181 дней, процентный доход = 49.589,04). Если этот доход прибавить к сумме инвестиции (1.000.000 + 49.589,04 = 1.049.589,04) и на эту базу рассчитать процентный доход на 2-ую половину года: 1.049.589,04*10%*184/365 = 52.910,79. В сумме за год получается 49.589,04+52.910,79=102.499,83. Это эквивалентно ставке : 102.499,83/1.000.000 = 10,249983%.
А функция ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.07.2018 : -1.049.589,04) высчитывает 10,25212% . Тоже, к сожалению, нет точного совпадения.

Т.е. функция ЧИСТВНДОХ, возможно, предполагает капитализацию процентного дохода.
Если вернуться к 1-му примеру: 01.01.2018 — 01.07.2018 (181 дней, процентный доход = 49.589,04). Если этот доход прибавить к сумме инвестиции (1.000.000 + 49.589,04 = 1.049.589,04) и на эту базу рассчитать процентный доход на 2-ую половину года: 1.049.589,04*10%*184/365 = 52.910,79. В сумме за год получается 49.589,04+52.910,79=102.499,83. Это эквивалентно ставке : 102.499,83/1.000.000 = 10,249983%.
А функция ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.07.2018 : -1.049.589,04) высчитывает 10,25212% . Тоже, к сожалению, нет точного совпадения.

Сообщение Т.е. функция ЧИСТВНДОХ, возможно, предполагает капитализацию процентного дохода.
Если вернуться к 1-му примеру: 01.01.2018 — 01.07.2018 (181 дней, процентный доход = 49.589,04). Если этот доход прибавить к сумме инвестиции (1.000.000 + 49.589,04 = 1.049.589,04) и на эту базу рассчитать процентный доход на 2-ую половину года: 1.049.589,04*10%*184/365 = 52.910,79. В сумме за год получается 49.589,04+52.910,79=102.499,83. Это эквивалентно ставке : 102.499,83/1.000.000 = 10,249983%.
А функция ЧИСТВНДОХ (01.01.2018 : 1.000.000 ; 01.07.2018 : -1.049.589,04) высчитывает 10,25212% . Тоже, к сожалению, нет точного совпадения.

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