работа. Технология использования средств Excel для финансовых расчетов
 Скачать 43.74 Kb.
|
|
Информация на тему: «Технология использования средств Excel для финансовых расчетов» К средствам EXCEL финансового анализа относятся: финансовые функции EXCEL; подбор параметров; диспетчер сценариев: для создания, редактирования, объединения, удаления и просмотра созданных сценариев расчетов для ячеек текущего рабочего листа; таблица подстановки: для выбора наиболее оптимального варианта. Финансовый анализ инвестиций. Описание аргументов финансовых функций EXCEL, использующих базовые модели1.
Финансовые функции EXCEL предназначены для вычисления базовых величин, необходимых при проведении сложных финансовых расчетов. Методика изучения и использования финансовых функций EXCEL требует соблюдения определенной технологии. 1. На рабочем листе в отдельных ячейках осуществляется подготовка значений основных аргументов функции. 2. Для расчета результата финансовой функции EXCEL курсор устанавливается в новую ячейку для ввода формулы, использующей встроенную финансовую функцию; если финансовая функция вызывается в продолжении ввода другой формулы, данный пункт опускается. 3. Осуществляется вызов Мастера функции с помощью команды ВСТАВКА, Функция или нажатие модноименной кнопкина панели инструментов Стандартная. 4. Выполняется выбор категории Финансовые. В списке Функция содержится полный перечень доступных функций выбранной категории. Поиск функции осуществляется путем последовательного просмотра списка. Для выбора функции курсор устанавливается на имя функции. В нижней части окна приведен краткий синтаксис и справка о назначении выбираемой функции. Кнопка Справка вызывает экран справки для встроенной функции, на которой установлен курсор. Кнопка Отмена прекращает работу Мастера функций. При нажатии на кнопку OK осуществляется переход к работе с диалоговым окном выбранной функции. 5. Выполняется выбор в списке требуемой финансовой функции, в результате выбора появляется диалоговое окно для ввода аргументов. Для каждой финансовой функции существует регламентированный по составу и формату значений перечень аргументов. 6. В поля ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов2. 7. Если аргумент является результатом расчета другой встроенной функции EXCEL, возможно организовать вычисление вложенной встроенной функции путем вызова Мастера функции одноименной кнопкой, расположенной перед полем ввода аргумента. 8.Возможна работа с экраном справки, поясняющей назначение и правила задания аргументов функции; вызов справки осуществляется путем нажатия кнопки Справка. 9. Для отказа от работы со встроенной функцией нажимается кнопка Отмена. 10. Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки OK. При необходимости корректировки значений аргументов функции (изменение ссылок, постоянных значений и т. п.) необходимо установить курсор в ячейку, содержащую формулу, и вызвать Мастер функций. Возможен также вариант непосредственного ввода формулы, содержащей имена и параметры встроенных финансовых функций (без вызова Мастера функций)3. Формула начинается со знака =. Далее следует имя функции, а в круглых скобках указываются ее аргументы в последовательности, соответствующей синтаксису функции. В качестве разделителя аргументов используется выбранный при настройке Windows разделитель, обычно это точка с запятой (;) или запятая (,). Например, в ячейку В10 введена формула: = ДОХОД(В16; В17; 0.08; 47.727; 100; 2; 0). Отдельные аргументы функции могут быть как константами, так и ссыпками на адреса ячеек (например, в данном случае). Рассмотрим специфику задания значений аргументов финансовых функций. 1.Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды), представляются положительными числами. Все даты как аргументы функции имеют числовой формат представления, например, дата 1 января 1995 года представлена числом 34 700. Если значение аргумента типа дата берется из ячейки (например, дата_согл — ссылка на ячейку В16), то дата в ячейке может быть записана в обычном виде, например, как 1.01.95. При вводе аргумента типа дата непосредственно в поле ввода Мастера функции можно воспользоваться встроенной функцией ДАТА, которая осуществляет преобразование строки символов в дату. Для этого нажимается кнопка вызова Мастера функций, находящаяся перед полем, и выбирается функция категории Дата и время — ДАТА. Далее заполняется экран ввода (рис. 2.4). При нажатии кнопки OK произойдет возврат в предыдущий экран Мастера функций для продолжения ввода аргументов основной финансовой функции. При нажатии кнопки <Назад произойдет также возврат в предыдущий экран, но при этом значение аргумента не будет рассчитано. Кнопка Отмена позволяет полностью отказаться от использования вызванной вложенной функции. 2.Для аргументов типа логический возможен непосредственный ввод констант типа ИСТИНА или ЛОЖЬ, либо использование встроенных функций аналогичного названия категории Логические. 3. При непосредственном вводе формулы в ячейку необходимо следить за тем, чтобы каждый аргумент находился строго на своем месте. Если какие-то аргументы не используются, то необходимо поставить соответствующее число разделительных знаков. Если не используется последний аргумент или несколько идущих подряд последних аргументов, то соответствующие разделительные знаки можно опустить (в большинстве случаев это замечание относится к аргументам тип и базис). Функции excel для расчёта операций по кредитам и займам. Определение будущей стоимости. Понятие будущей стоимости основано на принципе неравноценности денег, относящихся к разным моментам времени. Вложения, сделанные сегодня, в будущем составят большую величину. Эта группа функций позволяет рассчитать: будущую или наращенную стоимость серии фиксированных периодических платежей, а также будущую стоимость текущего значения вклада или займа при постоянной процентной ставке (функция БС (БЗ)); будущее значение инвестиции после начисления сложных процентов при переменной процентной ставке (функция БЗРАСПИС). Расчёты на основе постоянной процентной ставки. Функция бс (бз). Синтаксис БС (ставка; кпер; плт; пс; тип). Рассмотрим различные варианты использования этих функции при решении конкретных задач. Допустим, необходимо рассчитать будущую стоимость единой суммы вклада, по которой начисляются сложные проценты определённое число периодов. В этом случае на рабочем листе EXCELформула примет вид: =БС (ставка; кпер; ; пс). При решении конкретной задачи вместо названий аргументов следует записать соответствующие числа4. Рассмотрим ситуации, когда платежи производятся систематически, а не один раз, как в предыдущем примере. Эти платежи могут осуществляться в начале каждого расчетного периода (так называемые платежи пронумеран до или обязательные платежи) или в конце(постнумерандо или обычные платежи) в течение периодов. Допустим, что в каждом периоде вносится одинаковая сумма. Требуется найти совокупную величину таких вложений (их будущую стоимость) в конце-ого периода для обоих случаев. Отличие в расчёте при этом заключается в том, что во втором случае не происходит начисления процентов на последний вклад, т.е. все вклады пренумерандо увеличиваются на сложные проценты на один расчётный период больше, чем вклады постнумерандо. Для расчёта будущей стоимости серии фиксированных периодических платежей, если они вносятся в начале каждого периода, формула имеет вид: =БС (ставка; кпер; плт; ; 1). 2.2.Для расчёта будущей стоимости серии фиксированных периодических платежей, если выплаты происходят в конце периода, формула имеет вид: =БС (ставка; кпер; плт; ; 0). Аргумент тип=0 можно опустить и записать: =БС (ставка; кпер; плт), подставив вместо аргумента соответствующие числа. Подбор параметра. Вычислительные возможности электронных таблиц позволяют решать как "прямые", так и "обратные" задачи, выполнять исследование области допустимых значений аргументов, а также подбирать значения аргументов под заданное значение функции. Необходимость в этом обусловлена, в ряде случаев, отсутствием соответствующих "симметричных" финансовых функций. При установке курсора в ячейку, содержащую формулу, построенную с использованием финансовых функций, и выполнении команды СЕРВИС, Подбор параметра появляется диалоговое окно, в котором задается требуемое значение функции: В поле Изменяя значение ячейки указывается адрес ячейки, содержащей значение одного из аргументов функции. EXCEL решает обратную задачу: подбор значения аргумента для заданного значения функции. В случае успешного завершения подбора выводится окно, в котором указан результат — текущее значение функции для подобранного значения аргумента, новое значение аргумента функции содержится в соответствующей ячейке. При нажатии кнопки ОК подобранное значение аргумента сохраняется в ячейке аргумента; при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном завершении подбора параметра выдается соответствующее сообщение о невозможности подбора аргумента5. Примеры. Рассчитаем, какая сумма окажется на счете, если 27 тыс.руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода. Решение:для расчета требуется найти будущее значение единой суммы вклада. Обратите внимание, что в условии задачи указангодовой процент и числолет. Если проценты начисляются несколько раз в год, то необходимо рассчитать общее количество периодов начисления процентов и ставку процента за период начисления. Эти величины легко определить по таблице:
Итак, в данной задаче при полугодовом учете процента общее число периодов начисления равно 33*2 (аргумент кпер), а процент за период начисления равен 13,5%/2 (аргументставка). По условию аргументпс = – 27.Это отрицательное число, означающее вложение денег. Используя функцию БС, получим: БС(13,5%/2; 33*2; ; –27)=2012,07 тыс.руб. Предположим, есть два варианта инвестирования средств в течение четырех лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 300 тыс. руб. Определим, сколько денег окажется на счете в конце четвертого года для каждого варианта. Решение: в данном случае производятся периодические платежи для первого варианта обязательные, для второго обычные. При работе с функцией БС следует указать аргументыставка=26%,кпер=4,плт= – 300,тип=1 для первого варианта;ставка=38%, кпер=4,плт= – 300 для второго варианта. Аргументтип=0 можно опустить. БС(26%; 4; –300; ; 1)=2210,53- для первого варианта, БС(38%; 4; –300)=2073,74- для второго варианта. Расчеты показали, что первый вариант предпочтительнее. Взносы на сберегательный счет составляют 200 тыс. руб. в начале каждого года. Определите, сколько будет на счете через семь лет при ставке процента 10%. Решение: =БС(ставка; кпер; плт; ; 1)= (10%; 7; –200; ;1)= 2087,18 тыс. руб. Расчеты на основе переменной процентной ставки. Функция бзраспис. Функция БЗРАСПИС СинтаксисБЗРАСПИС (инвестиция; {ставка1; ставка2;…; ставкаN}). Если применяется массив процентных ставок- {ставка1; ставка2;…; ставкаN}),то ставки необходимо вводить не в виде процента, а как числа, например, {0,1; 0,15; 0,05}. Однако проще записать вместо массива ставок соответствующий интервал ячеек, содержащих значения переменнных процентных ставок6. Примеры. По облигации номиналом 100 тыс.руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год- 10%, в два последующих года- 20%, в оставшиеся 3года- 25%.Рассчитаем будущую (наращенную) стоимость облигации по сложной процентной ставке. Решение: пусть в ячейки А1:А6 введены числа 10%, 20%, 20%, 25%, 25%, 25% соответственно. Тогда наращенная стоимость облигации равна: БЗРАСПИС (100; А1:А6)=309,38. Исходя их плана начисления процентов, приведённого в примере 1, рассчитаем номинал облигации, если известно, что её будущая стоимость составила 1546,88 тыс.руб. Решение: для решения такой задачи необходимо использовать аппарат подбора параметров пакетаEXCEL, вызываемый командой меню ДАННЫЕ, АНАЛИЗ ЧТО–ЕСЛИ, ПОДБОР ПАРАМЕТРА вExcel2007(СЕРВИС, ПОДБОР ПАРАМЕТРА вExcel97–2003). Пу  сть в ячейке А1:А6 введён план начисления процентов. В ячейкуB1 запишем формулу =БЗРАСПИС (В2; А1:А6). Т.к. ячейка В2 пустая, то в В1 окажется нулевое значение. Установив курсор в ячейку В1, выбираем в меню EXCEL команду ДАННЫЕ, АНАЛИЗ ЧТО–ЕСЛИ, ПОДБОР ПАРАМЕТРА и заполняем диалоговое окно следующим образом:В результате в ячейке В2 появится значение номинала облигации-500 тыс.руб. Список используемой литературы 1. Кочетыков А.А. Финансовая математика. Серия «Учебники, учебные пособия». – Ростов н/Д: «Феникс», 2004. – 480с. 2. Кутуков В.Б. Основы финансовой истраховой математики: методы расчета кредитных, инвестиционных, пенсионных и страховых схем. – М.:Дело, 1998. – 304с. 3. Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. Издание 3-е, переработанное и дополненное – М.: Информационно-издательский дом «Филин», 1999. – 328с. 4. Четыркин Е.М. Методы финансовых и коммерческих расчетов. – 2-е изд., испр. И доп. – М.: Дело Лтд., 1995. – 320с. 1 Четыркин Е.М. Методы финансовых и коммерческих расчетов. – 2-е изд., испр. И доп. – М.: Дело Лтд., 1995. – 320с. 2 Кочетыков А.А. Финансовая математика. Серия «Учебники, учебные пособия». – Ростов н/Д: «Феникс», 2004. – 480с. 3 Кутуков В.Б. Основы финансовой истраховой математики: методы расчета кредитных, инвестиционных, пенсионных и страховых схем. – М.:Дело, 1998. – 304с. 4 Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. Издание 3-е, переработанное и дополненное – М.: Информационно-издательский дом «Филин», 1999. – 328с. 5 Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. Издание 3-е, переработанное и дополненное – М.: Информационно-издательский дом «Филин», 1999. – 328с. 6 Кочетыков А.А. Финансовая математика. Серия «Учебники, учебные пособия». – Ростов н/Д: «Феникс», 2004. – 480с. |