Чтобы рассчитать эффективную доходность к погашению с помощью функции ЧИСТВНДОХ (XIRR) в Excel, выполните следующие шаги: Важные замечания: Пример использования:
Предположим, вы покупаете облигацию и хотите учесть: Для каждого из этих событий укажите сумму и дату, затем примените функцию ЧИСТВНДОХ — и вы получите эффективную доходность к погашению. Этот метод позволяет учесть все особенности денежных потоков по облигации и получить более точный результат по сравнению с другими способами расчёта доходности.
Чтобы рассчитать эффективную доходность к погашению с помощью функции ЧИСТВНДОХ (XIRR) в Excel, выполните следующие шаги:
- Подготовьте данные:
- укажите все денежные потоки, связанные с облигацией: первоначальную стоимость покупки (с учётом накопленного купонного дохода — НКД), купонные выплаты и выплату номинальной стоимости при погашении;
- для каждого денежного потока укажите точную дату, когда он будет получен (или уплачен).
- Введите данные в Excel:
- создайте два массива: один для денежных потоков, другой для дат;
- первый платёж должен быть отрицательным и равен сумме, которую вы заплатите за облигацию (включая НКД);
- даты должны соответствовать каждому денежному потоку — от даты покупки до даты погашения.
- Примените функцию ЧИСТВНДОХ:
- выберите ячейку для результата;
- введите формулу =ЧИСТВНДОХ(значения; даты), где:
значения — диапазон ячеек с денежными потоками;
даты — диапазон ячеек с датами, соответствующими каждому денежному потоку.
- Получите результат:
- функция ЧИСТВНДОХ рассчитает внутреннюю норму доходности (IRR) для указанных денежных потоков и дат;
- результат будет представлен в виде годовой процентной ставки — это и есть эффективная доходность к погашению (YTM).
Важные замечания:
- функция ЧИСТВНДОХ учитывает неравномерность периодов, что важно для точного расчёта YTM;
- в отличие от функции ВСД (IRR), ЧИСТВНДОХ всегда выдаёт годовую ставку;
- при расчёте учитывайте, что купонные выплаты и другие денежные потоки должны быть указаны с корректными датами;
- результат не учитывает налоговые отчисления — для их учёта можно умножить доходность на 0,87 (учитывая НДФЛ в 13 %).
Пример использования:
Предположим, вы покупаете облигацию и хотите учесть:
- стоимость покупки (отрицательный денежный поток);
- купонные выплаты каждые полгода;
- погашение номинала в конце срока.
Для каждого из этих событий укажите сумму и дату, затем примените функцию ЧИСТВНДОХ — и вы получите эффективную доходность к погашению.
Этот метод позволяет учесть все особенности денежных потоков по облигации и получить более точный результат по сравнению с другими способами расчёта доходности.