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

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

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

=B2:B3

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

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

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

=SUM(C10:C369)

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

=SUM(F10:F369)

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

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

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

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

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