Розрахуємо в MS EXCEL скільки часу буде потрібно для погашення кредиту в разі рівних щомісячних платежів (за ануїтетною схемою). Процентна ставка і величина платежу - відомі, нарахування відсотків за користування кредитом - щомісячне. Також в статті розберемо випадок накопичення вкладу.
ануїтетна схема передбачає погашення кредиту періодичними рівновеликими платежами (як правило, щомісячними), які включають як виплату основного боргу, так і процентний платіж за користування кредитом. Такий рівновеликий платіж називається ануїтет.
У ануїтетною схемою погашення передбачається незмінність процентної ставки по кредиту протягом усього періоду виплат. у статті Ануїтет. Розрахунок періодичного платежу в MS EXCEL. Погашення позики (кредиту, позики) показано як розрахувати величину регулярної суми для погашення кредиту або позики.
Розрахунок кількості періодів, необхідних для погашення кредиту зробимо спочатку за допомогою фінансової функції MS EXCEL КПЕР (), потім приведемо розрахунок за допомогою еквівалентної формули (див. Файл прикладу).
Функція КПЕР (ставка; плт; пс; [БС]; [тип]) дозволяє обчислити кількість періодів, через яке поточна сума вкладу (Пс) стане рівною заданої сумі (БС) при відомій процентній ставці за період (ставка) і відомої величиною поповнення вкладу (плт). Бс ( майбутня вартість ) Може бути = 0 або опущена. Тип - це число 0 або 1, що означає, коли повинна проводитися виплата (і відповідно нарахування відсотків): 0 - в кінці періоду, 1 - на початку.
Також функцію КПЕР () можна використовувати для визначення кількості періодів, необхідних для погашення боргу за позикою.
. Огляд всіх функцій ануїтету знайдете тут .
Еквівалентна формула для розрахунку кількості періодів:
Якщо ставка дорівнює 0, то:
Кпер = (Пс + Бс) / ПЛТ
Задача1
Скільки часу буде потрібно для погашення кредиту 1 млн. Рублів в разі рівних щомісячних внесків. Нарахування відсотків за користування кредитом - щомісячне, річна процентна ставка = 10%. Щомісячний платіж 50 000р.
рішення 1
Так як погашення кредиту проводиться щомісяця (12 разів на рік), то ставка за період складе 10% / 12.
Формула = КПЕР (10% / 12; -50000; 1000000; 0; 0) поверне кількість періодів, після яких кредит буде повернений в повному розмірі.
Знак мінус перед 2-м аргументом функції (величина щомісячного платежу) показує, що різноспрямовані грошові потоки повинні мати різні знаки (+1000000 - це гроші, які банк дав нам, -50000 - це гроші, які ми повертаємо банку). Звичайно, можна і навпаки: гроші, які банк дав нам враховувати зі знаком мінус, а ті гроші, які ми віддаємо банку враховувати зі знаком +.
Формула поверне 21,97, тобто буде потрібно 22 місяці, щоб повністю повернути 1 млн. руб., повертаючи щомісяця по 50 тис. руб. (Останній платіж буде трохи менше, про те, як його розрахувати - читайте нижче).
Розрахунок останнього платежу
При розрахунку кількості періодів погашення позики може вийти нецілим кількість періодів. В цьому випадку, останній платіж буде трохи менше, ніж попередні. Знайдемо величину цього платежу.
Врахуємо, що в останньому періоді нам буде необхідно погасити заборгованість, що залишилася і заплатити% банку (% від суми заборгованості, що залишилася).
Знайдемо спочатку кількість повних періодів = ЦІЛЕ (КПЕР (10% / 12; -50000; 1000000; 0; 0)) і помістимо результат в осередок G21.
Щоб обчислити скільки було погашено основної суми боргу за все цілі періоди можна використовувати формулу = СУММПРОИЗВ (ОСПЛТ (10% / 12; СТРОКА (ДВССИЛ ( "1:" & G21)); КПЕР (10% / 12; -50000; 1000000; 0 ; 0); 1000000; 0; 0))
(Альтернатива - використовуйте функцію ОБЩДОХОД ())
. Суму платежу, що йде на оплату основної суми боргу в один певний період, можна обчислити за допомогою функції ОСПЛТ (). Складаючи результат ОСПЛТ () для всіх цілих періодів за допомогою функції СУММПРОИЗВ () отримаємо скільки було погашено основної суми боргу.
Далі віднімемо з суми кредиту виплачену суму за всі цілі періоди (з урахуванням знаків).
Потім обчислимо відсотки за останній період = СУММПРОИЗВ (...) * 10% / 12
Складемо залишок основної суми боргу і відсотки за останній (неповний) період (див. Файл прикладу). В результаті отримаємо -48487,18р. (Це трохи менше регулярного платежу -50000р.)
Якщо функція КПЕР () повертає помилку
У деяких випадках функція КПЕР () повертає значення помилки # ЧИСЛО! Розібратися, чому це відбувається, можна, згадавши альтернативну формулу (див. Вище). Зрозуміло, що логарифм числа може бути обчислений лише для позитивного числа, а це означає, що величина платежу ПЛТ повинна бути більше величини щомісячних відсотків нараховуються на залишок тіла кредиту, тобто більше ПС * СТАВКА (це справедливо тільки при БС = 0 і ТИП = 0). Воно і зрозуміло, нам потрібно щомісяця не тільки оплачувати відсотки, а й повертати основну суму боргу.
Кількість періодів, через яке буде погашено 80% кредиту
Скільки часу буде потрібно, щоб погасити кредит в повному обсязі, а наприклад, на 80%? Записавши формулу = КПЕР (10% / 12; -50000; 1000000; -1000000 * (1-80%); 0) отримаємо, що для цього буде потрібно 17,88 періодів (місяців).
Величину БС = -1000000 * (1-80%) ми знайшли користуючись тотожністю для ануїтету (справедливо, якщо Тип = 0):
СУМ (ОСПЛТ (за всі періоди)) + ПС + БС = 0
З умов завдання ПС = 1000000 (початкова сума кредиту), виплачена сума кредиту, тобто СУМ (ОСПЛТ (за всі періоди)) дорівнює -1000000 * 80%. Вирішуючи рівняння, отримаємо, що БС = -1000000 * (1-80%). БС в даному випадку - це непогашена сума кредиту (звичайно, щоб знайти, що БС = 20% від суми кредиту, не вимагає використання тотожності. Але важливий знак БС).
Задача2
Розрахувати, через скільки часу вклад розміром 200 000 руб. досягне 1 000 000 руб., якщо річна процентна ставка за вкладом 10% річних, нарахування відсотків здійснюється щоквартально, також щоквартально внесок поповнюється на 10 000 руб.
Рішення2
Так як внески і нарахування відсотків відбувається щоквартально (4 рази на рік), то ставка за період складе 10% / 4. Формула = КПЕР (10% / 4; -10000; -200000; 1000000; 0) поверне кількість періодів, після яких вклад досягне 1 млн. Руб., Тобто 34,31 квартал (див. Файл прикладу, лист Задача2).