Реализация функции ЧИСТВНДОХ (XIRR) – расчет внутренней нормы доходности

В данной статье мы рассмотрим, что такое внутренняя норма доходности, какой экономический смысл она имеет, как и по какой формуле рассчитать внутреннюю норму доходности, рассмотрим некоторые примеры расчёта, в том числе при помощи формул MS Exel. Загрузить программу ВІ Демонстрации решений Аналитика бизнеса Оглавление Что такое внутренняя норма доходности? Внутренняя норма доходности (IRR — Internal Rate […]

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

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

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

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

Пример 1.

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

=ЧИСТВНДОХ(B3:B12;A3:A12)

Описание параметров:

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

Результат вычислений:

ЧИСТВНДОХ.

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

Описание

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

А что делать, если инвестор периодически пополняет счет или снимает средства?

На помощь нам приходит функция Excel XIIR или в русской версии ЧИСТВНДОХ.

Функция очень простая в использовании, но сложная для понимания. ЧИСТВНДОХ рассчитывает IRR, внутреннюю норму доходности при нерегулярных денежных потоках. Этот показатель часто используется при оценке привлекательности инвестиционных проектов. IRR — такая ставка дисконтирования, при которой совокупный денежный поток проекта равен нулю. Не буду вдаваться в подробности оценки проектов, сейчас не об этом. Как мы можем применить ЧИСТВНДОХ для расчета доходности нашего портфеля?

Для этого нам понадобятся вводные данные, а именно: сумма на начало периода, сумма на конец периода и суммы ввода/вывода средств с датами. Эти данные можно найти в брокерском отчете или отчете о движении денежных средств.

Ниже приведен пример. Сумма на начало периода — 100 тыс. руб. Сумма на счете на конец периода — 170 тыс. руб. Конечная сумма и вывод средств выписываются со знаком «-», начальная сумма и пополнения счета со знаком «+».

Знаки можно расставить наоборот, итоговый результат не изменится. Тут уже кому как удобнее.

Дата

Поток, руб.

31.12.2019

100000

28.02.2020

10000

02.05.2020

15000

18.09.2020

-20000

31.12.2020

15000

15.04.2021

30000

30.06.2021

-20000

30.09.2021

10000

20.10.2021

-170000

Далее прописываем функцию. Первый аргумент — наши денежные потоки, второй — соответствующие им даты, третий вводить не нужно.

Расчет ЧИСТВНДОХ

В итоге мы получили ответ — 13,08%. Функция считает сразу годовую доходность.

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

Расчет доходности за периодДоходность за период, а именно за 659 дней, составила 24,8%.

Итог

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

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

monetary aggregates 300x270.jpg.pagespeed.ce .hkbbsl97o  - Внутренняя норма доходности на excel

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

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

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

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

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

Неверный расчет доходности

Новичок в инвестициях задаст вполне предсказуемый вопрос: “А что тут считать то? Нужно разделить общую стоимость портфеля на общие вложения. Это и будет доходностью”. К сожалению, данная формула показывает лишь абсолютную доходность, которая никак не поможет вам в анализе своих инвестиций. Она работает только в том случае, если вы единовременно купили акции, а ровно через год посчитали доходность.

Но представьте следующие ситуации:

Ситуация №1. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 1000 рублей. Ежемесячно вы докупали ценные бумаги на 1000 рублей, но в последний месяц года (декабрь 2019) вы купили акций на 50 000 рублей. 

Ситуация №2. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 50000 рублей. Ежемесячно вы докупали ценные бумаги на 1000 рублей до конца года. 

Ситуация №3. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 1000 рублей. Ежемесячно в течение года вы докупали ценные бумаги на 1000 рублей, но в середине года, а именно в июле 2020 года, вы вложили 50 000 рублей.

ИТОГО: Во всех 3-х случаях вы вложили в фондовый рынок 61 000 рублей. Через год ваши вложения выросли до 75 000 рублей. Посчитаем доходность по формуле: Общая стоимость портфеля / общая сумма вложений. Я свел все данные расчета в табличную форму для наглядности.

Неправильный расчет доходности

Как видим, во всех трёх случаях доходность портфеля за год составила 22,95% годовых, что, конечно же, неверно. Кстати, приложение от брокера тоже считает неправильно. Ниже я приведу верный расчет и вы все поймете. 

Таблица значений в табличный документ (СКД)

Универсальные функции v8 Абонемент ($m)

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

1 стартмани

12.08.2020    21897    8    markivkin    4    

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

​Смотрите также​ + 0.000001; Иначе​ он саму функцию​​ ячейке.) , нажмите​​ если он отобразится.​

Описание

​ (01.01.2018 : 1.000.000​ 01.01.2019 (ровно год,​ 7% годовых. Рассчитаем​Часто IRR сравнивают в​ сутью сложных процентов).​.​ – значительно меньше,​ при открытии банковского​Эффективная процентная ставка​

Синтаксис

​ процентов, где Р​

​-10 000​ случае эта функция​

  • ​В этой статье описаны​​ Возврат Окр(ПСК *​ и аргументы.​ клавишу F3, щелкните​Рассмотрите следующие возможные​ ; 01.07.2018 :​ процентный доход =​ модифицированную внутреннюю норму​ процентами по банковскому​Примечание​Функция ЧИСТВНДОХ() похожа​ если имеется альтернатива​ счета.​ по кредиту отражает​ – начальная сумма​1-янв-08​ возвращает значение ошибки​ синтаксис формулы и​ 100, 2, 1);​

  • ​LEO037​​ требуемое имя, а​ причины и решения.​ -1.049.589,04) высчитывает 10,25212%​ 100.000) и 01.01.2018​ доходности:​ депозиту. Если проценты​. Сравнение графиков погашения​ на ВСД() (используется​ положить эту сумму​Сначала составим График платежей​ реальную стоимость кредита​ вклада.​2750​ #ЧИСЛО!.​ использование функции​ КонецЕсли; КонецЦикла; КонецЕсли;только​: У меня такая​

  • ​ затем нажмите ОК.​​Используется функция «ПЕРЕСЧЕТЕВРО»​ . Тоже, к​

Замечания

  • ​ – 01.07.2018 (181​Полученная норма прибыли в​ по вкладу выше,​ дифференцированными платежами и​ для расчета ставки​ в банк под​ по кредиту с​ с точки зрения​S = Р*(1+iэфф)​1-мар-08​Если хотя бы одно​ЧИСТВНДОХ​ он не очень​

  • ​ же проблема с​Ошибка в написании​

  • ​ без загрузки надстройки​ сожалению, нет точного​ дней, процентный доход​ три раза меньше​ то лучше поискать​ по аннуитетной схеме​ внутренней доходности, IRR),​ 15%). Для сравнения​

  • ​ учетом дополнительных расходов​ заёмщика, то есть​ – для простых​4250​ из чисел в​в Microsoft Excel.​ быстро выполняется​

  • ​ функцией ЧИСТРАБДНИ и​ имени функции​ «Инструменты для евро»​ совпадения.​ = 49.589,04).​ предыдущего результата. И​

  • ​ другой инвестиционный проект.​ приведено в этой​ в которой используется​ сумм, относящихся к​ (см. файл примера​

  • ​ учитывает все дополнительные​ процентов​30-окт-08​ аргументе “даты” не​Возвращает внутреннюю ставку доходности​wwall​ еще некоторыми.​

  • ​Исправьте написание имени.​Функция «ПЕРЕСЧЕТЕВРО» требует​pavel2018​На первом временном​ ниже ставки финансирования.​​ статье.​

  • ​ аналогичное дисконтирование регулярных​ разным временным периодам​ Лист Кредит).​ выплаты, непосредственно связанные​Так как финансовый результат​3250​ является допустимой датой,​ для графика денежных​: Чуть быстре заработает​Формула выглядит так​ Вставьте в формулу​ использования надстройки (Адрес.​: При ежедневной капитализации​ интервале функция ЧИСТВНДОХ​ Поэтому прибыльность данного​Быстро рассчитать IRR можно​Примечание.​ платежей, но на​ используют дисконтирование, т.е.​

    Уравнение

    ​Затем сформируем Итоговый​

    • ​ с кредитом (помимо​ S должен быть,​

    • ​15-фев-09​ то функция ЧИСТВНДОХ​

    • ​ потоков, которые не​ если В таблицеДат​

Пример

​=ЕСЛИ(D6>0;(ЧИСТРАБДНИ(C6;D6;S$7:S$18)-1);0)​ правильное имя функции​ Путь к объекту,​ процентного дохода в​ выдает правильный результат​ проекта сомнительна.​ с помощью встроенной​Эффективную ставку по​ основе номера периода​ приведение их к​ денежный поток заемщика​ платежей по самому​ по определению, одинаков​

​2750​

​ возвращает значение ошибки​

​ обязательно носят периодический​

​ будет приведено все​

​Загружаю файл, в​

​ путем выбора команды​

​ документу, файлу, странице​

​ течение 1-ого года​

​ = 10%, а​

​Значение IRR можно найти​

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

​ кредиту можно рассчитать​

​ выплаты, а не​

​ одному моменту времени.​

​ (суммарные платежи на​

​ кредиту). Такими дополнительными​

​ для обоих случаев,​

​1-апр-09​ #ЧИСЛО!.​

​ характер. Чтобы рассчитать​

support.office.com>

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

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

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

Описание аргументов:

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

Примечания:

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

Бухгалтерские программы

На текущий момент (осень 2021 года) можно найти отдельно написанные программки, предназначенные для проведения вычислений, которые мы описали выше. Сделали их после появления ФСБУ 25/2018 для тех, кто хотел перейти на стандарт раньше установленного срока. Как вы понимаете, особенной популярностью это ПО не пользовалось. Даже то, которое коннектилось с 1С.

Что же касается 1С, то корректно работающий функционал для расчетов с приведенной стоимостью есть в 1С МСФО (там все настроено на МСФО и даже после внедрения ФСБУ логика расчетов все равно может отличаться) и в 1С Управление предприятием 2 (Бюджетирование лизинговых платежей).

Следует ожидать, что разработчики 1С отреагируют на необходимость ведения учета по ФСБУ 25/2018 и к 01.01.2022 появится необходимый функционал и в Бухгалтерии. Как только он появится – мы сразу же подготовим подробную инструкцию со скриншотами по работе с ним.

Автор материала:
Оксана Лим

Автор материала:

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...