Разработка планов погашения кредитов и займов

6. РАЗРАБОТКА ПЛАНОВ ПОГАШЕНИЯ КРЕДИТОВ И ЗАЙМОВ

6.1. Разработка  планов погашения кредитов. Финансовые  функции в ППП EXCEL (ПРОЦПЛАТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД)

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

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

Рассмотрим  механизм работы функций на следующем  примере.

 

Пример 1. Банком выдан кредит в 10 000 денежных единиц на 5 лет под 15% годовых, начисляемых один раз в конце каждого периода. По условиям договора кредит должен быть погашен равными долями в течение указанного срока, выплачиваемыми в конце каждого периода. Разработать план погашения кредита для банка.

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

Будущая величина сделки на данном этапе анализа нам пока неизвестна. Однако она не является обязательным аргументом, поэтому мы можем ее опустить.

Периодический платеж по данной операции составит:

ПЛТ(0,15; 5; –10 000,00)=2 983,16.

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

ПЛТ( 0,15; 5; –10 000,00; ) ∙5=14 915,80.

Ответ. Периодический платеж для погашения кредита размером 10 000 денежных единиц составит 2 983,16 денежной единицы.

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

Для осуществления  подобных расчетов используются функции ПРОЦПЛАТ и ОСПЛТ, позволяющие выделить для заданного периода из платежа его процентную и основную части.

Функция ПРОЦПЛАТ выделяет из периодического платежа его процентную часть:

ПРОЦПЛАТ(ставка; период; кпер; пс)

Определим процентную часть платежа на первый период для  рассматриваемого примера:

ПРОЦПЛАТ ( 0,15; 1; 5; –10 000,00)=1 500,00 (ден. ед.).

Функция ОСПЛТ  позволяет определить ту часть платежа, которая направлена на погашение основного долга:

ОСПЛТ (ставка; период; кпер; [нз]; [бс]; [тип]).

 Определим основную часть платежа:

ОСПЛТ( 0,15; 1; 5; -10 000,00)=1483,16(ден. ед.).

Нетрудно  заметить, что:

ПРОЦПЛАТ + ОСПЛТ = ПЛТ =2983,16.

Таким образом, процентный доход банка от выданного  кредита на конец первого периода составит 1 500 денежных единиц, а вернувшаяся часть основного долга - 1483,16.

Функции этой ОБЩПЛАТ и ОБЩДОХОД предназначены для вычисления процентов и суммы погашенного долга между любыми двумя периодами выплат. Для этих функций необходимо указывать все аргументы, причем в виде положительных величин.

Функция ОБЩПЛАТ служит для вычисления накопленной суммы процентов за период между двумя любыми выплатами. Определение данной величины играет важнейшую роль в банковском деле:

ОБЩПЛАТ (ставка; кпер; нз; нач_период; кон_период; тип).

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

ОБЩДОХОД (ставка, количество периодов, нз, нач_период, кон_период, тип).

 

 Воспользуемся  этими функциями для проверки  итоговых результатов (т.е. за 5 лет) по примеру 1.

ОБЩПЛАТ(0,15; 5; 10 000; 1; 5; 0)= –4915,78.

ОБЩДОХОД(0,15; 5; 10 000; 1; 5; 0)= –10 000,00.

Как следует из проведенных расчетов, сумма полученных величин равна общей сумме, выплаченной по данному займу:

10 000 + 4 915,78 = 14 915,78.

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

Пример 2. Банком выдан кредит в 10 000 денежных единиц на 5 лет под 12% годовых, который должен быть погашен равными долями, выплачиваемыми раз в конце каждого года. Разработать план погашения кредита.

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

-ПЛТ(0,12/5; 5∙1;10 000; ; 0)=2 774,10 (ден. ед.).

 

Номер периода	Баланс на конец	Основной долг	Проценты	Наколенный долг	Наколенный  процент
1	8425,90	1574,10	1200,00	1574,10	1200,00
2	6662,91	1762,99	1011,11	3337,09	2211,11
3	4688,37	1974,55	799,55	5311,63	3010,66
4	2476,87	2211,49	562,60	7523,13	3573,26
5	0,00	2476,87	297,22	10000,00	3870,49

 

Столбец  Основной долг таблицы вычислены с помощью функции ОСПЛТ. Столбец Проценты – с помощью функции ПРПЛТ. Столбец Наколенный долг – с помощью функции ОБЩЦОХОД. Столбец Наколенный процент – с помощью функции ОБЩПЛАТ.

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

 

 

6.2. Расчет схемы  погашения займа. Финансовые  функции в ППП EXCEL (ПЛТ, ПРОЦПЛАТ, ПРПЛТ, ОБЩПЛАТ, ОСПЛТ, ОБЩДОХОД)

 

Для изучения вопроса  расчета схемы погашения займа  в ППП EXCEL рассмотрим следующий пример.

 

Пример 1. Имеется заем 70 000 денежных единиц выданный на срок 3 года под 17 % годовых. Рассчитать сумму погашения этого займа.

Решение.

Год	Сумма займа на начало года	Общая сумма платежа	Платежи по процентам	Сумма основного платежа по займу	Сумма займа на конец года
1	70 000,00	31 680,16	11 900,00	19 780,16	50 219,84
2	50 219,84	31 680,16	8 537,57	23 142,78	27 077,06
3	27 077,06	31 680,16	4 603,10	27 077,06	0
Итого:		95 040,47	25 040,47	70 000,00

 

Элементы этой таблицы  вычислены с помощью специальных  функций ППП EXCEL.

Колонка общая сумма платежа заполняется с помощью функции ПЛТ. Для нашей задачи будем иметь ПЛТ (0,17;3; –70000)=31 680,16.

 

Пример 2. Предположим, что необходимо накопить 4 000 денежных единиц за 3 года. Откладывая постоянно сумму в конце месяца, какой должна быть эта сумма, если процентная ставка составляет 12 % годовых.

Решение. ПЛТ (0,12/12;3∙12; ; –4000)=92,86 (ден. ед.).

Ответ. Для того, что бы накопить 4 000 денежных единиц за три года при процентной ставке 12 % годовых, необходимо каждый месяц вносить 92,86 денежных единиц.

Платежи по процентам рассчитываются с помощью функции ПРОЦПЛАТ. Эта функция вычисляет платежи по процентам за заданный период. Для примера 6.2. получим:

за 1 год ПРОЦПЛАТ (0,17;1;3; –70000)= 11 900,00 (ден. ед.),

за 2 год ПРОЦПЛАТ (0,17;2;3; –70000)= 8 537,57 (ден. ед.),

за 3 год ПРОЦПЛАТ (0,17;3;3; –70000)= 4 603,10 (ден. ед.).

 

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

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

за 1 год ОСПЛТ (0,17;1;3; –70000)= 19 780,1 (ден. ед.),

за 2 год ОСПЛТ (0,17;2;3; –70000)= 23 142,78 (ден. ед.),

за 3 год ОСПЛТ (0,17;3;3; –70000)= 27 077,06 (ден. ед.).

 

Существует функция, которая  вычисляет сумму платежей по процентам, по займу который погашается равными платежами в конце или начале каждого расчетного периода между двумя периодами выплат ОБЩПЛАТ.

 

Пример 3. Пусть заем под недвижимость сделан на следующих условиях. Процентная ставка равна 9 % годовых, срок 30 лет, размер ссуды 125 000 денежных единиц. Проценты начисляются ежемесячно. Найти сумму выплат по процентам за первый месяц, за второй год.

Решение.

За первый месяц: ОБЩПЛАТ (0,09/12; 30∙12; –125000;1 ;1)= 937,5 (ден. ед.),

За второй год: ОБЩПЛАТ (0,09/12; 30∙12; –125000; 13; 24)= 11 135,23 (ден. ед.).

Почти аналогичный результат  можно получить с помощью функции  ПРПЛТ, но только за 1-ый месяц 

ПРПЛТ(0,09/12;1;30∙12; –125000)=937,5 (ден. ед.).

Ответ. Сумма выплат по процентам за первый месяц составит 937,5 денежных единиц, за второй год – 11 135,23 денежных единиц.

 

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

 

Пример 4. Допустим, выдана ссуда размером 1000 денежных единиц сроком на 6 месяцев, под 15 % годовых, проценты начисляются ежеквартально. Определить величину основных выплат за 5-й год.

Решение.  ОБЩДОХОД(0,15/4;6∙4; –1000;17;20)=201,43 (ден. ед.).

Ответ. Величина основных выплат составит 201,43 денежных единицы.

 

 

6.4 Упражнения

Упражнение 1. Банком выдан кредит в X тыс. денежных единиц на Y лет под Z% годовых, который должен быть погашен равными долями, выплачиваемыми раз в конце каждого года. Разработать план погашения кредита.

Значения переменных в таблице 6.3.1.

Таблица 6.3.1

Переменные	Варианты
	1	2	3	4	5	6	7	8	9	10
X тыс.ден. ед.	100	110	120	130	140	150	160	170	180	190
Y лет	5	6	7	4	5	6	7	3	4	5
Z %	10	11	12	13	14	15	16	17	18	19

 

Упражнение 2. Рассчитать схему погашения займа размером X тыс. денежных единиц выданного на Y лет под Z % годовых.

Значения переменных в таблице 6.3.2.

Таблица 6.3.2

Переменные	Варианты
	1	2	3	4	5	6	7	8	9	10
X тыс.ден. ед.	400	420	440	460	480	500	520	540	560	580
Y лет	5	6	7	4	5	6	7	3	4	5
Z %	10	11	12	13	14	15	16	17	18	19