Робимо кредитний калькулятор в excel, Ексель практик

Отже, будь-який кредит має 4 основних параметри:

Так само є дві форми оплати - аннуїтетниє (коли ви щомісяця платите одну і ту ж суму) і диференційовані (коли постійної залишається частина щомісячного платежу - та, яка погашає основний борг, а друга частина регулярно перераховується).

Якщо ви знаєте 3 показника, то зможете підібрати четвертий.

Ми зробимо спочатку калькулятор. За розрахунок всіх чотирьох показників відповідають ці функції:

Параметри функцій одні і ті ж - знаєте три з 4-х показників, відповідна функція видасть 4-й. Найбільш наочно дивіться перший лист файлу.

Щоб підготувати графік платежів, нам знадобиться дата видачі кредиту.

Невеликий відступ по достроковому погашенню. Дострокове погашення зменшує суму основного боргу, тому після нього зазвичай перераховується щомісячний платіж або змінюється термін кредиту.

Переходимо до другого аркушу.

Перший рядок графіка - дата видачі, тому тут буде тільки початкова сума кредиту.

На другому рядку -

1. Дата - визначається як той же номер, що і видача кредиту, але наступного місяця. Використовуємо функцію ДАТА. де рік і число ті ж, що і в попередньому періоді, а місяць на один більше. Але є нюанс - банк адже не прийме платіж у вихідний день. Тому робимо коригування числа за допомогою функції ДЕНЬНЕД. Важливо. дату можна коригувати вручну, на наступну дату впливу не матиме.
2. Сума щомісячного платежу (яка визначається за функцією ПЛТ).
3. Сума погашення відсотків як множення величини минулого періоду на відповідний відсоток. Використовується функція ДОЛЯГОДА. щоб прибрати наслідки високосний. Банки скрупульозно підходять до розрахунків, тому період вважається в днях, інакше можна було б зробити простіше - взяти річний відсоток, поділити на 12 місяців і помножити на суму.
4. Сума погашення основного боргу - береться як різниця щомісячного платежу і суми погашення відсотків.
5. Дострокове погашення і його дата ставляться довільно. Єдина умова - ставиться в той період, де дата або менше або збігається з датою дострокового погашення.
6. Сума боргу після платежу визначається як сума попереднього періоду за вирахуванням погашення основної частини і суми дострокового погашення.

І останній нюанс побудови графіка - на третьому рядку ми міняємо трохи формулу щомісячного платежу - ставимо умову, що якщо було дострокове погашення, то сума перераховується за функцією ПЛТ. а якщо немає, залишається як в попередньому рядку.

Тепер зробимо такий же графік для диференційованих платежів.

Міняємо дві формули:

1) Суму погашення основного боргу. Вона буде незмінною - сума боргу розділити на кількість періодів (місяців).

2) Щомісячний платіж визначаємо як суму двох частин - погашень основного боргу і відсотків.

Різниця двох форм по суті в тому, що ви більше платите в місяць за диференційованим платежу, але швидше розраховуєтесь і тому в підсумку платите менше відсотків.

Які ще можна витягти показники, які важливі нам, але не враховуються в доступних калькуляторах?

Для мене це була обґрунтованість взяття кредиту. Я тоді знімав квартиру і тому мені потрібно було розрахувати ціну кредиту. Ціна кредиту для мене дорівнювала сумі виплачених відсотків за мінусом орендних платежів за весь період кредиту. Якщо сума невелика або взагалі негативна, то кредит брати варто. Бонусом для мене було проживання в СВОЄМУ (!) Будинку, де я знав, що можу забити цвях в МОЮ стінку, та й взагалі психологічний вплив велике.

«Очі бояться, а руки роблять»

P.P.S. Для тих, хто хоче екстрено швидко вивчити Excel, я спеціально зробив практикум-самовчитель, можете подивитися його опис по ось цим посиланням.