Описать задачу
Шаблоны Excel › Личные финансы › График платежей по кредиту

График платежей по кредиту в Excel — описание и предпросмотр, скачать — по подписке

Аннуитетный график: платёж, проценты и основной долг по месяцам, остаток долга и переплата. Формулы, выпадающие списки и параметры уже настроены; файл проверен пересчётом.

Сделать по описанию — в одном окне

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

проверено пересчётомформул: 5
Скачать готовый файл

.xlsx для Excel 2016–365, LibreOffice, Р7-Офис; открывается в Яндекс и Google Таблицах.

Скачать .xlsx — по подписке
Настроить словами

Главное в книге

Что внутри

Лист «График»

№Дата платежаПлатёжПроцентыОсновной долгОстаток долга
1формулаформулаформулаформулаформула
2формулаформулаформулаформулаформула
3формулаформулаформулаформулаформула
4формулаформулаформулаформулаформула

Лист «Итоги»

ПоказательСумма
Сумма кредита=Сумма_кредита
Ежемесячный платёж=-ПЛТ(Ставка_кредита/1200;Срок_кредита;Сумма_кредита)
Всего выплатите=СУММ(График_кредита[Платёж])
Переплата (проценты)=СУММ(График_кредита[Проценты])

Лист «Параметры»

Как пользоваться

  1. На листе «Параметры» укажите сумму кредита, ставку, срок в месяцах и дату первого платежа.
  2. Лист «График» сам покажет каждый месяц: платёж, сколько уходит на проценты и на долг, остаток долга.
  3. Строки после последнего месяца срока остаются пустыми; итоги — в нижней строке и на листе «Итоги».
  4. Банк округляет платёж до копеек, поэтому суммы у банка могут отличаться на несколько рублей.

Похожие шаблоны

Учёт личных расходовУчёт долговДосрочное погашение ипотекиКалькулятор вклада с капитализациейНакопления на цельПерепродажа вещей на Авито

График платежей по кредиту в Excel: аннуитет по месяцам

Аннуитетный платёж в Excel считает функция ПЛТ, долю процентов и долга в каждом месяце — ПРПЛТ и ОСПЛТ. В шаблоне по умолчанию кредит 1 500 000 ₽ под 18 % на 60 месяцев: платёж 38 090,14 ₽, в первом месяце 22 500 ₽ уходят на проценты и только 15 590 ₽ — в долг, переплата за весь срок около 785 408 ₽. Расчёт ориентировочный: график банка может отличаться на рубли из-за округления и подсчёта процентов по дням.

По шагам

  1. Откройте лист «Параметры» и впишите сумму кредита, ставку в процентах годовых, срок в месяцах (до 360) и дату первого платежа.
  2. Лист «График» заполнится сам: дата каждого платежа (ДАТАМЕС сдвигает её на месяц), платёж, проценты, основной долг и остаток.
  3. Строки после последнего месяца остаются пустыми — поэтому один и тот же файл годится и для кредита на год, и для ипотеки на 30 лет.
  4. Итоговая строка под графиком и лист «Итоги» показывают, сколько всего заплатите и сколько из этого — проценты.
  5. Сверьте первый платёж с договором. Если он отличается больше чем на несколько рублей, проверьте, не включены ли в сумму кредита страховка или комиссия.

Формулы, которые стоят в каждой строке

Если ставка в B2 (% годовых), срок в B3 (месяцев), сумма в B1, а номер месяца в A6, то платёж — =ПЛТ(B2/12/100;B3;-B1), проценты месяца — =ПРПЛТ(B2/12/100;A6;B3;-B1), погашение долга — =ОСПЛТ(B2/12/100;A6;B3;-B1). Минус перед суммой нужен, чтобы результат был положительным: в финансовых функциях Excel выданные и полученные деньги имеют разные знаки.

Дифференцированный платёж

Некоторые банки и большинство договоров займа между компаниями используют дифференцированную схему: долг гасится равными частями, а проценты начисляются на остаток. Первый платёж больше аннуитетного, последний — меньше, переплата ниже. Чтобы перестроить шаблон, замените «Основной долг» на сумму, делённую на срок, а «Проценты» — на остаток прошлого месяца, умноженный на месячную ставку:

=$B$1/$B$3+F5*$B$2/12/100

Сравнить два предложения банков

Скачайте файл дважды или скопируйте лист «Параметры» с графиком: одно предложение на 17 % на 5 лет, другое на 19 % на 4 года. Платёж во втором выше, но переплата может оказаться ниже. Смотрите на строку «Переплата (проценты)» и на полную стоимость кредита в договоре — ПСК по ст. 6 закона 353-ФЗ включает страховки и комиссии, которых в графике нет.

Первые месяцы кредита 1,5 млн ₽ под 18 %

МесяцПлатёжПроцентыОсновной долг
138 090,14 ₽22 500,00 ₽15 590,14 ₽
Последний (60-й)38 090,14 ₽≈ 563 ₽≈ 37 527 ₽
Всего за 60 месяцев≈ 2 285 408 ₽≈ 785 408 ₽1 500 000 ₽

Частые ошибки

Вопросы и ответы

Как рассчитать ежемесячный платёж по кредиту в Excel?

Функцией ПЛТ: =ПЛТ(18/12/100;60;-1500000) даёт 38 090,14 ₽. Аргументы — месячная ставка, число платежей и сумма кредита со знаком минус.

Как узнать, сколько процентов в каждом платеже?

Функцией ПРПЛТ с номером месяца; часть в погашение долга даёт ОСПЛТ. Их сумма в каждом месяце равна платежу.

Почему в начале кредита платим в основном проценты?

Проценты начисляются на остаток долга, а он в начале максимальный. При аннуитете платёж одинаковый, поэтому на долг сначала остаётся меньшая часть.

Чем аннуитетный платёж отличается от дифференцированного?

Аннуитет — равные платежи весь срок. Дифференцированный — равные части долга плюс проценты на остаток: платёж уменьшается каждый месяц, переплата меньше.

Совпадёт ли график с банковским?

Обычно в пределах нескольких рублей. Банки считают проценты по фактическим дням и переносят даты с выходных, поэтому точный график — только из договора.

Можно ли добавить досрочные платежи?

Для регулярной доплаты есть отдельный шаблон «Досрочное погашение ипотеки». Нестандартный график опишите в Формулнике словами — он соберёт книгу под ваш случай.