Чиствндох в excel как работает
Обновлено: 04.07.2024
При вычислении текущей стоимости инвестиций на основе показателей, прогнозируемых будущих регулярных взносов с разными суммами полученное значение, сравнивается с начальной суммой необходимого объема инвестиций. Если текущая стоимость инвестиций больше чем необходимая сумма вложения финансовых средств, значит процент возврата вложенных средств выше чем ожидается. Но в таком случае возникает вопрос: какая же реальная фактическая процентная ставка доходности инвестиций при данных условиях?
Функция ВСД для расчета внутренней ставки доходности IRR проекта в Excel
Для вычисления внутренней ставки нормы доходности будущих вложений Excel предлагает функцию ВСД (Внутренняя Ставка Доходности). Данная функция очень тесно связанна с функцией для вычисления текущей стоимости инвестиций ЧПС. Функция ВСД служит для расчета внутренней ставки доходности в Excel, при которой для тех самых регулярных будущих взносов функция ЧПС буде возвращать свой итоговый результат вычисления равный нулю (0).
Чтобы использовать функцию ВСД необходимо подготовить входные данные. В диапазоне значений должно находится минимум одно отрицательное и одно положительное число суммы будущих платежей. Если же все суммы будут положительными – это будет значит, что ничего не инвестируется, а только возвращается платежами доходности от инвестиций, например, дивиденды, прибыль с выручки и т.п. Это положительный сценарий, но мало вероятный на практике. Обычно платежи инвестирования идут в начале периода реализации инвестиционного проекта, а платежи доходов от инвестиций идут под его конец. Не всегда так бывает, но всегда будет минимум 1 платеж с отрицательным числом (на расход) и минимум 1 платеж с положительным платежом (на приход).
Ниже на рисунке представлен хронологический график дивидендных выплат. В нем учитывается начальная сумма инвестиций для того, чтобы функция ВСД возвращала правильный итоговый результат вычисления. В начале таблицы графика находится строка содержащая значение суммы инвестиционного вложения равной 300 000$. Ниже приведенная формула отображает внутреннюю ставку доходности равной 10,53%:
Первый аргумент функции ВСД содержит диапазон ячеек с платежами на протяжении всего инвестиционного периода:
Во втором аргументе указана внутренняя процентная ставка доходности инвестиций. Если второй аргумент не указан, Excel по умолчанию присевает значение 10%.
Принцип действия функции ВСД основан на вычислениях значений текущей стоимости суммы для каждого платежа при процентной ставке доходности инвестиций. Если в результате вычислений текущая стоимость суммы очередного платежа больше чем >0, функция уменьшает процентную ставку и выполняет то же вычисление еще раз. Excel повторяет эти же операции изменяя размер процентной ставки и суммируя текущие значения пока сумма не будет равна =0. Тогда функция ВСД возвращает в своем итоговом результате вычисления найденную подходящую процентную ставку.
Расчет внутренней ставки доходности с нерегулярными платежами по ЧИСТВНДОХ
Одинаково как для функции ЧПС так и для функции ВСД принято условия, что все будущие платежи будут регулярными и проведенными в одном и том же промежутке времени. Однако на практике так бывает далеко не всегда. На случай нерегулярных инвестиционных платежей программа Excel предлагает функцию ЧИСТВНДОХ:
В отличии от функции ВСД функция ЧИСТВНДОХ содержит дополнительный обязательный для заполнения аргумент в своем синтаксисе, в которой нужно указать все даты для всех платежей:
Для функции ВСД – даты не нужны, так как обязательным условием считается, что все платежи будут выполнены регулярно и между ними проходит один и тот же период времени. Будит ли это одни день или месяц, или год – для функции ВСД нет никакого значения. Вычисленная процентная ставка с помощью ВСД будет правильной для всех указанных платежей. Это значит, что при ежегодных платежах будет вычисленная процентная ставка доходности – годовых. Если же платежи выполняются ежеквартально – будет получена ставка в ежеквартальной процентной ставке внутренней нормы доходности.
Примечание. Функция ЧИСТВНДОХ имеет родственную функцию ЧИСТНЗ для вычисления текущей стоимости инвестиций при нерегулярных платежах. Данная функция также требует указать даты нерегулярных платежей.
Ниже на рисунке представлен хронологический график с нерегулярными платежами инвестиционных взносов и дивидендных выплат. Иногда инвестор меняет свою инвестиционную стратегию и делает дополнительные вложения на протяжении всего периода реализации инвестиционного проекта при различных условиях: непредвиденные убытки или новые возможности. Учитывая все нерегулярные платежи, инвестиция достигает внутреннюю ставку доходности равную 10,14%. Для ее вычисления была использована функция ЧИСТВНДОХ.
Внутренний принцип действия функции ЧИСТВНДОХ практически идентичный с функционированием ВСД. ЧИСТВНДОХ вычисляет текущую стоимость для отдельно каждого платежа и повторяет результат, изменяя внутреннюю ставку доходности пока вычисляемый результат не будет равен нулю. При вычислении текущей стоимости учитывается количество дней между датами актуального платежа и его предыдущего предшественника. В итоговом результате вычисления функцией ЧИСТВНДОХ будет возвращена реальная внутренняя ставка доходности для всех приходных и расходных нерегулярных инвестиционных платежей.
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает накопленный процент по ценным бумагам с периодической выплатой процентов.
Возвращает накопленный процент по ценным бумагам, процент по которым выплачивается в срок погашения.
Возвращает величину амортизации для каждого учетного периода, используя коэффициент амортизации.
Возвращает величину амортизации для каждого учетного периода.
Возвращает количество дней от начала действия купона до даты соглашения.
Возвращает количество дней в периоде купона, который содержит дату расчета.
Возвращает количество дней от даты расчета до срока следующего купона.
Возвращает порядковый номер даты следующего купона после даты соглашения.
Возвращает количество купонов между датой соглашения и сроком вступления в силу.
Возвращает порядковый номер даты предыдущего купона до даты соглашения.
Возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат.
Возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами.
Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.
Возвращает величину амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанный метод.
Возвращает ставку дисконтирования для ценных бумаг.
Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.
Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.
Возвращает продолжительность Маколея для ценных бумаг, по которым выплачивается периодический процент.
Возвращает фактическую (эффективную) годовую процентную ставку.
Возвращает будущую стоимость инвестиции.
Возвращает будущее значение первоначальной основной суммы после применения ряда (плана) ставок сложных процентов.
Возвращает процентную ставку для полностью инвестированных ценных бумаг.
Возвращает проценты по вкладу за данный период.
Возвращает внутреннюю ставку доходности для ряда потоков денежных средств.
Вычисляет выплаты за указанный период инвестиции.
Возвращает модифицированную продолжительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.
Возвращает внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разные значения ставки.
Возвращает номинальную годовую процентную ставку.
Возвращает общее количество периодов выплаты для инвестиции.
Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.
Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) первым периодом купона.
Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом купона.
Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) последним периодом купона.
Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом купона.
ПДЛИТ
Возвращает количество периодов, необходимых инвестиции для достижения заданного значения.
Возвращает регулярный платеж годичной ренты.
Возвращает платеж с основного вложенного капитала за данный период.
Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым выплачивается периодический процент.
Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, на которые сделана скидка.
Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым процент выплачивается в срок погашения.
Возвращает приведенную (к текущему моменту) стоимость инвестиции.
Возвращает процентную ставку по аннуитету за один период.
Возвращает сумму, полученную к сроку погашения полностью инвестированных ценных бумаг.
ЭКВ.СТАВКА
Возвращает эквивалентную процентную ставку для роста инвестиции.
Возвращает величину амортизации актива за один период, рассчитанную линейным методом.
Возвращает величину амортизации актива за данный период, рассчитанную методом суммы годовых чисел.
Возвращает эквивалентный облигации доход по казначейскому векселю.
Возвращает цену за 100 рублей номинальной стоимости для казначейского векселя.
Возвращает доходность по казначейскому векселю.
Возвращает величину амортизации актива для указанного или частичного периода при использовании метода сокращающегося баланса.
Возвращает внутреннюю ставку доходности для графика денежных потоков, не обязательно носящих периодический характер.
Возвращает чистую приведенную стоимость для денежных потоков, не обязательно носящих периодический характер.
Возвращает доход по ценным бумагам, по которым производятся периодические выплаты процентов.
Возвращает годовую доходность ценных бумаг, на которые сделана скидка (например, по казначейским векселям).
Возвращает годовую доходность ценных бумаг, по которым процент выплачивается в срок погашения.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
В этой статье описаны синтаксис формулы и использование функции ЧИСТВНДОХ в Microsoft Excel.
Описание
Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Чтобы рассчитать внутреннюю ставку доходности для ряда периодических денежных потоков, следует использовать функцию ВСД.
Синтаксис
Аргументы функции ЧИСТВНДОХ описаны ниже.
Значения Обязательный. Ряд денежных потоков, соответствующий графику платежей, приведенному в аргументе "даты". Первый платеж является необязательным и соответствует затратам или выплате в начале инвестиции. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение.
Даты Обязательный. График дат платежей, который соответствует платежам для денежных потоков. Даты могут быть в любом порядке. Дата должна быть введена с использованием функции ДАТА либо как результат других формул или функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем. .
Предп Необязательный. Величина, предположительно близкая к результату ЧИСТВНДОХ.
Замечания
В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.
Числа в аргументе "даты" усекаются до целых.
В большинстве случаев задавать аргумент "предп" для функции ЧИСТВНДОХ не требуется. Если этот аргумент опущен, то он полагается равным 0,1 (10 процентов).
Функция ЧИСТВНДОХ тесно связана с функцией ЧИСТНЗ. Ставка доходности, вычисляемая функцией ЧИСТВНДОХ — это процентная ставка, соответствующая ЧИСТНЗ = 0.
di = дата i-й (последней) выплаты;
d1 = дата 0-й выплаты (начальная дата);
Pi = сумма i-й (последней) выплаты.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция ЧИСТВНДОХ предназначена для расчета внутренней ставки доходности по денежным потокам, носящим непериодический характер, и возвращает соответствующее значение в процентах. Для расчета аналогичной ставки по периодическим платежам в Excel предусмотрена функция ВСД.
Расчет рентабельности инвестиций по денежным потокам в Excel
Примеры использования функции ЧИСТВНДОХ в Excel.
Пример 1. Инвестор получил кредит в размере 100000 руб. под 13% годовых и вложил средства в проект, финансовые потоки которого занесены в таблицу Excel. Определить, является ли его вложение прибыльным.
Вид таблицы данных:
Рассчитаем внутреннюю ставку доходности по формуле:
- B3:B12 – диапазон ячеек со значениями финансовых выплат. Первое значение соответствует затратам (вложение 100000 рублей в инвестиционный проект), поэтому указано в виде отрицательного числа.
- A3:A12 – диапазон ячеек с датами, соответствующие моментам совершения выплат (введены с использованием функции ДАТА).
Данный инвестиционный проект является убыточным, поскольку кредит был получен по ставке 13%.
Расчет внутренней ставки доходности облигаций в Excel
Пример 2. В таблице Excel внесены данные об операциях с облигацией (покупка, выплата купонов, погашение). Выплаты производились несистематично. Определить прибыль и внутреннюю ставку доходности облигации за указанный период.
Вид таблицы данных:
Для определения прибыли используем формулу:
То есть, определяем разницу между суммарными выплатами по купонам + погашением и расходами на приобретение облигации:
Для определения ставки доходности используем формулу:
На практике подобные расчеты имеют смысл для достаточно больших периодов времени, поскольку на рынке зачастую возникают кризисные явления. Оптимальный период расчета доходности инвестиционного портфеля с использованием функции ЧИСТВНДОХ – от 5 лет и более.
Читайте также: