__Расчёт выплат по кредиту

Расчёт выплат по кредиту

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

Построим таблицу при условии постоянных платежей.

Нам необходимо указать сумму кредита (ячейка Е1), процентную ставку (ячейка Е2) и количество периодов (ячейка Е3). Также потребуется выделить ячейку для указания величины постоянных платежей, в которой мы будем подбирать значения (ячейка Е5).

В каждом периоде нам необходимо выполнить постоянный платёж, который делится на две части: процент за пользование кредитом и сумма в погашение кредита. Также укажем номер периода и сумму долга на начало периода. Результат представлен на рис. 1.

Рис. 1. Подготовка таблицы

Заполним таблицу.

Сумма долга на начало первого периода берётся из исходных данных (ячейка Е1), а платёж из ячейки Е5. Проценты за кредит вычисляются как сумма долга (ячейка В8), умноженная на процентную ставку выплат по кредиту (ячейка Е2). Разница между платежом (ячейка Е8) и уплаченными процентами (ячейка С8) покажет величину погашения основного долга (ячейка D8). Величина долга на начало второго периода будет равна сумме долга на начало первого периода (ячейка В8) за вычетом погашенной части (ячейка D8) и будет записана в ячейку В9.

Результат заполнения первой строки показан на рис. 2.

Рис. 2. Заполнение первой строки с выплатами

Аналогично надо сделать и для оставшихся периодов.

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

Если просто применить эту операцию ко всем ячейкам строки первого периода, то результат будет неверным. Для правильного копирования необходимо указать, что не все части формулы должны автоматически исправляться, т.е. часть формул должна стать замороженной и не изменяться при копировании. Для этого в электронных таблицах была добавлена возможность использования относительных, абсолютных и смешанных ссылок. Пример определения и задания ссылок дан в Приложении 1.

Размножим данные из ячеек C8, D8, E8 и B9. Результат представлен на Рис. 3

Рис. 3. Заполнение всей таблицы

Теперь изменяя величину постоянного платежа следует добиться такого состояния, чтобы наш долг на начало 6 периода был равен 0.

Можно автоматизировать этот процесс, воспользовавшись механизмом подбора параметра.

Рис. 4. Включение функции «подбор параметра»

В качестве первого параметра указывается то значение, которое должно совпадать с требуемым результатом, указываемым во втором параметре (В разбираемом примере необходимо добиться того, чтобы величина долга на начало 6 периода была равна 0). В ячейке для первого параметра должна быть записана формула.

В качестве третьего параметра указывается ячейка, изменение которой приводит к изменению значения подбираемой ячейки, указанной в качестве первого параметра.

Пример использования функции «подбор параметра» вместе с полученным результатом представлен на рис 5.

Рис. 5. Задание параметров для подбора.

Если параметр подобрать удалось, то на экране появится соответствующее сообщение. Для разбираемого примера оно показано на Рис. 6

Рис. 6. Результат удачного подбора параметра

Полученного результата можно было достичь и другим способом, например, воспользовавшись встроенной функцией Excel ПЛТ() из категории «Финансовые». Добавим эту функцию на лист.

Для задания функции запустим «мастер функций» , и на первом шаге найдём требуемую функцию Рис. 7.

Рис. 7. Выбор функции для расчёта постоянного платежа.

На втором шаге следует указать параметры для расчёта.

Первый параметр – процентная ставка (ячейка E2). Второй параметр – количество периодов (ячейка Е3). Третий параметр – первоначальная сумма кредита (ячейка Е1). Четвёртый параметр – конечное значение кредита, обычно кредит гасится полностью и значение равно 0 (можно не указывать, а получить это значение по умолчанию). Пятый параметр определяет когда производится погашение кредита, в начале или конце периода (можно также не указывать, а получить значение по умолчанию – 0, которое соответствует погашению кредита в конце периода).

Рис. 8. Выбор функции для расчёта постоянного платежа.

Результат вычислений представлен на Рис. 9.

Рис. 9. Результат вычислений в таблице и по функции.

Можно заметить, что результат расчёта по встроенной функции Excel совпадает с вычисленным нами значением. Все исходные данные для расчёта находятся в ячейках заголовка, поэтому изменяя их, мы можем рассчитать платежи при другой сумме кредита и новой процентной ставке. После ввода новых данных потребуется перезапустить функцию подбора параметра.

Что произойдёт, если надо будет изменить количество периодов для кредита?

Для случая расчёта по встроенной формуле понадобится только изменить количество периодов. Для ручного расчёта понадобится, по крайней мере, изменить количество строк. Изменим срок кредитования до 10 периодов.

Добавление строк в работающую таблицу лучше всего проводить в середину, например, перед последней строкой. Добавим в таблицу 5 строк снизу перед последней. Для этого надо выделить последнюю строку (в примере строка 12 с периодом № 5), щёлкнуть правой кнопкой мыши и в появившемся контекстном меню выбрать пункт «Вставить». В результате появится ещё одна пустая строка над последней.

Рис. 10. Добавление новых строк.

Эту операцию надо повторить ещё 4 раза. Можно было при первом выделении выделить 5 строк, начиная с последней, тогда бы добавилось сразу 5 необходимых нам строк.

Рис. 11. Результат после добавления 5 пустых строк.

Для заполнения пустых строк надо выделить строку перед пустыми и растянуть её как мы делали ранее.

Рис. 12. Результат после добавления и заполнения 5 строк.

Осталось только изменить номер периода в ячейке А18 на 11.

Полученный шаблон можно использовать для расчёта любых периодов. Долг перед банком должен становиться равным 0 в периоде, следующем за последним расчётным. В нашем примере для 5 периодов на начало 6 периода расчёт с банком был произведён полностью.

Чтобы сделать шаблон ещё более удобным можно добавить условие, что платёж необходим только в том случае, если активный период не превосходит общее число периодов кредитования. Для этого воспользуемся функцией ЕСЛИ(), которую запишем в ячейку Е8, а затем продублируем во все остальные.

Рис. 13. Использование функции ЕСЛИ() для убирания лишних платежей.

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

Для добавления итогов по выплаченным процентам можно встать в ячейку под нижней строкой таблицы (ячейку С19) и выбрать знак . Компьютер проанализирует данные и предложит свой вариант, который нас устраивает.

Рис. 14. Использование автосуммы для подсчёта уплаченных процентов.

Полученную формулу с суммой по столбцу следует размножить в 2 столбца справа, что позволяет получить требуемый результат. По полученным результатам видно, что при получении кредита на 5 лет под 20% годовых переплата составит 67% от суммы кредита.

Для построения графиков можно воспользоваться мастером функций Excel. Если освободить ячейку в левом верхнем углу таблицы (переместив «№ периода» из ячейки А7 в ячейку А6), пометить таблицу с данными и запустить мастер функции, то первый столбец будет использован в качестве подписей оси Х, а по исходным данным будут построены графики.

Рис. 15. Добавление графика с автоматическим отбором данных.

Рис. 16. Результат с добавлением графиков.

Полученным шаблоном можно пользоваться для расчёта выплат по кредиту за любой период до 10 лет включительно. После изменения величины периода надо запустить функцию «Подбор параметра» для новой целевой ячейки (долг на первый период за периодом кредитования должен быть равен 0).

Чтобы не искать постоянно ячейку с долгом на конец периода можно вынести её к платежам в заголовок. Для отбора нужной ячейки можно воспользоваться встроенными функциями типа ВЫБОР() или ИНДЕКС(), которые позволяют получить требуемые нам данные по их номеру. Воспользуемся функцией ИНДЕКС(), которую внесём с помощью мастера функций в ячейку Е4.

На первом шаге потребуется указать один из двух форматов для функции ИНДЕКС(). В нашем случае все данные находятся в одном столбце, поэтому достаточно выбрать первый вариант.

Рис. 17. Выбор набора аргументов для функции ИНДЕКС().

На втором шаге требуется задать массив с исходными данными (в нашем случае это диапазон ячеек с величиной долга по периодам – В8:В18), номер строки (номер периода + 1). Последний параметр – номер столбца можно не задавать, поскольку информация хранится в одном столбце.

Полученный результат представлен на Рис. 19.

Рис. 18. Задание аргументов для функции ИНДЕКС().

Рис. 19. Результат работы после добавления функции ИНДЕКС().

Для того чтобы пользоваться шаблоном необходимо выполнить следующие действия:

1. Задать исходные данные (сумму кредита, процентную ставку и количество периодов);

2. Запустить функцию «подбор параметра» и указать, что надо подобрать в ячейке Е4 (долг на начало периода, следующего за расчётными) значение 0, изменяя ячейку Е5 (величину ежемесячных выплат);

3. Скрыть пустые строки с неиспользуемыми периодами.

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

Теперь построим подобную таблицу для случая погашения кредита равными долями. Можно скопировать первую таблицу, удалив формулы.

Рис. 20. Заготовка для второй таблицы.

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

С26: =B26*E$2

D26: =ЕСЛИ(A26<=$E$3;$E$23;0)

Е26: =C26+D26

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

Рис. 21. Результат заполнения формулами.

Также как и в первом случае построим графики и сравним результат с первой таблицей.

Рис. 21. Сравнение двух графиков погашения кредита.

Как видно из полученных результатов при использовании постоянных (аннуитентных) платежей на пользователя кредита ложится равномерная нагрузка (платёж 3 343,80 руб./период), однако в этом случае переплата составит 6 718.99 рублей. Во втором случае (при постоянном погашении части кредита) пользователю вначале приходится платить большие проценты (4 000 руб. в первом периоде), которые в дальнейшем будут уменьшаться вместе с суммой кредита (2 400 руб. в последнем периоде). В результате общая сумма переплаты составит 6 000 рублей, что более чем на 10% меньше первого варианта.

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

Рис. 22. Сравнение двух графиков погашения кредита для 10 лет.

В первом случае переплата составит 13 852,28 рублей или 138,5%, а во втором 11 000 рублей или 110%.

Таким образом, можно заметить, что аннуитентный платёж более привлекателен для банка, поскольку позволяет заработать больше на кредите.

С увеличением срока кредита примерно такая же ситуация, однако здесь для банка появляются дополнительные риски, связанные с большим периодом. Может произойти изменение экономической ситуации в стране, измениться положение заёмщика и т.д. Некоторые банки

Приложение 1.

Использование различных ссылок в формулах.

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

1. Записать (на бумагу!) и проверить формулу для первой ячейки (убрать знаки $ если они присутствуют).

2. Скопировать формулу в соседнюю ячейку (можно перетянуть за правый нижний угол).

3. Переписать на бумагу получившуюся формулу под первой (п.1).

4. Сравнить две формулы между собой и подчеркнуть в первой формуле те части, которые изменились.

5. Проверить вторую формулу, найти те части, в которых формула получилась неверной и подчеркнуть их в первой формуле.

6. Поставить знаки $ перед частями первой формулы, подчёркнутыми 2 раза.

7. Скопировать формулу из первой ячейки в остальные и проверить результат.

Проиллюстрируем работу этого алгоритма на примере расчёта величины уплаченных процентов. Для отображения стрелок воспользуемся механизмом Excel (Закладка Формулы, Кнопка Влияющие ячейки)

Рис. П1.0 Включение отображения стрелок в формулах

1. Запишем формулу в ячейке С8.

=В8*Е2

Рис. П1.1 Исходная формула для расчёта процентов.

2. Скопировать формулу в ячейку ниже

Рис. П1.2 Копирование формулы в ячеёку ниже.

3. Переписать на бумагу получившуюся формулу под первой (п.1).

=B9*E3

4. Сравнить две формулы между собой и подчеркнуть в первой формуле те части, которые изменились.

=В8*Е2

=B9*E3

5. Проверить вторую формулу, найти те части, в которых формула получилась неверной, и подчеркнуть их в первой формуле.

Рис. П1.3 Сравнение формул

Как видно из рис. П1.3 при копировании ссылка на величину долга изменилась верно, а ссылка на процентную ставку – неверно.

=В8*Е2

6. Поставить знаки $ перед частями первой формулы, подчёркнутыми 2 раза.

=В8*Е$2

Для изменения типа ссылки можно нажимать у выбранной в формуле ячейки клавишу F4, что позволит перебрать все возможные виды ссылок. В нашем примере последовательно будут получаться значения $E$2 (абсолютная ссылка – ничего не меняется), E$2 (смешанная ссылка – может измениться только столбец), $E2 (смешанная ссылка – может измениться только строка), E2 (относительная ссылка – при копировании может измениться целиком). Правило очень простое – та часть перед которой стоит знак ‘$’ не изменяется.

7. Скопировать формулу из первой ячейки в остальные и проверить результат.

Рис. П1.4 Результат правильного копирования формулы