Как создать свой собственный калькулятор амортизации кредита

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

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

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

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

Давайте рассмотрим пример, иллюстрирующий процесс.

Предположим, вы только что купили автомобиль с кредитом в 20 000 долларов, который должен быть полностью погашен через 4 года. Годовая процентная ставка по кредиту составляет 3%. Поскольку кредит амортизируется, ежемесячные платежи будут одинаковой суммы.

Первый шаг - рассчитать сумму ежемесячного платежа. Это можно сделать, используя функцию PMT (которая означает «оплата») в Excel.

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

Столбец А будет содержать следующие метки для наших данных:

A1 = исходный кредитный договор (сумма кредита)

A2 = срок кредита в годах

A3 = годовая процентная ставка

A4 = количество платежей в год

A5 = сумма платежа (что мы вычисляем)

Столбец B будет содержат соответствующие значения для каждой метки в столбце A:

B1 = $ 20000

B2 = 4

B3 = 3%

B4 = 12

B5 = значение, которое мы ищем

Функция PMT Excel может быть найдена в меню финансовых функций. Сначала выберите ячейку B5, затем щелкните меню финансовых функций на вкладке «Формулы» и выберите функцию PMT из списка. Появится окно, в которое вы должны ввести данные из электронной таблицы так же, как на этом изображении:

Здесь есть несколько важных вещей. Во-первых, нам нужно сделать две важные корректировки, потому что мы рассчитываем ежемесячные платежи. Нам нужно разделить наш годовой курс на 12, чтобы получить ежемесячную процентную ставку (курс), и нам нужно умножить 4 на 12, чтобы получить общее количество месяцев в нашем периоде погашения (Nper).

Во-вторых, текущее значение (PV) вводится как отрицательное значение, потому что эта сумма причитается кредитору.

После того, как ваши поля заполнены, нажмите «ОК».

Теперь ваша таблица должна выглядеть так:

Теперь мы начнем сбор таблицы амортизации.

Пропустите строку под таблицей, сделанной в первой части, и, начиная с строки 7, сделайте следующее метки, начинающиеся с столбца A и заканчивающиеся в столбце E:

A7 = месяц

B7 = оплата

C7 = интерес

D7 = основной

E7 = баланс

пять заголовков для таблицы амортизации.

Теперь пришло время начать заполнять таблицу. Первый шаг - указать количество месяцев в жизни кредита. Под заголовком «месяц» введите «0» в ячейку A8 (время выдачи кредита). Заполните последующие ячейки в последовательном порядке через номер 48 (который поместит вас в ячейку A56). Один трюк: после ввода 0 убедитесь, что ячейка выбрана (A8), затем перетащите нижний правый угол ячейки вниз до строки 56. Затем выберите «заполнять серию» из маленького прямоугольника, который отображается справа от последней ячейки. Это должно автоматически заполнять числовые ряды в течение 48 месяцев.

Затем все платежи могут быть заполнены, поскольку стоимость каждого ежемесячного платежа уже определена. Начиная с 1-го месяца (оплата не производится в месяц 0, так как платежи происходят в конце каждого месяца), введите значение $ 442,69 за каждый месяц 1-48. После этого процентные и основные суммы могут быть рассчитаны с использованием соответствующих функций Excel.

Вычисление процентов и основных компонентов платежей с использованием функции Excel является простым и может использоваться для заполнения всей таблицы амортизации. Чтобы рассчитать процентный платеж, выберите ячейку C9, а затем щелкните меню финансовых функций на вкладке «Формулы». Выберите из списка функцию IPMT (для «выплаты процентов»). Появится окно, в котором вы должны ввести данные из электронной таблицы так же, как и на этом изображении:

Примечание: Знаки «$» между буквами и номерами ячеек необходимы, когда мы копируем формулы до остальных ячеек позже. «$» Блокирует указанную ячейку, поэтому формулы остаются неизменными, когда мы копируем их в оставшиеся ячейки. Вы можете заблокировать ячейки (и увидеть «$»), нажав F4.

Затем мы можем рассчитать сумму принципала в платеже. Подобно расчёту IPMT, выберите ячейку D9, а затем нажмите меню финансовых функций на вкладке «Формулы». Выберите функцию PPMT (для функции «основной платеж») из списка. Будет показано поле, в котором вы должны ввести данные из электронной таблицы так же, как и на этом изображении:

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

Последний столбец (столбец E) - это оставшийся столбец баланса, который легко вычисляется для каждый месяц, когда мы определили суммы процентов и основной суммы. За месяц 0 платежей еще не было сделано, поэтому баланс составляет 20 000 долл. Однако мы хотим, чтобы Excel автоматически вычислял баланс в каждом периоде, поэтому вместо Мана введя «$ 20,000», введите «= B1», ссылаясь на приведенную выше таблицу для расчета платежей. За 1-й месяц, поскольку остаток рассчитывается путем вычитания баланса предыдущего месяца на основной платеж текущего месяца, введите «= E8-D9» в ячейку E9. Баланс будет автоматически рассчитан. Ваша таблица амортизации теперь завершена в течение 1-го месяца и должна выглядеть примерно так:

Все данные за оставшиеся месяцы можно заполнить, просто выбрав ячейки B9-E9, скопировав выделение, а затем вставив выбор в ячейки B10-E10. Поскольку мы ввели формулы для каждой ячейки и заблокировали наши ячейки, которые будут использоваться в каждом расчете, значения для процентов, принципала и баланса автоматически вычисляются. Увидев эту работу за месяц 2, вся таблица может быть заполнена путем простого выбора столбцов B-E до 48-го месяца и вставки уже скопированной информации. Теперь Excel рассчитал каждое значение таблицы амортизации с помощью формул, которые мы ввели. Ваша завершенная таблица должна выглядеть так:

Первый год займа:

Последний год займа:

Примечание: Эти таблицы содержат только первый и последний годы таблица амортизации. Годы 2 и 3 будут между этими двумя таблицами, образуя одну непрерывную таблицу амортизации за все 48 месяцев.

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