Содержание:
Вам когда-нибудь было интересно, как создать график погашения кредита в Google Таблицах? Тогда вам повезло! Программное обеспечение для работы с электронными таблицами, такое как Google Таблицы, поможет вам организовать и автоматизировать повседневные задачи, независимо от того, хотите ли вы поддерживать бюджет, отслеживать расходы, планировать действия или своевременно выплачивать кредиты.
Таблица амортизации может быть действительно эффективным способом увидеть, как ваши ежемесячные платежи могут повлиять на общую стоимость вашего кредита, будь то ипотека, автокредит или личный заем.
В этом руководстве мы продемонстрируем от начала до конца, как вы можете создать свой собственный график погашения кредита, просто используя Google Таблицы. Это означает, что вам не нужно искать необычные приложения и вы можете полностью контролировать процесс погашения кредита, просто используя несколько формул Google Таблиц.
Что такое амортизированный заем?
Амортизированная ссуда — это тип ссуды, который включает периодические платежи, запланированные на определенный период времени. Выплаты относятся как к основной сумме кредита, так и к процентам.
Процесс состоит из первой выплаты соответствующих процентов за период, чтобы оставшуюся часть платежа можно было направить на уменьшение основной суммы.
Из чего состоит таблица погашения ссуды?
Таблица погашения ссуды состоит из графика периодических выплат по ссуде. График показывает основную сумму и размер процентов по каждому платежу от начала до погашения кредита в конце срока.
Процесс погашения заключается в уплате одинаковой суммы каждым периодическим платежом. Однако в начале процесса большая часть суммы выплачивается по процентам, а ближе к концу большая часть суммы покрывает основную сумму.
Это, в свою очередь, делает процесс погашения комфортным для заемщика и снижает общую стоимость кредита.
В последней строке таблицы указаны общие проценты и выплаты основного долга заемщика за весь срок.
Как создать график погашения кредита в Google Таблицах
Итак, теперь, когда мы знаем основы, давайте приступим к созданию электронной таблицы.
Сначала мы начнем с создания базового каркаса листа, в который мы введем базовый график, дату начала, процентную ставку ссуды и общую сумму, подлежащую выплате.
После этого мы создадим таблицу и заполним первые две строки соответствующими формулами. В конце все, что нам нужно сделать, это просто перетащить формулы вниз, чтобы заполнить остальную часть расписания. В последней строке в столбце остатка ссуды должна отображаться сумма 0,0.
Создание базовой схемы таблицы графика погашения
Начнем с основного плана электронной таблицы.
Просто нажмите Файл -> Создать копию, чтобы вы могли редактировать и сохранять свой собственный документ.
- Основная сумма кредита или общая сумма кредита, которая должна быть погашена.
- Процентная ставка, которая будет применяться по кредиту.
- Срок (в годах) или период времени, в течение которого кредит подлежит погашению.
- Дата начала, с которой начинается график погашения кредита.
Предположим, у нас есть жилищный заем в размере 100 000 долларов США, который должен быть погашен через 10 лет с процентной ставкой 5%, начиная с 29.06.2021. Итак, заполним эти значения в таблице:
Заполнение основной таблицы амортизации
Пришло время заполнить основную часть рабочего листа. Вот столбцы, которые вам нужно будет заполнить:
Период
Колонка Period (Период) будет состоять из порядкового номера платежного периода. Таким образом, для первого взноса период будет 1, для второго — 2 и т. Д.
Введите 0 в первую строку этого столбца и 1 во вторую строку.
Дата
Столбец «Дата» содержит даты, когда должен быть произведен каждый платеж. В первой строке дата совпадает с датой начала (в ячейке E2). После этого каждая дата будет разницей в один месяц (поскольку вы выплачиваете каждый взнос ежемесячно).
Итак, в первой строке этого столбца введите формулу: = E2
=DATE(YEAR(B7),MONTH(B7)+1,Day(B7))
Это вернет дату ровно через месяц после даты в ячейке B7.
Ежемесячный платеж
Далее нам нужно рассчитать ежемесячный платеж для каждой строки. Для расчета ежемесячного платежа мы можем использовать функцию PMT Google Таблиц.
Функция PMT
Функция PMT вычисляет периодический платеж для аннуитетных инвестиций, где сумма и процентная ставка постоянны для каждого периодического платежа.
Синтаксис функции PMT следующий:
PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
Здесь,
- Rate — процентная ставка за период выплаты.
- number_of_periods — количество платежей.
- present_value — это не что иное, как основная сумма.
- future_value не является обязательным. Это будущая стоимость, остающаяся после совершения окончательного платежа.
- end_or_beginning также не является обязательным. Он указывает, должны ли платежи производиться в конце (0) или начале (0) каждого периода. По умолчанию это значение равно 0.
В нашем примере вот значения, которые нам нужно будет вставить:
- rate: процентная ставка в ячейке B2 рассчитана на целый год, но для этого параметра требуется ежемесячная процентная ставка. Таким образом, это значение будет B2 / 12.
- number_of_periods: этот параметр будет содержать количество периодов в месяцах. Поскольку ячейка E1 содержит период в годах, это значение будет E1 * 12.
- present_value: основная сумма находится в ячейке B1, поэтому мы будем вводить ее в этот параметр.
Поскольку вам не нужно платить ежемесячную плату в дату начала, это значение может быть 0,00 для первой строки.
Во второй строке вы можете ввести следующую формулу:
= PMT ($ B $ 2/12, $ E $ 1 * 12, $ B $ 1)
Мы заблокировали все ссылки на ячейки с помощью символов $, потому что мы не хотим, чтобы эти ссылки обновлялись, когда мы копируем формулу в остальные строки.
Помните, что ежемесячный платеж будет одинаковым за каждый период. Таким образом, все строки этого столбца будут иметь одинаковое значение.
Примечание . Возвращаемое значение отрицательное, поскольку это исходящий платеж. С финансовой точки зрения исходящие платежи всегда представлены отрицательным числом, а входящие — положительным числом.
Выплата процентов
В этом столбце будут указаны проценты, выплачиваемые по каждому взносу. Чтобы рассчитать ежемесячную выплату процентов, мы можем использовать функцию IPMT Google Таблиц.
Функция IPMT
Функция IPMT рассчитывает выплату процентов по аннуитетным инвестициям, где сумма и процентная ставка постоянны для каждого периодического платежа.
Синтаксис функции IPMT следующий:
IPMT(rate, period, number_of_periods, present_value, [future_value, end_or_beginning])
Здесь,
- rate — процентная ставка за период выплаты.
- period — порядковый номер периода амортизации.
- number_of_periods — количество платежей.
- present_value — основная сумма.
- future_value не является обязательным. Это будущая стоимость, остающаяся после совершения окончательного платежа.
- end_or_beginning также не является обязательным. Он указывает, должны ли платежи производиться в конце (0) или начале (0) каждого периода. По умолчанию это значение равно 0.
В нашем примере вот значения, которые нам нужно будет вставить:
- ставка: процентная ставка в ячейке B2 рассчитана на целый год, но для этого параметра требуется ежемесячная процентная ставка. Таким образом, это значение будет B2 / 12.
- period: он будет содержать порядковый номер платежного периода. Это значение содержится в столбце A для каждой соответствующей строки, поэтому для второй строки значение будет A8.
- number_of_periods: этот параметр будет содержать количество периодов в месяцах. Поскольку ячейка E1 содержит период в годах, это значение будет E1 * 12.
- present_value: основная сумма находится в ячейке B1, поэтому мы будем вводить ее в этот параметр.
Поскольку вам не нужно платить процентную сумму в дату начала, это значение может быть 0,00 для первой строки.
Во второй строке вы можете ввести следующую формулу:
= IPMT ($ B $ 2/12, A8, $ E $ 1 * 12, $ B $ 1)
Мы заблокировали все ссылки на ячейки с помощью символов $, потому что мы не хотим, чтобы эти ссылки обновлялись, когда мы копируем формулу в остальные строки.
Единственная ссылка, которую мы не заблокировали, — это вторая ссылка (представляющая период), потому что это значение должно изменяться с каждой строкой.
Основной платеж
В этом столбце указана основная сумма, которая должна быть оплачена для текущего платежа. Это значение может быть получено путем вычитания текущего процентного платежа из текущего значения ежемесячного платежа. Итак, для второй строки введите формулу:
= C8-D8
В первой строке снова будет значение 0,00.
Остаток ссуды
В этом столбце будет указана сумма еще не погашенной ссуды. Это значение может быть получено путем вычитания текущего платежа по основной сумме из предыдущего остатка по ссуде.
В первой строке, поскольку деньги еще не выплачены, это значение будет содержать всю основную сумму (или ссылку на ячейку B1).
Во второй строке значение будет:
= E8 + F7
Поскольку сумма основного платежа является отрицательным числом, мы использовали знак «+» вместо «-».
Это означает, что если вам нужно погасить ссуду через 10 лет, у вас должно получиться 10 * 12 = 120 строк.
Обобщение информации в последней строке
В последней строке таблицы амортизации должны быть показаны общие выплаты заемщика по процентам и основной сумме за весь срок.
Общий процент можно рассчитать, суммируя значения в столбце D. Итак, в ячейку C129 введите формулу:
=SUM(D7:D)
=SUM(E7:E)
Вот полный шаблон таблицы графика погашения кредита со всеми формулами.
Опять же, просто нажмите «Файл» -> «Сделать копию». Теперь вы можете добавить любые пользовательские данные, которые вам нравятся, и формулы будут автоматически обновляться.
График погашения в Google Таблицах можно легко настроить для любого вида ссуды. Все, что вам нужно сделать, это настроить значения в соответствии с вашими требованиями.
Надеемся, это было Вам полезно.