Автоматизация операционных задач, с использованием стандартных финансовых функций Excel. Определение будущей стоимости

ПРАКТИЧЕСКАЯ  РАБОТА №1

Автоматизация операционных задач, с использованием стандартных финансовых функций Excel. Определение будущей стоимости

 

Теоретическая часть.

Финансовый  анализ инвестиций

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

 

Таблица 1.1 - Описание аргументов финансовых функций EXCEL, использующих базовые модели

Аргумент	Описание аргумента
Бз	будущая стоимость фиксированных  периодических выплат или единой суммы; баланс наличности, который нужно  достичь после последней выплаты, по умолчанию равный 0 (например, будущая  стоимость займа равна 0)
Бс
выплата	фиксированная периодическая  выплата
Дата(); дата1;...;датаN	даты операций с наличными
Кпер	Общее число периодов выплат
Число периодов
Колпер
Нз	начальное значение (текущая  стоимость) вклада или займа
Пс
Ставка	процентная ставка за период, норма дисконтирования
Норма
суммаО; сумма1 ;...; cyммaN	значения выплат и  поступлений
значения
тип	число 0 или 1, обозначающее, когда производится выплата (1 - в  начале периода, О-в конце периода); по умолчанию равно 0.
предположение	предполагаемое значение процентной станки; по умолчанию равно 0.1
предп
прогноз
период	период, для которого требуется найти выплату по процентам; должен быть в интервале от 1 до аргумента  клер.
начпериод	номер первого периода, участвующего в вычислениях
конпериод	номер последнего периода, участвующего в вычислениях

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

Основными понятиями финансовых методов расчета являются:

% -абсолют. величина  дохода от предоставления денег  в долг в любой его форме;

процентная ставка –  относительная величина дохода за фиксированный  интервал времени, измеряемая в % или в виде дроби;

период начисления –  интервал времени, к которому приурочена процентная ставка;

капитализация процентов  – присоединение начисляемых  процентов к основной сумме;

наращение – увеличение первоначальной суммы в связи  с капитализацией;

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

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

 

Функции EXCEL для расчета операций по кредитам и займам

 

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

• определение наращенной суммы (будущей стоимости);

• определение начального значения (текущей стоимости);

• определение срока  платежа и процентной ставки;

• расчет периодических  платежей, связанных с погашением займов.

Эти расчеты используют функции БЗ(БС), БЗРАСП, КПЕР, НОРМА, ПЗ, ППЛАТ.

Определение будущей стоимости

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

1) будущую или наращенную  стоимость серии фиксированных  периодических платежей, а также  будущую стоимость текущего значения  вклада или займа при постоянной  процентной ставке (функция БЗ);

2) будущее значение  инвестиции после начисления сложных процентов при переменной процентной ставке (функция БЗРАСПИС).

Расчеты на основе постоянной процентной ставки. 

Функция БЗ (БС)

Функция БЗ рассчитывает будущую стоимость периодических  постоянных платежей и будущее значение единой суммы вклада или займа на основе постоянной процентной ставки. Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

Синтаксис

БС(ставка;кпер;плт;пс;тип)

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

Кпер    — это общее число периодов платежей по аннуитету.

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

Пс    — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент Пс опущен, то он полагается равным 0,  в этом случае должно быть указано значение аргумента Плт.

Тип    — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

Тип	Когда нужно  платить
0	В конце периода
1	В начале периода

 

Пример 1

1 2 3 4 5 6

A B Данные Описание 6% Годовая процентная ставка 10 Количество платежей -200 Объем платежей -500 Стоимость на текущий  момент 1 Платежи осуществляются в начале периода (см. выше) Формула Описание (результат) =БС(A2/12; A3; A4; A5; A6) Будущая стоимость инвестиции на приведенных выше условиях (2581,40)

 

Примечание.   Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.

Пример 2

1 2 3 4

A B Данные Описание 12% Годовая процентная ставка 12 Количество платежей -1000 Объем платежей Формула Описание (результат) =БС(A2/12; A3; A4) Будущая стоимость вклада на приведенных выше условиях (12 682,50)

 

Примечание.   Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.

 

Расчеты на основе переменной процентной ставки.

Функция БЗРАСПИС

 

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

БЗРАСПИС

Возвращает будущую стоимость  первоначальной основной суммы после  применения ряда (плана) ставок сложных  процентов. Функция БЗРАСПИС используется для вычисления будущей стоимости инвестиции с переменной процентной ставкой.

БЗРАСПИС (инвестиция, {ставка1; ставка2; . . .ставка N})

Если применяется массив процентных ставок, то ставки необходимо вводить не в виде процентов, а  как числа: {0,1; 0,15; 0,05}. Проще записать вместо массива ставок соответствующий интервал ячеек со значениями переменных процентных ставок.

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

Инструкции 

1. В меню Сервис выберите команду Надстройки.
2. В списке надстроек выберите Пакет анализа и нажмите кнопку OK.
3. Выполните инструкции программы установки, если это необходимо.

 

Синтаксис

БЗРАСПИС(первичное;план)

Первичное  — это стоимость инвестиции на текущий момент.

План  — это массив применяемых процентных ставок.

Значения в аргументе ставки могут быть числами или пустыми  ячейками; любые другие значения дают в результате значение ошибки #ЗНАЧ! при работе функции БЗРАСПИС. Пустые ячейки трактуются как нули (нет  дохода).

 

Пример 3

1 2	A B Формула Описание (результат) =БЗРАСПИС(1;{0,09;0,11;0,1}) Будущая стоимость капитала размером 1 при ставках сложных  процентов 0,09, 0,11 и 0,1 (1,33089)

 

 Подбор параметра

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

Необходимость в этом обусловлена в ряде случаев отсутствием  соответствующих «симметричных» финансовых функций.

При установке курсора  в ячейку, содержащую формулу, построенную, с использованием финансовых функций, и выполнении команды СЕРВИС - Подбор параметра (версия excel 2003) или Данные - Анализ «что-если» - Подбор параметра (версия excel 2007 и выше) появляется диалоговое окно, в котором задается требуемое значение функции. В поле Установить в ячейке: указывается ссылка на ячейку с нужным вам значением. В поле Значение: указывается само значение. В поле Изменяя значение ячейки: указывается адрес ячейки, содержащей значение одного из аргументов функции. Excel решает обратную задачу: подбор значения аргумента для заданного значения функции.

 

 

 

ПРАКТИЧЕСКОЕ  ЗАДАНИЕ

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

 

Часть 1.Определение будущей стоимости. Расчеты на основе постоянной процентной ставки (функция БЗ(БС)).

Задача 1.1

Рассчитать, какая сумма  окажется на счете, если 27000 руб. положены на 3 года под 13,5% годовых. Проценты начисляются  каждые полгода.

Задача 1.2

Предположим, есть два  варианта инвестирования средств в течение 4-х лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 300000 руб. Определить, сколько денег окажется на счете в конце 4-го года для каждого варианта.

Задача 1.3

Рассчитать, какая сумма будет на счете, если сумма размером 5000 руб. размещена под 12% годовых на 3 года, а проценты начисляются каждые полгода.