Создание графика погашения кредита в Excel – пошаговое руководство

Создание графика амортизации кредита в Excel — ценный навык, который позволяет наглядно отслеживать и эффективно управлять выплатами по кредиту. График амортизации представляет собой таблицу с подробной разбивкой всех периодических платежей по амортизируемому кредиту (обычно ипотеке или автокредиту). Он показывает, как каждый платёж делится на погашение процентов и основного долга, а также отражает остаток задолженности после каждого платежа. Далее приведено пошаговое руководство по созданию такого графика в Excel.

Что такое график погашения кредита?

Создание графика погашения кредита в Excel

Создание графика погашения кредита с переменным количеством периодов

Создание графика погашения кредита с дополнительными платежами

Создание графика погашения кредита (с дополнительными платежами) с использованием шаблона Excel

Что такое график погашения кредита?

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

Для построения графика амортизации кредита в Excel действительно требуются встроенные функции ПЛТ, ОСПЛТ и ПРПЛТ. Давайте разберёмся, какую роль выполняет каждая из них:

• Функция ПЛТ: Эта функция рассчитывает ежепериодный платёж по кредиту с учётом постоянной процентной ставки и фиксированного размера выплат.
• Функция ПРПЛТ: рассчитывает долю процентов в платеже за заданный период.
• Функция ОСПЛТ: рассчитывает основную часть платежа за указанный период.

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

Создание графика погашения кредита в Excel

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

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

⭐️ Шаг 1: Настройка информации о кредите и таблицы амортизации

1. Введите соответствующую информацию о кредите, например годовую процентную ставку, срок кредита в годах, количество платежей в год и сумму кредита в ячейки, как показано на следующем снимке экрана:
   
2. Затем создайте в Excel таблицу амортизации с заголовками «Период», «Платеж», «Проценты», «Основной долг» и «Остаток долга» в ячейках A7:E7.
3. В столбце «Период» укажите номера периодов. В данном примере общее количество платежей — 24 месяца (2 года), поэтому введите числа от 1 до 24 в столбец «Период». См. снимок экрана:
   
4. После настройки таблицы с заголовками и номерами периодов вы можете приступить к вводу формул и значений в столбцы «Платеж», «Проценты», «Основной долг» и «Остаток» в соответствии с условиями вашего кредита.

⭐️ Шаг 2: Расчет общей суммы платежа с помощью функции ПЛТ

Синтаксис функции ПЛТ:

Введите приведённую ниже формулу в ячейку B7, затем протяните маркер заполнения вниз, чтобы скопировать её в остальные ячейки, — и вы увидите неизменную сумму платежа для всех периодов. См. снимок экрана:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Шаг 3: Расчет процентов с помощью функции ПРПЛТ

На этом этапе вы рассчитаете проценты для каждого платежного периода с помощью функции ПРПЛТ в Excel.

Введите в ячейку C7 приведённую ниже формулу, а затем протяните маркер заполнения вниз по столбцу, чтобы применить её и рассчитать сумму процентов для каждого периода.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Шаг 4: Расчет основного долга с помощью функции ОСПЛТ

После расчета процентов за каждый период следующим шагом при составлении графика амортизации становится определение основной части каждого платежа. Для этого используется функция ОСПЛТ, которая рассчитывает сумму основного долга в платеже за указанный период при условии постоянных выплат и фиксированной процентной ставки.

Синтаксис функции ПРПЛТ:

Синтаксис и параметры функции ОСПЛТ полностью совпадают с теми, что используются в ранее рассмотренной функции ПРПЛТ.

Введите в ячейку D7 приведённую ниже формулу, а затем протяните маркер заполнения вниз по столбцу, чтобы автоматически рассчитать основной долг для каждого периода. См. снимок экрана:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Шаг 5: Расчет остатка долга

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

1. В первую ячейку столбца «Остаток» — E7 — введите следующую формулу, которая означает, что остаток долга будет равен первоначальной сумме кредита за вычетом основной части первого платежа:

   =B4-D7

   
2. Для второго и всех последующих периодов рассчитайте остаток долга, вычитая основную часть текущего периода из остатка предыдущего. Используйте следующую формулу в ячейке E8:

   =E7-D8

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

⭐️ Шаг 6: Создание сводки по кредиту

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

● Для расчета общей суммы платежей:

=SUM(B7:B30)

● Для расчета общей суммы процентов:

=SUM(C7:C30)

⭐️ Результат:

Теперь простой, но полноценный график амортизации кредита успешно создан. См. снимок экрана:

Раскройте магию Excel с помощью KUTOOLS AI

• Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё по простым командам.
• Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
• Написание кода VBA: Пишите и внедряйте код VBA легко и без усилий.
• Интерпретация формул: С лёгкостью разбирайтесь в сложных формулах.
• Перевод текста: Ломайте языковые барьеры прямо в своих таблицах.

Расширьте возможности Excel с помощью инструментов на основе ИИ.Скачать сейчаси ощутите эффективность как никогда раньше!

Создание графика погашения кредита с переменным количеством периодов

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

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

⭐️ Шаг 1: Настройте информацию по кредиту и таблицу амортизации

1. Введите соответствующую информацию о кредите, например годовую процентную ставку, срок кредита в годах, количество платежей в год и сумму кредита в ячейки, как показано на следующем снимке экрана:
   
2. Затем создайте в Excel таблицу амортизации с заголовками «Период», «Платеж», «Проценты», «Основной долг» и «Остаток долга» в ячейках A7:E7.
3. В столбце «Период» укажите максимальное количество платежей, которое может понадобиться для любого кредита — например, введите числа от 1 до 360. Этого достаточно, чтобы охватить стандартный 30-летний кредит с ежемесячными выплатами.
   

⭐️ Шаг 2: Измените формулы платежа, процентов и основного долга с использованием функции ЕСЛИ

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

● Формула платежа:

Обычно для расчёта платежа применяется функция ПЛТ. Чтобы добавить условие ЕСЛИ, используйте следующий синтаксис формулы:

Таким образом, формула будет следующей:

=IF(A7<,=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Формула процентов:

Синтаксис формулы:

Таким образом, формула будет следующей:

=IF(A7<,=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Формула основного долга:

Синтаксис формулы:

Таким образом, формула будет следующей:

=IF(A7<,=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Шаг 3: Настройте формулу остатка задолженности

Обычно остаток задолженности рассчитывается путём вычитания основного долга из предыдущего остатка. С использованием функции ЕСЛИ формула будет выглядеть следующим образом:

● Первая ячейка остатка: (E7)

=B4-D7

● Вторая ячейка остатка: (E8)

=IF(A8<,=$B$2*$B$3, E7-D8, "") 

⭐️ Шаг 4: Создайте сводку по кредиту

После настройки графика погашения с изменёнными формулами следующим шагом станет создание сводки по кредиту.

● Для расчета общей суммы платежей:

=SUM(B7:B366)

● Для расчета общей суммы процентов:

=SUM(C7:C366)

⭐️ Результат:

Теперь у вас в Excel есть полный и динамичный график погашения кредита с подробной сводкой. При изменении срока кредита график автоматически обновится с учётом всех внесённых изменений. Смотрите демонстрацию ниже:

Создание графика погашения кредита с дополнительными платежами

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

⭐️ Шаг 1: Настройте информацию по кредиту и таблицу амортизации

1. Введите соответствующую информацию о кредите, например годовую процентную ставку, срок кредита в годах, количество платежей в год, сумму кредита и дополнительный платеж в ячейки, как показано на следующем снимке экрана:
   
2. Теперь рассчитайте плановый платеж.
   Помимо ячеек для ввода данных, для последующих вычислений потребуется ещё одна предопределённая ячейка — сумма планового платежа. Это регулярная сумма ежемесячного платежа по кредиту при условии отсутствия дополнительных выплат. Введите следующую формулу в ячейку B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Затем создайте таблицу амортизации в Excel:
   • Задайте указанные метки, такие как Период, Плановый платёж, Дополнительный платёж, Общий платёж, Проценты, Основной долг, Остаток по счёту в ячейках A8:G8;
   • В столбце «Период» укажите максимальное количество платежей, которое может потребоваться для любого кредита — например, числа от 0 до 360. Этого достаточно, чтобы охватить стандартный 30-летний кредит с ежемесячными выплатами.
   • Для периода 0 (строка 9 в нашем случае) укажите остаток по формуле =B4 — это первоначальная сумма кредита. Все остальные ячейки в этой строке оставьте пустыми.
   • 

⭐️ Шаг 2: Создайте формулы для графика погашения с учётом дополнительных платежей

Поочерёдно введите приведённые ниже формулы в соответствующие ячейки. Для повышения устойчивости к ошибкам каждая из этих формул, включая текущую, обёрнута в функцию ЕСЛИОШИБКА — это надёжно защищает от множества потенциальных ошибок, вызванных пустыми или некорректными значениями во входных ячейках.

● Расчёт планового платежа:

Введите следующую формулу в ячейку B10:

=IFERROR(IF($B$6<,=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Расчёт дополнительного платежа:

Введите следующую формулу в ячейку C10:

=IFERROR(IF($B$5<,G9-E10,$B$5, G9-E10), "")

● Расчёт общего платежа:

Введите следующую формулу в ячейку D10:

=IFERROR(B10+C10, "")

● Расчёт основного долга:

Введите следующую формулу в ячейку E10:

=IFERROR(IF(B10>,0, MIN(B10-F10, G9), 0), "")

● Расчёт процентов:

Введите следующую формулу в ячейку F10:

=IFERROR(IF(B10>,0, $B$1/$B$3*G9, 0), "")

● Расчёт остатка задолженности

Введите следующую формулу в ячейку G10:

=IFERROR(IF(G9 >,0, G9-E10-C10, 0), "")

После завершения ввода всех формул выделите диапазон ячеек B10:G10 и скопируйте формулы на весь диапазон платёжных периодов с помощью маркера заполнения. В неиспользуемых периодах ячейки будут отображать нули. См. скриншот:

⭐️ Шаг 3: Создайте сводку по кредиту

● Получите плановое количество платежей:

=B2:B3

● Получите фактическое количество платежей:

=COUNTIF(D10:D369,">"&,0)

● Получите общую сумму дополнительных платежей:

=SUM(C10:C369)

● Получите общую сумму процентов:

=SUM(F10:F369)

⭐️ Результат:

Следуя этим шагам, вы создадите в Excel динамический график погашения кредита с учётом дополнительных платежей.

Создание графика погашения кредита с использованием шаблона Excel

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

1. Нажмите Файл > Создать, введите в поле поиска график амортизации и нажмите клавишу Enter. Затем выберите наиболее подходящий для ваших задач шаблон — просто щёлкните по нему. Например, здесь я выбираю шаблон «Простой калькулятор кредита». См. снимок экрана:
   
2. После выбора шаблона нажмите кнопку Создать, чтобы открыть его как файл новой рабочей книги.
3. Затем введите свои кредитные данные — шаблон автоматически произведёт расчёты и заполнит график платежей на основе указанных значений.
4. Лучшие инструменты для повышения продуктивности в офисе