Лабораторная работа 1. Специфика использования финансовых функций EXCEL

В результате освоения дисциплины обучающийся должен

Знать: профессионально-ориентированные компьютерные системы, комплексы, пакеты и программы и технологию их применение на железнодорожном транспорте.

Уметь: применять навыки и умения работать в среде специализированных информационных систем поддержки, анализа и исследования предметных областей экономики для получения объективной оценки экономической деятельности, прогнозирования и планирования научно-обоснованных управленческих решений.

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

КОМПЕТЕНЦИИ ОБУЧАЮЩЕГОСЯ, ФОРМИРУЕМЫЕ В РЕЗУЛЬТАТЕ ОСВОЕНИЯ ДИСЦИПЛИНЫ (МОДУЛЯ)

ОК-12: способен понимать сущность и значение информации в развитии современного информационного общества, сознавать опасности и угрозы, возникающие в этом процессе, соблюдать основные требования информационной безопасности, в том числе защиты государственной тайны;

ОК-13: владеет основными методами, способами и средствами получения, хранения, переработки информации, имеет навыки работы с компьютером как средством управления информацией, способен работать с информацией в глобальных компьютерных сетях;

ПК-5: способен выбрать инструментальные средства для обработки экономических данных в соответствии с поставленной задачей, проанализировать результаты расчетов и обосновать полученные выводы;

ПК-14: способен преподавать экономические дисциплины в образовательных учреждениях различного уровня, используя существующие программы и учебно-методические материалы;

ПК-15: способен принять участие в совершенствовании и разработке учебно-методического обеспечения экономических дисциплин.

ГЛАВА I КОМПЬЮТЕРНЫЕ ПРОГРАММЫ ДЛЯ РЕШЕНИЯ ФИНАНСОВО-ЭКОНОМИЧЕСКИХ ЗАДАЧ

Лабораторная работа 1. Специфика использования финансовых функций EXCEL

Цель работы:Освоить навыки работы с основными финансовыми функциями в Excel 2010 (ПС, БС, ПЛТ, ОСПЛТ, ПРПЛТ).

Порядок работы:

1. Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru При работе с финансовыми функциями используются специальные финансовые термины. Далее идет разъяснение основных финансовых терминов, необходимых для работы с финансовыми функциями.

  1. Запустите программу MS Excel 2010.

3. В открывшемся окне нажмите кнопку Новая книга для создания новой рабочей области. Щелкните по кнопке Создать.

4. В поле Имя файла (файл сохранить как) задайте имя вашей рабочей книги lab1_FIO (где FIO - ваши инициалы) и выберите диск D/папка студент, создайте папку со своей фамилией и номером группы, где будут храниться все ваши лабораторные работы.

  1. . При этом откроется окно вашей рабочей книги(Рис.1).

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.1. Новая рабочая книга - lab1_FIO

Финансовые функции EXCEL предназначены для вычисления базовых величин, необходимых при проведении сложных финансовых расчетов. Методика изучения и ис­пользования финансовых функций EXCEL требует соблюдения определенной техноло­гии.

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

2. Для расчета результата финансовой функции EXCEL курсор устанавливается в новую ячейку для ввода формулы, использующей встроенную финансовую функцию; если финансовая функция вызывается в продолжение ввода другой формулы, данный пункт опускается.

3. Осуществляется вызов Мастера функций с помощью команды ВСТАВКА-Функция или нажатием одноименной кнопки на панели инструментов Стандартная.

4. Выполняется выбор категории Финансовые (Рис.2).

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.2. Экран вызова Мастера функции

В списке Функция содержится полный перечень доступных функций выбранной категории. Поиск функций осуществляется путем последовательного просмотра списка. Для выбора функций курсор устанавливается на имя функции. В нижней части окна при­веден краткий синтаксис и справка о назначении выбираемой функции. Кнопка Отмена прекращает работу Мастера функции.

5. Выполняется выбор в списке требуемой финансовой функции, в результате выбора появляется диалоговое окно для ввода аргументов (Рис.3).

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.3. Диалоговое окно Ввода аргументов функции

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

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

7. Если аргумент является результатом расчета другой встроенной функции EXCEL, возможно организовать вычисление вложенной встроенной функции путем вызова Мастера функций одноименной кнопкой, расположенной перед полем ввода аргумента.

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

9. Для отказа от работы со встроенной функцией нажимается кнопка Отмена.

10. Завершение ввода аргументов и записи расчета значения встроенной функции выполняется нажатием кнопки ОК.

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

Технология работы в окне редактирования аналогична рассмотренной выше.

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

Формула начинается со значка ( = ). Далее следует имя функции, а в круглых скобках указываются ее аргументы в последовательности, соответствующей синтаксису функции. В качестве разделителя аргументов используется выбранный при настройке WINDOWS разделитель, обычно это точка с запятой ( ; ) или запятая ( , ).

Например: в ячейку В10 введена формула:

= ДОХОД (В16; В 18; 0,08; 47,727; 100; 2; 0)

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

Задание значений аргументов финансовых функций имеет определенную специфику.

1. Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды), представляются положительными числами.

2. Все даты как аргументы функции имеют числовой формат представления, например, дата 22 июня 2015 года представлена числом 42177. Если значение аргумента типа Дата берется из ячейки (например, дата_согл-ссылка на ячейку В16), то дата в ячейке может быть записана в обычном виде, например, как 22.06.2015.

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

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

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

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

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

Следует помнить, что финансовые операции можно проводить по разным схемам начисления процентов.

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

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru Сложный процент предполагает такой способ наращения, при котором начисление процентов производится на всю накопленную к этому моменту сумму.

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

Формула для начисления простых процентов выглядит следующим образом:

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru (1)

где

S - наращенная сумма

P - первоначальная сумма

t - количество периодов начисления процентов (лет, кварталов, месяцев, дней)

i - процентная ставка за период начисления

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

Например, если задана годовая процентная ставка равная 10%, а проценты должны быть начислены через 3 месяца, то процентная ставка за период будет равна Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru .

Пример 1. Требуется рассчитать сумму возврата ссуды и доход банка, если была выдана ссуда (P) 500 000 руб., на срок (t) один год, под простую процентную ставку (i) — 8% годовых.

Решение

Сумма наращения денег по простым процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Доход банка – это разность между суммой выдачи и суммой возврата (т.е. величина процентов по ссуде):

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Пример 2.Требуется рассчитать сумму возврата ссуды и доход банка, если была выдана ссуда (P) 500 000 руб., на срок (t) три года, под простую процентную ставку (i)— 8% годовых.

Решение

Сумма наращения денег по простым процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Пример 3. Требуется рассчитать сумму возврата ссуды и доход банка, если была выдана ссуда (P) 500 000 руб., на срок (t) 6 месяцев, под простую процентную ставку (i) — 8% годовых.

Решение

Так как период составляет 6 месяцев, а процентная ставка годовая (12 месяцев), то следует определить процентную ставку за период Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru .

Сумма наращения денег по простым процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Пример 4. Требуется рассчитать сумму, причитающуюся к возврату, если сумма кредита составляет 1 000 000 ден. ед., кредит взят 09.10.2015, срок погашения кредита 01.01.2020 при ставке простых процентов 18% годовых. В расчете использовать функцию ДНЕЙ360.

Решение

В приведенной задаче периодом начисления процентов является один день. С помощью функции ДНЕЙ360[1] определим количество периодов начисления процентов (т.е. разницу между двумя датами). Процентная ставка задана годовая, период начисления процентов один день, следовательно, процентная ставка за период равна Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru .

Сумма наращения денег по простым процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

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

Формула для начисления сложных процентов выглядит следующим образом:

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru (2)

где

S - наращенная сумма;

P - первоначальная сумма;

t - количество периодов начисления процентов (лет, месяцев, кварталов и т.п.);

i - процентная ставка за период начисления.

Процентная ставка должна соответствовать периоду начисления процентов также как и в случае начисления по простым процентам.

Пример 5. Требуется рассчитать сумму возврата ссуды и доход банка, если была выдана ссуда 500 000 руб., на срок один год, под сложную процентную ставку — 8% годовых. Начисление процентов осуществляется один раз в квартал.

Решение

Так как процентная ставка задана годовая, а проценты будут начисляться по истечении квартала, то следует найти процентную ставку за период - квартал. Для этого разделим годовую ставку 8% на 4 (количество кварталов в году). Таким образом, в приведенной задаче Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru .

Сумма наращения денег по сложным процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Пример 6. Требуется рассчитать сумму, причитающуюся к возврату, если сумма кредита составляет 20 000 ден.ед., срок 6 кварталов при ставке сложных процентов 12% годовых с начислением процентов 1 раз в полгода.

Решение

Так как процентная ставка задана годовая, а проценты будут начисляться по истечении полугодия, то следует найти процентную ставку за период - полугодие. Для этого разделим годовую ставку 12% на 2 (количество полугодий в году) и получим i=6%. Кредит взят на 6 кварталов, т.е. на 18 месяцев (в году 4 квартала, в квартале 3 месяца)). В течение 18 месяцев проценты будут начисляться каждые 6 месяцев, т.е. всего 3 раза. Таким образом, Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru .

Сумма наращения денег по сложным процентам составит

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

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

Мастер функций Excel 2010 в категории финансовых предлагает порядка 50 функций. Среди полного перечня финансовых функций Microsoft Excel, непосредственно предназначенных для финансовых расчетов, выделяется группа функций, используемая для анализа инвестиций и расчета операций по кредитам, ссудам и займам на основе периодических, равных по величине сумм платежей и постоянной процентной ставки. Рассмотрим некоторые из них.

БС (ставка; кпер; плт; пс; тип2) – вычисляет будущую стоимость инвестиции (вклада) на основе периодических, равных по величине сумм платежей и постоянной процентной ставки.

КПЕР (ставка; плт; пс; бс; тип) – вычисляет общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

ОСПЛТ (ставка; период; кпер; пс; бс; тип) – возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

ПЛТ (ставка; кпер; пс; бс; тип) – вычисляет сумму периодического платежа для аннуитета[2] на основе постоянства сумм платежей и постоянства процентной ставки.

ПРПЛТ (ставка; период; кпер; пс; бс; тип) – возвращает сумму платежей процентов по инвестиции заданный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

ПС (ставка; кпер; плт; бс; тип) – рассчитывает приведенную к текущему моменту стоимость инвестиции, которая на настоящий момент равноценна ряду будущих выплат.

СТАВКА (кпер; плт; пс; бс; тип; предположение) – определяет процентную ставку по аннуитету за один период, используя итерационный метод.

НОМИНАЛ (эффективная_ставка; кол_пер) – вычисляет номинальную годовую процентную ставку по эффективной ставке и количеству периодов в году, за которые начисляются сложные проценты.

ЭФФЕКТ (номинальная_ставка; кол_пер) – вычисляет эффективную (фактическую) годовую процентную ставку по номинальной ставке и количеству периодов в году, за которые начисляются сложные проценты.

Большинство функций имеют одинаковый набор базовых аргументов:

Ставка – процентная ставка за период (а не за год);

Кпер – число периодов начисления процентов (а не срок);

Плт – величина периодического платежа. Это значение не может изменяться на протяжении всего периода выплат. Платеж производится в период начисления процентов, т.е. если проценты начисляются раз в полгода, то платеж производится раз в полгода;

Пс – начальное значение вклада или займа;

Бс – будущее значение вклада или займа;

[Тип] – тип начисления процентов (1 – начало периода (пренумерандо), 0 – конец периода (постнумерандо)), необязательный аргумент.

Период – Период, для которого определяется прибыль (выплата); находится в интервале от 1 до Кпер.

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

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

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

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

Пример 7.

Нужно определить какую сумму кредита можно взять под 18% годовых с ежемесячным начислением процентов в конце периода (постнумерандо[3]), если

выплачивать мы можем на протяжении 2-х лет с периодическим платежом в 500 руб. и в итоге готовы выплатить 300 000 руб.

Алгоритм решения.

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

Из условия задачи понятно, что найти нужно начальное значение кредита, т.е. ПС. Раз это кредит, то ПС получит знак «плюс», т.к. ожидается приток денежных средств клиенту. Будущее значение кредита Бс равно 300 000 руб. со знаком «минус», т.к. эти деньги будут возвращены банку. Каждый период клиент выплачивает банку Плт по 500 руб. Эта сумма будет со знаком минус, т.к. происходит отток денежных средств у клиента. Проценты начисляются ежемесячно, следовательно, периодом является месяц и необходимо найти процентную ставку Ставка за месяц. Для этого годовую процентную ставку 18% нужно разделить на 12. Выплаты будут производиться в течение 2-х лет ежемесячно, следовательно, периодов выплат Кпер будет 24 (2*12). Тип равняется «0» так как проценты начисляются в конце периода.

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.4. Экран вызова Функция ПС

В результате получается, что кредит можно взять в размере 219 878,38 руб.

Пример 8.

Вычислить выплаты по процентам за первый месяц для трехгодичного займа в 100 000 рублей из расчета 10% годовых.

Алгоритм решения.

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

Выплаты по процентам рассчитываются по функции ПРПЛТ. Результат должен быть получен со знаком «минус», так как операция влечет за собой отток денежных средств клиента. Проценты начисляются ежемесячно, следовательно, периодом является месяц и необходимо найти процентную ставку Ставка за месяц. Для этого годовую процентную ставку 10% нужно разделить на 12. Период задается равным «1» (по условию задачи). Выплаты будут производиться в течение 3-х лет ежемесячно, следовательно, периодов выплатКпер будет 36 (3*12). ПС задается со знаком «плюс», так как операция займа означает приток денежных средств клиенту.

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.5. Экран вызова Функция ПРПЛТ

В результате получаем значение -833,33

Пример 9.

Для условий задачи из Примера 8 необходимо определить величину платежа в погашение основной суммы. В вашей рабочей книге lab1_FIO, откройте Лист 3, в пустую ячейку поставьте курсор и вызовите мастер функции, введите исходные данные для решения задачи как показано на рис. 6. Воспользуемся функцией ОСПЛТи получим результат - 2393,39 .

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.6. Экран вызова Функция ОСПЛТ

Пример 10.

Рассчитать платежи по кредиту суммой 100 000 рублей, сроком на 1 год под 18% годовых. Проценты начисляются ежемесячно. Показать динамику изменения платежей по основному долгу, по процентам и оставшейся суммы основного долга.

Алгоритм решения.

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

Ежемесячный платеж рассчитывается с помощью функции ПЛТили как сумма платежей по основному долгу и платежей по процентам.

Платежи по основному долгу рассчитываются по функции ОСПЛТ, процентные платежи рассчитываются по функции ПРПЛТ. В качестве аргумента «Период» задается номер месяца, для которого производится расчет.

Лабораторная работа 1. Специфика использования финансовых функций EXCEL - student2.ru

Рис.7. Экран вызова Лист расчета

Наши рекомендации