Финансовые функции Microsoft Excel

ЛАБОРАТОНАЯ РАБОТА №2. ФИНАНСОВЫЕ ФУНКЦИИ MICROSOFT EXCEL

Цель работы: научиться решать финансовые задачи, используя теоретический материал, предложенные преподавателем задания с готовыми решениями ( см. стр. 1-13) и возможности программы Microsoft Excel.

Задание:

1. Выполнить самостоятельно задачи со стр.13-15 и в формате Excel предоставить отчет преподавателю.
2. Ответить на контрольные вопросы со стр.13 и в формате Word предоставить отчет преподавателю.

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

Многие финансовые функции  Microsoft Excel имеют одинаковые аргументы. В таблице 1 приведены аргументы, используемые в функциях.

Таблица 1

Аргументы финансовых функциц

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

 

• Функция БЗ. Определение будущей стоимости на основе постоянной процентной ставки.

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

Синтаксис     БЗ (норма, число_периодов, выплата, нз, тип).

1. Будущую стоимость единой суммы вклада, по которой начисляются сложные проценты за определенное число периодов, можно рассчитать по формуле:

                                         fv=pv*(1+r)ⁿ                                             

где  fv - будущая стоимость вклада или займа,

pv - текущая стоимость вклада или займа;

п - общее число периодов начисления процентов,

r - процентная ставка по вкладу или займу.

Для вычисления будущего значения единой суммы функция БЗ используется в  виде:

=БЗ(норма,  число_периодов, , нз).

В качестве разделителя аргументов используется выбранный при настройке Windows разделитель, обычно это запятая (,) или точка с запятой (;).

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

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

fv = ртt * (1+r) + pmt * (1+r)² + ... + pmt(1+r)ⁿ

= pmt * ((1+r)ⁿ –1)/r* (1+r)              

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

pmt - фиксированная периодическая сумма платежа,

n  - общее число периодов выплат;

r - постоянная процентная ставка .

Функция БЗ в данном случае используется в виде

=БЗ(норма, число_периодов, выплата, , 1).

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

fv = ртt + ртt*(1+r) + ... + pmt (1+r)^n-1 = pmt*((1+r)ⁿ –1)/r

Функция БЗ в данном случае используется в виде:

=БЗ(норма, число_периодов, выплата, , 0)

или

=БЗ(норма, число_периодов, выплата).

 

 

 

Примеры.

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

Решение. Произведите расчеты двумя способами: используя финансовую формулу и с помощью финансовой функции БЗ. Для того чтобы осуществить расчет по формуле в ячейку А1 введите следующее выражение: = 27* (1+0,135/2)^(33*2), в результате получите 2012.07 тыс. руб. Для того чтобы решить задачу используя функцию БЗ, наведите курсор на ячейку А2, на панели инструментов нажмите кнопку , откроется окно Мастер функций (рис. 1.46).

Рис 1.46. Окно мастера функций

Выберите категорию Финансовые, далее в поле Выберите функцию – функцию БЗ или БС. В открывшемся окне введите в соответствующие строки имеющиеся данные следующим образом:

Рис. 1.47. Функции БС

По условию задачи сказано, что проценты начисляются каждые полгода, поэтому необходимо значение годового процента разделить на два, в случае с периодом вклада все наоборот, количество лет надо умножить на два (рис. 1.47). Сумма первоначального вклада записывается со знаком минус, так как эта сумма является исходящим денежным потоком. В результате получили ответ = 2012,07 тыс. руб. Таким образом решение задачи двумя способами дало результат равный 2012,07 тыс.руб.

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

Решение. Расчет ведется по формуле

fv = 300 *((1+0.26)^4-1)/0.26* (1+0,26) =2210.53 - для первого варианта и по формуле

fv = 300 *((1+0.38)^4-1)/0.38 =2073.74  - для второго

Используя функцию БЗ, получите:

Б3(26%, 4, -300, , 1) = 2210,53 тыс. руб. - для первого  варианта,

Б3(38%,4,-300)=2073,74тыс.руб. - для второго варианта.

Расчеты показали, что  первый вариант предпочтительнее.

 

• Определение текущей стоимости. Функция ПЗ.

Во многих задачах используется понятие текущей (современной) стоимости  будущих доходов и расходов. Текущая стоимость получается как результат приведения будущих доходов и расходов к начальному периоду времени (т.е. путем дисконтирования).

Расчет при помощи функции ПЗ требует денежных потоков равной величины и равных интервалов между операциями. Функция НПЗ допускает денежные потоки переменной величины через равные периоды времени.

Синтаксис       П3( норма, кпер, выплата, бс, тип).

Рассмотрим различные  варианты использования этой функции  при решении конкретных задач.

1. Допустим, известно будущее значение вклада (займа). Требуется определить текущее значение этого вклада, т.е. сумму, которую необходимо положить на счет сегодня, чтобы в конце n-го периода она достигла заданного значения. Это значение можно получить из формулы:

                                                     pv=fv/(1+r)ⁿ                                           

Такой же расчет при использовании  функции ПЗ в общем виде запишется  так 

= П3(норма, кпер, , бс).

2. Расчет текущей стоимости серии будущих постоянных периодических платежей, производимых в начале каждого периода и дисконтированных нормой дохода r, ведется по формуле:

pv = ртt + ртt/(1+r) +... + pmt/(1+r)^n-1 =pmt(1-1/(1+r)ⁿ)/r*(1+r)

где pv - текущая стоимость серии фиксированных периодических платежей,

pmt - фиксированная периодическая сумма платежа,

п   - общее число периодов выплат (поступлений),

r   - постоянная процентная ставка.

 Для расчета этой величины  функция ПЗ используется в  виде:

=ПЗ(норма, кпер, выплата, , 1).

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

pv = ртt/(1+r) + ртt/(1+r)² +...+ pmt/(1+r)ⁿ =pmt(1-1/(1+r)ⁿ)/r

Соответствующая этому расчету  формула в EXCEL имеет вид:

=ПЗ(Hоpмa, кпер, выплата).

По умолчанию аргумент тип равен 0, поэтому его можно не указывать.

 

Примеры. 

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

Решение. Для расчета используем формулу (2.1) или следующую функцию:

П3( 12%, 12,,5000)= -1283,38руб.

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

Задание 2. Рассматривается два варианта покупки дома: заплатить сразу 99000 руб. или в рассрочку - по 940 руб. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента - 8% годовых.

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

П3(8%/12, 15*12, -940) = 98362,16 руб.

Запрашиваемая цена 99000 руб. больше рассчитанной текущей стоимости периодических выплат, следовательно, невыгодно покупать дом сразу, лучше растянуть платежи на 15 лет.

 

•  Определение текущей стоимости. Функция НПЗ.

Функция НПЗ вычисляет чистую текущую  стоимость (NPV) периодических платежей переменной величины как сумму ожидаемых расходов и доходов, дисконтированных нормой процента r.

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

Синтаксис     НПЗ(норма, сумма1, сумма2,..., cyммaN).

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

 

Примеры

Задание 1. Инвестиции в проект к концу первого года его реализации составят 10000 руб. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб., 6800 руб. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.

Решение. Так как инвестиция размером 10000 руб. относится не к начальному моменту, на который производится расчет, то это значение следует включить в список аргументов. Поскольку этот денежный поток движется "от нас", то сумма 10000 руб. записывается со знаком “-“. Остальные денежные потоки представляют доходы, поэтому имеют знак "+". Чистый текущий объем инвестиции составит:

НПЗ(10%, -10000, 3000, 4200, 6800) = 1188,44 руб

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

Задача 2. Допустим, затраты на проект в начальный момент его реализации составляют 37000 руб., а ожидаемые доходы за первые пять лет: 8000 руб , 9200 руб., 10000 руб., 13900 руб. и 14500 руб. На шестой год ожидается убыток в 5000 руб. Цена капитала 8% годовых. Рассчитать чистую текущую стоимость проекта.

Решение. В этой задаче нет необходимости дисконтировать начальные затраты по проекту, так как они относятся к настоящему моменту, и их текущая стоимость равна 37000 руб. Для сравнения затрат с будущими доходами и убытками последние необходимо привести к начальному моменту. Если доходы ввести в ячейки В1:В5 соответственно, чистая текущая стоимость проекта составит: