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 ЧИСТВНДОХ

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как отформатировать файл excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Библиотеки для работы с excel c

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

Внутренняя норма доходности применяется для оценки инвестиционной привлекательности проекта или для сопоставительного анализа с другими проектами. Для этого 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 процентов).
  • Если ВСД возвращает значение ошибки #ЧИСЛО! или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента Предположение.

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

Как рассчитать доходность инвестиций с учетом ввода/вывода средств?

Автор: Алексей Мартынов · Опубликовано 05.06.2015 · Обновлено 23.11.2018

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

Для этого рассчитывают результат инвестирования с учетом вводов/выводов средств и делят его на средневзвешенную по времени величину вложенных средств. Данный метод расчета очень подробно описан на сайте УК Арсагера, поэтому здесь я его описывать не буду. Те, кто читал их статью, знают, что этот метод очень трудоемкий, все приходится считать вручную. Если вы много раз вводили и выводили деньги, то формула расчета доходности портфеля будет ооочень длинной, легко запутаться и сделать ошибку. Поэтому я объясню, как очень просто посчитать доходность инвестиций в Excel.

Как считать доходность инвестиций в Excel

В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:

  1. Инвестор купил акций на сумму 1000 рублей.
  2. Через 3 месяца он купил еще акций на 500 рублей.
  3. Еще через 4 месяца он продал часть акций на сумму 300 рублей.
  4. Через год после первоначального приобретения, стоимость акций составила 1300 рублей.

Доходность портфеля составила 8,004% годовых.

Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.

  • В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
  • Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
  • В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
  • В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.

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

Жмем ОК, получаем доходность — 8,009% годовых.

Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.

Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.

Расчет доходности инвестиционного портфеля за год

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

Рыночная стоимость портфеля на 31 декабря 2004 года: 10000$
20 марта 2005 года: внесение 1000$
25 июня 2005 года: изъятие 500$
1 октября 2005 года: внесение 1000$
Рыночная стоимость портфеля на 31 декабря 2005 года: 12000$

Вносим данные в Excel:

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

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

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