Учет акций и облигаций в портфеле excel
Составление инвестиционного портфеля по Марковицу для чайников
В данном обзоре мы представим простой пример составления оптимального инвестиционного портфеля по Марковицу.
Введение в портфельную теорию
Портфельная теория Марковица была обнародована в 1952 году. Позже автор получил за нее Нобелевскую премию.
Целью модели является составление оптимального портфеля, то есть с минимальным риском и максимальной доходностью.
Как правило, решается две задачи: максимизация доходности при заданном уровне риска и минимизация риска при минимально допустимом значении доходности.
Доходность портфеля измеряется как средневзвешенная сумма доходностей входящих в него бумаг.
wi — доля инструмента в портфеле;
ri — доходность инструмента.
Риск отдельного инструмента оценивается как среднеквадратичное (стандартное) отклонение его доходности. Для расчета общего риска портфеля необходимо отразить совокупное изменение рисков отдельного инструмента и их взаимное влияние (через ковариации и корреляции — меры взаимосвязи).
σi — стандартное отклонение доходностей инструмента;
kij — коэффициент корреляции между I,j-м инструментом;
Vij — ковариация доходностей i-го и j-го финансового инструмента;
n — количество финансовых инструментов в рамках портфеля.
Таким образом, в рамках правильно подобранного портфеля риски снижаются за счет обратной корреляции инструментов. При этом устраняются не только специфические риски инструмента, но и снижается систематический (рыночный) риск.
Для составления портфеля решается оптимизационная задача. При этом в базовом виде использование заемных средств не предполагается, то есть сумма долей активов равняется единице, а доли эти положительны.
Минимизируем риск при минимально допустимом уровне доходности
Максимизируем доходность при заданном уровне риска
Пример расчетов в Excel
Оптимальный портфель содержит различные группы активов — акции, облигации, товарные фьючерсы и т.д. Так легче подобрать инструменты с отрицательной корреляцией и минимизировать риски.
В нашем примере будет использован более простой подход — составление портфеля из нескольких американских акций. Для эффекта диверсификации возьмем представителей различных секторов — платежную систему VISA, ритейлера Macy’s, технологичного гиганта Apple и телеком AT&T.
Сразу отмечу, что это лишь пример. Все эмитенты интересны, но для грамотного составления портфеля необходимо учитывать фундаментальные показатели, включая рыночные мультипликаторы, оценивать технические уровни для входа в позицию.
Этап 1. Выкачиваем котировки. Необходимо взять данные минимум за год. В нашем примере были взяты ежемесячные цены закрытия с 31.06.2017 по 31.05.2018.
Этап 2. Считаем доходности по каждой бумаге. Для простоты не будем учитывать эффект дивидендов.
Считаем доходность за каждый месяц по формуле натурального логарифма. К примеру, доходность VISA за май 2018 = LN(C14/C13)
Для расчета ожидаемой доходности берем среднее значение за рассматриваемый период. В нашем случае это год. Ожидаемая доходность VISA = СРЗНАЧ(G3:G14)
Получаем отрицательную доходность AT&T, и убираем бумагу из портфеля. Сразу отмечу, что в этом заключается недостаток модели, ведь просевшие ранее акции в перспективе могут развернуться.
Этап 3. Расчет риска каждой акции. Производится по формуле стандартного отклонения. К примеру, риск VISA =СТАНДОТКЛОН(G3:G14)
Указываем окне входной интервал — ежемесячные доходности акций, а в опции «Группирование» выбираем «по столбцам».
В результате получаем ковариационную матрицу.
Этап 5. Расчет общей доходности портфеля. Для начала установим произвольные доли бумаг в портфеле. Они положительны, их сумма равна 1.
Считаем средневзвешенное значение доходностей отдельных акций. Воспользуемся формулой G15*G23+H15*H23+I15*I23
Этап 6. Расчет общего риска портфеля. Производится по формуле массива КОРЕНЬ(МУМНОЖ(МУМНОЖ(G23:I23;G20:I22); E20:E22))
Этап 7. Портфель минимального риска.
Речь идет о долях отдельных бумаг в портфеле. Для начала необходимо определить минимальный уровень допустимой доходности портфеля (rp). Возьмем rp >= 3,2%.
При оценке долей акций воспользуемся надстройкой в Excel «Поиск решений», для этого выбираем Главное меню → «Данные» → «Поиск решений».
В надстройке «Поиск решений» необходимо ввести ссылку на ячейку, которую следует оптимизировать (общий риск портфеля, минимизируем), ввести какие параметры необходимо изменять (доли акций) и ограничения. Введем ограничения на весовые значения коэффициентов у акций: сумма долей акций должна быть равна 1 и сами доли должны иметь положительный знак.
В результате имеем портфель с 73% долей VISA и 27% долей Macy’s.
Визуально портфель выглядит так:
Этап 8. Портфель максимальной доходности.
Для начала необходимо определить максимальный уровень допустимого риска портфеля (σp). Возьмем σp 30
Последние новости
Рекомендованные новости
Итоги торгов. Внешний фон не оставил покупателям шанса
Неделя после краха, или девелоперы под ударом
Взгляд на золото в 2022
Рынок США. Омикрон бродит по Европе
Нефть с утра падает на 2% из-за новых локдаунов
Совкомфлот объявляет байбэк. Акции будут выкупаться с рынка
Акции, которые выросли на 50% и имеют потенциал еще +50%
Нефть Brent снижается на 5%. В чем дело
Адрес для вопросов и предложений по сайту: bcs-express@bcs.ru
* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.
Информация не может рассматриваться как публичная оферта, предложение или приглашение приобрести, или продать какие-либо ценные бумаги, иные финансовые инструменты, совершить с ними сделки. Информация не может рассматриваться в качестве гарантий или обещаний в будущем доходности вложений, уровня риска, размера издержек, безубыточности инвестиций. Результат инвестирования в прошлом не определяет дохода в будущем. Не является рекламой ценных бумаг. Перед принятием инвестиционного решения Инвестору необходимо самостоятельно оценить экономические риски и выгоды, налоговые, юридические, бухгалтерские последствия заключения сделки, свою готовность и возможность принять такие риски. Клиент также несет расходы на оплату брокерских и депозитарных услуг, подачи поручений по телефону, иные расходы, подлежащие оплате клиентом. Полный список тарифов ООО «Компания БКС» приведен в приложении № 11 к Регламенту оказания услуг на рынке ценных бумаг ООО «Компания БКС». Перед совершением сделок вам также необходимо ознакомиться с: уведомлением о рисках, связанных с осуществлением операций на рынке ценных бумаг; информацией о рисках клиента, связанных с совершением сделок с неполным покрытием, возникновением непокрытых позиций, временно непокрытых позиций; заявлением, раскрывающим риски, связанные с проведением операций на рынке фьючерсных контрактов, форвардных контрактов и опционов; декларацией о рисках, связанных с приобретением иностранных ценных бумаг.
Приведенная информация и мнения составлены на основе публичных источников, которые признаны надежными, однако за достоверность предоставленной информации ООО «Компания БКС» ответственности не несёт. Приведенная информация и мнения формируются различными экспертами, в том числе независимыми, и мнение по одной и той же ситуации может кардинально различаться даже среди экспертов БКС. Принимая во внимание вышесказанное, не следует полагаться исключительно на представленные материалы в ущерб проведению независимого анализа. ООО «Компания БКС» и её аффилированные лица и сотрудники не несут ответственности за использование данной информации, за прямой или косвенный ущерб, наступивший вследствие использования данной информации, а также за ее достоверность.
Таблица для расчета доходности облигаций
Возник у меня как-то вопрос: насколько корректно отображается доходность к погашению у облигаций в приложениях брокера и на различных сервисах наподобие РУСБОНДС. И, как оказалось, действительно реальная доходность может отличаться от указанной, а иногда даже быть отрицательной. Да, да, вы не ослышались. Об этом подробнее расскажу ниже. А сначала продемонстрирую таблицу, благодаря которой я пришел к такому выводу.
Преимущество данной таблицы заключается в том, что вам необходимо заполнить только желтые ячейки:
И всё! Остальное все таблица сделает за вас: покажет название облигации, номинал, цену, дату погашения, НКД, купон, периодичность выплаты и даже дату оферты (если она есть). Данные подтягиваются с сайта Московской биржы. Ну, и самое главное — таблица рассчитает реальную доходность с учетом НДФЛ и без него, с учетом комиссии и без нее. Но я рекомендую смотреть на доходность с учетом комиссии и НДФЛ. В этом-то и смысл этой таблицы. Если вы снимите галочку «с учетом комиссии», то она не будет учитываться. Помимо этого, для облигации сформируется график денежного потока. И качестве бонуса — есть визуализация денежного потока.
В процессе работы с этой таблицей у меня для некоторых облигаций получалась отрицательная реальная доходность. Я начал разбираться и оказалось, что так и есть, таблицу не обманешь:). Дело в том, что с 01.01.2021 купоны по облигациям стали облагаться налогом. И из налогооблагаемой базы почему-то не вычитают потраченные средства на НКД — накопленный купонный доход. То есть если я покупаю облигацию за пару дней до выплаты купона — допустим 30 Р, то я дополнительно к цене облигации еще плачу НКД — допустим 29 Р. Справочно по колхозному: НКД равен 0 в день выплаты купона, затем каждый день он увеличивается на определенное значение, пока в день выплаты купона он не станет равным величине купона. В этот день он опять обнуляется и так далее до следующей выплаты.
Так вот получается я отдал 29 Р, а получил 30 Р — 13% налога, то есть всего 26,1 Р. Если последующих выплат еще много, то данная «несправедливость» не значительно уменьшит вашу доходность, а если эта выплата была последней (то есть в день погашения облигации), то получается вы вложите больше, чем вам вернется. То есть получите отрицательную реальную доходность!
Именно поэтому данная таблица имеет преимущество перед сторонними сервисами, которые не учитывают нюансы налогооблажения и комиссию брокера.
Сделаем вывод: облигацию выгодно покупать сразу после выплаты купона, когда НКД минимален. А таблица вам в этом поможет.
Эксельки. Здесь хвалятся своими наработками в «Гугл-таблицах»
Таблица для учета инвестиций
Я продал квартиру и вложил деньги в фондовый рынок. Чтобы отслеживать изменения по портфелю, попробовал несколько публичных сервисов — платных и бесплатных, но все они показались неудобными, либо с ежемесячной оплатой. Вернулся к старому доброму «Экселю». На разработку таблицы потратил две недели.
Таблица фиксирует все мои активы: акции, облигации, кэш, фонды. Активы записаны в количестве, рублях и долларах по среднему курсу. Распределены по секторам экономики, доля каждого актива и каждого сектора измеряется в рублях и в процентах от общей стоимости портфеля.
По каждой бумаге просчитана будущая дивидендная/купонная доходность на основе публичных данных и прогнозов. Все в процентах и деньгах. Это удобно: я точно знаю, на какую сумму дивидендов могу рассчитывать в будущем году, и могу контролировать ДД по долларовой и рублевой части портфеля независимо. Мой портфель имеет перекос в сторону дивидендных акций, поэтому мне важно понимать, сколько я заработаю за следующий год, а курсовая стоимость акций меня не интересует совсем, поэтому я ее не отслеживаю (бумаги не продаю, а только покупаю).
На основе данных в таблице построены графики: по типам активов (акции роста, акции дивидендов, защитные активы, бонды), разбивка по секторам экономики (я визуал), по валютам всех активов.
Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.
В таблице есть дополнительные вкладки: планы по будущим покупкам (по какой цене планирую какой актив купить с обоснованием), контроль поставлений дивов / купонов (дата, сумма, эмитент), динамика капитала с графиком, подборка коротких бондов, которые я использую для финансовой подушки, портфель сына и план по пассивному доходу на 15 лет вперед, по которому я следую.
Таблицу прикладываю, но все данные по эмитентам, суммам и стоимости акций я изменил, так как мой портфель непубличный.
Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.
Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.
В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.
Благодаря таблице я точно знаю, сколько денег заработаю в следующий год. Могу отследить исторические данные по портфелю: сколько ДД принес, например, октябрь этого года, и могу сравнить его с октябрем прошлого года и оценить прибавку в ДД.
Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.
Таблицу постоянно дорабатываю. Сейчас планирую добавить столбец, который бы просчитывал рост дивдоходности эмитента за то время, что я его держу, и средний рост в год.
Как я создал собственную гугл-таблицу для учета капитала
После того как 10 лет пользовался разными программами
Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.
Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.
Мне было сложно увидеть полную картину активов, потому что у разных финансовых посредников нет единой формы и стандарта отчетов. Ни одна из программ, которыми я пользовался, не подходила мне на сто процентов: в основном приходилось слишком долго возиться с добавлением новых бумаг, подтягиванием нужных котировок.
Поэтому я разработал собственную отчетную форму в «Гугл-таблицах»: туда я импортирую отчеты разных брокеров и записываю активы, чтобы понимать, что происходит с моим капиталом, и видеть достоверный бюджет поступлений на месяц вперед.
Как работает таблица
Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу без использования скриптов.
Чтобы таблица была не просто очередным шаблоном, я дал ей собственное имя — SilverFir: Investment Report. Название говорит о том, что это инвестиционный отчет, а silver fir отсылает к разновидности вечнозелёных деревьев.
Прежде чем пошагово расписать, как пользоваться шаблоном гугл-таблицы, необходимо сделать несколько важных пояснений.
Форматы данных. В настройках таблицы указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа — точка, то есть 105.1 — правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в качестве разделителя.
Даты указаны в формате «год-месяц-день», то есть «2020-03-11» — 11 марта 2020 года.
Разделитель в формулах при американских региональных настройках — запятая, в отличие от российского формата — точки с запятой. Если вы будете переносить формулы в какие-то свои таблицы, имейте это в виду.
Как победить выгорание
Основные параметры, используемые в таблице. Чтобы заполнить таблицу и корректно ею пользоваться, необходимо знать следующие параметры:
Знание экселя и регулярных выражений не помешает
Актуальные цены многих активов подтягиваются со сторонних сайтов с помощью функции ImportXML. Для разных активов используются разные сайты. Например, данные по актуальной стоимости квартиры на Арбате я беру с сайта «Домофонд». И тут две проблемы.
Во-первых, если «Домофонд» обновит структуру сайта, формула может слететь, потому что она обращается к конкретной части страницы. На момент публикации статьи все формулы работают, но со временем что-то может поменяться.
Во-вторых, если вы захотите подтягивать актуальную цену квартиры в другом районе или городе, формулу нужно будет переписать.
Если вам нужна будет помощь с этим, я постараюсь отвечать в комментариях к статье.
Пошаговое руководство по заполнению
По ссылке откроется сразу ваша копия таблицы — можно редактировать данные прямо в ней. Никто другой не имеет доступа к данным в вашей копии.
Представим, что у вас есть несколько типов активов: два вклада в разных валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец». Разберемся, как получить полную картину по сбережениям.
Начнем с вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.
Пусть это будет вклад 50 000 Р под 5,8% годовых, открытый 22 марта 2020 года сроком на год — до 22 марта 2021 года. Разнесем данные по столбцам таблицы:
Как следить за бюджетом
Если ваш вклад не в рублях, то таблица автоматически рассчитает начальные затраты в рублях в столбце «Цена покупки, Р » по курсу на дату открытия вклада.
Индивидуальный инвестиционный счет (ИИС). Допустим, что на ИИС куплено 100 облигаций федерального займа ОФЗ-ПД 26225. Код этой ценной бумаги — SU26225RMFS1. Облигации куплены 3 сентября 2018 года по цене 89% от номинала.
Код ценной бумаги можно посмотреть в отчете брокера или на сайте биржи
Разнесем данные по столбцам таблицы, которые надо заполнить вручную:
Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:
Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:
Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» — это, строго говоря, дата первой покупки актива.
Разнесем данные по столбцам таблицы:
Разнесем данные по столбцам таблицы:
Что делать после заполнения данных
После того как вы внесете исходные данные, сразу можно увидеть работу формул. Данные начнут скачиваться, и таблица автоматически заполнится недостающими параметрами.
Теперь можно узнать следующие показатели по каждому из активов:
Дополнительно вручную можно указать категории и классы активов, если вы хотите смотреть распределение и по ним. Автоматическое скачивание возможно реализовать только на гугл-скриптах.
Анализ сводных показателей портфеля
Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.
«Данные» — это главная вкладка, куда вносятся все исходные. Светло-голубым выделены ячейки, которые надо заполнить вручную. Также на этой вкладке рассчитывается прибыль и убыток по позиции, дата и размер ближайшего поступления от актива.
«Валюты» — полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.
«Посредники» — отчетная вкладка, которая показывает распределение сумм по брокерам и весовое значение процента капитала. Еще она показывает количество бумаг у каждого брокера и расчетный ежемесячный доход, также этот доход отображается в процентах годовых.
На этой вкладке можно оценить, насколько успешен тот или иной счет, потому что отображаются изменения в рублях с момента покупки.
«Классы активов» — здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описания классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.
«Покупки» — это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.
«Капитал» — на этой вкладке отображается текущая дата и две совокупных стоимости всех активов: стоимость покупки и текущая рыночная стоимость портфеля в рублях. Эта вкладка реализована с помощью формул, а формулы не могут сами копироваться в другие ячейки — для создания истории придется вручную копировать эти данные на строчку ниже.
«Капитал график» — визуализирует данные с вкладки «Капитал».
«Идентификаторы» — в графическом виде отображает распределение по бумагам в таблице.
«Отчет» — сводный отчет о планируемых поступлениях на три месяца вперед в рублях, то есть сумма купонов, арендных платежей. Также вкладка дает информацию о ближайших выплатах на 30 дней вперед и назад, а еще — о лидерах роста и падения вместе с историей капитала.
Запомнить
Тебе не придётся напрягаться с учётом инвестиций, если у тебя их нет!
(картинка, с умным негром)
Спасибо за материал, взял на заметку пару интересных моментов.
Для себя тоже веду Excel-таблицу, сначала она была такая же сложная, потом постепенно приходил к выводу, что та или иная аналитика избыточна. В итоге пришел к варианту буквально с двумя страницами: на первой веду все операции с активами (покупка, продажа, купоны/дивиденды), на второй автоматически собирается вся необходимая мне сводная информация:
1. Текущая доля каждого класса актива и отклонение от желаемой доли.
2. Текущая доля распределения по валютам и по отраслям.
3. Текущая доля каждого актива относительно всего портфеля.
4. Текущая стоимость каждого актива в рублевом эквиваленте (тоже подтягивается автоматически из разных источников).
5. Количество.
6. Годовая доходность каждого актива.
7. Общая стоимость и годовая доходность всего портфеля.
8. Денежный поток по каждому активу (сколько купонов/дивидендов получено).
9. Общая прибыль в рублевом выражении с учетом изменения цены актива и всех полученных по нему купонов/дивидендов.
А, ну еще есть одна страница, на которой красивый график, показывающий прогнозную стоимость моего портфеля на 20 лет вперед, если я буду продолжать дисциплинированно инвестировать дальше. И на этом же графике вторая кривая, показывающая фактический результат. Очень мотивирует.
Анализ операций с ценными бумагами с Microsoft Excel
2.2.4 Автоматизация анализа купонных облигаций
Для анализа облигаций с фиксированным купоном в ППП EXCEL реализованы 15 функций (табл. 2.4). Все функции этой группы предварительной установки специального дополнения – «Пакет анализа» (см. приложение 1).
Таблица 2.4
Функции для анализа облигаций с фиксированным купоном
Наименование функции
Англоязычная версия
ДАТАКУПОНДО(дата_согл; дата_вступл_в_силу; частота; [базис])
дата_вступл_в_силу; частота; [базис])
ДНЕЙКУПОНДО(дата_согл; дата_вступл_в_силу; частота; [базис])
ДНЕЙКУПОНПОСЛЕ (дата_согл; дата_вступл_в_силу; частота; [базис])
ДНЕЙКУПОН(дата_согл; дата_вступл_в_силу; частота; [базис])
ЧИСЛКУПОН(дата_согл; дата_вступл_в_силу; частота; [базис])
ДЛИТ(дата_согл; дата_вступл_в_силу; ставка; доход; частота; [базис])
МДЛИТ(дата_согл; дата_вступл_в_силу; ставка; доход; частота; [базис])
ЦЕНА(дата_согл; дата_вступл_в_силу; ставка; доход; погашение; частота; [базис])
НАКОПДОХОД(дата_вып; дата_след_ куп; дата_согл; ставка; номинал; частота; [базис])
ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; [базис])
ДОХОДПЕРВНЕРЕГ(дата_согл; дата_ пог; дата_вып; дата_перв_ куп; ставка; цена; погашение; ÷àñòîòà;[базис])
ДОХОДПОСЛНЕРЕГ(дата_согл; дата_ пог; дата_вып; дата_посл_ куп; ставка; цена; погашение; ÷àñòîòà; [базис])
ЦЕНАПЕРВНЕРЕГ(дата_согл; дата_ пог; дата_вып; дата_перв_куп; ставка; доход; погашение; ÷àñòîòà; [базис])
ЦЕНАПОСЛНЕРЕГ(дата_согл; дата_ пог; дата_вып; дата_посл_куп; ставка; доход; погашение; ÷àñòîòà; [базис])
Рассмотрим технологию применения этих функций на реальном примере из практики российского рынка ОВВЗ.
Дата выпуска ОВВЗ – 14.05.1996 г. Дата погашения – 14.05.2011 г. Купонная ставка – 3%. Число выплат – 1 раз в год. Средняя курсовая цена на дату операции – 37,34. Требуемая норма доходности – 12% годовых.
На рис. 2.8 приведена исходная ЭТ для решения этого примера с использованием функций рассматриваемой группы.
Рис. 2.8. Исходная ЭТ для решения примера 2.9
В приведенной ЭТ исходные (неизменяемые) характеристики займа содержатся в блоке ячеек В2.В8. Значения изменяемых переменных задачи вводятся в ячейки Е2.Е4. Вычисляемые с помощью соответствующих функций ППП EXCEL параметры ОВВЗ, наименования которых содержатся в блоке А10.А22, будут помещаться по мере выполнения расчетов в ячейки блока В10.В22. Руководствуясь рис. 2.8 подготовьте исходную таблицу и заполните ее исходными данными. Приступаем к проведению анализа и рассмотрению функций.
Функции для определения характеристик купонов
Первые 6 функций (табл. 2.4) предназначены для определения различных технических характеристик купонов облигаций и имеют одинаковый набор аргументов :
дата_согл – дата приобретения облигаций (дата сделки);
дата_вступл_в_силу – дата погашения облигации;
частота – количество купонных выплат в году (1, 2, 4);
базис – временная база (необязательный аргумент).
В нашем примере эти аргументы заданы в ячейках E2, B4 и B8 соответственно (рис. 2.8).
Функция ДАТАКУПОНДО() вычисляет дату предыдущей (т.е. до момента приобретения облигации) выплаты купона. С учетом введенных исходных данных функция, заданная в ячейке В10, имеет вид:
=ДАТАКУПОНДО(E2; B4; B8) (Результат: 14.05.96).
И первый же блин вышел комом! В данном случае можно считать, что функция выдала ошибочный результат, поскольку вычисленное значение является датой выпуска облигации в обращение и никаких выплат в тот день быть не могло. Очевидно, что для более корректной реализации этой функции разработчикам следовало бы предусмотреть задание еще одного аргумента – даты выпуска. Однако, утешив свое самолюбие, признаем, что если бы такая выплата производилась, по условиям займа она действительно должна была бы состояться именно 14.05.96.
Функция ДАТАКУПОНПОСЛЕ() вычисляет дату следующей (после приобретения) выплаты купона. Формат функции в ячейке В11:
=ДАТАКУПОНПОСЛЕ(E2; B4; B8) (Результат: 14.05.97).
Нетрудно заметить, что полученная дата совпадает со сроком выплаты первого купона, как и следует из условий примера.
Функция ДНЕЙКУПОНДО() вычисляет количество дней, прошедших с момента начала периода купона до момента приобретения облигации. В нашем примере эта функция задана в ячейке В12:
=ДНЕЙКУПОНДО(E2; B4; B8) (Результат: 304).
Таким образом, с момента начала периода купона до даты приобретения облигации (18 марта 1997 года) прошло 304 дня.
Функция ДНЕЙКУПОН() вычисляет количество дней в периоде купона. По условиям выпуска облигаций валютного займа Минфина России купоны выплачиваются 1 раз в году. Таким образом, число дней в периоде купона должно быть равным 360 (финансовый год), что подтверждается результатом применения функции (ячейка В13):
=ДНЕЙКУПОН(E2; B4; B8) (Результат: 360).
В случае необходимости проведения расчетов с точным числом дней в году достаточно просто указать необязательный аргумент » базис «, равным 1 или 3:
=ДНЕЙКУПОН(E2; B4; B8; 3) (Результат: 365).
Следует отметить, что функция правильно работает и в случае високосного года.
Функция ДНЕЙКУПОНПОСЛЕ() вычисляет количество дней, оставшихся до даты ближайшей выплаты купона (с момента приобретения облигации). В нашем примере эта функция задана в ячейке В14:
=ДНЕЙКУПОНПОСЛЕ(E2; B4; B8) (Результат: 56).
Таким образом, периодический доход по облигации будет получен через 56 дней после ее приобретения.
Функция ЧИСЛКУПОН() вычисляет количество оставшихся выплат (купонов), с момента приобретения облигации до срока погашения. Функция задана в ячейке В15:
=ЧИСЛКУПОН(E2; B4; B8) (Результат: 15).
Согласно полученному результату, с момента приобретения облигации и до срока ее погашения будет произведено 15 выплат, что полностью соответствует условиям займа.
Функции для определения дюрации
Следующие две функции (табл. 2.4) позволяют определить одну из важнейших характеристик облигаций – дюрацию.
Функция ДЛИТ() вычисляет дюрацию D и имеет два дополнительных аргумента:
ставка – купонная процентная ставка (ячейка В6);
доход – норма доходности (ячейка Е4).
Заданная в ячейке В17, функция с учетом размещения исходных данных имеет вид:
=ДЛИТ(E2; B4; B6; E4; B8) (Результат: 9,39).
Таким образом, средневзвешенная продолжительность платежей по 15-летней ОВВЗ седьмой серии со сроком обращения составит 9 лет и около 140 дней (0,39 ´ 360).
Функция МДЛИТ( ) реализует модифицированную формулу для определения дюрации MD и имеет аналогичный формат (ячейка В18):
=МДЛИТ(E2; B4; B6; E4; B8) (Результат: 8,39).
Полученный результат на целый год меньше. Напомним, что для бескупонных облигаций дюрация всегда равна сроку погашения.
погашение – стоимость 100 единиц номинала при погашении (ячейка В7);
доход – требуемая норма доходности (ячейка Е4);
ставка – годовая ставка купона (ячейка В6)
цена – цена, уплаченная за 100 единиц номинала (ячейка Е3).
Функции для определения курсовой цены и доходности облигации
Функция ЦЕНА() позволяет определить современную стоимость 100 единиц номинала облигации (т.е. курс), исходя из требуемой нормы доходности на дату ее покупки. В нашем примере она задана в ячейке В19 и имеет следующий формат:
=ЦЕНА(E2; B4; B6; E4; В7; B8) (Результат: 40,06).
Полученная величина 40,06 представляет собой цену облигации, которая обеспечивает нам требуемую норму доходности – 12% (ячейка Е3). Поскольку ее величина меньше средней цены покупки в 34,75 (ячейка Е2), мы также получим дополнительную прибыль приблизительно в 5,30 на каждые 100 единиц номинала при погашении облигации.
Функция ДОХОД() вычисляет доходность облигации к погашению (yield to maturity – YTM ). Данный показатель присутствует практически во всех финансовых сводках, публикуемых в открытой печати и специальных аналитических обзорах. В рассматриваемом примере функция для его вычисления задана в ячейке В20:
=ДОХОД(E2; B4; B6; E3; B7; B8) (Результат: 13,63%).
Полученный результат несколько выше требуемой нормы доходности и в целом подтверждает прибыльность данной операции.
Ячейка В21 содержит формулу для расчета текущей (на момент совершения сделки) доходности Y – отношение купонной ставки (ячейка В6) к цене приобретения облигации (ячейка Е3):
=В6/Е3 (Результат: 8,63%).
Таким образом, текущая доходность операции составляет 8,63%, что значительно выше купонной ставки, однако ниже доходности к погашению.
Последним показателем, рассчитанным в электронной таблице (ячейка В22), является величина накопленного купонного дохода НКД на дату сделки. Для его вычисления используется функция НАКОПДОХОД( ) :
=НАКОПДОХОД(B3;B11;E2;B6;B7;B8) (Результат: 2,53).
Отметим, что в качестве одного из аргументов здесь используется дата ближайшей (после заключения сделки) выплаты купона (ячейка В11). Данную функцию также удобно использовать при определении суммы дохода, подлежащей налогообложению, которая представляет собой разность между накопленным процентом на момент погашения или перепродажи ценной бумаги и накопленным процентом на момент ее приобретения.
Полученная в результате таблица должна иметь вид рис. 2.9.
Рис. 2.9. Результаты анализа ОВВЗ седьмой серии
Очистите таблицу от исходных данных (блоки ячеек В2.В8 и Е2.Е4) и сохраните на магнитном диске в виде шаблона BONDCOUP.XLT.
Осуществите проверку работы шаблона на следующем примере.
Дата выпуска ОВВЗ – 14/05/1993 г. Дата погашения – 14/05/1999 г. Купонная ставка – 3%. Число выплат – 1 раз в год. Средняя курсовая цена на дату операции – 85,83. Требуемая норма доходности – 10% годовых.
Полученная в результате таблица должна иметь вид рис. 2.10.
Рис. 2.10. Решения примера 2.10
Большинство из рассмотренных функций можно использовать и для анализа облигаций с плавающей ставкой купона (ОГСЗ, ОФЗ и др.). Однако следует отметить, что результаты расчета доходности к погашению будут справедливы только для текущей ставки купона (т.е. для периода между двумя купонными выплатами).
Следует отметить, что рассмотренные в данном параграфе фундаментальные зависимости справедливы для любых ценных бумаг, отражающих отношения займа.