Как рассчитать дюрацию в excel

Обновлено: 22.01.2025

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

На рабочем столе своего компьютера открываем файл "Мониторинг_облигаций.xlsx". Далее запускаем торговую систему Quik. Выгружаем из него данные. Для этого в любом месте таблицы со списком облигаций нажимаем правой кнопкой мыши и выбераем пункт "Вывод через DDE сервер". Через пару минут кликните "Остановить вывод". Произойдёт обновление информации в экселе.

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

Приступим к работе с экселем. Кликаем один раз мышкой по ячейке Q1 и вводим название колонки "Кол-во купонных выплат". Торговая система Квик показывает нам информацию только о сумме ближайшей купонной выплаты. Для расчёта доходности облигации к погашению нам нужно обладать сведениями о количестве предстоящих купонных выплат.

Напомню, второй этап расчёта доходности облигации: (Сумма всех предстоящих купонных выплат с учётом текущего - 13%) + Номинал = Сумма к погашению. Вся методика вычисления опубликована на этой странице моего блога .

Для определения количества предстоящих купонных выплат мы разделим количество дней до погашения на длительность купонного периода. Кликаем мышкой на ячейку Q2. Ставим знак "=". Далее нажимаем на ячейку М2 (дни до погашения). Ставим знак деления / (косая черта). Кликаем на ячейку F2 (длительность купона) и нажимаем Enter для подтверждения ввода.

Формула выглядит так: = M2/F2

Результатом вычислений стало дробное значение, но количество купонов это всегда целое число. Например, 1, 2, 3 и так далее. Нам необходимо округлить полученный результат. Для этого мы воспользуемся функцией (командой) ОКРУГЛВВЕРХ.

Она работает следующим образом =ОКРУГЛВВЕРХ(Число;Количество знаков). Число в нашем примере мы получаем с помощью формулы M2/F2, а округлить его нужно до 0 (нуля) знаков после запятой. Таким образом формула округления полученного результата будет выглядеть так:

Вводим её в ячейку Q2 и нажимаем Enter. Эксель округлил результат до целого числа. Проверим. До погашения облигации осталось 29 дней. Длительность купона 92 дня. Действительно, за этот период мы получим только одну выплату.

Формула работает правильно. Применяем её ко всем последующим строкам. Кликаем один раз на ячейку Q2. Она будет выделена зелёной рамкой. Нажимаем левой кнопкой мыши на нижний левый угол ячейки и продолжая удерживать левую кнопку тянем курсор вниз до конца списка. Затем отпускаем. Произойдёт автоматическое заполнение результатами вычисления созданной нами формулы по всем строкам.

Переходим к следующей колонке. Кликаем один раз на ячейку R1 и вводим название столбца "Сумма всех купонов без налога".

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

Ключевые выводы

Понимание длительности Маколея

Дюрация Маколея для бескупонной облигации равна времени до погашения облигации. Проще говоря, это вид ценных бумаг с фиксированным доходом, по которым не выплачиваются проценты на основную сумму. Чтобы компенсировать отсутствие купонной выплаты, бескупонная облигация обычно торгуется с дисконтом, что позволяет трейдерам и инвесторам получать прибыль в дату погашения, когда облигация выкупается по ее номинальной стоимости.

Формула длительности Маколея

Вычисление дюрации Маколея может быть сложным и имеет ряд вариаций, но основная версия рассчитывается путем сложения купонной выплаты за период, умноженной на время до погашения, деленной на 1, плюс доходность за период, увеличенная до времени. к зрелости. Полученное значение затем добавляется к общему количеству периодов, умножается на номинальную стоимость облигации, деленную на 1, плюс доходность за период, увеличенная до общего количества периодов. Полученное значение делится на текущую цену облигации.

Расчет продолжительности Маколи в Excel

Предположим, у вас есть двухлетняя бескупонная облигация с номинальной стоимостью 10 000 долларов США, доходностью 5%, и вы хотите рассчитать дюрацию в Excel.

• В столбцах A и B щелкните столбцы правой кнопкой мыши, выберите «Ширина столбца» и измените значение на 30 для обоих столбцов.
• Затем введите « Номинальная стоимость » в ячейку A2, «Доходность» в ячейку A3, «Ставка купона» в ячейку A4, «Время до погашения» в ячейку A5 и «Продолжительность Маколея» в ячейку A6.
• Введите «= 10000» в ячейку B2, «= 0,05» в ячейку B3, «= 0» в ячейку B4 и «= 2» в ячейку B5.
• В ячейке B6 введите формулу «= (B4 + (B5 * B2) / (1 + B3) ^ 1) / ((B4 + B2) / (1 + B3) ^ 1)».

Поскольку по бескупонной облигации имеется только один денежный поток и не выплачиваются купоны, итоговая дюрация Маколея составляет 2.

дюрации Маколея и используется для расчета изменения продолжительности облигации и цены на каждый процент изменения доходности к погашению.

Ключевые выводы

• Формула модифицированной дюрации показывает изменение стоимости облигации по отношению к изменению ее доходности к погашению.
• В Excel формула встроена в функцию MDURATION.
• Следуйте этому пошаговому примеру, чтобы заполнить формулу.

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

Что вам говорит измененная продолжительность

Модифицированная дюрация определяет изменение стоимости ценной бумаги с фиксированным доходом по отношению к изменению доходности к погашению. Формула, используемая для расчета модифицированной дюрации облигации, представляет собой дюрацию Маколея, деленную на 1, плюс доходность облигации к погашению, деленную на количество купонных периодов в году.

В Excel формула, используемая для расчета модифицированной дюрации облигации, встроена в функцию MDURATION. Эта функция возвращает измененную продолжительность Маколея для ценной бумаги, предполагая, что номинальная стоимость составляет 100 долларов США.

Пример расчета модифицированной продолжительности в Excel

Например, вы хотите рассчитать модифицированную дюрацию Маколея 10-летней облигации с датой погашения 1 января 2020 г., датой погашения 1 января 2030 г., годовой купонной ставкой 5% и годовой доходность к погашению 7%. Купон выплачивается ежеквартально.

Чтобы узнать измененную продолжительность, выполните следующие действия в Excel:

1. Сначала щелкните правой кнопкой мыши столбцы A и B.
2. Затем щелкните левой кнопкой мыши по ширине столбца и измените значение на 32 для каждого из столбцов, затем нажмите кнопку ОК. Введите «Описание облигации» в ячейку A1, затем выберите ячейку A1 и нажмите клавиши CTRL и B вместе, чтобы выделить заголовок жирным шрифтом. Затем введите «Данные облигации» в ячейку B1, затем выберите ячейку B1 и нажмите клавиши CTRL и B вместе, чтобы выделить заголовок жирным шрифтом.
3. Введите «Дата погашения облигации» в ячейку A2 и «1 января 2020 года» в ячейку B2. Затем введите «Дата погашения облигации» в ячейку A3 и «1 января 2030 года» в ячейку B3. Затем введите «Годовая купонная ставка » в ячейку A4 и «5%» в ячейку B4. В ячейке A5 введите «Годовая доходность к погашению», а в ячейке B5 введите «7%». Поскольку купон выплачивается ежеквартально, частота составляет 4. Введите «Частота выплаты купона» в ячейку A6 и «4» в ячейку B6.
4. Затем введите «Основа» в ячейку A7 и «3» в ячейку B8. В Excel базис не является обязательным, и выбранное значение рассчитывает измененную продолжительность с использованием фактических календарных дней для периода начисления и предполагает, что в году 365 дней.
5. Теперь вы можете решить модифицированную дюрацию Маколея для облигации. Введите «Измененная продолжительность» в ячейку A8 и формулу «= MDURATION (B2, B3, B4, B5, B6, B7)» в ячейку B8. В результате модифицированная длительность составляет 7,59.

Формула, используемая для расчета процентного изменения цены облигации, представляет собой изменение доходности к погашению, умноженное на отрицательное значение модифицированной дюрации, умноженное на 100%. Следовательно, если процентные ставки увеличиваются на 1%, цена облигации, как ожидается, упадет на 7,59% = [0,01 * (-7,59) * 100%].

Довольно часто случаются ситуации, когда доходность к погашению и другие важные параметры облигации необходимо рассчитать самостоятельно. Встроенные функции EXCEL позволяют это сделать довольно легко. Единственное, что необходимо знать – это данные о будущих выплатах купонов и предполагаемые условия сделки.

Получаем данные

Из таблицы купонов нужны только даты и суммы выплат.

Для прогнозирования доходности к погашению также потребуется:

• Цена покупки (обычно выражается в процентах от номинала)
• НКД (накопленный купонный доход)
• Номинал облигации
• Брокерская комиссия

Калькулятор доходности к погашению в EXCEL

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

В примере использованы данные еврооблигации Московского кредитного банка со сроком погашения в 2024 году (тикер: CBOM-24).

В EXCEL для этого существует довольно удобная функция XIRR (ЧИСТВНДОХ), которая позволяет быстро и просто считать доходность к погашению. Функция использует две колонки данных: колонка «Даты» и колонка «Денежный поток».

Доходность к погашению (Yield to maturity, YTM) – это IRR (ВНД) денежного потока инвестора, покупающего облигацию. При этом предполагается, что облигация держится до погашения.

Кроме доходности к погашению калькулятор считает:

• Купонную доходность
• Доходность при погашении (ценовая доходность)
• Модифицированную доходность (сумма купонной доходности и ценовой доходностей)
• Дюрацию
• Модифицированную дюрацию

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

Учет налогов

Для некоторых облигаций предусмотрен налог на купон. Часто инвестор должен заплатить НДФЛ при погашении. Шаблон позволяет учесть такие ситуации. Для этого выберете, платится ли НДФЛ за купон и платится ли НДФЛ при погашении:

Самостоятельное изменение калькулятора

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

UPDATE 23.01.2020

Добавлен калькулятор доходности к погашению для еврооблигаций с учетом валютной переоценки и соответствующего налога.

В новой версии калькулятора IRR считается не только в валюте, но и в рублях с учетом курсовой разницы и налога на валютную переоценку. Для того, чтобы воспользоваться этими возможностями, необходимо вставить курсы валюты в колонку "Курс ЦБ РФ". Курсы могут быть историческими данными, если вы проверяете реальный IRR уже погашенной облигации, или прогнозными, если необходимо определить доходность к погашению при каком-то сценарии изменения курса валюты.

Файлы для скачивания

Калькулятор доходности к погашению облигаций в EXCEL
Файл: ytm_calculator.xlsx
Размер: 36439 байт

Калькулятор доходности к погашению для еврооблигаций в EXCEL
Файл: ytm_eurobonds.xlsx
Размер: 58522 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Читайте также:

  
• Как в ворде вернуться на предыдущее место просмотра
  
• 1с зависает при проведении документа
  
• Антураж для фотошопа вид сверху
  
• Гифки не работают дискорд
  
• Удалить ключ касперского из реестра