Foreversoft.ru

IT Справочник
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 vb net

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

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

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

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

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

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

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

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

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

Объявление

Почему результат ЧИСТВНДОХ не совпадает с реальной ставкой

ЦБ рекомендует считать ЭПС по формуле ЧИСТВНДОХ.
Но если рассматривать денежный поток, соответствующий кредитованию под «голый» процент (без комиссий), то резултат формулы не совпадает со ставкой кредитования. Причем результат обычно выше (и не на десятые, а на 2-4 процента — можете сами проверить для любого кредита на любой срок).

Вопрос: с чем это связано? как корректно пользоваться ЧИСТВНДОХом?

Самое интересное, что в рекомендациях ЦБ по расчету разобраны примеры диференцированного списания и прочие, но самого главного (аннуитетный) я не нашел.

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

Модераторам: просьба не убирать эту тему очередным сообщением в тему «эффективная ставка» — в том топике уже мало аткуального, а этот вопрос, думаю, многим будет важен.

  • Регистрация: 04.08.2005
  • Сообщений: 69

vasgun Причем результат обычно выше
не обычно, а всегда не ниже
ЭПС будет совпадать с ПС только в одном случае:
1) Нет иных платежей (комиссий и т.п.)
2) Проценты уплачиваются в конце срока

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

Комментарий

  • Регистрация: 15.08.2005
  • Сообщений: 25
Читать еще:  Vba excel find синтаксис

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

И именно в этом случае ЧИСТВНДОХ дает ЭПС не равную диктуемой ставке, а завышенную на пару процентов.

Комментарий

  • Регистрация: 04.08.2005
  • Сообщений: 69

Комментарий

  • Регистрация: 11.01.2001
  • Сообщений: 6684

Комментарий

  • Регистрация: 15.08.2005
  • Сообщений: 25

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

Inspectr, какую формулу?
ЧИСТВНДОХ решает приближенными методами уравнение, которое описано в 1759-У.

Комментарий

  • Регистрация: 04.08.2005
  • Сообщений: 69

Возможно, но все таки не понятно почему именно год.

Комментарий

  • Регистрация: 11.01.2001
  • Сообщений: 6684

Комментарий

  • Регистрация: 15.08.2005
  • Сообщений: 25

Комментарий

  • Регистрация: 11.01.2001
  • Сообщений: 6684

Комментарий

  • Регистрация: 21.07.2005
  • Сообщений: 25

Комментарий

  • Регистрация: 23.04.2002
  • Сообщений: 1716

Комментарий

  • Регистрация: 11.01.2001
  • Сообщений: 6684

Комментарий

  • Регистрация: 23.04.2002
  • Сообщений: 1716

Комментарий

  • Регистрация: 23.04.2002
  • Сообщений: 1716

Комментарий

  • Регистрация: 15.08.2005
  • Сообщений: 25

Господа и дамы) я ведь не спор хочу затеять, а выяснить правду все-таки. Согласен с Мариной, что по 39-П проценты начисляются сугубо на остаток по долгу. Да и по клиентским графикам платежей мы с каждым месяцем начисляем на остаток по долгу.
Но объясните мне тогда, почему имеет место формула:
S=Ann*SUMM(v^i),
где S — сумма кредита
Ann — аннуитет
i-индекс суммы (меняется от 1 до n — кол-во периодов кредитования)
v = 1/(1+p)
p — ставка за период.

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

И потом. Если кредитование идет по простым процентам, а ЧИСТВНДОХ вычисляет доходность по сложным, то значение ЧИСТВНДОХА для срока больше 1 года должно быть всегда меньше, чем показатель ставки _простого_ годового процента по кредиту (без комиссий и прочих сторонних выплат).

Функция чиствндох в excel

Форумчане, еще раз доброго вечера!

Пытаюсь применить формулы ЧИСТНЗ и ЧИСТВНДОХ, возможно неправильно что-либо вношу в формулы, либо еще что, но считает некорректно . Буду рад, если сможете помочь.

И, есть ли возможность рассчитать данные функции если имеются только месяц и год (без добавления строки с последней датой месяца)?

Форумчане, еще раз доброго вечера!

Пытаюсь применить формулы ЧИСТНЗ и ЧИСТВНДОХ, возможно неправильно что-либо вношу в формулы, либо еще что, но считает некорректно . Буду рад, если сможете помочь.

И, есть ли возможность рассчитать данные функции если имеются только месяц и год (без добавления строки с последней датой месяца)?

Никогда не поздно научиться!

Ответить

Сообщение Форумчане, еще раз доброго вечера!

Пытаюсь применить формулы ЧИСТНЗ и ЧИСТВНДОХ, возможно неправильно что-либо вношу в формулы, либо еще что, но считает некорректно . Буду рад, если сможете помочь.

И, есть ли возможность рассчитать данные функции если имеются только месяц и год (без добавления строки с последней датой месяца)?

Заранее спасибо! Автор — Anton1013
Дата добавления — 16.11.2015 в 18:39

СветлыйДата: Понедельник, 16.11.2015, 19:57 | Сообщение № 2

Программировать проще, чем писать стихи.

Ответить

_Boroda_Дата: Понедельник, 16.11.2015, 20:09 | Сообщение № 3

Скажи мне, кудесник, любимец ба’гов.
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Anton1013Дата: Вторник, 17.11.2015, 09:42 | Сообщение № 4

_Boroda_, В очередной раз огромное спасибо 🙂


[offtop]Александр, есть хоть что-нибудь в икселе, чего Вы не знаете?=)[/offtop]

_Boroda_, В очередной раз огромное спасибо 🙂


[offtop]Александр, есть хоть что-нибудь в икселе, чего Вы не знаете?=)[/offtop] Anton1013

Никогда не поздно научиться!

Сообщение _Boroda_, В очередной раз огромное спасибо 🙂


[offtop]Александр, есть хоть что-нибудь в икселе, чего Вы не знаете?=)[/offtop] Автор — Anton1013
Дата добавления — 17.11.2015 в 09:42

_Boroda_Дата: Вторник, 17.11.2015, 14:36 | Сообщение № 5

Скажи мне, кудесник, любимец ба’гов.
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Anton1013Дата: Вторник, 17.11.2015, 15:14 | Сообщение № 6

Можно еще раз обратиться за помощью.

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

Неработающий пример прилагаю

Можно еще раз обратиться за помощью.

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

Неработающий пример прилагаю

Никогда не поздно научиться!

Сообщение Можно еще раз обратиться за помощью.

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

Неработающий пример прилагаю

Anton1013Дата: Вторник, 17.11.2015, 15:29 | Сообщение № 7

Никогда не поздно научиться!

Ответить

_Boroda_Дата: Вторник, 17.11.2015, 15:39 | Сообщение № 8

Скажи мне, кудесник, любимец ба’гов.
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Anton1013Дата: Вторник, 17.11.2015, 15:44 | Сообщение № 9

Я просто тут читал, вроде про 1ый отрицательный ни слова http://msoffice-prowork.com/spravka. kh-xirr

В принципе я понял суть «костыля» — задать ненулевое значение в 1ом периоде, но максимально приближенное к 0.

Спасибо еще раз!

Думаю теперь точно

Я просто тут читал, вроде про 1ый отрицательный ни слова http://msoffice-prowork.com/spravka. kh-xirr

В принципе я понял суть «костыля» — задать ненулевое значение в 1ом периоде, но максимально приближенное к 0.

Спасибо еще раз!

Думаю теперь точно Anton1013

Никогда не поздно научиться!

Сообщение Я просто тут читал, вроде про 1ый отрицательный ни слова http://msoffice-prowork.com/spravka. kh-xirr

В принципе я понял суть «костыля» — задать ненулевое значение в 1ом периоде, но максимально приближенное к 0.

Спасибо еще раз!

Думаю теперь точно Автор — Anton1013
Дата добавления — 17.11.2015 в 15:44

Vladimir82Дата: Четверг, 08.06.2017, 07:50 | Сообщение № 10

Доброе утро уважаемые форумчане. Помогите пожалуйста советом. Есть у меня ряд значений и я пытаюсь эти значения обработать инструментом ЧИСТВНДОХ, но почему то получаю от такой результат 2,98023E-09. Вот обрабатываемый ряд значений 11.02.2014
11.02.2014
24.03.2014
23.03.2015
20.04.2016
29.05.2016
25.01.2017
10.02.2017
22.02.2017
03.03.2017
07.03.2017
31.03.2017
07.06.2017

3 630,00
2 419,80
12 501,00
13 600,00
16 135,00
21 342,10
14 765,00
15 455,00
13 668,00
13 446,00
13 299,00
12 785,00
-130 832,70
Предположил, что дело в формате данных. Открыл на другом ПК эксель, внёс всё значения ручками на чистый лист и получил всё тоже неверное значения 2,98023E-09.

Доброе утро уважаемые форумчане. Помогите пожалуйста советом. Есть у меня ряд значений и я пытаюсь эти значения обработать инструментом ЧИСТВНДОХ, но почему то получаю от такой результат 2,98023E-09. Вот обрабатываемый ряд значений 11.02.2014
11.02.2014
24.03.2014
23.03.2015
20.04.2016
29.05.2016
25.01.2017
10.02.2017
22.02.2017
03.03.2017
07.03.2017
31.03.2017
07.06.2017

3 630,00
2 419,80
12 501,00
13 600,00
16 135,00
21 342,10
14 765,00
15 455,00
13 668,00
13 446,00
13 299,00
12 785,00
-130 832,70
Предположил, что дело в формате данных. Открыл на другом ПК эксель, внёс всё значения ручками на чистый лист и получил всё тоже неверное значения 2,98023E-09. Vladimir82

Сообщение Доброе утро уважаемые форумчане. Помогите пожалуйста советом. Есть у меня ряд значений и я пытаюсь эти значения обработать инструментом ЧИСТВНДОХ, но почему то получаю от такой результат 2,98023E-09. Вот обрабатываемый ряд значений 11.02.2014
11.02.2014
24.03.2014
23.03.2015
20.04.2016
29.05.2016
25.01.2017
10.02.2017
22.02.2017
03.03.2017
07.03.2017
31.03.2017
07.06.2017

3 630,00
2 419,80
12 501,00
13 600,00
16 135,00
21 342,10
14 765,00
15 455,00
13 668,00
13 446,00
13 299,00
12 785,00
-130 832,70
Предположил, что дело в формате данных. Открыл на другом ПК эксель, внёс всё значения ручками на чистый лист и получил всё тоже неверное значения 2,98023E-09. Автор — Vladimir82
Дата добавления — 08.06.2017 в 07:50

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