Загрузка...
Учебная работа. Контрольная работа: Функции Excel для финансовых расчетов по ценным бумагам
Набросок 1. – Окно аргументов функции
Функция ДОХОДПЕРВНЕРЕГ() возвращает Доход по ценным бумагам с нерегулярным (маленьким либо длинноватым) первым периодом.
Если данная функция недосягаема либо возвращает ошибку #имя?, установите и загрузите надстройку «Пакет анализа».
ДОХОДПЕРВНЕРЕГ (дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; стоимость; погашение; частота; базис)
Даты должны вводиться с внедрением функции ДАТА либо как итог вычисления остальных формул и функций. к примеру, для 23‑го мая 2008 года следует употреблять ДАТА (2008; 5; 23). Препядствия могут появиться, если даты вводятся как текст.
Дата_согл – дата расчета за ценные бумаги (наиболее поздняя, чем дата выпуска, когда ценные бумаги были проданы покупателю).
Дата_вступл_в_силу – срок погашения ценных бумаг. Эта дата описывает момент истечения срока деяния ценных бумаг.
Дата_выпуска – дата выпуска ценных бумаг.
Первый_купон – дата первого купона для ценных бумаг.
Ставка – процентная ставка для ценных бумаг.
Стоимость – стоимость ценных бумаг.
Погашение – выкупная стоимость ценных бумаг за 100 грн. номинальной цены.
Частота – количество выплат по купонам за год. Для каждогодних выплат частота = 1; для полугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.
Базис – применяемый метод вычисления денька.
· Microsoft Excel хранит даты как ряд поочередных номеров, что дозволяет делать над ними вычисления. По дефлоту денек 1 января 1900 года имеет номер 1, а 1 января 2008 – номер 39448, потому что интервал в деньках меж этими датами составляет 39448.
· Дата соглашения является датой реализации покупателю купона, к примеру облигации. Срок платежа представляет собой дату истечения срока деяния купона. Пусть, к примеру, облигация со сроком деяния 30 лет выпущена 1 января 2008 года и была приобретена покупателем через 6 месяцев опосля собственного выпуска. Датой выпуска будет являться 1 января 2008 года, датой соглашения – 1 июля 2008 года, а сроком погашения таковой облигации – 1 января 2038 года, другими словами дата через 30 лет опосля даты выпуска.
· Дата_согл, дата_вступл_в_силу, дата_выпуска, первый_купон и базис усекаются до целых.
· Если дата_согл, дата_вступл_в_силу, дата_выпуска либо первый_купон не является допустимой датой, то функция ДОХОДПЕРВНЕРЕГ возвращает
· Если ставка < 0 либо стоимость ≤ 0, то функция ДОХОДПЕРВНЕРЕГ возвращает
· Должны быть выполнены последующие условия, в неприятном случае функция ДОХОДПЕРВНЕРЕГ возвращает значение ошибки #ЧИСЛО!:
дата_вступл_в_силу > первый_купон > дата_согл > дата_выпуска
· Microsoft Excel вычисляет функцию ДОХОДПЕРВНЕРЕГ способом итераций. Употребляется способ Ньютона на базе формулы для функции ЦЕНАПЕРВНЕРЕГ. Доход рассчитывается за 100 итераций, до того времени, пока вычисляемая стоимость для данного дохода не станет близкой к значению аргумента стоимость.
Пример
Чтоб просмотреть числа в виде процентов, выделите ячейку и изберите в меню
команду
. На вкладке
изберите в перечне
вариант
.
ДОХОДПОСЛНЕРЕГ
Возвращает Доход по ценным бумагам с нерегулярным (маленьким либо длинноватым) крайним периодом.
ДОХОДПОСЛНЕРЕГ (дата_согл; дата_вступл_в_силу; последняя_выплата; ставка; стоимость; погашение; частота; базис)
Даты должны вводиться с внедрением функции ДАТА либо как итог вычисления остальных формул и функций. к примеру, для 23‑го мая 2008 года следует употреблять ДАТА (2008; 5; 23). Препядствия могут появиться, если даты вводятся как текст.
Дата_согл – дата расчета за ценные бумаги (наиболее поздняя, чем дата выпуска, когда ценные бумаги были проданы покупателю).
Дата_вступл_в_силу – срок погашения ценных бумаг. Эта дата описывает момент истечения срока деяния ценных бумаг.
Последняя_выплата – дата крайнего купона для ценных бумаг.
Ставка – процентная ставка для ценных бумаг.
Стоимость – стоимость ценных бумаг.
Погашение – выкупная стоимость ценных бумаг за 100 грн. номинальной цены.
Частота – количество выплат по купонам за год. Для каждогодних выплат частота = 1; для полугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.
Базис – применяемый метод вычисления денька.
Базис
Метод вычисления денька
0 либо опущен
Южноамериканский (NASD) 30/360
1
Фактический/фактический
2
Фактический/360
3
Фактический/365
4
Европейский 30/360
· Microsoft Excel хранит даты как ряд поочередных номеров, что дозволяет делать над ними вычисления. По дефлоту денек 1 января 1900 года имеет номер 1, а 1 января 2008 – номер 39448, потому что интервал в деньках меж этими датами составляет 39448.
· Дата соглашения является датой реализации покупателю купона, к примеру облигации. Срок платежа представляет собой дату истечения срока деяния купона. Пусть, к примеру, облигация со сроком деяния 30 лет выпущена 1 января 2008 года и была приобретена покупателем через 6 месяцев опосля собственного выпуска. Датой выпуска будет являться 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 тыс. грн.:
— Высчитать величину амортизации актива линейным способом за один год, за один квартал и за один месяц.
— Высчитать величину амортизации актива способом «суммы чисел» и способом двойного уменьшения остатка за 4-ый год, за девятнадцатый квартал и за 50 6-ой месяц.
— Высчитать величину амортизации актива способом двойного уменьшения остатка за последующие периоды: 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 тыс. грн. – величина амортизации актива за один месяц
Амортизации актива способом «суммы чисел»:
За 4-ый год – =АСЧ (3000; 1800; 6; 4) = 171429 грн.
За девятнадцатый квартал – АСЧ (3000; 1800; 24; 19) = 24000 грн.
За 50 6-ой месяц – АСЧ (3000; 1800; 72; 56) = 7763 грн.
способ двойного уменьшения остатка
За 4-ый год – =ДДОБ (3000; 1800; 6; 4) = 0
За девятнадцатый квартал – ДДОБ (3000; 1800; 24; 19) = 0
За 50 6-ой месяц – ДДОБ (3000; 1800; 72; 56) = 0
1-ый год – ДДОБ (3000; 1800; 6; 1) = 1000000 грн.
2-ой год – ДДОБ (3000; 1800; 6; 2) = 200000 грн.
3-ий год – ДДОБ (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. При помощи средства Расширенный фильтр сформировать новейший документ, в который поместить лишь те статьи доходов коммерческого банка, сумма каждой из которых меньше среднего значения этого показателя по всему начальному документу либо равна 100 354 тыс. грн.
Статьи доходов
тыс. грн.
Плати за кредитные ресурсы
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
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 грн.
]]>