11. Использование финансовых функций excel

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

Основные параметры финансовых функций Excel:

• Ставка - процентная ставка за период (i);

• Кпер - срок финансовой операции или общее число периодов начисления процентов за весь срок финансовой операции (n или m * n);

• Пс - текущая стоимость, или общая сумма всех будущих платежей с настоящего момента (PV, PVA);

• Бс – будущая стоимость разовой денежной суммы или периодических постоянных платежей (FV, FVA)

• Плт - выплата, производимая в каждый период и не меняющаяся за все время выплаты (R).

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

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

Все расходы денежных средств (платежи) представляются отрицательными числами, а все поступления денежных средств – положительными числами. Основные финансовые функции представлены в приложении Р.

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

Аргументы данной функции: Ставка, Кпер, Плт, Пс, Тип,

Тип – означает вид финансовой ренты в зависимости от метода выплаты платежей: если платежи в конце периода, т.е. обычная рента или пренумерандо – число 1, если платежи в начале периода, т.е. постнумерандо – число 0.

Для решения задач наращения по простым процентам рабочими аргументами являются: Ставка, Кпер, Пс. Причем параметр Ставка и Кпер определяются следующим образом;

Кпер =1

Исходящие денежные потоки представляются отрицательными числами; входящие денежные потоки - положительными числами.

Пример 1. Депозитный счет в размере 5000 грн. открыт в коммерческом банке на 6 месяцев под 17% годовых. С помощью финансовой функции EXCEL найти сумму, которую получит вкладчик при начислении простых и сложных процентов.

Решение:

1. При начислении простых процентов аргументы функции следующие:

2. При начислении сложных процентов:

Таким образом, вкладчик получит 5025 грн. при начислении простых процентов и 5408,33 грн. при начислении сложных процентов.

Пример 2.. Рассчитать с помощью финансовых функций EXCEL, какая сумма будет на счете клиента через 3 года, если проценты начисляются по ставке 20 % годовых и денежные средства поступают: а) ежегодно по 1500 грн. с начислением процентов один раз в год, б) ежемесячно по 150 грн. с ежемесячным начислением процентов?

Решение:

а) Ежегодное поступление денежных средств:

б) ежемесячное поступление денежных средств:

Таким образом, при ежегодном поступлении денежных средств на счете клиента будет сумма 5460 грн., при ежемесячном поступлении – 7318.17 грн.

Функция ПС – текущее значение – рассчитывает наращенную величину разовой денежной суммы или периодических постоянных платежей на основе постоянной процентной ставки, с ее помощью можно упростить расчет PV или PVA.

Аргументы данной функции: Ставка, Кпер, Плт, Бс, Тип

Пример 3. Для приобретения бытовой техники через 3 месяца необходима сумма 3000 грн., рассматривается вариант открытия депозитного счета с начислением процентов по ставке 21% годовых. Какую сумму надо положить на депозитный счет, если проценты начисляются: а) по простой процентной ставке, б) по сложной процентной ставке. Расчеты выполнить с помощью финансовых функций EXCEL.

Решение:

а) При начислении простых процентов аргументы функции следующие:

б) При начислении сложных процентов аргументы функции следующие:

Таким образом, при начислении процентов по простым процентам необходима сумма 2850.36 грн., при начислении по сложным процентным ставкам – 2860,39 грн.

Функция КПЕР – количество периодов – рассчитывает число периодов для разового вклада, или для периодических, постоянных выплатах и постоянной норме процентного дохода.

Аргументы данной функции: Ставка, Плт, Пс, Бс, Тип

Пример 4. На какой срок клиент может разместить в банке 6000 грн. при годовой процентной ставке – 18%, чтобы по истечении срока получить 1000 грн., при этом осуществляя ежемесячное пополнение счета по 200 грн., начисление процентов – ежемесячное.

Решение:

Таким образом, необходимая сумма будет на счете через 12,6 месяца.

Для составления плана погашения используются следующие финансовые функции EXCEL:

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

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

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

Пример 5. Фермер приобрёл трактор в кредит за 100 000 грн.. За кредит он должен платить 23% годовых и выплатить весь долг за 4 года. Составить план погашения долга с использованием финансовых функций EXCEL..

Решение:

Платежи по кредиту (расходы по кредиту, R) осуществляются равными суммами каждый год в течении всего срока и определяются с помощью функции ПЛТ:

Проценты по кредиту рассчитываются для каждого периода (изменяется значение аргумента Период) с помощью функции ПРПЛТ:

Сумма основного платежа по кредиту рассчитывается для каждого периода (изменяется значение аргумента Период) с помощью функции ОСПЛТ:

Результаты расчетов приведены в таблице 11.1.

Таблица 11.1 – План погашения кредита

Период	Остаток долга по кредиту	Расходы по кредиту, R (ПЛТ)	Проценты по кредиту, I (ПРПЛТ)	Сумма основного платежа, Y (ОСПЛТ)
1	100000,00	40845,14	23000.00	17845,14
2	82154,86	40845,14	18895,62	21949,52
3	60205,34	40845,14	13847,23	26997,91
4	33207,43	40845,14	7637,71	33207,43
Итого		163380,.56	63380,56	100000.00

Для оценки инвестиционных проектов используются следующие функции EXCEL:

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

ЧИСТВНДОХ – рассчитывает внутреннюю норму доходности:

МВСД – рассчитывает модифицированную внутреннюю норму доходности при заданной ставке рефинансирования;

ЧИСТНЗ - рассчитывает величину чистого дисконтированного дохода, если суммы поступлений и выплат осуществляются в произвольные периоды времени.

Пример 6. Сравнить два проекта, денежные потоки по которым приведены в таблице 11.2 при ставке дисконтирования 18%, используя финансовые функции EXCEL.

Таблица 11.2 – Денежные потоки по инвестиционным проектам

Показатели	Проект 1	Проект 2
Инвестиции 0-го года, грн	-100000	-150000
Доход 1 года, грн.	60000	80000
Доход 2 года, грн	80000	100000
Доход 3 года, грн	90000	105000
Доход 4 года, грн	105000	110000
Доход 5 года, грн	110000	130000

Решение: Дисконтированный доход определяем с помощью функции ЧПС:

1 проект: дисконтированный доход 140100,71 грн., чистый дисконтированный доход 40100,71 грн,

2 проект: дисконтированный доход 141595,14 грн., чистый дисконтированный доход – 8404,86 грн.

Таким образом, следует принять проект 1.

Пример 7. 1.01.08 приобретена новая технологическая линия стоимостью 10000 грн.. В результате эксплуатации этой линии ожидаются следующие денежные поступления: 01.02.2009 в сумме 5000 грн., 01.03.2009 в сумме 8000 грн., 01.08.2009 в сумме 9000 грн. Определить чистую текущую стоимость инвестиций на 1.01.2008 при ставке дисконтирования 20 %.

Решение:

Создаем таблицу с исходными данными на листе EXCEL, и с помощью функции ЧИСТНЗ находим чистую текущую стоимость на 01.01.2008 г.

Таким образом, чистая текущая стоимость приобретения технологической линии на 01.01.2008 г. составляет 7522,06 грн.

Вопросы для самостоятельного изучения

1. Технология работы с финансовыми функциями EXCEL.

2. Расчет эффективной и номинальной ставки процентов.

3 Расчет суммы платежей по процентам по кредиту (ОБЩПЛАТ).

4. Расчет суммы основных платежей по займу (ОБЩДОХОД).

5. Анализ аннуитетов с помощью финансовых функций.

Задачи

1. Определить, какая сумма будет на счете, если некоторая сумма положена на определенный срок под заданные проценты (исходные данные в таблице 11.3). Расчеты выполнить с помощью функции EXCEL и проверить с помощью расчетной формулы.

Таблица 11.3 - Исходные данные

Вложенная сумма, грн	Начисление процентов	Срок вклада, в годах	Процентная ставка годовая, %	Сумма на счете
				Функция EXCEL	Расчетная формула
6000	ежегодное	3	19
5000	ежемесячное	2,5	18
5000	ежеквартальное	2	20
4500	ежедневное	2	20

2. На сберегательный счет ежемесячно вносятся платежи по 100 грн.. С помощью финансовых функций рассчитать сумму на счете через год для различных процентных ставок, если проценты начисляются ежемесячно по ставке 24 %, 22 %, 21 % , 20 % годовых и платежи вносятся: а) в начале месяца; б) в конце месяца. Построить диаграмму зависимости накопленных сумм от процентных ставок.

3. Ставка банка по депозитному вкладу «Копилка» на начало года 20 % годовых, начисляемых раз в квартал. В течении года ожидается снижение процентной ставки на 1% каждый квартал. Сумма депозита 5000 грн. Определить величину депозита к концу года с помощью финансовых функций.

4. У предприятия есть возможность в течение 4-х лет инвестировать ежегодно по 20000 грн. в два проекта: 1-й проект – начисление процентов 22 % годовых в конце года, 2-й проект – 27 % годовых в начале года. Определить с помощью финансовых функций, какой из вариантов предпочтительнее.

5. Компании потребуется 200000 грн. через 1 год. Для накопления данной суммы решено открыть депозитный счет. Рассчитать с помощью финансовых функций, какова должна быть процентная ставка при следующих условиях: а) в начале вкладывают 100000 грн., и затем ежемесячно по 5000 грн.; б) единовременный платеж в начале года 150000 грн.

6. Ставка банка по депозиту «Стандарт» 20 % годовых. С помощью финансовых функций определить реальную доходность вклада, если: а) проценты начисляются ежемесячно; б) проценты начисляются ежедневно.

7. Клиент банка хочет положить на депозитный счет 2000 грн. для накопления 3500 грн.. По данному депозиту ставка – 22 % годовых, проценты начисляются ежемесячно. С помощью финансовой функции рассчитать срок в днях, за который может быть накоплена требуемая сумма (в году 360 дней).

8. Эффективная ставка составляет 24 % годовых, начисление процентов производится ежемесячно. Используя финансовые функции EXCEL определить номинальную ставку.

9. Используя функции EXCEL рассмотреть два варианта приобретения недвижимости стоимостью 180000 грн.: а) единовременный платеж; б) ежемесячные платежи в течение 10 лет размером 2500 грн. при начислении процентов ежемесячно по ставке 28 % годовых.

10. Фермер в начале первого года приобретает трактор стоимостью 90000 грн. За первые три года эксплуатации трактора получены доходы 30000 грн., 50000 грн., 60000 грн. На 4-й год фермер потерпел убытки в сумме 40000 грн. С помощью финансовых функций рассчитать чистый приведенный доход для 3-х и 4-х лет при ставке 20 % годовых

11. Фирма, внедряя новые технологии, инвестирует 50000 грн. 1.01. текущего года. При этом предполагается, что их внедрение принесет следующие доходы в течение двух лет: 3.03. – 9000 грн., 30.10. – 9500грн., 15.02 – 10000 грн., 01.04 – 10000 грн., 4.06 – 13000 грн., 1.01- – 13000 грн.. Определить внутреннюю скорость оборота. Оцените, как оптимально запланировать денежные поступления в указанные сроки, чтобы внутренняя скорость оборота была равна рыночной норме дохода в 20 %.

12. Три года назад взят кредит в 100000 грн. под 25 % годовых для финансирования проекта, по которому за эти годы были следующие денежные поступления: 35000 грн., 40000 грн., 43000 грн.. Эти деньги были реинвестированы под 27 % годовых. Определить модифицированную внутреннюю норму доходности.

13. От реализации проекта, в который вложено 30.12 10000 грн., в течение следующего года поступили денежные средства, представленные в таблице 11.4. Определить, какими должны быть поступления 01.09 следующего года, чтобы доход от реализации проекта составил 5000 грн.

Таблица 11.4 – Поступления денежных средств по проекту

Сроки поступления	Сумма денежных средств
01 марта	2000
01 июня	-3000
01 сентября	0
01 декабря	5000

Тесты

Возможно несколько вариантов ответов

1. К какой категории функций EXCEL относится функция КПЕР:

a) логические;

b) дата и время;

c) финансовые:

d) математические.

2. С помощью какой финансовой функции EXCEL можно рассчитать текущую стоимость финансовой ренты:

a) КПЕР;

b) ПС;

c) БС;

d) ПЛТ.

3. Какой из перечисленных аргументов должен быть равен 0 при расчете суммы на депозитном счете без пополнения вклада:

a) КПЕР;

b) ПС;

c) ПЛТ;

d) СТАВКА.

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

a) Кпер=; Ставка = 1;

b) Кпер=1: Ставка=;

c) Кпер=; Ставка = 1;

5. Если выплаты в течение финансовой операции осуществляются в начале каждого временного интервала, то:

a) Тип =0;

b) Тип =1;

c) Кпер =1;

d) Кпер =0;

6. В каких финансовых функциях используется аргумент Период:

a) КПЕР;

b) ПЛТ;

c) ПС;

d) ОСНПЛТ.

7. Что обозначает результат вычисления стоимостной финансовой функции со знаком « - »:

a) денежные выплаты;

b) денежные поступления;

c) неверно выполнен расчет.

8. С помощью каких функций EXCEL можно рассчитывать параметры аннуитетов:

a) БЗРАСПИС;

b) КПЕР;

c) ПЛТ;

d) СТАВКА.

9) Какие функции EXCEL могут быть использованы при составлении плана погашения кредита:

a) ОБЩПЛАТ;

b) ПЛТ;

c) ПРПЛТ:

d) БС.

10) С помощью какой функции EXCEL можно рассчитать начисленные проценты по кредиту:

a) СТАВКА;

b) ПЛТ;

c) ПРПЛТ:

d) БС.