В даному уроці будемо створювати кредитний калькулятор ануїтету (оплата кредиту рівними платежами) в Excelе для розрахунку по таких параметрів як:
- щомісячний платіж;
- суму оплати за користуванням кредиту;
- ефективну ставку по кредиту.
Важливо розуміти, що дані, які розрахуємо, будуть близькі до банківських розрахунків, але все ж можуть незначно відрізнятися.
Крок 1. Створюємо таблицю значень
У новому документі Excel створюємо таблицю з даними, які будемо використовувати для розрахунку:
- Сума кредиту;
- Відсоткова ставка (річна);
- Щомісячна комісія;
- Одноразова комісія;
- Термін кредиту в місяцях.
Осередки для введення даних позначимо жовтим.
Дані які будемо розраховувати:
- Щомісячний платіж;
- Сума переплати по кредиту;
- Відсоток переплати;
- Еффектівнаяставка.
Крок 2. Розраховуємо щомісячний платіж
- Ставка - Вибираємо осередок процентної ставки і ділимо її на 12. Це пов'язано з тим, що відсоткову ставку вказуємо річну, а платіж ми розраховуємо щомісячний
- Кпер - термін кредитування;
- ПС - сума кредиту, обов'язково ставимо знак «-» перед значенням. Так як в параметрах є Одноразова комісія Сума боргу = Сума кредиту + Сума кредиту * Одноразова комісія. Всі кредитні установи Одноразова комісія включають в основний борг і налічують на них річну відсоткову ставку.
Після використання формули розрахунку щомісячного платежу по ануїтету «ПЛТ» з урахуванням «Одноразова комісія», залишається врахувати ще щомісячну комісію. Таким чином, в рядку формули до функції додаємо розрахунок суми щомісячної комісії.
Крок 3. Розрахунок оплати за кредит.
Розрахунок суми оплати за кредитом виробляє шляхом множення щомісячного платежу по кредиту на термін кредиту і віднімаємо основну суму кредиту.
Відсоток переплати по кредиту розраховується як сума оплати поділена на суму кредиту і помножена на 100.
Крок 4. Розрахунок ефективної ставки по кредиту
Ефективна ставка по кредиту включає в себе всі відсотки і всі платежі по кредиту:
- Процентна ставка;
- Одноразова комісія;
- Щомісячна комісія.
- Кпер - термін кредитування;
- Плт - розрахований щомісячний платіж, який включає в себе всі відсотки і комісії;
- Пс - сума кредиту, обов'язково зі знаком «-«.
Після використання функції «СТАВКА» необхідно в рядку формули помножити цю функцію на 12, щоб обчислити річну ефективну ставку.
За допомогою даного калькулятора, легко, просто і швидко розрахувати щомісячний платіж по будь-яким кредитом, а також вирахувати ефективну ставку.