Лабораторная работа № 8. Финансовые функции Excel – 2

Цель работы: ознакомление с финансовыми функциями, связанными с ценными бумагами (4 часа)

Многие функции могут использоваться для вычисления при операциях с ценными бумагами. Причем речь идет о двух видах ценных бумаг: с твердым процентом и беспроцентных. Твердопроцентные ценные бумаги приносят одинаковый доход на протяжении определенного периода времени. Беспроцентные ценные бумаги эмитируются со скидкой (дисконтом), то есть курс при эмиссии ценных бумаг этого вида меньше номинальной стоимости, по которой происходит выкуп ценных бумаг в конце срока обращения. Более подробно о финансовых функциях, предназначенных для работы с ценными бумагами, см. в учебном пособии [1].

Все ниже рассматриваемые функции становятся доступными после установки дополнения Пакет Анализа.

Аргументы функций имеют следующий смысл.

Дата_вступления_в_силу – дата вступления в силу ценных бумаг, выраженная как дата в числовом формате.

Дата_соглашения – дата соглашения для ценных бумаг, выраженная как дата в числовом формате.

Погашение – цена при погашении за 100 руб. нарицательной стоимости ценных бумаг.

Ставка – годовая процентная ставка по купонам для ценных бумаг.

Цена – цена ценных бумаг на 100 руб. нарицательной стоимости.

Базис – это используемый способ вычисления дня.

Базис Способ вычисления дня

0 или опущено US (NASD) 30/360

1 Фактический/фактический

2 Фактический/360

3 Фактический/365

4 Европейский 30/360

Ко всем функциям применимы следующие замечания:

Все аргументы усекаются до целых.

Если любой из аргументов не число, то рассматриваемая функциявозвращает значение ошибки #ЗНАЧ!.

Если дата_соглашения или дата_вступления_в_силу не являются допустимой датой в числовом формате, то функция возвращает значение ошибки #ЧИСЛО!.

Если базис < 0 или если базис > 4, то функция возвращает значение ошибки #ЧИСЛО!.

Если дата_соглашения >= дата_вступления_в_силу, то функция возвращает значение ошибки #ЧИСЛО!.

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

Синтаксис:

ДОХОД(дата_соглашения;дата_вступления_в_силу;ставка;цена; погашение; частота; базис)

Замечания.

Дата_соглашения, дата_вступления_в_силу, частота и базис усекаются до целых.

Если только один или менее периодов купона укладываются до даты погашения, то функция ДОХОД вычисляется по формуле, приведенной в справочном материале пакета Excel.

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

Пример.

Облигации выпущены на следующих условиях:

Дата соглашения 15 февраля 1991 года.

Дата вступления в силу 15 ноября 1999 года.

Купон 5,75%.

Цена 95,04287 руб.

Цена при погашении 100 руб.

Полугодовая частота.

Базис 30/360.

Доход от облигаций (в Системе дат 1900) составит:

ДОХОД(33284;36479;0,0575;95,04287;100;2;0) равняется 0,065 или 6,5%

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

Синтаксис:

ДОХОДСКИДКА(дата_соглашения; дата_вступления_в_силу; цена; погашение; базис)

Пример.

Облигации выпущены на следующих условиях:

Дата соглашения 15 февраля 1993 года.

Дата вступления в силу 1 марта 1993 года.

Цена 99,795 руб.

Цена при погашении 100 руб.

Базис фактический/360.

Доход по облигации (в Системе дат 1900) составит:

ДОХОДСКИДКА(34015;34029;99,795;100;2) равняется 5,2823%.

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

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

Создадим рабочую книгу и введем в ячейку A1первого рабочего листа название нашего примера, например, Доходность ЦБ, а в ячейке A3название нашей первой таблицы – Твердопроцентные ЦБ. Соответствующее имя, например, ТПЦБ, следует присвоить и рабочему листу. Затем укажем дату, на которую производится расчет доходности ценных бумаг. Введем в ячейку A2текст Дата, а в ячейкуB2вставим функцию, с помощью которой в этой ячейке всегда будет представлено текущее значение даты. Укажем в ячейке B2формулу: =СЕГОДНЯ(). Во второй строке для информации укажем рыночную процентную ставку процента на текущую дату. Она вводится с клавиатуры. Укажем в ячейкеD2значение Ставка, а в ячейке E2введем значение текущей рыночной процентной ставки и сформатируем ячейку процентным стилем с двумя десятичными знаками. В качестве текущей рыночной процентной ставки будем указывать дисконтную или ломбардную ставки центрального банка.

В первом столбце таблицы укажем код ценных бумаг. В рассматриваемом примере этот код совершенно произволен. В остальных столбцах будут представлены другие важные данные для ценных бумаг – данные, которые в последующем будут использованы при определении годовой доходности. Введем в ячейки A5– I5заголовки столбцов:

Код ЦБ

Курс

Дата выпуска

Дата погашения

Купон

Погашение (%)

Периодичность

Базис

Доход (Рендита).

Ценные бумаги в примере будут расположены (как это часто практикуется) в порядке убывания номинальной доходности. Укажем в ячейке A6 код первой ценной бумаги, а в ячейке B6– ее текущий курс. Данные в столбце Bнеобходимо обновлять каждый день (или хотя бы в те дни, в которые меняется курс ценных бумаг). В данном примере для нас не имеет значения номинал ценной бумаги, поскольку и текущий курс, и курс при погашении указыва-

Рис. 8.1

ся в процентах от номинальной стоимости.

Введем в ячейки C6и D6даты выпуска и погашения ценных бумаг. Для простоты в примере представлены бумаги со сроком обращения 5 – 10 лет. Столбец Eбудет содержать данные о номинальной процентной ставке. Выделим этот столбец с помощью щелчка на его заголовке, выберем команду Ячейкименю Формати в разделе Число выберите из списка Числовые форматыэлемент Процентный, а также задайте отображение двух десятичных знаков. Теперь для задания процентной ставки 7%достаточно ввести с клавиатуры просто 7, а не 0,07. В ячейке F6укажем число 100 (как правило, большинство рентных ценных бумаг выкупается по их номинальной стоимости).

Столбец Gбудет содержать сведения о периодичности выплат процентов. Если выплата производится раз в год, то следует указать 1, если раз в полгода – 2, если ежеквартально – 4. В столбце Hпоставим 1, так как будет считаться фактическое количество дней. В результате получаем таблицу, приведенную на рис. 8.1.

Рис. 8.2

Теперь необходимо ввести формулу для определения годового дохода (рендиты) по ценной бумаге для оставшегося срока обращения. Воспользуемся функцией ДОХОД. Во избежание представления значения ошибкив ячейках предварительно зададим с помощью функции ЕСЛИ не представленные

Рис. 8.3

Рис. 8.4

значения в ячейках столбцаI в том случае, если комплект данных не введен полностью. Укажем в ячейке I6 формулу:

= ЕСЛИ(B6« »; « »;ДОХОД($B$2;D6;E6;B6;F6;G6;H6)).

Скопируем формулу из ячейки I6в остальные ячейки столбцаI. В результате получает таблицу (рис. 8.2.).

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

Перейдем во второй рабочий лист, присвоим ему имя ДЦБ, а в ячейку A3 введем название примера Дисконтные ЦБ. Таблица, полученная после заполнения исходных данных, приведена на рис. 8.3.

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

После ввода исходных данных в ячейке G6зададим формулу

= ЕСЛИ(B6« »; « »;ДОХОДСКИДКА($B$2;D6;B6;E6;F6))

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

Задание к лабораторной работе: проанализировать доходность ценных бумаг, проварьировать параметры, определяющие доходность.

Литература

1. Андреев В.В. Табличный процессор Excel в экономических расчетах. Учебное пособие по курсу «Технологическая обработка экономической информации». Казань: КГЭУ, 2002.

2. Буза М.К., Певзнер Л.В., Хижняк И.А. Операционная система Windows и ее приложения. Минск: Вышэйшая школа, 1997.

3. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. СПб.: BHV – Санкт-Петербург, 2000.

4. Лавренов С.М. Excel: Сборник примеров и задач. М.: Финансы и статистика, 2002.

5. Пасько В. Microsoft Office 97 (русифицированная версия). Киев: Издательская группа BHV, 1998.

6. Пробитюк А. Excel 7.0 для Windows 95 в бюро. Киев: Торгово-издательское бюро BHV, 1996.

7. Справочные материалы пакета Excel 2000.