Excel: кредиты с переменными выплатами, конверсия и консолидации займов

EXCEL: КРЕДИТЫ С ПЕРЕМЕННЫМИ ВЫПЛАТАМИ, КОНВЕРСИЯ И КОНСОЛИДАЦИЯ ЗАЙМОВ

к. ф-м. н., доцент

ОУ ВПО «Чебоксарский кооперативный институт», г. Чебоксары *****@

В настоящее время разработаны эффективные программные средства для проведения различных финансовых расчетов. Так, с помощью электронных таблиц Excel нетрудно разработать план погашения кредита равными платежами [1, 2]. В данной статье мы рассмотрим методику финансовых расчетов, которые возникают при рассмотрении:

·  кредитов с переменными выплатами,

·  конверсии займов,

·  консолидации займов.

1.  Кредиты с переменными выплатами

Задача 1. Кредит в 100000 д. е. выдан на 5 лет под 20% годовых при условии возврата в конце второго года 30000 д. е., в конце каждого последующего года – по 40000 д. е. Найти, какую сумму надо заплатить в конце первого года, чтобы к концу 5-го года кредит был погашен полностью.

Решение основано на применении инструмента Подбор параметра.

1)  Считая, что сумма, возвращаемая в конце первого года, известна и равна 20000 д. е. подготовим в Еxcel расчетную таблицу:

	A	B	C	D
1	Погашение кредита переменными выплатами
2	Сумма	Ставка
3	100000	0,2
4
5	Год	Долг к концу года	Возвращаемая сумма	Остаток долга
6	1	120000	20000	100000
7	2	120000	30000	90000
8	3	108000	40000	68000
9	4	81600	40000	41600
10	5	49920	40000	9920

При этом введем формулы согласно таблице:

Ячейка	Формула	Примечание
B6	=A4*(1+B3)
B7	=D6*(1+$B$3)	формулу копируем вниз до строки 10
D6	=B6-C6	формулу копируем вниз до строки 10

2)  Воспользуемся инструментом Подбор параметра, заполнив его окно следующим образом: Установить в ячейке – D10, Значение – 0, Изменяя значение ячейки – С6.

В итоге получим:

Год	Долг к концу года	Возвращаемая сумма	Остаток долга
1	120000	24784	95216
2	114259	30000	84259
3	101111	40000	61111
4	73333,3	40000	33333
5	40000	40000	0

2.  Конверсия займов

Изменение условий погашения кредитов называется конверсией займа. При достижении соглашения о конверсии могут изменяться срок погашения займа, процентная ставка, порядок годовых выплат и т. п.

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

Задача 2. Кредит в сумме 40 тыс. долл., выданный на 5 лет под 16% го­довых, подлежит погашению равными ежегодными выплатами в конце каж­дого года. Проценты начисляются в конце года. После выплаты третьего платежа достигнута договоренность между кредитором и заемщиком о про­длении срока погашения займа на 2 года и увеличении процентной ставки с момента конверсии до 20%. Необходимо составить план погашения остав­шейся части долга.

Решение. Подготовим таблицу:

	A	B	C
1	Конверсия займа
2	Сумма кредита	40
3	Ставка	0,16
4	Срок (лет)	5
5	План погашения
6	Период. платеж	12,2164
7	Год	Основной долг	Накоплен %
8	1	5,816	6,400
9	2	12,563	11,869
10	3	20,390	16,259
11	4	29,469	19,397
12	5	40,000	21,082
13
14	Остаток долга	19,610
15	Новая ставка	23%
16	срок	4
17	Период. платеж	8,001

Приведем формулы, необходимые для расчетов:

Ячейка	Формула
B6	=ППЛАТ(B3;B4;-B2)
B8	=-ОБЩДОХОД($B$3;$B$4;$B$2;1;A8;0)
C8	=-ОБЩПЛАТ($B$3;$B$4;$B$2;1;A8;0)
В14	=B2-B10
B17	=ППЛАТ(B15;B16;-B14)

3.  Консолидация займов

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

Задача 3. Банком было предоставлено предприятию два кредита. Пер­вый, в размере 2,0 млн руб. под 18% годовых, должен погашаться равными полугодовыми выплатами в течение 6 лет, начисление процентов — по полугодиям. Второй — 1,5 млн руб. со сроком погашения 4 года, ставка 22%, капитализация ежегодная.

После выплаты в течение двух лет два долга объединяются в один на сле­дующих условиях: консолидированный долг имеет срок погашения 8 лет, погашение производится равными полугодовыми срочными выплатами, процентная ставка 24%, капитализация полугодовая. Определить величину полугодовой срочной уплаты.

Решение. Подготовим таблицу:

	A	B	C	D	E
1	Консолидация займов
2	Кредит1		Кредит2
3	Сумма кредита	2		Сумма кредита	1,5
4	Ставка	0,18		Ставка	0,22
5	Срок (лет)	6		Срок (лет)	4
6	m	2		m	1
7	План погашения		План погашения
8	Период. платеж	0,2793		Период. платеж	0,6015
9	Год	Основной долг		Год	Основной долг
10	1	0,099		1	0,272
11	2	0,208		2	0,603
12	3	0,326		3	1,007
13	4	0,454		4	1,500
14
15	Остаток долга	1,546		Остаток долга	0,897
16	Общий долг	2,443
17	Новая ставка	24%
18	срок	8
19	m	2
20	Период. платеж	0,350

Приведем формулы, необходимые для расчетов:

Ячейка	Формула
B8	=ППЛАТ(B4/B6;B5*B6;-B3)
E8	=ППЛАТ(E4/E6;E5*E6;-E3)
B10	=-ОБЩДОХОД($B$4/$B$6;$B$5*$B$6;$B$3;1;A10;0)
E10	=-ОБЩДОХОД($E$4/$E$6;$E$5*$E$6;$E$3;1;D10;0)
В15	=B3-B13
E15	=E3-E11
B16	=B15+E15
B17	=-ППЛАТ(B17/B19;B18*B19;B16)

Литература

1.  Лукасевич финансовых операций. Методы, модели, техника вычислений.– М.: Финансы, ЮНИТИ, 1998.– 400 с.

2.  Просветов хозяйственной деятельности предприятия: Задачи и решения.– М.: Издательство РДЛ, 2005,– 192 с.