Анализ операций с ценными бумагами с Microsoft Excel

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

Следующие две функции рассматриваемой группы – ДОХОДСКИДКА() и ЦЕНАСКИДКА(), также предназначены для анализа краткосрочных финансовых обязательств, реализуемых с дисконтом. Однако они обеспечивают большую гибкость при моделировании расчетов. Разница заключается прежде всего в том, что цена погашения, задаваемая соответствующим аргументом "погашение", может отличаться от номинала, т.е. от 100%. Кроме того, обе функции позволяют указать требуемую для расчетов временную базу, что избавляет от необходимости использования поправочных коэффициентов. Эти функции можно использовать для анализа практически любых видов краткосрочных обязательств, а также арбитражных операций. На рис. 3.3 приведен фрагмент электронной таблицы, решающий следующую задачу.

Пример 3.4

МКО Санкт-Петербурга серии SU32016GSPMO, выпущенные 23/10/96 со сроком погашения 14/05/97, приобретены 18/03/97 по курсу 96,19. Рассматривается возможность их продажи 05/05/97 по цене 99,60. Проанализировать эффективность операции для продавца.

Рис. 3.3. ЭТ для решения примера 3.4

Доходность операции (ячейка В14) рассчитана с помощью функции ДОХОДСКИДКА():

=ДОХОДСКИДКА(B6;B7;B8;B9;B10) (Результат: 26,96%).

Цена (ячейка В16) исходя из определенной в ячейке В15 скидки рассчитана как:

=ЦЕНАСКИДКА(B6;B7;B15;B9;B10) (Результат: 96,19).

Нетрудно заметить, что она соответствует норме дисконта (учетной ставке), полученной при покупке данного обязательства. Полный список используемых в ЭТ формул приведен в таблице 3.4.

Таблица 3.4  
Формулы ЭТ (рис. 3.3)

Ячейка	Формула
В14	=ДОХОДСКИДКА(B6;B7;B8;B9;B10)
В15	=СКИДКА(B6;B7;B8;B9;B10)
В16	=ЦЕНАСКИДКА(B6;B7;B15;B9;B10)
В17	=(B9/B8)^(365/(B7-B6))-1
В18	=B7-B6
В19	=B9-B8

Таким образом, проведение этой операции обеспечивает продавцу доходность в 26,96%. Эффективная доходность при этом составит 30,33%. Отметим, что реальная эффективность сделки будет ниже, так как арбитражные операции подлежат налогообложению. Продолжим анализ.

Определим доходность этой облигации при условии, что продавец будет хранить ее до погашения.

Введите в ячейку В7: 14/05/97.

Введите в ячейку В9: 100.

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

Очистив блок ячеек В5.В9 от исходных данных, получаем новый вариант шаблона для анализа краткосрочных обязательств, продаваемых с дисконтом. Сохраните полученный шаблон на магнитном диске под именем SH_BOND2.XLT.

Осуществим проверку работоспособности шаблона на данных примера 3.3. Полученная в результате таблица ЭТ иметь следующий вид (рис. 3.4).

Рис. 3.4. Анализ бескупонных облигаций (шаблон II)

Определение цены (курсовой стоимости) краткосрочной бескупонной облигации, соответствующей требуемой норме доходности, с использованием функций ЦЕНАКЧЕК() или ЦЕНАСКИДКА() связано с рядом неудобств. Как следует из табл. 3.1, реализуемые функциями алгоритмы расчета цены Р предполагают использование нормы скидки d (т.е. показателя, отражающего позицию эмитента), а не нормы доходности Y, которой оперирует инвестор. Данная проблема в среде ППП EXCEL может быть решена двумя путями:

• преобразованием при проведении вычислений нормы доходности Y в эквивалентную по величине учетную ставку d (см. соотношения (3.13) и (3.15));
• использованием специального инструмента "Подбор параметра".

Рассмотрим указанные способы более подробно. Сущность первого сводится к реализации соотношения (3.13) в виде отдельной формулы ППП EXCEL и использования полученной величины в качестве аргумента "скидка" в функциях ЦЕНАКЧЕК() или ЦЕНАСКИДКА().

Данный метод можно было бы реализовать путем небольшой модификации разработанных ранее шаблонов. Однако более простым решением является разработка отдельного универсального шаблона и последующего его использования для определения стоимости подобных обязательств. Один из вариантов такого шаблона приведен на рис. 3.5. Формулы, используемые в шаблоне, приведены в табл. 3.5.

Рис. 3.5. Шаблон для определения цены краткосрочной облигации

Таблица 3.5  
Формулы шаблона

Ячейка	Формула
В14	=(365*B8)/(365+B18*B8)
В15	=ДОХОДСКИДКА(B6;B7;B16;B9;B10)
В16	=ЦЕНАСКИДКА(B6;B7;B14;B9;B10)
В17	=(B9/B16)^(365/(B7-B6))-1
В18	=B7-B6
В19	=B9-B16

Формула, осуществляющая расчет эквивалентной норме доходности учетной ставки, задана в ячейке В14. Руководствуясь рис. 3.5 и табл. 3.5 сформируйте данный шаблон и сохраните его на магнитном диске под именем SH_BONDP.XLT. Осуществим проверку работоспособности шаблона на решении следующей задачи.

Пример 3.5

Рассматривается возможность приобретения облигаций внутреннего займа г. Москвы серии МФ73300155 со сроком погашения 20/08/97 на дату 22/04/97. Требуемая доходность равна 36,18% годовых. Какова приемлемая стоимость облигации для инвестора?

Осуществите ввод исходных данных в шаблон. Полученная в результате таблица должна соответствовать рис. 3.6.

Рис. 3.6. Определение цены облигации

Таким образом, предельный курс облигации, обеспечивающий получение требуемой нормы доходности в 36,18%, равен 89,37.

Второй способ определения цены (использование специального инструмента "Подбор параметра") более эффективен и не требует разработки специальных шаблонов, или модификаций предыдущих. Вместе с тем, его применение предъявляет повышенные требования к необходимой точности проводимых вычислений.

Использование инструмента "Подбор параметра"

Инструмент "Подбор параметра" удобно применять в тех случаях, когда требуется определить некоторое входное значение, обеспечивающее получение заранее известного результата.

Покажем технику применения инструмента "Подбор параметра" на решении примера 3.5. При этом воспользуемся ранее разработанным шаблоном SH_BOND2. Осуществите загрузку шаблона SH_BOND2 и введите исходные данные примера 3.5. Для определения цены облигации выполните следующую последовательность действий.

1. Введите в ячейку В8 некоторое число, являющееся приблизительным значением цены. В подобных задачах удобно задавать начальное приближение равным 100 (т.е. – максимальный курс), хотя вы можете указать любое число от 1 до 100.
2. Сделайте активной ячейку В14, содержащую формулу расчета доходности (т.е. функцию ДОХОДСКИДКА()). Выберите в основном меню тему "Сервис" пункт "Подбор параметра". Результатом этих действий должно стать появление диалогового окна (рис. 3.7).
3. Введите в поле "Значение" величину нормы доходности: 0,3618.
4. Введите в поле "Изменяя значение ячейки": В8.
5. Нажмите кнопку "ОК" или клавишу ENTER.

Результатом выполнения указанных действий будет появление диалогового окна "Результат подбора параметра", содержащего результаты вычислений (рис. 3.8).

Рис. 3.7. Диалоговое окно "Подбор параметра"

Рис. 3.8. Диалоговое окно "Результат подбора параметра"

Если текущее значение, приведенное в диалоговом окне, в точности совпадает с заданным (либо полученный результат вас устраивает), решение найдено. Нажмите кнопку "ОК" или клавишу ENTER.

В случае, если значения отличаются, сбросьте полученное решение, нажав кнопку "Отмена" и попробуйте увеличить точность вычислений. Эта операция выполняется в теме меню "Сервис", пункте "Параметры", подпункте "Вычисления", путем ввода соответствующего значения в поле "Относительная погрешность". Установленное по умолчанию значение погрешности равно 0,001.

Нетрудно заметить, что этой точности вычислений недостаточно для решения нашей задачи, так как процентная ставка задается величиной с 4 знаками после запятой. Точное решение большинства подобных задач (в т.ч. и рассматриваемой) достигается установкой погрешности, равной 0,00001.

При использовании инструмента "Подбор параметра" следует помнить, что изменяемая ячейка должна содержать число, а не формулу. При этом на нее должна ссылаться формула, для которой осуществляется подбор параметра (т.е. формула в ячейке, указываемой в поле "Изменяя значение ячейки" диалогового окна "Подбор параметра").

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

3.3 Краткосрочные бумаги с выплатой  процентов в момент погашения

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

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

Анализ доходности краткосрочных сертификатов

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

С учетом введенных ранее обозначений, абсолютный размер дохода по сертификату S может определен, как:

, (3.24)

где r – ставка по сертификату; N – номинал; t – срок погашения в днях; B – временная база.

Соответственно годовая доходность к погашению Y, исчисленная по простым процентам, будет равна:

. (3.25)

Из (3.24) и (3.25) следует, что если обязательство размещено по номиналу и держится до срока погашения, его доходность будет равна указанной в контракте ставке процентов (т.е. Y = r).

Если сертификат продается (покупается) между датами выпуска и погашения, абсолютная величина дохода S будет распределена между покупателем и продавцом в соответствии с рыночной ставкой (нормой доходности покупателя) Y по аналогичным обязательствам на данный момент времени и пропорционально сроку хранения ценной бумаги каждой из сторон. Часть дохода, причитающаяся покупателю за оставшийся до погашения срок t2, будет равна:

, (3.26)

где t2 – число дней от момента покупки до погашения сертификата.

Соответственно продавец получит величину:

Sпрод = S - Sпок. (3.27)

Соотношения (3.26 – 3.27) отражают ситуацию равновесия на рынке (т.е. "справедливого" распределения доходов в соответствии с рыночной ставкой Y и пропорционально сроку хранения бумаги каждой из сторон). Любое отклонение в ту или иную сторону повлечет за собой перераспределение дохода в пользу одного из участников сделки. Нетрудно заметить, что при r < Y, накопленный доход продавца будет ниже обещанного по условиям контракта.

Предельная величина рыночной ставки Y, при которой продавец бумаги получит доход, должна удовлетворять неравенству:

, (3.28)

где r – ставка по сертификату; Y – рыночная ставка; t1 – число дней до погашения в момент покупки; t2 – число дней до погашения в момент перепродажи.

При этом доходность операции для продавца будет равна:

. (3.29)

. (3.30)

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

Оценка стоимости краткосрочных сертификатов

Цена краткосрочного обязательства с выплатой процентов в момент погашения равна современной стоимости будущих потоков платежей, рассчитанной по простым процентам и обеспечивающей получение требуемой нормой доходности (доходности к погашению). С учетом накопленного на момент проведения операции дохода, стоимость обязательства Р, соответствующая требуемой норме доходности Y, может быть определена из следующего соотношения:

, (3.31)

где t – число дней до погашения.

Нетрудно заметить, что при Y = r, рыночная стоимость обязательства на момент выпуска будет равна номиналу (т.е. Р = N). Соответственно, при Y > r, P < N и сертификат будет размещаться с дисконтом, а в случае Y < r – с премией (т.е. P > N) .

Таким образом, рыночная стоимость сертификата с учетом накопленного дохода, определяемая из (3.31), может отклоняться от номинала. Однако в биржевой практике подобные обязательства принято котировать в процентах к номиналу, т.е. за 100 ед. на дату сделки. При этом ставка дохода по обязательству r показывается отдельно. Курсовая стоимость обязательства К, приводимая в биржевых сводках, определяется как:

, (3.32)

где t – число дней до погашения; S1 – абсолютная величина дохода, накопленная к дате совершения сделки.

В свою очередь величина S1 может быть найдена из следующего соотношения:

, (3.33)

где t1 – число дней от момента выпуска до даты сделки.

Таким образом, полная рыночная стоимость сертификата Р может быть также определена как:

Р = К + S1. (3.34)

Соотношения (3.24 – 3.34) будут использованы нами в дальнейшем при разработке шаблона для анализа подобных обязательств.

Автоматизация анализа краткосрочных сертификатов

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

Таблица 3.6  
Функции для анализа обязательств с выплатой доходов при погашении

Наименование функции		Формат функции
Англоязычная версия	Русифицированная версия
ACCRINTM	НАКОПДОХОДПОГАШ	НАКОПДОХОДПОГАШ(дата_вып; дата_вступл_в_силу; ставка; погашение; [базис])
YIELDMAT	ДОХОДПОГАШ	ДОХОДПОГАШ(дата_согл; дата_вступл_в_силу; дата_вып; ставка; цена; [базис])
PRICEMAT	ЦЕНАПОГАШ	ЦЕНАПОГАШ( дата_согл; дата_вступл_в_силу; дата_вып; ставка; доход; [базис])