Как планировать платежи по кредиту с помощью Excel

Елена Исаева, независимый консультант
Расчетная модель в Excel – кредитный калькулятор. Она рассчитает денежные потоки для трех графиков получения и погашения кредита и поможет выбрать подходящий для компании.

У каждого графика получения и погашения кредита свой одноименный лист в файле. Первый график – индивидуальный, кредитная линия из нескольких траншей. Второй – аннуитетный. Банк перечисляет заемные средства на счет компании единовременно. Компания возвращает основной долг и оплачивает проценты равными суммами в течение кредитного периода. Доля основного долга в платежах увеличивается от месяца к месяцу. Третий график – дифференцированный. Компания получает весь кредит сразу, долг гасит равными частями плюс проценты. Платежи в течение срока кредита уменьшаются.

Какие исходные данные потребуются для кредитного калькулятора в Excel
В кредитном калькуляторе три листа: «Индивидуальный график», «Аннуитетный график» и «Дифференцированный график». На каждом в верхней части есть таблица для исходных данных, см. рисунок 1. Укажите здесь параметры кредита: сумму в денежных единицах, срок кредита в месяцах, процентную ставку в процентах в год, комиссию за выдачу кредита в процентах, а также периодичность выплаты комиссии – ежемесячно или единовременно.

Рисунок 1. Исходные данные для планирования платежей по кредиту

Далее введите кредитный период: в ячейку С9 введите дату поступления денежных средств на счет компании, D9 – когда начнут начислять проценты. Остальные даты укажите одним из двух способов: через верхнее меню программы или контекстное меню.

Первый способ – выделите диапазон D9:AB9. Затем во вкладке «Главная» раздела «Редактирование» выберите операцию «Заполнить». В выпадающем списке кликните по команде «Прогрессия», см. рисунок 2.


Рисунок 2. Заполнение строки с датами кредитного периода с помощью команды «Прогрессия»

В окне выберите параметры прогрессии, см. рисунок 3.

Рисунок 3. Настройка параметров прогрессии

Если нажмете кнопку «ОК», строка автоматически заполнится датами.

Второй способ – выделите ячейку D9 мышкой, поставьте курсор на границу этой ячейки. Как увидите крестик, левой кнопкой мышки протяните до ячейки AB9. В конце диапазона появится раскрывающийся список, из которого выберите команду «Заполнить по месяцам», см. рисунок 4.


Рисунок 4. Заполнение строки с датами кредитного периода с помощью команды «Заполнить по месяцам»

На листе «Индивидуальный график» к вводным данным относятся графики получения и погашения кредита. Эти сведения укажите вручную в строках 11 и 12 соответственно. В примере ячейки, в которые вводили данные, выделены желтым цветом.

Расчетчик. Воспользуйтесь расчетчиком, чтобы определить итоговую сумму кредита.

Как рассчитать выплаты по кредитам в Excel


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

Индивидуальный график. На листе «Индивидуальный график», чтобы рассчитать проценты по кредиту, используется формула вида:
«ПРПЛТ(Процентнаяставка*ДОЛЯГОДА(D$9;E$9;1);1;1;-(-E$10))», где: ПРПЛТ – функция, которая вычисляет проценты к уплате; Процентнаяставка (специально заданное имя для ячейки С4) – ставка по кредиту за год. Чтобы рассчитать проценты с учетом того, сколько дней в том или ином месяце, ставка умножается на количество дней, рассчитанное с помощью функции ДОЛЯГОДА;
1 – количество периодов, за которые рассчитываются проценты по кредиту (в примере месяц);
1 – общее количество периодов, которое используется в расчете процентов (тоже месяц);
E$10 – ссылка на ячейку, в которой указана сумма задолженности по кредиту.
Аргументы функции ДОЛЯГОДА:

D$9 – ссылка на ячейку, где указано начало расчетного периода;
E$9 – ссылка на ячейку, в которой указан конец расчетного периода;
1 – параметр (базис) функции ДОЛЯГОДА, который определяет способ вычисления количества дней. Единица обеспечивает расчет с учетом фактического числа дней в месяце и году. Так можно рассчитывать проценты, не проверяя, сколько их в каждом периоде (28, 29, 30, 31 – в рассматриваемом месяце, 365 или 366 – в году).
Аннуитетный график. Суммы погашения основного долга рассчитываются по формуле вида:
«ОСПЛТ(Процентнаяставка/12;E$8;Сроккредитамес.;Суммакредита)», где:
ОСПЛТ – функция, которая определяет размер платежа для погашения основного долга;
Процентнаяставка – годовая процентная ставка, указанная в ячейке С4. Если проценты начисляются ежемесячно, разделите ее на 12, а если ежеквартально, то на 4; E$8 – ссылка на ячейку с номером периода, за который производится расчет; Сроккредитамес. – срок начисления процентов, который указан в исходных данных (ячейка С3); Суммакредита – сумма кредита, указанная в исходных данных в ячейке С2.
Чтобы вычислить проценты, применяется формула вида:

«ПРПЛТ(Процентнаяставка/12;E$8;Сроккредитамес.;Суммакредита)».

Ее аргументы те же, что и функции ОСПЛТ выше.

Дифференцированный график. Суммы погашения определяются как частное общей величины кредита и его срока, которые указаны в исходных данных.
Чтобы рассчитать начисленные проценты, применяется формула вида:
«ПРПЛТ(Процентнаяставка/12;1;Сроккредита_мес.;E$10)», где:

ПРПЛТ – функция, которая вычисляет величину процентов по кредиту;
Процентнаяставка – годовая процентная ставка. Если проценты начисляются ежемесячно, разделите ее на 12, а если ежеквартально, то на 4; 1 – количество периодов, за которые рассчитываются проценты по кредиту (в примере месяц); Сроккредита_мес. – срок начисления процентов, который указан в исходных данных (ячейка С3);
E$10 – сумма задолженности по кредиту.

Шаблон документа. Скачайте план погашения долгосрочных кредитов.

Рубрики: Метки: ,

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.