Реферат: Функции Excel для финансовых расчетов по ценным бумагам

Функции EXCEL для финансовых расчетовпо ценным бумагам: функции для расчетов по ценных бумагам с нарушениемпериодичности выплаты процентов (ДОХОДПЕРВНЕРЕГ(), ДОХОДПОСЛНЕРЕГ()).

/>

Рисунок 1. – Окно аргументов функции

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

Еслиданная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузитенадстройку «Пакет анализа».

ДОХОДПЕРВНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; цена; погашение;частота; базис)

Датыдолжны вводиться с использованием функции ДАТА или как результат вычислениядругих формул и функций. Например, для 23‑го мая 2008 года следует использоватьДАТА (2008; 5; 23). Проблемы могут возникнуть, если даты вводятся как текст.

Дата_согл– дата расчета за ценные бумаги (более поздняя, чем дата выпуска, когда ценныебумаги были проданы покупателю).

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

Дата_выпуска– дата выпуска ценных бумаг.

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

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

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

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

Частота– количество выплат по купонам за год. Для ежегодных выплат частота = 1; дляполугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.

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

·          MicrosoftExcel хранит даты как ряд последовательных номеров, что позволяет выполнять надними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января2008 – номер 39448, так как интервал в днях между этими датами составляет39448.

·          Датасоглашения является датой продажи покупателю купона, например облигации. Срокплатежа представляет собой дату истечения срока действия купона. Пусть,например, облигация со сроком действия 30 лет выпущена 1 января 2008 года ибыла приобретена покупателем через шесть месяцев после своего выпуска. Датойвыпуска будет являться 1 января 2008 года, датой соглашения – 1 июля 2008 года,а сроком погашения такой облигации – 1 января 2038 года, то есть дата через 30лет после даты выпуска.

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

·          Еслидата_согл, дата_вступл_в_силу, дата_выпуска или первый_купон не являетсядопустимой датой, то функция ДОХОДПЕРВНЕРЕГ возвращает значение ошибки #ЗНАЧ!.

·          Еслиставка < 0 или цена ≤ 0, то функция ДОХОДПЕРВНЕРЕГ возвращает значениеошибки #ЧИСЛО!.

·          Должныбыть выполнены следующие условия, в противном случае функция ДОХОДПЕРВНЕРЕГвозвращает значение ошибки #ЧИСЛО!:

дата_вступл_в_силу> первый_купон > дата_согл > дата_выпуска

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

Пример

/>

Чтобыпросмотреть числа в виде процентов, выделите ячейку и выберите в меню Форматкоманду Ячейки. На вкладке Число выберите в списке Числовыеформаты вариант Процентный.

ДОХОДПОСЛНЕРЕГ

Возвращаетдоход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом.

ДОХОДПОСЛНЕРЕГ(дата_согл; дата_вступл_в_силу;последняя_выплата; ставка; цена; погашение; частота; базис)

Датыдолжны вводиться с использованием функции ДАТА или как результат вычислениядругих формул и функций. Например, для 23‑го мая 2008 года следуетиспользовать ДАТА (2008; 5; 23). Проблемы могут возникнуть, если даты вводятсякак текст.

Дата_согл– дата расчета за ценные бумаги (более поздняя, чем дата выпуска, когда ценныебумаги были проданы покупателю).

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

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

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

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

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

Частота– количество выплат по купонам за год. Для ежегодных выплат частота = 1; дляполугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.

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

Базис

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

0 или опущен Американский (NASD) 30/360 1 Фактический/фактический 2 Фактический/360 3 Фактический/365 4 Европейский 30/360

·          MicrosoftExcel хранит даты как ряд последовательных номеров, что позволяет выполнять надними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января2008 – номер 39448, так как интервал в днях между этими датами составляет39448.

·          Датасоглашения является датой продажи покупателю купона, например облигации. Срокплатежа представляет собой дату истечения срока действия купона. Пусть,например, облигация со сроком действия 30 лет выпущена 1 января 2008 года ибыла приобретена покупателем через шесть месяцев после своего выпуска. Датойвыпуска будет являться 1 января 2008 года, датой соглашения – 1 июля 2008года, а сроком погашения такой облигации – 1 января 2038 года, то естьдата через 30 лет после даты выпуска.

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

·          Еслидата_согл, дата_вступл_в_силу или последняя_выплата не является допустимойдатой, то функция ДОХОДПОСЛНЕРЕГ возвращает значение ошибки #ЗНАЧ!.

·          Еслиставка < 0 или цена ≤ 0, то функция ДОХОДПОСЛНЕРЕГ возвращает значениеошибки #ЧИСЛО!.

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

·          Должныбыть выполнены следующие условия, в противном случае функция ДОХОДПОСЛНЕРЕГвозвращает значение ошибки #ЧИСЛО!:

дата_вступл_в_силу> дата_согл > последняя_выплата

·          ДОХОДПОСЛНЕРЕГвычисляется следующим образом:

·          

/>

где:

Ai= количество накопленных дней для i‑го или последнего квазикупонногопериода в нерегулярном периоде, отсчитанное вперед от даты последней выплатыперед погашением.

DCi= количество дней, сосчитанных для i‑го или последнего квазикупонногопериода, разделенное на продолжительность фактического купонного периода.

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

NLi= нормальная продолжительность в днях i‑го или последнего квазикупонногопериода в нерегулярном купонном периоде.

/>


Задание1. Логические и статистические функции

Подсчитайтевступительный бал, если при среднем бале аттестата >=10 добавляется 0,5 балак вступительному балу.

 

Решение

 

/>

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


/>

Рисунок 3. – Расчет вступительного балла

 Задание 2. Финансово-экономические расчеты

1 Предприятие приобрело станок на сумму 3млн. грн., период отчислений в амортизационный фонд составляет 6 лет.Остаточная стоимость станка составит 1,8 тыс. грн.:

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

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

-   Рассчитатьвеличину амортизации актива методом двойного уменьшения остатка за следующиепериоды: 1–3 год, за 14–23 квартал и за 45–66 месяц.

-   Рассчитатьвеличину амортизации актива методом фиксированного уменьшения остатка за 2‑йгод и 5‑й год, если только в течение 11 месяцев в году происходитамортизационное отчисление.

2 Необходимонакопить 5600 тыс. грн. за два года, откладывая постоянную сумму в конце каждогоквартала. Какой должна быть эта сумма, если норма процента по вкладу составляет17% годовых.

Решение

Спомощьюфункции ExcelАПЛнайдем годовую амортизацию имущества дляуказанного периода.

АПЛ (стоимость; остаточная_стоимость;время_эксплуатации)

Нач_стоимость– это начальная стоимость имущества.

Ост_стоимость– это стоимость в конце периода амортизации (иногда называется остаточнойстоимостью имущества).

Время_эксплуатации– это количество периодов, за которые собственность амортизируется (иногданазывается периодом амортизации).

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

=АПЛ (3000; 1800;6) = 200 тыс. грн. – величина амортизации актива за один год

=ФУО (3000; 1800;6; 1; 3) = 61,5 тыс. грн. – величина амортизации актива за один квартал

=ФУО (3000; 1800;6; 1; 1) = 20,5 тыс. грн. – величина амортизации актива за один месяц

Амортизацииактива методом «суммы чисел»:

За четвертыйгод – =АСЧ (3000; 1800; 6; 4) = 171429 грн.

Задевятнадцатый квартал – АСЧ (3000; 1800; 24; 19) = 24000 грн.

За пятьдесятшестой месяц – АСЧ (3000; 1800; 72; 56) = 7763 грн.

Методдвойного уменьшения остатка

За четвертыйгод – =ДДОБ (3000; 1800; 6; 4) = 0

Задевятнадцатый квартал – ДДОБ (3000; 1800; 24; 19) = 0

За пятьдесятшестой месяц – ДДОБ (3000; 1800; 72; 56) = 0

Первый год – ДДОБ(3000; 1800; 6; 1) = 1000000 грн.

Второй год – ДДОБ(3000; 1800; 6; 2) = 200000 грн.

Третий год – ДДОБ(3000; 1800; 6; 3) = 0

Величина амортизацииактива методом двойного уменьшения остатка за 14–23 кварталы – 0 грн. и за 45–66месяц – 0 грн.

Величинаамортизации актива методом фиксированного уменьшения остатка за 2‑й год и5‑й год, если только в течение 11 месяцев в году происходит амортизационноеотчисление.

За 2‑йгод – ФУО (3000; 1800; 6; 2; 11) = 227509 грн.

За 5‑йгод – ФУО (3000; 1800; 6; 5; 11) = 176006 грн.

2. Необходимонакопить 5600 тыс. грн. за два года, откладывая постоянную сумму в конце каждогоквартала. Какой должна быть эта сумма, если норма процента по вкладу составляет17% годовых.

=ПЛТ (17%/4; 8;0; 5600; 0) = 602,36 грн.

Задание 3. Сортировка. Фильтрация. Команда итоги. Анализ данных спомощью диаграмм

Таблица Б.13– Структура доходов коммерческого банка

Статьи доходов тыс. грн. % к итогу Начисленные и полученные проценты 100354 NPP% Плати за кредитные ресурсы 18157 PKR% Комиссионные за услуги и корреспондентские отношения 37649 KUKO% Доходы по операциям с ценными бумагами и на валютном рынке 3427 DOCB% Доходы от лизинговых операций 512 DLO% Доходы от участия и деятельности предприятий, организаций и банков 1973 DUD% Плата за юридические услуги 2136 PUU% Итого: 100%

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

2 Выполнитьсортировку таблицы в алфавитном порядке по наименованиям статей доходов и поубыванию % к итогу.

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

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

6 Добавьте введомость новую графу«Вид дохода», присвоить вид – «По операциям», длядоходов коммерческого банка полученных от различных операций, а всем остальнымприсвоить вид «Иной». Создайте итоговые строки с подсчетом среднего удельноговеса дохода по одинаковым видам статей.

7 Построитьна новом рабочем листе EXCEL смешанную диаграмму, в которой представить ввиде гистограмм суммы доходов банка, а их удельные веса показать в виделинейного графика на той же диаграмме. Вывести легенду и название графика«Анализ доходов коммерческого банка».

8 Показать ввиде графика суммы доходов коммерческого банка, вывести линию тренда суравнением.

 

Решение

Таблица 1. – Сортировка документа по возрастанию наименований статейдоходов коммерческого банка

Статьи доходов тыс. грн. % к итогу Начисленные и полученные проценты 100354 NPP% Комиссионные за услуги и корреспондентские отношения 37649 KUKO% Плати за кредитные ресурсы 18157 PKR% Доходы по операциям с ценными бумагами и на валютном рынке 3427 DOCB% Плата за юридические услуги 2136 PUU% Доходы от участия и деятельности предприятий, организаций и банков 1973 DUD% Доходы от лизинговых операций 512 DLO% Итого: 100%

Таблица 2. – Сортировка таблицы в алфавитном порядке по наименованиямстатей доходов и по убыванию, % к итогу

Статьи доходов тыс. грн. % к итогу Плати за кредитные ресурсы 18157 3546% Плата за юридические услуги 2136 417% Начисленные и полученные проценты 100354 19600% Комиссионные за услуги и корреспондентские отношения 37649 7353% Итого: 158296 100% Доходы по операциям с ценными бумагами и на валютном рынке 3427 669% Доходы от участия и деятельности предприятий, организаций и банков 1973 385% Доходы от лизинговых операций 512 100%

3. Статьидоходов, значения сумм в тыс. грн. которых больше среднего значения

Статьи доходов тыс. грн. % к итогу Начисленные и полученные проценты 100354 19600% Итого: 158296 100%

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


Статьи доходов тыс. грн. Плати за кредитные ресурсы 18157 Плата за юридические услуги 2136 Комиссионные за услуги и корреспондентские отношения 37649 Доходы по операциям с ценными бумагами и на валютном рынке 3427 Доходы от участия и деятельности предприятий, организаций и банков 1973 Доходы от лизинговых операций 512

Таблица 3. – Итоговые строки с подсчетом среднего удельного веса доходапо одинаковым видам статей

Статьи доходов тыс. грн. % к итогу Вид дохода Плати за кредитные ресурсы 18157 11% иной Комиссионные за услуги и корреспондентские отношения 37649 23% иной Доходы по операциям с ценными бумагами и на валютном рынке 3427 2% иной

иной Среднее

12% #ДЕЛ/0! Начисленные и полученные проценты 100354 61% По операциям Доходы от лизинговых операций 512 0% По операциям Доходы от участия и деятельности предприятий, организаций и банков 1973 1% По операциям Плата за юридические услуги 2136 1% По операциям

По операциям Среднее

16% #ДЕЛ/0!

Общее среднее

14% #ДЕЛ/0!

7. Построитьна новом рабочем листе EXCEL смешанную диаграмму, в которой представить ввиде гистограмм суммы доходов банка, а их удельные веса показать в виделинейного графика на той же диаграмме. Вывести легенду и название графика«Анализ доходов коммерческого банка».


/>

8 Показать ввиде графика суммы доходов коммерческого банка, вывести линию тренда суравнением.

/>

Рисунок 4. – График суммы доходов коммерческого банка, линию тренда суравнением

 
Задание 4. Оптимизация решений

Кондитерскаяфабрика для производства трёх видов карамели «Дюшес», «Персик», «Клубничная»использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре.Нормы расхода сырья каждого типа на производство 1 т карамелиданноговида приведены в таблице. В ней же указано общее количество сырья каждого вида,которое может быть использовано фабрикой, а также приведена прибыль отреализации 1т карамели каждого вида.

Определитьоптимальный план производства продукции кондитерской фабрики, обеспечивающиймаксимальную прибыль от её реализации, если максимальный выпуск карамели вида«Барбарис» составляет 140 т., «Дюшеса» не менее 160 т.

Вид ингредиента Нормы расхода сырья, т. на 1т карамели Общее количество сырья, т

«Барбарис»

«Дыня»

«Дюшес»

Сахарный песок

Патока

Фруктовое пюре

0,55

0,2

0,5

0,25

0,3

0,7

0,6

0,51

470

265

250

Прибыль, грн./т 4000 4800 4100

Решение

/>

/>

Итак, решениенайдено: «барбариса» выпускаем 140 т, «дыни» – 561,33 т, «Дюшеса» – 160 т.Максимальная прибыль – 3 910 400 грн.

еще рефераты
Еще работы по информатике, программированию