Закрыть

Войти на сайт

email: пароль

Зарегистрироваться?

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

напечатать

5 мая 2015, 16:27

В настоящее время в интернете можно обнаружить большое количество калькуляторов для расчета платежей по кредиту. Однако они имеют один существенный недостаток: непрозрачность с точки зрения используемых формул. Статья расскажет, как произвести самостоятельные расчеты, используя общедоступные функции в 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. Для расчетов целесообразно использовать заявленную ставку того банка, в котором планируется взять кредит, либо ее среднерыночное значение.

Читайте также

Отзывы и комментарии