Функция ИПМТ в Excel - расчет процентов по кредиту

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

Всякий раз, когда вы берете кредит, будь то ипотека, жилищный кредит или кредит на покупку автомобиля, вам необходимо вернуть сумму, которую вы первоначально заняли, и проценты сверх нее. Проще говоря, проценты — это стоимость использования чьих-либо (обычно банковских) денег.

Процентная часть платежа по кредиту может быть рассчитана вручную путем умножения процентной ставки за период на остаток. Но в Microsoft Excel для этого есть специальная функция — функция IPMT. В этом уроке мы подробно объясним его синтаксис и предоставим примеры формул из реальной жизни.

Функция Excel IPMT — синтаксис и основные способы использования

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

Чтобы лучше запомнить название функции, обратите внимание, что «I» означает «процент», а «PMT» — «платеж».

Синтаксис функции IPMT в Excel следующий:

IPMT (ставка, за, кпер, pv, [fv], [type])

Где:

Ставка (обязательно) – постоянная процентная ставка за период. Вы можете указать его в процентах или десятичном числе.
Например, если вы делаете ежегодные платежи по кредиту с годовой процентной ставкой 6 процентов, используйте 6% или 0,06 для оценивать.
Если вы производите еженедельные, ежемесячные или ежеквартальные платежи, разделите годовую ставку на количество периодов платежей в году, как показано на рис. этот пример. Скажем, если вы делаете ежеквартальные платежи по кредиту с годовой процентной ставкой 6 процентов, используйте 6%/4 для оценивать.
Per (обязательно) – период, за который вы хотите рассчитать проценты. Это должно быть целое число в диапазоне от 1 до Например.
Кпер (обязательно) – общее количество платежей в течение срока действия кредита.
Pv (обязательно) – текущая стоимость кредита или инвестиции. Другими словами, это основная сумма кредита, то есть сумма, которую вы заимствовали.
Fv (необязательно) – будущая стоимость, т.е. желаемый баланс после последнего платежа. Если он опущен, подразумевается, что он равен нулю (0).
Тип (необязательно) — указывает, когда должны быть произведены платежи:
- 0 или опущен – выплаты производятся в конце каждого периода.
- 1 – выплаты производятся в начале каждого периода.

Например, если вы получили кредит в размере 20 000 долларов США, который вы должны погасить ежегодными платежами в течение следующих 3 лет с годовой процентной ставкой 6%, процентная часть платежа за 1-й год может быть рассчитана по этой формуле:

=IPMT(6%, 1, 3, 20000)

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

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

Если вы предпочитаете получать проценты как положительное число, поставьте знак минус либо перед всей функцией IPMT, либо перед аргументом pv:

=-IPMT(6%, 1, 3, 20000)

или же

=IPMT(6%, 1, 3, -20000)

Примеры использования формулы IPMT в Excel

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

Прежде чем мы углубимся, следует отметить, что формулы IPMT лучше всего использовать после функции PMT, которая вычисляет общую сумму периодического платежа (проценты + основная сумма).

Формула IPMT для разной периодичности платежей (недели, месяцы, кварталы)

Чтобы получить процентную часть платежа по кредиту правильно, вы всегда должны конвертировать годовую процентную ставку в ставку соответствующего периода и количество лет в общее количество периодов платежей:

Для аргумента ставки разделите годовую процентную ставку на количество платежей в год, предполагая, что последнее равно количеству периодов начисления сложных процентов в году.
В качестве аргумента nper умножьте количество лет на количество платежей в год.

В следующей таблице показаны расчеты:

Частота выплаты Аргумент Ставка Аргумент Nper Еженедельная годовая процентная ставка / 52 года * 52 Ежемесячная годовая процентная ставка / 12 лет * 12 Квартальная годовая процентная ставка / 4 года * 4 Полугодовая годовая процентная ставка / 2 года * 2

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

Годовая процентная ставка: 6%
Срок кредита: 2 года
Сумма кредита: 20 000 долларов США
Период: 1

Баланс после последнего платежа должен быть $0 ( фв аргумент опущен), а платежи должны производиться в конце каждого периода (т. тип аргумент опущен).

Еженедельно:

=IPMT(6%/52, 1, 2*52, 20000)

Ежемесячно:

=IPMT(6%/12, 1, 2*12, 20000)

Ежеквартальный:

=IPMT(6%/4, 1, 2*4, 20000)

Полугодовой:

=IPMT(6%/2, 1, 2*2, 20000)

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

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

Полная форма функции IPMT

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

Для начала определим входные ячейки:

B1 – годовая процентная ставка
B2 – срок кредита в годах
B3 – количество платежей в год
B4 – сумма кредита (pv)
B5 – будущая стоимость (fv)
B6 – когда должны быть произведены платежи (тип):
- 0 – в конце периода (регулярная рента)
- 1 – на начало периода (аннуитетный платеж)

Предполагая, что первый номер периода находится в A9, наша формула процентов выглядит следующим образом:

=IPMT($B$1/$B$3, A9, $B$2*$B$3, $B$4, $B$5, $B$6)

Примечание. Если вы планируете использовать формулу IPMT более чем за один период, обратите внимание на ссылки на ячейки. Все ссылки на входные ячейки должны быть абсолютными (со знаком доллара), поэтому они привязаны к этим ячейкам. Аргумент per должен быть относительной ссылкой на ячейку (без знака доллара, например A9), потому что он должен меняться в зависимости от относительного положения строки, в которую копируется формула.

Итак, вводим приведенную выше формулу в ячейку B9, перетаскиваем ее вниз для оставшихся периодов и получаем следующий результат. Если сравнить цифры в Интерес столбцы (обычный аннуитет слева и аннуитетный платеж справа), вы заметите, что проценты немного ниже, когда вы платите в начале периода.

Функция Excel IPMT не работает

Если ваша формула IPMT выдает ошибку, скорее всего, это одна из следующих причин:

#ЧИСЛО! возникает ошибка, если аргумент выходит за пределы диапазона от 1 до Например.
#ЦЕННОСТЬ! ошибка возникает, если какой-либо из аргументов не является числовым.

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