Диференційовані платежі по кредиту в ms excel - сумісно з microsoft excel 2018, excel 2018
Складемо в MS EXCEL графік погашення кредиту диференційованими платежами.
При розрахунку графіка погашення кредиту диференційованими платежами сума основного боргу ділиться на рівні частини пропорційно терміну кредитування. Регулярно, протягом усього терміну погашення кредиту, позичальник виплачує банку ці частини основного боргу плюс нараховані на його залишок відсотки. Якщо кредитним договором період погашення встановлений рівним місяцю, то з місяця в місяць сума основного боргу пропорційно зменшується. Тому при диференційованих платежах основні витрати позичальник несе на початку кредитування, розміри щомісячних платежів в цей період найбільші. Але поступово, зі зменшенням залишку позикової заборгованості, зменшується і сума нарахованих відсотків по кредиту. Виплати за кредитом значно скорочуються і стають не такими обтяжливими для позичальника.
Примітка. При розрахунку кредиту диференційованими платежами сума переплати за відсотками буде нижче, ніж при ануїтетних платежах. Тож не дивно, що сьогодні практично всі українські банки застосовують в розрахунках аннуїтетную схему погашення кредиту. Порівняння двох графіків погашення кредиту наведено в статті Порівняння графіків погашення кредиту диференційованими аннуїтетнимі платежами в MS EXCEL.
Графік погашення кредиту диференційованими платежами
Завдання. Сума кредиту = 150т.р. Термін кредиту = 2 роки, Ставка по кредиту = 12%. Погашення кредиту щомісячне, в кінці кожного періоду (місяця).
Рішення. Спочатку обчислимо частина (частку) основної суми кредиту, яку позичальник виплачує за період: = 150т.р. / 2/12, тобто 6250р. (Суму кредиту ми розділили на загальну кількість періодів виплат = 2 роки * 12 (міс. На рік)).
Кожен період позичальник виплачує банку цю частину основного боргу плюс нараховані на його залишок відсотки. Розрахунок нарахованих відсотків на залишок боргу наведено в таблиці нижче - це і є графік платежів.
Для розрахунку нарахованих відсотків може бути використана функція ПРОЦПЛАТ (ставка; період; кпер; пс), де Ставка - відсоткова ставка за період; Період - номер періоду, для якого потрібно знайти величину нарахованих відсотків; Кпер - загальне число періодів нарахувань; ПС - приведена вартість на поточний момент (для кредиту ПС - це сума кредиту, для вкладу ПС - початкова сума вкладу).
Примітка. Не дивлячись на те, що назви аргументів збігаються з назвами аргументів функцій ануїтету - ПРОЦПЛАТ () не входить в групу цих функцій (не може бути використана для розрахунку параметрів ануїтету).
Примітка. Англійський варіант функції - ISPMT (rate, per, nper, pv)
Функція ПРОЦПЛАТ () передбачає нарахування відсотків на початку кожного періоду (хоча в довідці MS EXCEL це не сказано). Але, функцію можна використовувати для розрахунку відсотків, що нараховуються і в кінці періоду для це потрібно записати її у вигляді ПРОЦПЛАТ (ставка; період-1; кпер; пс), тобто «Зрушити» обчислення на 1 період раніше (див. Файл прикладу).
Функція ПРОЦПЛАТ () нараховані відсотки за користування кредитом вказує з протилежним знаком, щоб відрізнити грошові потоки (якщо видача кредиту - позитивний грошовий потік ( «в кишеню» позичальника), то регулярні виплати - негативний потік "з кишені").
Розрахунок сумарних відсотків, сплачених з дати видачі кредиту
Виведемо формулу для знаходження суми відсотків, нарахованих за певну кількість періодів з дати початку дії кредитного договору. Запишемо суми відсотків нарахованих в перших періодів (нарахування і виплата в кінці періоду):
ПС * ставка
(ПС-ПС / кпер) * ставка
(ПС-2 * ПС / кпер) * ставка
(ПС-3 * ПС / кпер) * ставка
...
Підсумуємо отримані вирази і, використовуючи формулу суми арифметичної прогресії, отримаємо результат.
= ПС * Ставка * період * (1 - (період-1) / 2 / кпер)
Де, Ставка - це процентна ставка за період (= річна ставка / число виплат на рік), період - період, до якого потрібно знайти суму відсотків.
Наприклад, сума відсотків, виплачених за перші півроку користування кредитом (див. Умови задачі вище) = 150000 * (12% / 12) * 6 * (1- (6-1) / 2 / (2 * 12)) = 8062, 50р.
За весь термін буде виплачено = ПС * Ставка * (кпер + 1) / 2 = 18750р.
Через функцію ПРОЦПЛАТ () формула буде складніше: = СУММПРОИЗВ (ПРОЦПЛАТ (ставка; СТРОКА (ДВССИЛ ( "1:" кпер)) - 1; кпер; -ПС))
пов'язані статті
Прочитайте інші статті, які вирішують схожі завдання в MS Excel. Це дозволить Вам вирішувати широкий клас подібних задач.