Лабораторная работа №11.
Использование финансовых функций MS Excel для расчета по займам и кредитам
Цель: Изучение возможностей пакета MS Excel при решении финансовых задач. Приобретение навыков решения финансовых задач, расчетов по займам и кредитам.
Технология работы.
Упражнение 1.
Задача:
Фирма создает фонд для погашения долгосрочных обязательств, для чего перечисляет ежегодно в течение 4 лет платежи размером 200тыс.р. В конце каждого года, на которые начисляются сложные проценты по ставке 25% годовых, начисляемых ежеквартально. Определить величину фонда к концу выплат.
Технология работы:
Для решения данной задачи необходимо выполнить следующие действия:
1. Вызвать мастер функций.
2. Из общего списка выбрать функцию БС.
3. Заполнить диалоговое окно, то есть внести необходимые аргументы функции (в поля ввода можно вводить как ссылки на ячейки, содержащие значения аргументов, так и сами значения), рис.1.
4. Завершить ввод аргументов и запуск расчета значений функции нажатием – кнопки ОК.
5. При отказе работы с функцией нажать кнопку Отмена.
Рис. 1. Диалоговое окно ввода аргументов функции БС.
Краткие комментарии по значениям аргументов функции БС:
• величина периодических выплат (поле Выплата), согласно правилам заполнения должна быть введена со знаком минус;
• по условию задачи мы соответствующим образом скорректировали поля Норма и Число периодов, так как предусматривается ежеквартальное начисление процентов.
• значение 0 у параметра Тип принимается по умолчанию и означает, что применяется аннуитет постнумерандо (взносы в конце года), значение –1 означает аннуитета пренумерандо (взносы в начале года).
Параллельно процессу заполнения полей аргументов происходит формирование результата расчетов, окончательно он равен 2271,93. Для завершения ввода функции БЗ в ячейку рабочего листа MS Excel следует нажать кнопку ОК. Читателю в качестве упражнения предлагается проверить самостоятельно, что в случае аннуитета пренумерандо результат был бы равен 5241,37.
В результате ячейка будет содержать формулу:
=БЗ(0,25/4*4;-4;-200;;0)
Ответ: 5241,37.
Упражнение 2.
Задача:
Банк выдает долгосрочный кредит в размере 2000 тыс. р. по сложной ставке 15% годовых. Определить сумму долга через 10 лет.
Технология работы:
Для решения данной задачи необходимо выполнить следующие действия:
1. Вызвать мастер функций.
2. Из общего списка выбрать функцию БС.
3. Заполнить диалоговое окно, то есть внести необходимые аргументы функции (в поля ввода можно вводить как ссылки на ячейки, содержащие значения аргументов, так и сами значения), рис.2.
4. Завершить ввод аргументов и запуск расчета значений функции нажатием – кнопки ОК.
5. При отказе работы с функцией нажать кнопку Отмена.
Рис.2. Диалоговое окно ввода аргументов функции БС.
Необходимо правильно заполнить поля ввода функции БС. Для решения этой задачи нужно заполнить не поле Выплата, а поле ПС, так как в этом случае финансовая функция будет рассчитывать значение FV просто по формуле нахождения будущей стоимости дли сложных процентов:
FV=PV(1+i)".
Заполним финансовую функцию БС следующим образом: =БЗ(0,15;10;;-2000).
Ответ: 8091,12.
Упражнение 3.
Задача:
Платежи в фонд будут вноситься ежегодно по 350 тыс.р. в течение 7 лет с начислением па них сложных процентов по ставке 12% годовых. Определить современную сумму всех платежей с начисленными процентами.
Технология работы:
Для решения данной задачи необходимо выполнить следующие действия:
1. Вызвать мастер функций.
2. Из общего списка выбрать функцию ПС.
3. Заполнить диалоговое окно, то есть внести необходимые аргументы функции (в поля ввода можно вводить как ссылки на ячейки, содержащие значения аргументов, так и сами значения), рис.3.
4. Завершить ввод аргументов и запуск расчета значений функции нажатием – кнопки ОК.
5. При отказе работы с функцией нажать кнопку Отмена.
Для решения следует использовать функцию
=ПС(норма:0,12; число периодов:7; выплата:-350;; тип:0).
Рис.3. Диалоговое окно ввода аргументов функции ПС.
Ответ: 1597,31.
Упражнение 4.
Задача:
Инвестиции в проект составляют 700тыс.р. В последующие 4 года ожидаются следующие годовые доходы по проекту: 150; 250; 300; 450тыс.р. Издержки привлечения капитала 5%. Рассчитать чистую текущую стоимость проекта.
Технология работы:
Для решения данной задачи необходимо выполнить следующие действия:
1. Вызвать мастер функций.
2. Из общего списка выбрать функцию ЧПС.
3. Заполнить диалоговое окно, то есть внести необходимые аргументы функции (в поля ввода можно вводить как ссылки на ячейки, содержащие значения аргументов, так и сами значения), рис.4.
4. Завершить ввод аргументов и запуск расчета значений функции нажатием – кнопки ОК.
5. При отказе работы с функцией нажать кнопку Отмена.
Для решения следует использовать функцию
=ЧПС(норма: 0,05; значения: — диапазон ячеек со значениями доходов по проекту).
Для нахождения непосредственного значения NPV необходимо вычесть начальную инвестицию.
Рис.4. Диалоговое окно ввода аргументов функции ЧПС.
Для нахождения непосредственного значения NPV необходимо вычесть начальную инвестицию: 998,98-700=298,98.
Следует заметить, что положительное значение NPV является показателем того, что проект приносит чистую прибыль своим инвесторам после покрытия всех связанных с ним расходов.
Ответ: 298,98.
Упражнение 5.
Задача:
Инвестиции в проект составляют 700 тыс.р. В последующие 4 года ожидаются следующие годовые доходы по проекту: 150; 250; 300; 450 тыс.р. Издержки привлечения капитала 5%. Рассчитать чистую текущую стоимость проекта.
Заданы конкретные даты: выплата – 1.01.11г., поступления –2.02.11г., 15.03.11г., 25.03.11г., 10.04.11г. соответственно.
Технология работы:
Для решения данной задачи необходимо выполнить следующие действия:
1. В ячейки А2:А6 денежные значения. В ячейки В2:В6 ввести значения даты. В С2 значение процентной ставки. Установить курсор в ячейку А1. Далее, вызвать мастер функций.
2. Из общего списка выбрать функцию ЧИСТНЗ.
3. Заполнить диалоговое окно, то есть внести необходимые аргументы функции (в поля ввода можно вводить как ссылки на ячейки, содержащие значения аргументов, так и сами значения), рис.5.
4. Завершить ввод аргументов и запуск расчета значений функции нажатием – кнопки ок.
5. При отказе работы с функцией нажать кнопку Отмена.
Для
решения следует использовать функцию
ЧИСТНЗ
Рис.5. Диалоговое окно ввода аргументов функции ЧИСТНЗ.
Заметим, что значение начальной выплаты должно быть введено со знаком минус.
Вид листа MS Excel представлен на рисунке 6.
Рис.6. Вид рабочего листа.
Ответ: 437,70.
Приложение 1. Список финансовых функций ms excel
АМОРУВ Возвращает пропорциональную амортизацию имущества для каждого отчетного периода
АПЛ Возвращает величину амортизации актива за один период, рассчитанную линейным методом
АСЧ Возвращает величину амортизации актива за данный период, рассчитанную методом суммы годовых чисел
БЗРАСПИС Возвращает будущее значение основного капитала после начисления сложных процентов
БС Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки
ВСД Возвращает внутреннюю ставку доходности для ряда потоков денежных средств, представленных численными значениями
ДАТАКУПОНДО Возвращает предыдущую дату купона перед датой соглашения
ДАТАКУПОНПОСЛЕ Возвращает следующую дату купона после даты соглашения
ДДОБ Возвращает значение амортизации актива за данный период, использую метод двойного уменьшения остатка или иной явно указанный метод
ДЛИТ Возвращает ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам
ДНЕЙКУПОН Возвращает число дней в периоде купона, который содержит дату соглашения
ДНЕЙКУПОНДО Возвращает количество дней от начала периода купона до даты соглашения
ДНЕЙКУПОНПОСЛЕ Возвращает число дней от даты соглашения до срока следующего купона
ДОХОД Возвращает доход от ценных бумаг, который составляет периодические процентные выплаты
ДОХОДКЧЕК Возвращает доход по казначейскому чеку
ДОХОДПЕРВНЕРЕГ Возвращает доход по ценным бумагам с первой процентной выплаты
ДОХОДПОГАШ Возвращает годовой доход от ценных бумаг, который составляет доход в срок вступления в силу
ДОХОДПОСЛНЕРЕГ Возвращает доход по ценным бумагам с последней процентной выплатой
ДОХОДСКИДКА Возвращает ежегодный доход по уцененным ценным бумагам. Например, по казначейскому чеку
ИНОРМА Возвращает процентную ставку для полностью инвестированных ценных бумаг
КПЕР Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки
МВСД Возвращает внутреннюю ставку доходности для ряда периодических денежных потоков, учитывая как затраты на привлечение инвестиции, так и процент, получаемый от реинвестирования денежных средств
МДЛИТ Возвращает модифицированную длительность Макалея для ценных бумаг с предполагаемой нарицательной стоимостью 100руб.
НАКОПДОХОД Возвращает накопленных доход по ценным бумагам с периодической выплатой процентов
НАКОПДОХОДПОГАШ Возвращает накопленный доход по ценным бумагам, процент по которым выплачивается в срок вступления в силу
НОМИНАЛ Возвращает номинальную годичную процентную ставку
ОБЩДОХОД Возвращает общую выплату по займу между двумя периодами
ОБЩПЛАТ Возвращает общую выплату, проведенную между двумя периодическими выплатами
ОСПЛТ Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки
ПЛТ Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки
ПОЛУЧЕНО Возвращает сумму, полученную в срок вступлении в силу полностью обеспеченных ценных бумаг
ПРОЦПЛАТ Вычисляет проценты, выплачиваемые за определенный инвестиционный период
ПРПЛТ Возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки
ПС Возвращает приведенную (к текущему моменту) стоимость инвестиции – общую сумму, которая на настоящий момент равноценна ряду будущих выплат
ПУО Возвращает величину амортизации актива для любого выбранного периода, в том числе для частичных периодов, с использованием метода двойного уменьшения остатка или иного указанного метода
РАВНОКЧЕК Возвращает эквивалентный облигации доход по казначейскому чеку
РУБЛЬ.ДЕС Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом
РУБЛЬ.ДРОБЬ Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби
СКИДКА Возвращает норму скидки для ценных бумаг
СТАВКА Возвращает процентную ставку по аннуитету за один период. Например при годовой процентной ставке в 6% для квартальной ставки используется значение 6%/4
ФУО Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка
ЦЕНА Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым выплачивается периодический доход
ЦЕНАКЧЕК Возвращает цену за 100 руб. нарицательной стоимости для казначейского чека
ЦЕНАПЕРВНЕРЕГ Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг с нерегулярным первым периодом
ЦЕНАПОГАШ Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, доход по которым выплачивается в срок вступления в силу
ЦЕНАПОСЛНЕРЕГ Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг с нерегулярным последним периодом
ЦЕНАСКИДКА Возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, на которые сделана скидка
ЧИСЛКУПОН Возвращает количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу
ЧИСТВНДОХ Возвращает внутреннюю сумму дохода для расписания денежных поступлений
ЧИСТНЗ Возвращает чистую текущую стоимость инвестиции, вычисляемую на основе ряда периодических поступлений наличных и нормы амортизации
ЧПС Возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования и стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).
ЭФФЕКТ Возвращает действующие ежегодные процентные ставки