Материал: Ивличева Н.А. Информиционные системы... Практикум Ч. 1
46
ЗАДАНИЕ 6. КРЕДИТНЫЙ КАЛЬКУЛЯТОР
Составление графика платежей для платежей по фактическому остатку
Одним из способов расчета процентов по выданному кредиту является расчет процентов по фактическому остатку основного долга. В этом случае ежемесячный платеж состоит из трех частей.
1. Ежемесячное погашение основного долга: MC OSM , где
OS – остаток основного долга на начало месяца платежа,
M – количество оставшихся платежей (включая рассчитываемый).
Для автоматизации расчетов количество оставшихся платежей вычисляют по формуле:
M S TM 1, где
S – срок кредита,
TM – номер текущего платежа.
2. Проценты:
MP OS12 P , где
P – годовая процентная ставка кредита.
3. Дополнительные комиссии MK .
При такой форме платежа основной долг погашается каждый месяц на одну и ту же сумму, общая сумма выплат ежемесячно уменьшается.
Задача
Потребитель взял в банке кредит на сумму 100000 рублей на 24 месяца под 15% годовых. Ежемесячно взимается дополнительная комиссия в размере 0,1% от первоначальной суммы кредита. Составьте график платежей по кредиту при условии, что платежи производятся по фактическому остатку.
Для решения задачи расположите на листе Excel исходные данные, установив соответствующие форматы (денежный, процентный):
Оформите шапку и стартовую строку таблицы-графика, введите в
столбце Остаток основного долга ссылку на ячейку B1 с суммой кредита:
47
Введите в столбец Месяц номера месяцев от 1 до 24, как показано на ри-
сунке 6.1, оформите границы ячеек в таблице.
Заполните формулами ячейки платежа за первый месяц. При вводе формул расставьте в нужных местах абсолютные ссылки на исходные данные так, чтобы формулу можно было копировать по столбцу.
При вводе формул учитывайте, что новый остаток основного долга равен остатку за предыдущий месяц, уменьшенному на сумму основного долга в составе ежемесячного платежа.
В ячейках платежа за первый месяц установите денежный формат с двумя знаками после запятой, как показано на рисунке 6.1.
Скопируйте введенные формулы автозаполнением (протягиванием) на следующие строки, чтобы полностью заполнить график платежей за 24 месяца. Сравните результат с данными на рисунке 6.1.
Рисунок 6.1
В первой строке листа Excel заполните две ячейки для расчета общей суммы выплат. В ячейку E1 введите формулу для подсчета общей суммы платежей по столбцу Всего.
В ячейке F1 рассчитайте сумму переплаты.
48
В ячейке G1 рассчитайте эффективный процент, то есть, определите,
сколько процентов составит реальная выплата за пользование кредитом. Сравните эффективный процент с заявленной годовой ставкой.
Составление универсального графика платежей по фактическому остатку
Составленный при решении предыдущей задачи график платежей не обладает достаточной универсальностью – его можно использовать только для расчета кредитов, взятых на 24 месяца.
Измените срок кредита на 12 месяцев. Обратите внимание на появившиеся сообщения об ошибках. Исправьте срок кредита на 24 месяца.
Устраним ошибки и отображение лишних границ ячеек таблицы с помощью функции ЕСЛИ() и условного форматирования ячеек.
С помощью логической функции ЕСЛИ() организуйте вычисления в столбцах Основной долг, Процент и Комиссия за первый месяц так, чтобы рас-
чет этих величин по формулам происходил только в том случае, когда номер месяца не превышает срока кредита; в противном случае в ячейках установите значение 0.
Измените форматирование всех ячеек первого месяца с помощью условного форматирования: в случае, когда значение в столбце А соответствующей стро-
ки меньше или равно сроку кредита, установите в ячейке черный цвет шрифта, прозрачную заливку и черные тонкие границы; в противном случае, установите в ячейке белый цвет шрифта, прозрачную заливку и отсутствие границ.
Скопируйте формулы и форматирование (например, с помощью автозаполнения и форматирования по образцу) так, чтобы можно было рассчитать график любого кредита на срок до 240 месяцев.
Измените срок кредита на 12 месяцев, убедитесь в работоспособности калькулятора. Измените срок кредита на 30 месяцев, оцените результат.
Задания для самостоятельного решения
1.Заемщик планирует взять кредит на сумму 250000 рублей под 21% годовых на срок 18 месяцев. Ежемесячная комиссия составляет 0,1% суммы кредита. Платежи производятся по фактическому остатку. Рассчитайте по этим данным график погашения кредита, определите сумму переплаты и найдите, во сколько раз уменьшится платеж к концу срока по сравнению с первоначальным.
2.В качестве альтернативы ту же сумму (задание 1) с той же ежемесячной комиссией можно взять в другом банке под 21% годовых на срок 12 месяцев. Как изменится в этом случае величина первоначального платежа и сумма переплаты?
3.Семья планирует взять ипотечный кредит на 1500000 рублей под 14% годовых без ежемесячной комиссии. С учетом доходов семьи ежемесячный взнос по кредиту не может превышать 20000 рублей. Можно ли подобрать срок кредита, если вся сумма должна быть выплачена не более чем за 15 лет? (Платежи производятся по фактическому остатку.)
49
4.С помощью надстройки Сервис – Поиск решения определите максимальную сумму кредита, который может взять семья (задание 3) на 10 лет и на 15 лет. (В качестве целевой ячейки используйте сумму кредита, ее же укажите в поле Изменяя ячейки, в качестве ограничений используйте ограничение на размер первоначального взноса.)
5.Банком принято решение о разработке индивидуальной ипотечной программы для этой семьи (задание 3) по льготным условиям. Ставка снижена до 9%. Может ли в этом случае семья взять кредит на 1500000 рублей на 15 лет при условии, что ежемесячный взнос не может превышать 20000 рублей?
6.С помощью надстройки Сервис – Поиск решения определите минималь-
ный срок, на который семья (задание 3) может взять кредит в 1500000 рублей при условии, что ежемесячный взнос не может превышать 20000 рублей.
Составление универсального графика платежей для аннуитетных платежей
В случае аннуитетных платежей размер ежемесячного платежа всегда остается постоянным и для любого месяца рассчитывается на основе аннуитетной выплаты, которая определяется по формуле:
|
|
OS |
|
P |
|
|
|
||
AN |
12 |
|
|
, где |
|||||
|
|
|
|
||||||
|
|
|
|
|
|||||
1 |
|
|
1 |
|
|
|
|||
|
|
|
P M |
||||||
|
|
|
|
||||||
|
|
1 |
|
|
|
|
|||
12 |
|||||||||
|
|
|
|
|
|||||
OS – остаток основного долга на начало месяца платежа, P – годовая процентная ставка кредита,
M S TM 1 – количество оставшихся платежей (включая рассчитывае-
мый),
S – срок кредита,
TM – номер текущего платежа.
Структурно ежемесячный платеж по аннуитету состоит из тех же частей, что и платеж по остатку.
1. Проценты:
MP OS12 P .
2. Ежемесячное погашение основного долга:
MC AN MP .
(Таким образом, аннуитетная выплата представляет собой совокупность выплаты процентов и суммы погашения основного долга.)
3. Дополнительные комиссии MK .
Задача
Потребитель взял в банке кредит на сумму 100000 рублей на 24 месяца под 15% годовых. Ежемесячно взимается дополнительная комиссия в размере 0,1%
50
от первоначальной суммы кредита. Составьте график платежей по кредиту для аннуитетных платежей.
Для решения задачи расположите на листе Excel исходные данные:
Оформите на листе таблицу и заполните ее формулами для расчета платежей по кредиту. (При вводе формул за первый месяц расставьте в нужных местах абсолютные ссылки на исходные данные так, чтобы формулу можно было копировать по столбцу, формулы размножайте автозаполнением.) Установите в соответствующих ячейках денежный формат.
При расчете ежемесячных выплат сверяйте результаты с рисунком 6.2.
Рисунок 6.2
С помощью логической функции ЕСЛИ() организуйте вычисления в столб-
цах Аннуитет, Основной долг, Процент и Комиссия так, чтобы расчет
этих величин по формулам происходил только в том случае, когда номер месяца не превышает срока кредита; в противном случае в ячейках установите значе-