В этой статье мы узнаем, как рассчитать график погашения кредита в Excel. Для расчета выплаты по кредиту мы будем использовать формулы «СТАВКА», «КПЕР», «ПС», «ПЛТ», «ППМТ» и «IPMT». Все эти формулы помогут составить таблицу амортизации в Excel.

PMT: возвращает регулярный ежемесячный платеж по ссуде (основной процент), когда процент по каждому из ежемесячных платежей постоянен. Рассчитывает выплаты по кредиту на основе постоянных платежей и постоянной процентной ставки.

Синтаксис функции «PMT»: = PMT (Rate, Nper, -Loan Amount)

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

Синтаксис функции «КПЕР»: = КПЕР (Ставка, Pmt, -Сумма кредита)

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

Синтаксис функции «PV»: = PV (Rate, Nper, Pmt)

PPMT: возвращает сумму основной суммы кредита за определенный период на основе периодических постоянных платежей и постоянной процентной ставки.

А также возвращает сумму основного долга в рамках ежемесячного платежа (ежемесячный платеж состоит из основного долга + процентов).

Синтаксис функции «PMT»: = PPMT (Ставка, какой период, Nper, -Сумма кредита) IPMT: возвращает процентную выплату за данный период по ссуде на основе периодических, постоянных платежей и постоянной процентной ставки. А также возвращает сумму процентов в рамках ежемесячного платежа (ежемесячный платеж состоит из основной суммы + процентов).

Синтаксис функции «IPMT»: = IPMT (Ставка, Какой период, Кол-во, -Сумма кредита) Рассмотрим пример:

Банк одобрил сумму кредита в размере 300 000 долларов США. Процентная ставка составляет 5% с плавающей ставкой * Количество месяцев 360 (12 x 30)

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

Определение ежемесячного платежа

  • Если бы не было процентной ставки, ежемесячный платеж составлял бы (300 000 долл. США / 360 = 833,33 долл. США)

  • Мы можем рассчитать ежемесячный платеж с помощью функции PMT

Синтаксис = PMT (ставка, кол-во, pv, fv, тип)

Давайте быстро рассмотрим аргументы этой функции.

  • В ячейке B4 указана процентная ставка, деленная на 12, так как это годовая ставка, которая рассчитывается ежемесячно.

В ячейке B5 указано «кпер» *, которое рассчитывается путем умножения количества лет, в течение которых требуется заем, на 12 месяцев. 10 — количество месяцев, как показано в примере выше.

  • Ячейка B3 имеет текущую стоимость.

  • Тип предназначен для оплаты в конце периода, для которого мы введем «0» или его также можно не указывать. Для платежей, произведенных в начале периода, введем 1.

Ячейка B6 = PMT (B4 / 12, B5, -B3,0)

Ежемесячный платеж составляет * 1 610,46 $

img1

Функция PMT используется для расчета периодического платежа по стандартной амортизируемой ссуде. Возьмем еще один пример — см. Рисунок 2 ниже:

Рисунок 2

img2

Ежемесячный платеж составляет 5 150,17 долларов США для суммы кредита 50 000 долларов США и процентной ставки 6,50% на срок 10 месяцев.

Рис. 2а

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

На рис. 2a, основной баланс в ячейке F11 = основной баланс в ячейке F12 = F11-C12 (проценты)

img3

Давайте посчитаем, что проценты, основная сумма, кумулятивная основная сумма и совокупный процент рассчитываются в ячейке B12 = $ B $ 4 / 12F11, как показано на рисунке 2b.

Формула для принципала в ячейке C12 * = проценты в ячейке B12 вычитаются из платежей в ячейке $ B $ 6.

Формула кумулятивного принципала в ячейке * D12 = C12 + D11

Формула накопительного процента в ячейке * E12 = B12 + E11

После ввода всех формул:

Вам нужно выбрать ячейку * от B12 до F12

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

Основной баланс

становится равным нулю.

Рисунок 2b

img4

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