Расчет платежей по кредиту в excel

5 мая 2015

Содержание:

• Необходимые данные для расчета графика платежей по кредиту в Excel 
• Формула расчета ежемесячного платежа по кредиту в Excel
• Пример расчета графика платежей по кредиту
• Расчет графика платежей по кредиту в Excel. Советы

В настоящее время в интернете можно обнаружить большое количество калькуляторов для расчета платежей по кредиту. Однако они имеют один существенный недостаток: непрозрачность с точки зрения используемых формул. Статья расскажет, как произвести самостоятельные расчеты, используя общедоступные функции в MS Excel. 

Необходимые данные для расчета графика платежей по кредиту в Excel 

Основные вопросы, связанные с расчетом кредита, заключаются, как правило, в следующем:

• какая величина кредита может быть получена, если известен примерный размер платежа;
• каким будет платеж, учитывая предварительно известную сумму займа.

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

Величина процентной ставки зависит от многих параметров: от кредитной политики конкретного банка, срока займа, вида программы кредитования, обеспечения и т.д.

Срок кредита, как правило, может выбираться заемщиком. Обычно он является кратным 12 месяцам и не превышает 7 лет (по ипотеке – до 30 лет).

Формула расчета ежемесячного платежа по кредиту в Excel

Если величина платежа по аннуитетной схеме известна и требуется определить возможный размер кредита, используйте формулу ПС. Ее аргументы:

• ставка (в годовых процентах, разделенная на 12);
• период кредита (в месяцах);
• сумма предполагаемого платежа.

Для расчета аннуитетных платежей по кредиту в Excel используются следующие функции:

• ПЛТ – определяет сумму платежа с учетом части основного долга и процентов. Аргументы: ставка (в годовых процентах, разделенная на 12); период кредита (в месяцах); размер займа.
• ПРПЛТ – рассчитывает величину процентов в составе платежа. Аргументы: ставка (в годовых, разделенная на 12); номер периода выплат; время кредита (в месяцах); сумма займа.
• ОСПЛТ – определяет сумму основного долга в структуре платежа. Аргументы: ставка (в годовых, разделенная на 12); номер периода выплат; время кредита (в месяцах); сумма займа.

Пример расчета графика платежей по кредиту

Данные для проведения вычислений:

• ставка 20% годовых;
• срок кредита 12 месяцев;
• сумма платежа 5 тыс. р. в месяц (для расчета размера кредита);
• сумма займа 100 тыс. р. (для расчета размера платежа).

В данном случае функция ПС представлена следующим образом: ПС(20%/12;12;5000). Результатом вычислений является максимально возможная сумма кредита 53 976 р.

Функции, используемые при расчете платежа, будут представлены таким образом:

• ПЛТ (20%/12;12;100000);
• ПРПЛТ (20%/12;{числа от 1 до 12};12;100000);
• ОСПЛТ (20%/12;{числа от 1 до 12};12;100000).

Итогом расчетов будут значения:

• сумма регулярного платежа 9 263 р.;
• величина процентов в составе аннуитета от 1 667 р. до 152 р.;
• размер погашаемого долга в структуре платежа от 7 597 р. до 9 112 р.

В случае расчета дифференцированного платежа сумма погашаемого основного долга остается одинаковой в течение всего периода. Ее размер рассчитывается как отношение суммы кредита к сроку кредитования в месяцах. Вычисление размера процентов в составе платежа происходит следующим образом:

• Размер текущей задолженности * процентная ставка (%) / 365 (366) дней в году * фактическое количество дней в месяце.

Для простоты расчетов (без вычисления количества дней в каждом из периодов) используется следующая формула:

• Размер текущей задолженности * процентная ставка / 12 месяцев в году.

В примере ниже использован именно такой подход.

Скачать таблицу расчета платежей по кредиту в Excel с приведенными примерами можно здесь.

Расчет графика платежей по кредиту в Excel. Советы

1. Самостоятельный расчет ежемесячного платежа по кредиту в Excel носит информативный характер, и может незначительно отличаться от данных банка. Это связано с несколькими причинами: различный учет количества дней в периоде, различающийся подход при округлении значений и т.д. 
2. При выборе между аннуитетным и дифференцированным платежом необходимо обратить внимание, что сумма переплаты по аннуитету всегда будет выше. Так, согласно приведенным примерам, при аннуитетных платежах общая сумма переплаты составляет 11 161 р., при дифференцированных – 10 833 р.
3. Для расчетов целесообразно использовать заявленную ставку того банка, в котором планируется взять кредит, либо ее среднерыночное значение.