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

Что такое график амортизации?

Создайте график амортизации в Excel

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

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

Создайте график амортизации (с дополнительными платежами) с помощью шаблона Excel.

Что такое график амортизации?

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

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

• Функция ПЛТ: Эта функция используется для расчета общего платежа за период по кредиту на основе постоянных платежей и постоянной процентной ставки.
• Функция ИПМТ: эта функция рассчитывает процентную часть платежа за определенный период.
• Функция ППМТ: Эта функция используется для расчета основной части платежа за определенный период.

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

Создайте график амортизации в Excel

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

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

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

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

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

Синтаксис PMT:

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

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

⭐️ Шаг 3. Рассчитайте проценты с помощью функции IPMT.

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

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

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

⭐️ Шаг 4. Рассчитайте основную сумму с помощью функции PPMT.

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

Синтаксис IPMT:

Синтаксис и параметры формулы PPMT идентичны тем, которые использовались в ранее обсуждавшейся формуле IPMT.

Введите следующую формулу в ячейку 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)

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

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

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

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

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

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

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

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

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

● Формула оплаты:

Обычно для расчета платежа используется функция ПЛТ. Для включения оператора IF используется следующая синтаксическая формула:

Итак, формулы таковы:

=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. Настройте формулу остатка баланса.

Для оставшегося баланса вы обычно можете вычесть основную сумму из предыдущего баланса. С помощью оператора IF измените его следующим образом:

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

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

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

=B2:B3

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

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

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

=SUM(C10:C369)

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

=SUM(F10:F369)

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

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

Создайте график амортизации с помощью шаблона Excel

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

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