Доходность облигаций в Excel: как рассчитать

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

Расчёт доходности облигации в Excel — это не «магия», а аккуратная бухгалтерия денежных потоков. Разберём, какие данные понадобятся, как выгрузить параметры выпуска — купон, НКД, даты, оферту, — и какие формулы превращают этот набор в цену покупки и купонный доход. Отдельно пройдём доходность к погашению через ЧИСТВНДОХ и XIRR, учтём комиссии брокера и НДФЛ, построим график платежей. В финале — сценарии с офертой и досрочным погашением в одной таблице и типичные ошибки новичка, из-за которых расчёт завышает результат.

Что понадобится для расчёта доходности облигации в Excel

Расчёт доходности облигации в Excel — это не «магия», а аккуратная бухгалтерия денежных потоков. Чтобы результат имел смысл, нужен полный набор входных данных по конкретному выпуску: номинал, цена покупки (в процентах от номинала или в рублях), размер купона и периодичность выплат, дата покупки и дата погашения (или оферты), накопленный купонный доход (НКД) на момент сделки, комиссия брокера и ставка НДФЛ. Без любого из этих элементов расчёт превращается в оценку «на глаз».

Технически достаточно любой версии Excel или бесплатного LibreOffice Calc. Понадобятся функции работы с датами (ДАТА, ДАТАКУПОНДО, ДАТАКУПОНПОСЛЕ), финансовые функции (ЧИСТВНДОХ, XIRR, ДОХОД) и стандартные арифметические операции. Для сценариев с офертой удобно использовать таблицу с несколькими строками-вариантами.

Практический минимум — три листа: «Параметры» (вводные по выпуску), «Денежные потоки» (даты и суммы), «Результат» (формулы доходности и график). Такое разделение снижает риск ошибки: вы всегда видите, откуда взялась каждая цифра. И главное — не смешивайте в одной ячейке ручные допущения и формулы: любое «примерно» должно быть вынесено в отдельную ячейку с пометкой, иначе через месяц вы не вспомните, откуда взялось число.

Отдельно оговорюсь о данных. Витрина FINTAL и сайты брокеров показывают доходность к погашению, но она рассчитана по их методике и часто без учёта вашей личной комиссии и налога. Excel нужен именно для того, чтобы получить цифру «под себя», а не усреднённую по рынку. Это особенно важно для выпусков с амортизацией долга и плавающим купоном — там стандартная формула с витрины может давать заметное расхождение с фактическим результатом.

Как выгрузить параметры выпуска: купон, НКД, даты, оферта

Источник данных — официальные документы эмитента и биржевая информация. Порядок действий:

1. Найдите карточку выпуска на сайте Московской биржи или в приложении брокера. Там указаны ISIN, номинал, дата погашения, дата ближайшей оферты, размер купона и периодичность выплат.
2. Скачайте файл со списком купонов (обычно доступен как XLSX или CSV) — в нём перечислены все даты выплат и суммы на одну облигацию. Это удобнее, чем вбивать вручную: при 20–40 выплатах ручной ввод почти гарантирует ошибку.
3. Возьмите НКД из карточки сделки или из отчёта брокера на дату покупки. НКД — это часть купона, которую вы компенсируете продавцу; она входит в цену покупки, но не является вашим доходом.
4. Уточните условия оферты в решении о выпуске или проспекте эмитента: дата, цена выкупа (обычно 100% номинала плюс НКД), порядок предъявления. Без этого сценарий с досрочным выкупом построить нельзя.
5. Зафиксируйте комиссию брокера — тариф на покупку и продажу (в процентах от суммы сделки или фиксированная сумма).

Дальше приведите даты к формату Excel (иначе функции не распознают их). Проверьте, что все даты — реальные даты, а не текст: функция ЕЧИСЛО или формат ячейки «Дата» помогут отловить проблему. Для купонных выплат постройте столбец дат и столбец сумм на одну бумагу; для количества бумаг умножьте на объём позиции.

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

Формулы Excel для купонного дохода и цены покупки

Начните с цены покупки. Если вы покупаете по цене в процентах от номинала, сумма сделки на одну бумагу = номинал × цена% ÷ 100 + НКД. Например, при номинале 1 000 ₽ и цене 98% получаем 980 ₽ плюс НКД — скажем, 20 ₽, итого ≈ 1 000 ₽. В Excel это две ячейки: =B2*C2/100 (цена без НКД) и =D2+E2 (полная сумма с НКД). Комиссия брокера добавляется отдельной строкой: =F2*ставка_комиссии.

Купонный доход за период владения считается как сумма всех выплат, попавших в интервал между датой покупки и датой продажи/погашения. Удобно использовать СУММЕСЛИМН по столбцу дат: =СУММЕСЛИМН(суммы; даты; ">"&дата_покупки; даты; "<="&дата_продажи). Так вы не пропустите выплату и не захватите лишнюю. Если бумага куплена между купонными датами, первый купон вы получите полностью, но часть его уже оплатили продавцу через НКД — это учтено в цене покупки, отдельно вычитать не нужно.

Для проверки купонной доходности используйте функцию ДОХОД (YIELD): она принимает дату расчёта, дату погашения, ставку купона, цену и частоту выплат. Результат — годовая доходность в процентах, но без учёта вашей комиссии и налога. Это ориентир, а не итог. И помните: купонная доходность не равна полной доходности — разницу даёт дисконт к номиналу и реинвестирование купонов.

Отдельная строка — реинвестирование. Если вы не тратите купоны, а покупаете на них новые бумаги, доходность растёт; в простой таблице это моделируется добавлением купонных выплат к сумме следующей покупки. Не усложняйте: для первого расчёта достаточно допущения «купоны выводятся», а сценарий с реинвестированием сделайте отдельным листом.

Расчёт доходности к погашению через ЧИСТВНДОХ и XIRR

Самый надёжный способ — построить денежный поток и применить функцию ЧИСТВНДОХ (в английской версии XIRR). Она решает уравнение: сумма приведённых к дате расчёта платежей равна нулю, и возвращает годовую ставку. Синтаксис: =ЧИСТВНДОХ(значения; даты; [предположение]). Значения — все платежи по счёту: покупка со знаком минус, купоны и погашение со знаком плюс. Даты — соответствующие даты. Предположение можно не указывать, Excel подставит 10%.

Пошагово:

1. В столбец A внесите даты: дата покупки, даты всех купонов, дата погашения (или продажи).
2. В столбец B — суммы: покупка (цена + НКД + комиссия) с минусом, купоны с плюсом, погашение (номинал + последний купон) с плюсом.
3. В свободной ячейке вызовите =ЧИСТВНДОХ(B2:B20; A2:A20). Получите годовую доходность в долях — отформатируйте как процент.

Функция чувствительна к знаку: если все платежи одного знака, она вернёт ошибку. Проверьте, что покупка точно отрицательная. Второй частый сбой — текстовые даты: преобразуйте их через ДАТАЗНАЧ или введите заново.

Сравните результат с цифрой на витрине брокера. Расхождение в 0,3–1 процентный пункт нормально: витрина часто считает по номиналу без комиссии и без налога, а вы — по фактическим деньгам. Если расхождение больше, ищите ошибку в датах или пропущенный купон. Для наглядности рядом постройте вторую формулу — ДОХОД — и покажите разницу: так вы увидите, сколько «съедают» комиссия и налог.

Учёт комиссий брокера и НДФЛ в таблице доходности

Комиссия брокера — это прямой вычет из результата. В таблице заведите отдельные ячейки для тарифа на покупку и на продажу (или погашение). Если тариф — процент от суммы сделки, формула простая: =сумма_сделки*тариф. При покупке на 100 000 ₽ и тарифе 0,3% комиссия составит 300 ₽ — на первый взгляд мелочь, но на горизонте года при коротких сделках она может съесть заметную часть купонного дохода. Включайте комиссию в денежный поток со знаком минус в дату сделки — тогда ЧИСТВНДОХ учтёт её автоматически.

НДФЛ с купонов. С 2021 года купоны всех облигаций, включая ОФЗ, облагаются НДФЛ по обычной ставке — прежнего освобождения гособлигаций больше нет (ст. 217 НК РФ в ред. ФЗ-102 от 01.04.2020). На практике брокер удерживает налог при выплате купона, поэтому в таблице удобнее сразу уменьшать каждую купонную выплату на ставку НДФЛ: =купон*(1-ставка). Если вы считаете «грязную» доходность до налога, добавьте отдельную строку «налог» и вычтите её из итогового потока — иначе результат будет завышен.

Налог на доход от продажи облигации считается с разницы между ценой продажи (без НКД) и ценой покупки (без НКД). В таблице это отдельная строка в дату продажи. Льгота на долгосрочное владение (ЛДВ) распространяется на акции и паи, а не на облигации, поэтому рассчитывать на освобождение купонного дохода или дисконта по облигациям не стоит. Для ИИС-3 доход по облигациям может освобождаться от налога при соблюдении минимального срока владения — но это отдельный сценарий, и его лучше вынести на отдельный лист с пометкой «ИИС».

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

Как построить график денежных потоков по облигации

График нужен не для красоты, а чтобы увидеть структуру выплат: когда приходят деньги, какова их доля относительно вложений, нет ли «провала» между купонами. Простейший вариант — столбчатая диаграмма по датам: по оси X — даты, по оси Y — суммы платежей. Положительные значения (купоны, погашение) и отрицательные (покупка) на одной диаграмме сразу показывают, когда вложения возвращаются.

Порядок:

1. Выделите два столбца — даты и суммы денежного потока.
2. Вставка → Диаграмма → Гистограмма (столбчатая).
3. Отформатируйте ось дат, чтобы подписи не наезжали друг на друга: выберите формат «MMM YYYY» или разверните подписи.
4. Добавьте линию накопленного потока: постройте вспомогательный столбец с нарастающим итогом (=СУММ($B$2:B2)) и выведите его на вторую ось как график.

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

Если строите сценарии с офертой, добавьте на график вертикальную линию в дату оферты — визуально видно, где поток обрывается. Не пытайтесь совместить на одном графике больше двух-трёх выпусков: диаграмма станет нечитаемой. Лучше сделайте отдельный лист сравнения с ключевыми цифрами (доходность, дюрация, дата оферты), а график оставьте для одного выпуска.

Сценарии с офертой и досрочным погашением в одной таблице

Оферта — это право (или обязанность) эмитента выкупить бумагу по заранее объявленной цене в определённую дату. Для расчёта это означает, что у вас не один денежный поток, а несколько: до оферты, до погашения, с продажей по оферте или без. В Excel удобно сделать таблицу-матрицу: строки — сценарии, столбцы — ключевые параметры.

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

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

Практический совет: сделайте на листе «Сценарии» переключатель (ячейка с выпадающим списком) и формулу ВПР или ИНДЕКС/ПОИСКПОЗ, которая подставляет нужный поток. Так вы сможете менять сценарий одним кликом и сразу видеть доходность — без копирования листов.

Ошибки новичка при расчёте доходности в Excel

Первая и самая частая — путаница между НКД и купоном. НКД входит в цену покупки, но не является доходом; если вы прибавите его к купонам, доходность завысится. Вторая — забытая комиссия: без неё расчёт выглядит оптимистичнее реальности, особенно на коротких горизонтах. Третья — налог на купоны не учтён: с 2021 года он есть у всех облигаций, включая ОФЗ, и «грязная» доходность может отличаться от «чистой» на заметную величину.

Четвёртая ошибка — неверные даты. Excel хранит даты как числа, но если ячейка отформатирована как текст, ЧИСТВНДОХ вернёт ошибку или посчитает неверно. Пятая — все платежи одного знака: покупка должна быть отрицательной, иначе функция не найдёт решение. Шестая — игнорирование оферты: расчёт «до погашения» для бумаги с офертой может дать цифру, которая никогда не реализуется.

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

Что делать: перед финальным расчётом сверьте денежный поток с отчётом брокера — суммы и даты должны совпадать. Прогоните контрольный пример: купите одну бумагу, дождитесь купона и сравните фактическое поступление с прогнозом. Расхождение в 1–2% нормально (округления), больше — ищите ошибку. И держите таблицу с явными пометками допущений: через полгода вы забудете, что заложили в ячейку, и рискуете принять решение на основе устаревших вводных.

Информационный сервис. Не является финансовой рекомендацией. Окончательные условия уточняйте на сайте банка.