Работа 10. Использование функций excel при кредитных расчетах Цель работы

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

2. Основные теоретические положения

Кредитные расчеты основаны на использовании понятия ренты.

Рента – это финансовая схема с постоянными взносами или выплатами. Если выплаты в разные периоды R₁ = R₂ = …= R_n = R (равномерная рента).

Наращенная сумма для ренты определяется по формуле

. (1)

В Excel для вычисления по формуле (5) используется функция БС, в которой аргумент выплаты – это значение R (в ОС Windows 98 – функция БЗ).

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

. (2)

В Excel для расчета R используется функция ПЛТ (промежуточные платежи) с аргументами

=ПЛТ(норма;число периодов;начальное значение;будущее значение;тип).

Здесь аргумент будущее значение – величина S. (В Windows 98 это функция ППЛАТ).

Кроме того, так как выплаты включают не только плату за взятый кредит, но и платежи по процентам, есть функции ПРОЦПЛАТ (плата по процентам) и ОСПЛТ (основные платежи). (В Windows 98 эти функции называются: вместо ПРОЦПЛАТ – функция ПЛТПРОЦ, вместо функция ОСПЛТ – функция ОСНПЛАТ). Синтаксис функции ПРОЦПЛАТ:

=ПРОЦПЛАТ(норма; период; число периодов; начальное значение; будущее значение; тип)

У функции ОСПЛТ аргументы те же самые.

ПРИМЕР 1

Для покупки квартиры стоимостью 201 900 руб. взята 30-летняя ипотечная ссуда со ставкой 8 % годовых при начальном взносе 20 % суммы. Сделать расчет для ежемесячных и ежегодных выплат по ссуде.

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

РЕШЕНИЕ

n = 30 лет, r = 8 %, P = 201 900 руб.

Начальный взнос А = 0,2201900 = 40380 руб.

1). Вычисление ежемесячных выплат

Ставка за период , число периодов

n = 30 (лет)*12 (месяцев) =360 (месяцев).

По формуле (6) размер платежа R равен

.

Для расчета по этой формуле нужно знать размер ссуды S. Он вычисляется как разность между стоимостью квартиры и начальным взносом

S=P-A=201 900-0,2201 900=201 900 (1-0,2)=201 9000,8=161 520 (руб).

Тогда

(руб).

2) Вычисление ежегодных выплат

Ставка за период i = 8 %, число периодов n = 30 лет

(руб).

3. Порядок выполнения работы

Задание 1. Рассчитать ежемесячные и ежеквартальные выплаты для примера 1 в Excel.

Задание 2. Рассчитать отдельно выплаты по процентам и основным платежам.

3.1. Вычисление ежемесячных и ежегодных выплат в Excel

Для решения этой задачи создадим в Excel электронную таблицу. В режиме показа формул она представлена в табл. 32, а в режиме показа вычислений в табл. 33.

Таблица 32

	А	В	С	D	E
1	Расчет ипотечной ссуды
2	Исходные данные
3	Цена квартиры	201900
4	Первый взнос (%)	0,2
5	Годовая процентная ставка	0,08
6	Размер ссуды	=B3*(1-B4)
7		Ежемесячные платежи		Ежегодные платежи
8	Срок погашения ссуды	=D8*12	месяцев	30	лет
9	Результаты расчетов
10	Периодические выплаты	=ПЛТ(B5/12;B8;B6)		=ПЛТ(B5;D8;B6)
11	Общая сумма выплат	=B8*B10		=D8*D10
12	Общая сумма комиссионных	=B11+B6		=D11+В6

Таблица 33

	А	В	С	D	E
1	Расчет ипотечной ссуды
2	Исходные данные
3	Цена квартиры	201 900,00р.
4	Первый взнос (%)	20%
5	Годовая процентная ставка	8%
6	Размер ссуды	161 520,00р.
7		Ежемесячные платежи		Ежегодные платежи
8	Срок погашения ссуды	360	месяцев	30	лет
9	Результаты расчетов
10	Периодические выплаты	-1 185,18р.		-14 347,41р.
11	Общая сумма выплат	-426 663,55р.		-430 422,21р.
12	Общая сумма комиссионных	-265 143,55р.		-268 902,21р.