6. разработка планов погашения кредитов и займов разработка планов погашения кредитов. Финансовые функции в ппп excel

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

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

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

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

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

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

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

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

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

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

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

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

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

ПРОЦПЛАТ ( 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.

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

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

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

На листе ППП 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.2.