Вычисление будущего значения

В Excel будущему значению FV соответствует функция

БЗ(Норма; Число_периодов; Выплата;НЗ; тип). (2.10)

В принятых в данной работе обозначениях

FV=БЗ(r/m; k·m; С; PV; тип). (2.10а)

Рассмотрим пример 1.2 (простые проценты, выплаты отсутствуют)

FV=БЗ(0,12/12;5·12;;-50000)=90834,83 руб. Результаты совпали.

Пенсионер положил 3000 руб. на срочный пенсионный вклад на полгода под 14% годовых. Какая сумма у него накопится в конце срока.

Решение

PV=-3000 руб., r=12, С=0, k=0,5, m=2, FV=?

Рассмотрим пример 1.4 (сложные проценты, выплаты отсутствуют)

FV=БЗ(0,12/12; 5*12;;-50000)=90834,83 руб. Результаты совпали.

Решение.

PV=-50000 руб.

С=0

m=12

r=0,12

k=5

FV=?

Рассмотрим пример 2.2 (сложные проценты с выплатами)

Решение.

PV=-1000руб.

FV=БЗ(0,18/12;12;-300;-1000)=5107,98руб. Результаты совпали

С=-300руб.

r=0.18

k=1

m=12

FV=?

Расчет текущей суммы

В Excel текущему значению PV соответствует функция первоначальное значение

ПЗ(Норма; Кпер; Выплата; Бс; тип). (2.11)

В принятых здесь обозначениях

PV=ПЗ(r/m; k*m; С; FV; тип). (2.11а)

Рассмотрим пример 2.3

Рассчитаем выплаты с начала месяца

FV=0

PV=ПЗ(0,24/12; 5*12; 500;; 1)= -17728,05 руб.

С=500

r=0,24

k=5

m=12

PV=?

Определение периодических выплат

В Excel периодические выплаты С определяет функция

ППЛАТ(Норма; Кпер; Нз; Бс; тип). (2.12)

В принятых здесь обозначениях

С=ППЛАТ(r/m; k*m; PV; FV; тип). (2.12а)

Рассмотрим пример 2.4

FV=50000руб.

С=ППЛАТ(0,06/12; 18*12;;50000)= -129,08руб.

PV=0

r=0,06

k=18

m=12

С=?

Расчет срока ренты

Функция Excel

КПЕР(Норма; Выплата; Нз; Бс; Тип) (2.13)

вычисляет количество периодов выплат или начислений процентов n= m·k.

В принятых здесь обозначениях

n=КПЕР(r/m; C; PV; FV; Тип). (2.13 а)

Рассмотрим пример 2.6

Решение.

PV = 300 млн. руб. n = КПЕР (0,12; - 111,52; 300;; 1) = 3,00

FV = 0

С = - 111,52 млн. руб. k = n/m = 3,00 года

r = 0,12

m = 1

тип = 1

k =?

Определение размера процентной ставки

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

НОРМА (кпер; выплата; нз; бс; тип; предположение). (2.14)

Функция Норма фактически решает методом последовательных приближений нелинейное уравнение (2.5) относительно r/m при заданных остальных значениях входящих в уравнение параметров. Поэтому требуется задавать начальное приближение (предположение) ставки за период. По умолчанию оно принимается равным 10%. Как правило, предположение можно не вводить. Однако, если ЭВМ не может решить уравнение с этим начальным приближением и выдает ответ: #ЧИСЛО, то следует попытаться решить задачу с другим начальным приближением.

В принятых в работе обозначениях

r = НОРМА (k·m; C; PV; FV; тип; начальное_приближение). (2.14а)

Пример 2.7

Финансовая компания дала в кредит фирме 1 млн. руб. на 3 года с условием погашения равномерными платежами раз в полгода по 200 000 руб. и выплатой в конце срока 250 000 руб. Определите, выгодна ли эта сделка компании, если банк обеспечивает 18% годовых.

Если компания отдает деньги в кредит, то процентная ставка за полгода составит r =НОРМА(3*2; 0,2; -1; 0,25)=10,37%. Следовательно, эффективность этой сделки r_эфф=10,37×2=20,74% Она выше ставки банка r=18%. Следовательно, для компании эта сделка выгодна.

Решение

PV=-1 000 000 руб.

С=200 000 руб.

FV=250 000 руб.

k=3

m=2

r=0,18

r_эфф=?

ВЫБОР БАНКА КРЕДИТОВАНИЯ И СОСТАВЛЕНИЕ ПЛАНА ПОГАШЕНИЯ КРЕДИТА

Постановка задачи

Пример 2.8

Фирма собирается взять на расширение производства кредит в размере 500 000 долларов сроком на 5 лет с погашением равномерными платежами основного долга и процентов в конце каждого года. Фирма направила запросы на финансирование в 3 банка, из которых пришли ответы с соответствующими условиями (таблица 2.1).

Требуется: 1) сравнить условия и выбрать банк, обеспечивающий наименьшее отношение; ;

2) составить план погашения кредита по годам.

Расчет проведем в Excel.

Выбор банка кредитования

На листе, который назовем “Кредит”, введем исходные данные и рассчитаем отношение выплаты/получено для каждого банка.

Таблица 2.1

A	B	C	D	E	F	G	H	I	J
Выбор кредита
Входные данные				Выходные данные
Банк	Объем кредита	Выдача	Плата за оформление	Ставка (%)	Срок (лет)	Дизажио	Получено	Выплаты	Выплаты/Получено
Альфа Банк	$500 000	0,96	$300	24,0%			$479 700	-$182 123,86	0,379662
НДБ	$500 000	0,965	$250	23,5%			$482 250	-$180 233,77	0,3737351
ИНКБ	$500 000	0,95	$350	22,0%			$474 650	-$174 602,97	0,3678562

Кредит

Пояснение: в графе “Выдача” показано, какую часть от запрашиваемой ссуды выдает банк. Кое - что (дизажио) банк оставляет себе, как плату за риск финансовой сделки.

Ввод данных.

1. Для того, чтобы заголовки помещались в одной строке,

1.1 выделим строку 3;

1.2 Формат – Ячейки;

1.3 в открывшемся окне выбираем вкладку Выравнивание;

1.4 þ переносить по словам (щелчок слева по квадратику - появляется галочка); ОК.

2. Столбцы В4:В6 и D4:D6 форматируем в долларах:

2.1 выделяем первый блок В4:В6;

2.2 нажимаем клавишу Сtrl и, не отпуская ее, выделяем второй блок D4:D6;

2.3 Формат – Ячейки – Число – Денежный;

2.4 в поле Обозначения: выбираем $; ОК.

3. Прежде, чем вводить проценты, отформатируем блок F4:F6 в процентах:

3.1 выделяем блок;

3.2 Формат – Ячейки – Число – Процентный;

3.3 в поле Число десятичных знаков:;

3.4 ОК.

4. Вводим все заголовки и данные.

Расчет

1. Рассчитаем дизажио в ячейке G4 по формуле 500000·(1 – 0,96)

В Excel эта формула выглядит так =В4*(1 – С4)

2. Вычислим в ячейке Н4, что получила фирма на руки от первого банка, по формуле

П=500000 – 500000(1 – 0,96) – 300

В Excel в ячейку Н4 введем соответствующую ей формулу =В4 – G4 – D4

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

В данном примере

PV=$500000

FV=0 – кредит через 5 лет должен быть погашен.

r=0,24; k=5 лет; m=1; тип=0

По формуле (2.8)

B Excel вычислим выплаты с помощью финансовой функции

ППЛАТ(Норма; Кпер; Нз; БС; тип).

В ячейку I4 введем функцию ППЛАТ(Е4; F4; B4).

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

4. Рассчитаем отношение выплаты/получено (С/П). Для удобства восприятия возьмем их по абсолютной величине. Для этого в ячейку J4 введем формулу

=ABS(I4/H4)

5. Проведем аналогичные расчеты для всех остальных банков. Для этого:

5.1 Выделим блок G4:J4;

5.2 Скопируем формулы на блок G5:J6.

Вывод

Хотя банк ИНБК больше всех оставляет себе плату за страх и за оформление, но он предлагает наиболее низкий процент кредита 22%. Поэтому его условия оказались самыми выгодными, отношение выплаты к получению самым низким. Именно с ним фирма заключила финансовую сделку.

Остается отформатировать лист Кредит, как показано в таблице 2.1.

План погашения кредита

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

ПЛПРОЦ(Норма; Период; Кпер; Тс; Бс)

ОСНПЛАТ(Норма; Период; Кпер; Тс; Бс).

В обозначениях, принятых в данной работе, приведенные формулы запишутся в виде

ПЛПРОЦ(r/m; i; m·k; PV; FV)

ОСНПЛАТ(r/m; i; m·k; PV; FV).

Здесь

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

1. На листе, который назовем Погашение, введем заголовки и года погашения кредита (Таблица 2.2).

Таблица 2.2

План погашения кредита

Год

Плата по процентам

Погашение основного долга

Остаток

-$110 000,00

-$64 602,97

$435 397,03

-$95 787,35

-$78 815,62

$356 581,41

-$78 447,91

-$96 155,06

$260 426,36

-$57 293,80

-$117 309,17

$143 117,19

-$31 485,78

-$143 117,19

-$0,00

Погашение

FV=

$1 351 354,08

2. В ячейку В4 вводим формулу

ПЛПРОЦ(Кредит!$E$6; Погашение!А4; Кредит!$F$6; Кредит!$B$6)

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

2.1 Первый аргумент функции – Норма, берется с листа Кредит из ячейки Е6. знаки долларов в адресе ячейки показывают, что адрес этой ячейки не должен меняться ни при каких манипуляциях. Для того, чтобы набрать этот адрес:

а) щелкаем по ярлыку листа Кредит;

б) щелкаем по ячейке Е6 этого листа;

в) нажимаем клавишу F4. Появляются значки $: $F$4.

2.2 Второй аргумент – номер года i является относительным адресом. Он берется с листа Погашение.

2.3 Четвертый и пятый аргументы - срок договора и сумма кредита берутся из соответствующих ячеек листа Кредит. Они являются абсолютными адресами.

2.4 ОК.

Если вместо числа в ячейке появляются решетки #, это значит, что результат не вписывается в размеры ячейки. Нужно раздвинуть ячейку (поставить курсор между столбцами В и С, добиться курсора вида и при нажатой левой клавише мыши потянуть курсор вправо).

3. Аналогично вводим в ячейку С4 функцию

ОСНПЛАТ(Кредит!$Е$6;А4;Кредит!$F$6;А4; Кредит!$В$6).

Если сравним результаты в ячейках В4 и С4 листа Погашение с величиной в ячейке I4 листа Кредит, то убедимся, что плата по процентам и плата по основному долгу в сумме равны ежегодной выплате.

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

5. Проследим, какой же долг остается за фирмой по годам.

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

В ячейку D4 вводим формулу

=Кредит!В6+Погашение!С4

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

В ячейке D5 записываем формулу

=D4+C5

5.3 Копируем эту формулу в оставшиеся ячейки. В конце пятого года долг равен $0.

Вычислим, какую сумму выплатила фирма за весь срок кредита.

По формуле (2.3)

Вычислим FV по этой формуле в ячейке В10.В ячейке С10 проверим ее с помощью функции БЗ(Кредит!Е6; Кредит!F6!; Кредит!I7)=$1351 354,08 Как видим, результаты совпали.

PV=0

C=-$174602,97

m=1

k=5

r=0,22

Тип=0

FV=?

Фирма переплачивает за срок 5 лет больше 800 тыс. долларов, то есть переплачивает в 1,6 раза больше, чем берет (платит в 2,6 раза больше, чем берет)! А это по нашим меркам еще божеский кредит.