Лабораторные работы

3.2. Создание таблицы расчета прибыли при ежемесячном погашении кредита

3.2.1.Скопируйте всю таблицу на Лист2.

3.2.2.Отредактируйте в ячейке С11 формулу:

=ЕСЛИ($C$5>A11;0;ППЛАТ($C$2;$C$6;$C$7)). При ежемесячной выплате процентов и ежемесячном погашении долга используется функция:

=ППЛАТ(СТАВКА;ЧИСЛО_ВЫПЛАТ;СУММА_КРЕДИТА).

3.2.3. Скопируйте отредактированную формулу из ячейки С11 на диапазон ячеек С12:С34. Получится результат прибыли $69 67,11, так как в ячейке С5 осталось значение 24.

3.2.4. Измените значение в ячейке С5 на 5. Получится, что при начале погашения кредита на пятый месяц, прибыль составит $47 391,62. Очевидно, что чем позже вы возвращаете деньги, тем большую выгоду вы получаете от кредита.

3.3. Самостоятельная работа

3.3.1. Определите сумму, которую надо положить на депозит для получения через пять лет суммы в 100 000 р. Используйте функцию

Подбор параметра.

3.3.2. Активизируйте Лист3 и переименуйте его в Сумма вклада. 3.3.3. Введите текст из таблицы:

3.3.4. Выделите столбец С и установите ширину, равной 17,00. 3.3.5. Примените для форматирования ячеек С1, С3, С5 любой

Финансовый формат из списка, и установите для этих ячеек числовой формат типа # ##0,00.

3.3.6. В ячейку С1 введите число −10 000 р. Знак минус означает, что эти деньги надо отдавать, а не получать. Эта сумма и будет подбираться.

3.3.7. Установите в ячейке С2 Процентный формат и введите в нее число 100. Это годовая процентная ставка, которая не меняется в течение 5 лет.

3.3.8. Введите в ячейку С3 число 12 000. Здесь находится сумма, которую вы получаете по истечении каждого года.

3.3.9.Введите в ячейку С4 число 5. Это число лет, на которое размещается депозит.

3.3.10.С помощью мастера Вставка функций введите в ячейку С5 финансовую формулу =БЗ(С2;С4;С3;С1;0). Эта финансовая формула вычисляет будущее значение вклада в зависимости от % ставки, количества лет, суммы ежегодных выплат и суммы вклада. Последний ноль означает, что выплаты производятся по окончании года. Полученный отрицательный итог говорит о том, что суммы вклада недостаточно для выполнения всех условий.

Таблица 26

3.3.11.Автоматизируйте подбор требуемой суммы вклада. Для этого выполните команды меню Сервис – Подбор параметра. В диалоговом окне в поле Установить в ячейке – щелкните мышью в ячейке $С$5, в

поле Значение введите 100 000, в поле Изменяя значение ячейки –

щелкните мышью в ячейке $С$1. Нажмите ОК в окне Результат

подбора параметра.

3.3.12.Нажмите ОК в окне Подбор параметра. В ячейке С1

появится результат −14750 р. Задача решена. Для получения желаемой суммы следует положить на депозит сумму 14750 рублей. В результате вычислений на Листе3 следующая таблица:

4.Отчет о проделанной работе

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

Литература: [5], c.276-284.

Работа 10 АНАЛИЗ РЫНКА ОБЛИГАЦИЙ. ОЗНАКОМЛЕНИЕ

С ОПИСАНИЕМ РЫНКА ОБЛИГАЦИЙ

1. Цель работы

Научить пользователя основным принципам и приемам работы с электронной таблицей Excel на примере анализа рынка облигаций.

2. Основные теоретические положения.

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

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

также право на получение зафиксированного в ней процента от номинальной стоимости облигации либо иные имущественные права (ст.816 Гражданского кодекса РФ, ст.2 закона “О рынке ценных бумаг”).

Таким образом, облигация - это долговое свидетельство, которое включает два главных элемента:

-обязательство эмитента (выпустившего ее лица) выплатить держателю облигации по истечении оговоренного срока (срок погашения) определенную сумму (номинал облигации);

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

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

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

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

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

Зная номинальную стоимость облигации и процентную ставку купона, можно вычислить сумму выплачиваемого купона.

Сумма купона = Номинал * (Ставка процента /100%).

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

Для этого используется следующая формула:

Рt =(Pt+1 +C)/(1+r),

где Pt - стоимость облигации в момент времени (год) t; Pt+1 - стоимость облигации в срок (год) t+1;

С - сумма купонного платежа; г - ставка дисконтирования, которая, рассчитывается по формуле:

если, например, ставка дисконтирования 20%, то г = 20/100 = 0,2. При этом считается, что в момент погашения Р=F (номинальной стоимости облигации).

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

Доходность до погашения - это доходность в расчете на год, которую обеспечит себе инвестор (кредитор), если, купив облигацию, продержит ее до погашения.

Доходность до погашения рассчитывается по следующей формуле: Y = (F + C*N - P)/(N*P),

где Y - доходность до погашения;

F - номинальная стоимость облигации; С - сумма купонной выплаты;

N - количество лет до погашения; Р - текущая стоимость облигации.

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

3. Порядок выполнения работы

Задание. Создать таблицу для анализа доходности облигаций.

3.1. Запуск приложения Microsoft Excel

• Выполните команды: кнопка Пуск – Программы – Microsoft Excel. На экране появится чистый рабочий лист. Создадим таблицу, состоящую из трех вспомогательных таблиц.

3.2. Cоздание первой таблицы

• Введите следующие данные на рабочий лист из представленной табл.28.

3.3. Форматирование, обрамление и цветовое оформление таблицы

•выделите диапазон ячеек A3:D3. В меню выберите пункт Формат

–Ячейки - выберите вкладку Выравнивание и щелчком мыши установите "х" в окне Переносить по словам. Нажмите кнопку ОК.

•выделите диапазон ячеек A3:D3, щелкните по кнопке

Полужирный.

•выделите диапазон ячеек B3:D6. Щелкните по кнопке По центру.

•выделите диапазон ячеек А4:А6. Щелкните по кнопке

Полужирный.

•выделите всю таблицу (диапазон ячеек A3:D6), щелкните по кнопке Границы. В появившемся окне щелкните по варианту рамки.

•выделите диапазон ячеек АЗ:А6 (первый столбец), щелкните по кнопке Цвет заливки. В появившемся окне выберите цвет для закраски первого столбца и щелкните мышью по квадрату с этим цветом. Проделайте аналогичные действия для следующих диапазонов ячеек

B3:D3 и B4:D6.

3.4. Создание второй таблицы

• введите данные на рабочий лист из табл.29.

•создайте обрамление для диапазона ячеек A11:G15;

•выделите диапазон ячеек А11:А12. Нажмите пункт Формат – Ячейки - выберите вкладку Выравнивание и установите "х" в окошке

Объединение ячеек и в окошке Переносить по словам. Нажмите ОК;

•выделите диапазон ячеек B11:G11. Нажмите кнопку По центру;

•установите курсор на ячейке В13. Нажмите кнопку Копировать, выделите диапазон ячеек C13:F13. В меню выберите пункт Правка -

Специальная вставка. В диалоговом окне щелкните по пункту

Значения и нажмите ОК;

•проделайте аналогичные действия для копирования значения ячейки В14 в диапазон C14:F14 и ячейки В15 в диапазон C15:F15;

•измените цветовое и шрифтовое исполнение таблицы.

3.5. Создание третьей таблицы

• введите следующие данные на рабочий лист из табл.30.

В результате вашей работы на экране появятся следующие две таблицы:

Табл.31 – режим показа формул, табл. 32 – режим вычислений

3.6. Расчет доходности облигаций А, В и С при их приобретении по текущим ценам (в нулевой год)

•в ячейку А25 введите заголовок четвертой таблицы Доходность облигаций. Выделите ячейку А25, нажмите кнопку Полужирный и затем кнопку Курсив, выберите цвет шрифта заголовка (инструмент

Цвет шрифта);

•выделите интервал ячеек АЗ:А6, нажмите кнопку Копировать, затем установите курсор на ячейке А26 и нажмите кнопку Вставить;

•в ячейку В27 введите формулу расчета доходности для облигации

Апри ее приобретении по текущей цене:

•(Номинал+Сумма купонных выплат*Количество лет до

погашения-Цена облигации)/(Цена облигации* Количество лет до погашения) или =(B4+C13*D4 - B20)/(B20*D4).

• копирование формулы в остальные ячейки столбца. Скопируйте формулу из ячейки В27 в ячейки В28 и В29.

•выделите ячейки В27:В29, выберите пункт в главном меню

Формат – Ячейки - выберите вкладку Число - Числовые форматы - Процентный. В окно Число десятичных знаков - введите цифру 3.

Нажмите ОК.

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

(ячейки А27:В27);