Как создать кредитный калькулятор в Excel?

Добрый день уважаемый пользователь!

Сегодня я хотел бы поговорить о таком необходимом зле, как кредит. Почему зло, вы и так знаете, особенно это касается потребительского кредитования, когда за вещь вы переплачиваете в 2-3 раза больше ее реальной цены. Это всё необходимо учитывать и просчитывать, поэтому и научитесь создавать свой личный кредитный калькулятор в котором вы реально увидите картинку «мышеловки», в которую попадают обычный обыватель. Хотя есть еще кредиты для бизнеса, но там немного другая история, их берут, чтобы зарабатывать деньги. Главная проблема кредита даже не в «космических» процентах, а в том, что вы получаете удовольствие сейчас, а расплата и проблемы вас ждут в будущем, а это убивает личную мотивацию практически в зародыше. Пропадает желание, что-то делать, развиваться, напрягаться, учиться, создавать источники дохода, когда можно «тупо» взять паспорт и за 15 минут в ближайшем банке вас быстренько возьмут в кабалу и грамотно навешают на вас кучу всего разного и лишнего, лишь бы было, типа страховку и прочее.

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

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

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

Рассмотрим три самых популярных варианта использования кредитного калькулятора в Excel:

1. Для простых кредитов;
2. Кредит с досрочным погашением;
3. Кредит с нерегулярными платежами.

Кредитный калькулятор для расчёта простых кредитов

Начнём с простого варианта, быстро прикинем, сколько нам нужно ежемесячно оплачивать по аннуитетному кредиту, это когда выплаты делают одинаковыми суммами, как в большинстве случаев. Это можно произвести одной функцией Excel и несколькими простыми формулами. Для получения результата в Excel существует функция ПЛТ в разделе «Финансовые». Указываем, в какую ячейку нужен результат, вызываем «Мастер функций» ищем функцию ПЛТ, нажимаем кнопочку «ОК» и в окне мастера вводим необходимые аргументы для нашего расчёта, формула получается следующего вида:

=ПЛТ(B5/12;B6;B4;0;0), где:

• Ставка (B5/12) – является аргументом, указывающим на процентную ставку по взятому кредиту в разрезе периодов выплат, в нашем случае это месяцы. Если ставка по кредиту в год 18%, то за один месяц будет составлять 1,5%;
• Кпер (B6) – аргумент, указывающий на количество периодов, то есть, на сколько месяцев взят кредит;
• Бс (B4) – указываем, какую сумму кредита будем рассчитывать;
• Пс (0) – это финишная пряма, какой итог кредита должен быть в конце, скорее всего это будет 0, что означает, что вы никому и ничего не должны;
• Тип (0) – аргумент необходимый для учёта выплат каждый месяц. Если равно 1 – это учитываем выплаты к началу месяца, если 0 – то учитываем на конец. В постсоветском пространстве большинство банков используют последний вариант, а значит вводим 0.

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

Теперь в поле «Тело кредита» в ячейку Е2 вводим формулу функции ОСПЛТ следующего вида:

=ОСПЛТ($B$4/12;D2;$B$5;$B$3;0)

Как видите, ее орфография практически аналогична функции ПЛТ, добавился только аргумент «Период», который указывает на номер текущего месяца, и дополнительно рассматривать ее я не буду. Единственное, на что обращу ваше внимание, это то, что формула будет растянута на диапазон, а значит, аргументы необходимо закрепить абсолютными ссылками. Следующим шагом для столбика «Проценты» будем использовать возможности функции ПРПЛТ. Вводится она аналогично вышеописанной и с теми же условиями и будет иметь такой вид:

=ПРПЛТ($B$4/12;D2;$B$5;$B$3;0) Теперь в оставшиеся столбики будем вводить простые формулы, для получения суммы выплаты нам нужна формула: =E2+F2, а для определения суммы остатка кредита используем формулу: =$B$3+СУММ($E$2:E2). При необходимости, возможно, немножко улучшить и автоматизировать ваш кредитный калькулятор в Excel для уменьшения количества ошибок.

Для начала пропишем формулу в ячейку D3 для того чтобы она подстраивала и отслеживала срок кредита:

=ЕСЛИ(D2>=$B$5;»«;D2+1)

Следующим шагом с помощью логической функции ЕСЛИ для поля «Тело кредита», сделаем автоматическую проверку достигли ли вы последнего срока выплат или нет. Если период, достигнут, получаем пустую ячейку «», а если нет, то функцией ОСПЛТ выводим необходимый расчёт:

=ЕСЛИ(D3<>»»;ОСПЛТ($B$4/12;D3;$B$5;$B$3;0);»»)

Кредитный калькулятор для кредита с досрочным погашением

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

Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:

• во-первых, сокращения суммы выплат по кредиту на каждый месяц;
• во-вторых, уменьшения срока выплат.

Для лучшей наглядности будем рассматривать каждый случай в отдельности.

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

Создаем кредитный калькулятор для кредитов с нерегулярными платежами

Последний из рассматриваемых вариантов будет расчёт кредита с нерегулярными платежами, это когда на повышенную процентную ставку вам предоставляют лояльную программу вносить платежи на нерегулярной основе и без определений сумм взносов. Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до дня, а не до месяца. Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.

А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!