Как рассчитать дюрацию в excel
На рынке облигаций под выпуклостью понимается соотношение между ценой и доходностью. На графике эта зависимость нелинейна и образует длинную наклонную U-образную кривую. Облигация с высокой степенью выпуклости будет испытывать относительно резкие колебания при изменении процентных ставок.
Ключевые выводы
- Выпуклость – это отношение между ценой и доходностью, она нелинейна и имеет U-образную форму (выпуклость).
- Облигации с высокой выпуклостью испытывают большие движения при изменении процентных ставок.
- В Excel нет функции выпуклости облигации, но ее можно аппроксимировать с помощью формулы с несколькими переменными.
- Выпуклость считается лучшим показателем процентного риска, чем дюрация.
Длительность связи против выпуклости
Дюрация облигации – это изменение цены облигации относительно изменения процентных ставок. Более высокая дюрация означает, что цена облигации будет двигаться в большей степени в направлении, противоположном движению процентных ставок. Если дюрация низкая, облигация будет демонстрировать меньшее движение.
Выпуклость измеряет чувствительность дюрации облигации к изменению ставок. Выпуклость считается лучшим показателем процентного риска. Дюрация предполагает, что связь между ценами облигаций и процентными ставками является линейной, в то время как выпуклость включает другие факторы, создавая наклон.
Отрицательная выпуклость возникает, когда дюрация облигации увеличивается по мере увеличения ставок. Это означает, что цена облигации будет падать быстрее, если ставки вырастут, чем если бы они упали. Облигация имеет положительную выпуклость, если ее дюрация увеличивается, а процентная ставка падает.
Хотя в Microsoft Excel нет функции выпуклости облигаций, ее можно аппроксимировать с помощью формулы с несколькими переменными.
Упрощение формул выпуклости
Стандартная формула выпуклости включает временной ряд денежных потоков и довольно сложный расчет. Это не может быть легко воспроизведено в Excel, поэтому необходима более простая формула:
Выпуклость = ((P +) + (P-) – (2Po)) / (2 x ((Po) (изменение Y) ²)))
- (P +) – цена облигации при понижении процентной ставки.
- (P-) – цена облигации при увеличении процентной ставки.
- (Po) – текущая цена облигации.
- «Изменение Y» – это изменение процентной ставки, представленное в десятичной форме. «Изменение Y» также можно описать как эффективную дюрацию облигации.
На первый взгляд это может показаться непростым, но это самая простая формула для использования в Excel.
Как рассчитать выпуклость в Excel
Чтобы вычислить выпуклость в Excel, начните с обозначения другой пары ячеек для каждой из переменных, указанных в формуле. Первая ячейка действует как заголовок (P +, P, Po и эффективная продолжительность), а вторая содержит цену, которая представляет собой информацию, которую вы должны собрать или рассчитать из другого источника.
Предположим, что значение (Po) находится в ячейке C2, (P +) находится в C3, а (P-) находится в C4. Эффективная продолжительность находится в ячейке B5.
В отдельной ячейке введите следующую формулу: = (C3 + C4 – 2 * C2) / (2 * C2 * (B5 ^ 2))
Это должно обеспечить эффективную выпуклость связи. Более высокий результат означает, что цена более чувствительна к изменениям процентных ставок. Увеличение выпуклости означает, что системный риск, которому подвержен портфель, увеличивается.
Одной из важнейших характеристик, используемых в практических расчетах по денежным потокам, является дюрация, или продолжительность (duration). Дюрация представляет собой средневзвешенный срок по анализируемой последовательности платежей, где в качестве весов используется приведенная стоимость этих платежей (денежных потоков):
где PVt — платеж, относящийся ко времени t.
Одним из важнейших свойств дюрации является то, что она представляет собой интегральную характеристику денежных потоков, рассроченных во времени, — средний срок выплаты или получения причитающихся сумм. Например, если у компании много обязательств и выплаты по ним предполагаются в разные периоды времени, то средневзвешенная дюрация обязательств компании может быть принята как оценка среднего срока погашения обязательств.[1]
Таким образом, условно заменим множество рассроченных во времени обязательств всего одним платежом со сроком, равным дюрации.
Рассмотрим пример. Корпоративная облигация выпущена на 3 года с купоном 10% в год и выплатой купонов раз в год. По окончании 3-го года она будет погашена по 100%-ной номинальной стоимости. Ставка дисконтирования – 12% годовых. Какова дюрация облигации?
Решение. Облигация генерирует последовательность платежей (денежных потоков):
3-й год = 100% + 10% (купон + погашение) = 110%.
Иногда в целях упрощения в качестве весов в этой формуле используют сами платежи, а не их приведенные стоимости, т.е. в нашем примере 10; 10; 110 вместо 8,93; 7,97 и 78,3. Это может быть оправданно, если ставка дисконтирования мало отличается от купона, а ее точное определение вызывает сложности теоретического или практического характера.
Довольно часто случаются ситуации, когда доходность к погашению и другие важные параметры облигации необходимо рассчитать самостоятельно. Встроенные функции 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 байт
Для скачивания файлов необходимо зарегистрироваться или авторизоваться
дюрации Маколея и используется для расчета изменения продолжительности облигации и цены на каждый процент изменения доходности к погашению.
Ключевые выводы
- Формула модифицированной дюрации показывает изменение стоимости облигации по отношению к изменению ее доходности к погашению.
- В Excel формула встроена в функцию MDURATION.
- Следуйте этому пошаговому примеру, чтобы заполнить формулу.
Вы можете использовать Microsoft Excel для расчета модифицированной дюрации облигации с учетом следующих параметров: дата расчета, дата погашения, купонная ставка, доходность к погашению и частота.
Что вам говорит измененная продолжительность
Модифицированная дюрация определяет изменение стоимости ценной бумаги с фиксированным доходом по отношению к изменению доходности к погашению. Формула, используемая для расчета модифицированной дюрации облигации, представляет собой дюрацию Маколея, деленную на 1, плюс доходность облигации к погашению, деленную на количество купонных периодов в году.
В Excel формула, используемая для расчета модифицированной дюрации облигации, встроена в функцию MDURATION. Эта функция возвращает измененную продолжительность Маколея для ценной бумаги, предполагая, что номинальная стоимость составляет 100 долларов США.
Пример расчета модифицированной продолжительности в Excel
Например, вы хотите рассчитать модифицированную дюрацию Маколея 10-летней облигации с датой погашения 1 января 2020 г., датой погашения 1 января 2030 г., годовой купонной ставкой 5% и годовой доходность к погашению 7%. Купон выплачивается ежеквартально.
Чтобы узнать измененную продолжительность, выполните следующие действия в Excel:
- Сначала щелкните правой кнопкой мыши столбцы A и B.
- Затем щелкните левой кнопкой мыши по ширине столбца и измените значение на 32 для каждого из столбцов, затем нажмите кнопку ОК. Введите «Описание облигации» в ячейку A1, затем выберите ячейку A1 и нажмите клавиши CTRL и B вместе, чтобы выделить заголовок жирным шрифтом. Затем введите «Данные облигации» в ячейку B1, затем выберите ячейку B1 и нажмите клавиши CTRL и B вместе, чтобы выделить заголовок жирным шрифтом.
- Введите «Дата погашения облигации» в ячейку A2 и «1 января 2020 года» в ячейку B2. Затем введите «Дата погашения облигации» в ячейку A3 и «1 января 2030 года» в ячейку B3. Затем введите «Годовая купонная ставка » в ячейку A4 и «5%» в ячейку B4. В ячейке A5 введите «Годовая доходность к погашению», а в ячейке B5 введите «7%». Поскольку купон выплачивается ежеквартально, частота составляет 4. Введите «Частота выплаты купона» в ячейку A6 и «4» в ячейку B6.
- Затем введите «Основа» в ячейку A7 и «3» в ячейку B8. В Excel базис не является обязательным, и выбранное значение рассчитывает измененную продолжительность с использованием фактических календарных дней для периода начисления и предполагает, что в году 365 дней.
- Теперь вы можете решить модифицированную дюрацию Маколея для облигации. Введите «Измененная продолжительность» в ячейку A8 и формулу «= MDURATION (B2, B3, B4, B5, B6, B7)» в ячейку B8. В результате модифицированная длительность составляет 7,59.
Формула, используемая для расчета процентного изменения цены облигации, представляет собой изменение доходности к погашению, умноженное на отрицательное значение модифицированной дюрации, умноженное на 100%. Следовательно, если процентные ставки увеличиваются на 1%, цена облигации, как ожидается, упадет на 7,59% = [0,01 * (-7,59) * 100%].
Дюрацию Маколея можно рассматривать как точку экономического баланса группы денежных потоков. Другой способ интерпретации статистики заключается в том, что это средневзвешенное количество лет, в течение которых инвестор должен поддерживать позицию в облигации до тех пор, пока приведенная стоимость денежных потоков по облигации не сравняется с суммой, уплаченной за облигацию.
Ключевые выводы
- Дюрация Маколея – это средневзвешенное время до погашения денежных потоков, полученных от облигации.
- Для облигации с нулевым купоном дюрация Маколея – это время, оставшееся до погашения.
- Продолжительность Маколея может быть сложно вычислить, но ее можно упростить с помощью Excel.
Понимание длительности Маколея
Проще говоря, дюрация Маколея – это время, которое потребуется инвестору, чтобы вернуть все свои вложенные деньги в облигацию в виде периодических процентов, а также погашения основной суммы долга. Дюрация Маколея измеряется в годах и представляет собой дюрацию долгового фонда, которая представляет собой не что иное, как средневзвешенную дюрацию Маколея долговых ценных бумаг в портфеле.
Цена облигации, срок погашения, купон и доходность к погашению – все это влияет на расчет дюрации. При прочих равных, по мере увеличения срока погашения увеличивается срок действия. По мере увеличения купона облигации ее дюрация уменьшается. По мере увеличения процентных ставок дюрация уменьшается, и чувствительность облигации к дальнейшему увеличению процентной ставки снижается. Другие факторы, такие как наличие фонда погашения, запланированная предоплата до погашения и резервы до отзыва, могут снизить дюрацию облигации.
Дюрация Маколея для бескупонной облигации равна времени до погашения облигации. Проще говоря, это вид ценных бумаг с фиксированным доходом, по которым не выплачиваются проценты на основную сумму. Чтобы компенсировать отсутствие купонной выплаты, бескупонная облигация обычно торгуется с дисконтом, что позволяет трейдерам и инвесторам получать прибыль в дату погашения, когда облигация выкупается по ее номинальной стоимости.
Формула длительности Маколея
Вычисление дюрации Маколея может быть сложным и имеет ряд вариаций, но основная версия рассчитывается путем сложения купонной выплаты за период, умноженной на время до погашения, деленной на 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.
Читайте также: