5. Финансовые функции и анализ данных в электронной таблице Excel

Основные термины и определения

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

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

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

Аргументами финансовых функций часто являются следующие величины:

• будущее значение – стоимость вложения или ссуды по завершении всех отложенных платежей;

• количество выплат – общее количество платежей или периодов выплат;

• выплата – объем периодической выплаты по вложению или ссуде;

• текущее значение – начальная стоимость вложения или ссуды. Так, начальная стоимость ссуды равна, собственно, сумме займа;

• ставка – процентная ставка или скидка по вложению или ссуде;

• режим выплат – режим выплат, с которым осуществляются выплаты (в конце или в начале месяца). Финансовые функции имеют следующие аргументы:

Basis – базис (способ вычисления количества дней в году);

Cost – стоимость (стоимость имущества при приобретении);

Coupon – купон (процентная ставка по ценным бумагам);

Data _ purchased – дата приобретения;

Discount - скидка, дисконт;

Effect _ rate – эффективная процентная ставка;

End-period – последний период;

Factor - коэффициент;

Finance _ rate – ставка финансирования;

First _ period – первый период;

Frequency - частота (периодичность выполнения платежа);

Future value – будущая стоимость;

Cress – прогноз, предположение;

Investment – инвестирование (общая сумма инвестиций);

Issue – дата выпуска ценных бумаг;

Life – время эксплуатации, срок амортизации;

Matuarty – дата погашения ценных бумаг;

Nominal _ rate – номинальная процентная ставка;

No _ switch – без переключения (не переходить к использованию другой функции);

Nper – количество периодов;

Par – номинальная (нарицательная) стоимость;

Period – период;

Pmt (payment) - платеж, выплата;

Pr (price) – цена ценных бумаг;

Principal – основной долг, основной капитал;

Pv – текущая стоимость;

Rate - ставка;

Redemption – цена ценных бумаг при погашении;

Reinvest _ rate – ставка реинвестирования;

Salvage – ликвидационная (остаточная) стоимость имущества;

Schedule – массив процентных ставок;

Settlement – дата соглашения (дата приобретения ценных бумаг);

Type – тип (значение, задающее время исполнения платежа - в начале или в конце периода);

Values - значения;

Yld (yield) - доход.

Все финансовые функции можно разделить на четыре группы:

1. Функции для вычисления амортизации ФВА; 2. Функции для анализа инвестиций ФАИ; 3. Функции для вычисления скорости оборота ФВСО; 4. Функции для анализа ценных бумаг ФАЦБ.

ФАИ и ФВСО (аргументы и их описание):

• будущее значение – будущий объем вложений в конце срока (0 – если он опущен);

• плата n – изменяющиеся периодичные выплаты;

• число периодов – описание срока вложения;

• выплата – постоянные периодичные выплаты;

• тип – число обозначающее, когда должна производиться выплата (0 – конец периода, 1 – начало);

• период – номер конкретной периодической выплаты;

• текущее значение - текущая стоимость вложений;

• ставка - % ставка или учетная ставка;

• прогноз - является необязательным и дает начальное приближение для расчетов;

• значения - массив или ссылка на диапазон ячеек, содержащих числа;

• финансовая_ставка - ставка, по которой одалживаются средства, необходимые для вложения;

• ставка_реинвестирования - ставка, по которой инвестируются средства, поступающие от вложения.

ФВА (аргументы и их описание):

• стоимость – первоначальная стоимость имущества;

• время жизни – количество периодов, за которое имущество амортизируется, время полной амортизации;

• период - период, для которого требуется вычислить амортизацию;

• остаток – остаточная стоимость после полной амортизации (ликвидационная стоимость).

ФАЦБ (аргументы и их описание):

• базис – используемый способ вычисления дня (0 – 30/360 – по умолчанию, 1 – факт/факт, 2 – факт/360, 3 – факт/365, 4 – 30/360 – европейский тип);

• купон – годовая % ставка по купонам ценной бумаги;

• частота – периодичность выплат по купонам (количество выплат за год), 1 – ежегодная, 2 – полугодовая, 4 – ежеквартальная;

• вложения – первоначальная стоимость покупки ценной бумаги;

• выпуск – дата выпуска ценной бумаги;

• погашение – дата погашения ценной бумаги;

• номинал – номинальная стоимость ценной бумаги (если он опущен, то используется значение 1000 руб.);

• цена – цена за 100 рублей нарицательной стоимости ценной бумаги;

• ставка – процентная ставка по ценной бумаге на дату выпуска;

• выкуп – стоимость ценной бумаги после погашения;

• сделка – дата сделки по ценной бумаги (день оплаты ценной бумаги);

• доход – годовой доход по ценной бумаге в %.

Финансовые функции по ценным бумагам

Описание аргументов финансовых функций по ценным бумагам и варианты значений временного базиса в финансовых расчетах приведены соответственно в табл.1 и 2.

Таблица 1. Значения аргумента

Аргумент	Значение аргумента
базис	Принятый способ исчисления временного периода (года, месяца)
выкуп	Цена (в абсолютном выражении) или курс (в относительном выражении) при продаже ценной бумаги инвестором
дата_вступления_в_силу	Календарная дата погашения (дата окончания действия ценной бумаги) или выкупа ценной бумаги у инвестора
дата_выпуска	Календарная дата выпуска ценной бумаги эмитентом
дата_первой_выплаты	Календарная дата первой выплаты процентов по ценной бумаге
дата_соглашения	Календарная дата покупки (дата инвестиций в ценные бумаги) инвестором
доход	Годовая доходность ценной бумаги в % (ставка помещения)
инвестиция	Рыночная цена (в абсолютном выражении) или курс (в относительном выражении) ценной бумаги при ее покупке инвестором
купон	Годовая ставка выплат по купонам в %
номинал	Нарицательная стоимость (номинал) ценной бумаги
первый_купон	Календарная дата первой выплаты процентов по ценной бумаге
периодичность	Количество выплат по купонам в течение года
погашение	Цена или курс продажи ценной бумаги инвестором
последняя_выплата	Календарная дата последней выплаты процентов по ценной бумаге
скидка	Учетная (дисконтная) ставка % к цене погашения
ставка	Годовая ставка выплат по купонам в %
цена	Рыночная цена или курс ценной бумаги при покупке инвестором
частота	Количество выплат по купонам в течение года

 

 Таблица 2. Тип базиса и его условное обозначение

Тип базиса	Обозначение	Комментарий
0 или опущено	US (NASD) 30/360	Американский стандарт, месяц равен 30 дням, год – 360 дням
1	Фактический / фактический	Фактическая длина месяца и года (в том числе – високосный год равен 366 дням)
2	Фактический / 360	Фактическая длина месяца, год равен 360 дням
3	Фактический / 365	Фактическая длина месяца, год равен 365 дням
4	Европейский 30 /360	Европейский стандарт, месяц равен 30 дням, год – 360 дням

Для сравнения облигаций с позиции риска финансовых вложений используются следующие показатели:

средний срок платежей - определяется как взвешенная арифметическая величина периодических купонных выплат и номинала в конце срока действия облигации;

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

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

Функции ФАЦБ в Excel:

ДАТАКУПОНДО - Возвращает число, соответствующее предыдущей дате купона перед датой соглашения.

ДАТАКУПОНПОСЛЕ - Возвращает число, представляющее следующую дату купона после даты соглашения.

ДЛИТ - Возвращает длительность Макалея для предполагаемой номинальной стоимости 100 руб. Продолжительность определяется как взвешенное среднее текущих значений выплат и является мерой зависимости цены облигации от изменения доходности.

ДНЕЙКУПОН - Возвращает число дней в периоде купона, который содержит дату соглашения.

ДНЕЙКУПОНДО - Возвращает количество дней от начала действия купона до даты соглашения.

ДНЕЙКУПОНПОСЛЕ - Возвращает число дней от даты соглашения до срока следующего купона.

ДОХОД - Возвращает доход от ценных бумаг, который составляет периодические процентные выплаты. Функция ДОХОД используется для вычисления дохода по облигациям.

ДОХОДКЧЕК - Возвращает доход по казначейскому чеку.

ДОХОДПЕРВНЕРЕГ - Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом.

ДОХОДПОГАШ - Возвращает годовой доход от ценных бумаг, который составляет доход в срок вступления в силу.

ДОХОДПОСЛЕНЕРЕГ - Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом.

ДОХОДСКИДКА - Возвращает годовой доход по ценным бумагам, на которые сделана скидка.

ИНОРМА - Возвращает процентную ставку для полностью инвестированных ценных бумаг.

МДЛИТ - Возвращает модифицированную длительность для ценных бумаг с предполагаемой номинальной стоимостью 100 руб.

НАКОПДОХОД - Возвращает накопленный доход по ценным бумагам с периодической выплатой процентов.

НАКОПДОХОДПОГАШ - Возвращает накопленный доход по ценным бумагам, процент по которым выплачивается в срок погашения.

НОМИНАЛ - Возвращает номинальную годовую процентную ставку, если известны фактическая ставка и число периодов, составляющих год.

ПОЛУЧЕНО - Возвращает сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.

РАВНОКЧЕК - Возвращает эквивалентный облигации доход по казначейскому чеку.

РУБЛЬ.ДЕС - Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом. Функция РУБЛЬ.ДЕС используется для преобразования дробных значений денежных сумм, например, стоимость ценных бумаг, в десятичное число.

РУБЛЬ.ДРОБЬ - Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби. Функция РУБЛЬ.ДРОБЬ используется для преобразования десятичных чисел в дробные цены, например, стоимость ценных бумаг.

СКИДКА - Возвращает норму скидки для ценных бумаг.

ЦЕНА - Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым выплачивается периодический процент.

ЦЕНАКЧЕК - Возвращает цену на 100 руб. номинальной стоимости для казначейского чека.

ЦЕНАПЕРВНЕРЕГ - Возвращает цену за 100 руб. номинальной стоимости ценных бумаг для нерегулярного (короткого или длинного) первого периода.

ЦЕНАПОГАШ - Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым доход выплачивается в срок вступления в силу.

ЦЕНАПОСЛНЕРЕГ - Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг для нерегулярного (короткого или длинного) последнего периода купона.

ЦЕНАСКИДКА - Возвращает цену за 100 руб. номинальной стоимости ценных бумаг, на которые сделана скидка.

ЧИСЛКУПОН - Возвращает количество купонов, которые могут быть оплачены между датой соглашения и сроком погашения, округленное до ближайшего целого количества купонов.

Пример 1: Определить размер процентной ставки для облигаций, имеющих дату сделки 31 марта 1996 и дату погашения 30 сентября 1996. Объем вложений при этом составляет 1000000$, и для него установлен выкуп в размере 1032324$, по умолчанию используется базис 30/360.

Ответ: =ИНОРМА(“31.03.96”; ”30.09.96”; 1000000; 1032324). Процентная ставка равна 6,46%.

 

Пример 2: Срок обращения облигации с номиналом в 1000 составляет 10 лет. Ставка купона, выплачиваемая раз в год, равна 15%. Определить стоимость облигации, если:

а) рыночная ставка (требуемая норма доходности) равна 22%;

б) рыночная ставка (требуемая норма доходности) равна 10%.

Для иллюстрации зависимости стоимости облигаций от срока погашения воспользуемся уже хорошо известным нам инструментом ППП EXCEL - таблицами подстановки. Фрагмент ЭТ для решения первого условия примера приведен на рисунке 3.

Рис. 3. Исходные данные и решение для первого варианта

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

1. Заполнить ячейки ВЗ:В6 исходными данными.

2. Ввести в ячейку С9 формулу: =-ПЗ (В6;В4;ВЗ*В5;ВЗ).

3. Заполнить ячейки В10:В20 числами от 10 до 0.

4. Выделить блок ячеек В9:С20.

5. Выбрать из темы «Данные» главного меню пункт «Таблицы» подстановки и указать в поле «Подставлять значения» по строкам в ссылку на ячейку В4.

6. Ввести в ячейку D10 формулу: =1000-С10.

7. Скопировать ячейку D10 в блок D11:D20.

Аналогичная таблица, реализующая расчеты для второго случая, представлена на рисунке 4.

Рис. 4. Исходные данные и решение для второго варианта

Пример 3: Автоматизация анализа купонных облигаций

Для анализа облигаций с фиксированным купоном в электронной таблице Excel (ЭТ) реализованы 15 функций. Все функции этой группы являются дополнительными:

Таблица 3. Функции для анализа облигаций с фиксированным купоном

Наименование функции			Формат функции
Англоязычная версия	Русифицированная версия
COUPDAYBS	ДАТАКУПОНДО	ДАТАКУПОНДО(дата_согл; дата_вступл_в_силу; частота; [базис])
COUPNCD	ДАТАКУПОНПОСЛЕ	ДАТАКУПОНПОСЛЕ(дата_согл; дата_вступл_в_силу; частота; [базис])
COUPDAYSBS	ДНЕЙКУПОНДО	ДНЕЙКУПОНДО(дата_согл; дата_вступл_в_силу; частота; [базис])
COUPDAYSNC	ДНЕЙКУПОНПОСЛЕ	ДНЕЙКУПОНПОСЛЕ(дата_согл; дата_вступл_в_силу; частота; [базис])
COUPDAYS	ДНЕЙКУПОН	ДНЕЙКУПОН(дата_согл; дата_вступл_в_силу; частота; [базис])
COUPNUM	ЧИСЛКУПОН	ЧИСЛКУПОН(дата_согл; дата_вступл_в_силу; частота; [базис])
DURATION	ДЛИТ	ДЛИТ(дата_согл; дата_вступл_в_силу; ставка; доход; частота; [базис])
MDURATION	МДЛИТ	МДЛИТ(дата_согл; дата_вступл_в_силу; ставка; доход; частота; [базис])
PRICE	ЦЕНА	ЦЕНА(дата_согл; дата_вступл_в_силу; доход; погашение; частота; [базис])
ACCRINT	НАКОПДОХОД	НАКОПДОХОД(дата_вып; дата_след_куп; дата_согл; ставка; номинал; частота; [базис])
YIELD	ДОХОД	ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; [базис])
ODDFYIELD	ДОХОДПЕРВНЕРЕГ	ДОХОДПЕРВНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_вып; дата_перв_куп; ставка; цена; погашение; частота; [базис])
ODDLYIELD	ДОХОДПОСЛНЕРЕГ	ДОХОДПОСЛНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_вып; дата_посл_куп; ставка; цена; погашение; частота; [базис])
ODDFPRICE	ЦЕНАПЕРВНЕРЕГ	ЦЕНАПЕРВНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_вып; дата_перв_куп; ставка; доход; погашение; частота; [базис])
ODDLPRICE	ЦЕНАПОСЛНЕРЕГ	ЦЕНАПОСЛНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_вып; дата_посл_куп; ставка; доход; погашение; частота; [базис])

Рассмотрим технологию применения этих функции на реальном примере из практики российского рынка 0ВВЗ.

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

Дата выпуска ОВВЗ - 14.05.1996 г. Дата погашения - 14.05.2011 г. Купонная ставка - 3%.Число выплат - 1 раз в год. Средняя курсовая цена на дату операции - 37,34. Требуемая норма доходности - 12% годовых.

На рисунке 5 приведена исходная ЭТ для решения этого примера с использованием функций рассматриваемой группы:

Рис. 5. Исходная ЭТ - шаблон

В приведенной ЭТ исходные (неизменяемые) характеристики займа содержатся в блоке ячеек В2:В8. Значения изменяемых переменных задачи вводятся в ячейки Е2:Е4. Вычисляемые с помощью соответствующих функций Excel параметры ОВВЗ, наименования которых содержатся в блоке А10:А22, будут помещаться по мере выполнения расчетов в ячейки блока В10:В22. Руководствуясь рисунком, подготовьте исходную таблицу и заполните ее исходными данными. Приступаем к проведению анализа и рассмотрению функций.

Функции для определения характеристик купонов

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

дата_согл - дата приобретения облигаций (дата сделки);

дата_вступл_в_силу - дата погашения облигации;

частота - количество купонных выплат в году (1, 2, 4);

базис - временная база (необязательный аргумент).

В нашем примере эти аргументы заданы в ячейках Е2, В4 и В8 соответственно.

Функция ДАТАКУПОНДО() вычисляет дату предыдущей (т.е. до момента приобретения облигации) выплаты купона. С учетом введенных исходных данных функция, заданная в ячейке В10, имеет вид:

=ДАТАКУПОНДО(Е2;В4;В8) (Результат: 14.05.96).

И первый же блин вышел комом! В данном случае можно считать, что функция выдала ошибочный результат, поскольку вычисленное значение - это дата выпуска облигации в обращение и никаких выплат в тот день быть не могло. Очевидно, что для более корректной реализации этой функции разработчикам следовало бы предусмотреть задание еще одного аргумента - даты выпуска. Однако, утешив свое самолюбие, признаем, что если бы такая выплата производилась, по условиям займа она действительно должна была бы состояться именно 14.05.96.

Функция ДАТАКУПОНПОСЛЕ() вычисляет дату следующей (после приобретения) выплаты купона. Формат функции в ячейке В11:

=ДАТАКУПОНПОСЛЕ (Е2;В4;В8) (Результат: 14.05.97).

Нетрудно заметить, что полученная дата совпадает со сроком выплаты первого купона, как и следует из условий примера.

Функция ДНЕЙКУПОНДО()вычисляет количество дней, прошедших с момента начала периода купона до момента приобретения облигации. В нашем примере эта функция задана в ячейке В12:

=ДНЕЙКУПОНДО(Е2;В4;В8) (Результат: 304).

Таким образом, с момента начала периода купона до даты приобретения облигации (18 марта 1997 г.) прошло 304 дня.

Функция ДНЕЙКУПОН() вычисляет количество дней в периоде купона. По условиям выпуска облигаций валютного займа Минфина России купоны выплачиваются 1 раз в году. Таким образом, число дней в периоде купона должно быть равным 360 (финансовый год), что подтверждается результатом применения функции (ячейка В13):

=ДНЕЙКУПОН (Е2;В4;В8) (Результат: 360).

В случае необходимости проведения расчетов с точным числом дней в году достаточно просто указать необязательный аргумент базис, равным 1 или 3:

=ДНЕЙКУПОН (Е2;В4;В8;3) (Результат: 365).

Функция правильно работает и в случае високосного года.

Функция ДНЕЙКУПОНПОСЛЕ() вычисляет количество дней, оставшихся до даты ближайшей выплаты купона (с момента приобретения облигации). В нашем примере эта функция задана в ячейке В14:

=ДНЕЙКУПОНПОСЛЕ (Е2;В4;В8) (Результат: 56).

Таким образом, периодический доход по облигации будет получен через 56 дней после ее приобретения.

Функция ЧИСЛКУПОН() вычисляет количество оставшихся выплат (купонов) с момента приобретения облигации до срока погашения. Функция задана вячейке В15:

=ЧИСЛКУПОН (Е2;В4;В8) (Результат: 15).

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

Функции для определения дюрации

Следующие две функции позволяют определить одну из важнейших характеристик облигаций - дюрацию.

Функция ДЛИТ() вычисляет дюрацию D и имеет два дополнительных аргумента:

ставка - купонная процентная ставка (ячейка В6);

доход - норма доходности (ячейка Е4).

Заданная в ячейке В17 функция с учетом размещения исходных данных имеет вид:

=ДЛИТ(Е2;В4;В6;Е4;В8) (Результат: 9,39).

Таким образом, средневзвешенная продолжительность платежей по 15-летней ОВВЗ седьмой серии составит 9 лет и около 140 дней (0,39 * 360).

Функция МДЛИТ() реализует модифицированную формулу для определения дюрации MD и имеет аналогичный формат (ячейка В18):

=МДЛИТ(Е2;В4;В6;Е4;В8) (Результат: 8,39).

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

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

погашение - стоимость 100 единиц номинала при погашении (ячейка В7);

доход - требуемая норма доходности (ячейка Е4);

ставка - годовая ставка купона (ячейка В 6);

цена - цена, уплаченная за 100 единиц номинала (ячейка ЕЗ).

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

Функция ЦЕНА() позволяет определить современную стоимость 100 единиц номинала облигации (т.е. курс), исходя из требуемой нормы доходности на дату ее покупки. В нашем примере она задана в ячейке В19 и имеет следующий формат:

=ЦЕНА(Е2;В4;В6;Е4;В7;В8) (Результат: 40,06).

Полученная величина 40,06 представляет собой цену облигации, которая обеспечивает требуемую норму доходности - 12% (ячейка ЕЗ). Поскольку ее величина меньше средней цены покупки в 34,75 (ячейка Е2), мы также получим дополнительную прибыль приблизительно в 5,30 на каждые 100 единиц номинала при погашении облигации.

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

=ДОХОД(Е2;В4;В6;ЕЗ;В7;В8) (Результат: 13,63%).

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

Ячейка В21 содержит формулу для расчета текущей (на момент совершения сделки) доходности Y - отношение купонной ставки (ячейка В6) к цене приобретения облигации (ячейка Е3):

= В6 / ЕЗ * 100 (Результат: 8,63%).

Таким образом, текущая доходность операции составляет 8,63%, что значительно выше купонной ставки, однако ниже доходности к погашению.

Последним показателем, рассчитанным в электронной таблице (ячейка В22), является величина накопленного купонного дохода НКД на дату сделки. Для его вычисления используется функция НАКОПДОХОД():

=НАКОПДОХОД(ВЗ;В11;Е2;В6;В7;В8) (Результат: 2,53).

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

Последние четыре функции этой группы - ДОХОДПЕРВНЕРЕГ(), ДОХОДПОСЛНЕРЕГ(), ЦЕНАПЕРВНЕРЕГ() и ЦЕНАПОСЛНЕРЕГ() применяются для вычисления цены и доходности облигации в тех случаях, когда период выплаты первого или последнего купона отличается от остальных. При этом в списке аргументов должна быть указана дата выплаты первого (последнего) купона. В остальном выполняемые ими действия аналогичны рассмотренным выше.

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

Рис. 6. Результаты расчетов

Очистите таблицу от исходных данных (блоки ячеек В2:В8 и Е2:Е4) и сохраните на магнитном диске в виде шаблона BONDCOUP.XLT.

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

Дата выпуска ОВВЗ - l4.05.l993 г. Дата погашения - 14.05.1999 г. Купонная ставка - 3%. Число выплат - 1 раз в год. Средняя курсовая цена на дату операции - 85.83. Требуемая норма доходности - 10% годовых.

Полученная в результате таблица должна иметь вид:

Рис.7. Решение примера «Расчет эффективности операции на 18.03.l997г.»

Большинство из рассмотренных функций можно использовать и для анализа облигаций с плавающей ставкой купона (ОГСЗ, ОФЗ и др.). Однако результаты расчета доходности к погашению будут справедливы только для текущей ставки купона (т.е. для периода между двумя купонными выплатами).

При окончательном определении величины полученного дохода, т.е. при ретроспективном анализе операций с ОГСЗ, ОФЗ и ряда муниципальных бумаг с плавающей ставкой доходности, удобно пользоваться функцией БЗРАСПИС(). Ее можно применять и для приблизительной оценки будущих доходов, предположив, например, что купонная ставка будет изменяться с фиксированным шагом. Альтернативным вариантом является определение доходности YTM по значениям полученных платежей с помощью функции ЧИСТВНДОХ().

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