Функция Excel PPMT с примерами формул

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

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

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

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

Функция PPMT в Excel вычисляет основную часть платежа по кредиту за заданный период на основе постоянной процентной ставки и графика платежей.

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

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

Где:

  • Ставка (обязательно) – постоянная процентная ставка по кредиту. Может быть представлен в процентах или десятичном числе.

    Например, если вы делаете ежегодные платежи по кредиту или инвестициям с годовой процентной ставкой 7 процентов, укажите 7 процентов или 0,07. Если вы делаете ежемесячные платежи по тому же кредиту, то поставьте 7%/12.

  • Per (обязательно) – целевой период оплаты. Это должно быть целое число от 1 до nper.
  • Кпер (обязательно) – общее количество платежей по кредиту или инвестиции.
  • Pv (обязательно) – текущая стоимость, т.е. сколько сейчас стоит ряд будущих платежей. Текущая стоимость кредита – это сумма, которую вы первоначально заняли.
  • Fv (необязательный) – будущая стоимость, т.е. баланс, который вы хотите иметь после последнего платежа. Если он опущен, предполагается, что он равен нулю (0).
  • Тип (необязательно) — указывает, когда должны быть произведены платежи:
    • 0 или опущен – платежи должны производиться в конце каждого периода.
    • 1 – выплаты производятся в начале каждого периода.

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

=ППМТ(8%, 1, 3, 50000)

Если вы собираетесь вносить ежемесячные платежи по одному и тому же кредиту, то используйте такую ​​формулу:

=PPMT(8%/12, 1, 3*12, 50000)

Вместо жесткого кодирования аргументов в формуле вы можете ввести их в предопределенные ячейки и ссылаться на эти ячейки, как показано на этом снимке экрана:
Функция ППМТ в Excel

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

=-PPMT(8%, 1, 3, 50000)

или же

=ППМТ(8%, 1, 3, -50000)
Формула PPMT для возврата основной части платежа в виде положительного числа

3 вещи, которые вы должны знать о функции Excel PPMT

Чтобы успешно использовать формулы PPMT в своих рабочих листах, имейте в виду следующие факты:

  1. Основная сумма возвращается как отрицательное число, поскольку это исходящий платеж.
  2. По умолчанию к результату применяется денежный формат, отрицательные числа выделены красным цветом и заключены в скобки.
  3. При расчете основной суммы для различных частот платежей убедитесь, что вы согласуетесь с аргументами rate и nper. Для ставки разделите годовую процентную ставку на количество платежей в год (при условии, что оно равно количеству периодов начисления сложных процентов в году). Для nper умножьте количество лет на количество платежей в год.
    • недель: ставка – годовая процентная ставка/52; кпер – лет*52
    • месяцы: ставка – годовая процентная ставка/12; кпер – лет*12
    • кварталы: rate – годовая процентная ставка/4; кпер – лет*4

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

А теперь давайте рассмотрим пару примеров формул, которые показывают, как использовать функцию PPMT в Excel.

Пример 1. Краткая форма формулы PPMT

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

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

  • B1 – годовая процентная ставка
  • B2 – срок кредита (в годах)
  • B3 – количество платежей в год
  • B4 – сумма кредита

На основе входных ячеек определите аргументы для формулы PPMT:

  • Rate – годовая процентная ставка / количество платежей в год ($B$1/$B$3).
  • Per – первый платежный период (A7).
  • Кпер – годы * количество платежей в год ($B$2*$B$3).
  • Pv – сумма кредита ($B$4)
  • Fv – опущено, предполагается нулевой баланс после последнего платежа.
  • Тип — опущен, при условии, что платежи должны производиться в конце каждого периода.

Теперь сложите все аргументы вместе, и вы получите следующую формулу:

=PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

Обратите внимание, что мы используем абсолютные ссылки на ячейки во всех аргументах, кроме за где используется относительная ссылка на ячейку (A7). Это связано с тем, что аргументы rate, nper и pv относятся к входным ячейкам и должны оставаться постоянными независимо от того, куда копируется формула. Аргумент per должен меняться в зависимости от относительного положения строки.

Введите приведенную выше формулу в C7, затем перетащите ее на столько ячеек, сколько необходимо, и вы получите следующий результат:
Формула PPMT в Excel

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

Чтобы проверить результаты функции PPMT, вы можете сложить все основные платежи с помощью функции SUM и посмотреть, равна ли сумма первоначальной сумме кредита, которая в нашем случае составляет 20 000 долларов США.

Пример 2. Полная форма формулы PPMT

В этом примере мы будем использовать функцию PPMT для расчета платежей по основной сумме, необходимых для увеличения инвестиций с 0 долларов США до указанной вами суммы.

Поскольку мы собираемся использовать полную форму функции PPMT, мы определяем больше входных ячеек:

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

Как и в предыдущем примере, для ставки мы делим годовую процентную ставку на количество платежей в год ($B$1/$B$3). Для nper мы умножаем количество лет на количество платежей в год ($B$2*$B$3).

С первым номером платежного периода в A10 формула принимает следующий вид:

=PPMT($B$1/$B$3, A10, $B$2*$B$3, $B$4, $B$5, $B$7)

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

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

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

  1. Аргумент per должен быть больше 0, но меньше или равен nper, иначе #ЧИСЛО! возникает ошибка.
  2. Все аргументы должны быть числовыми, иначе #ЗНАЧ! возникает ошибка.
  3. При расчете еженедельных, ежемесячных или ежеквартальных платежей обязательно конвертируйте годовую процентную ставку в ставку за соответствующий период, как показано в приведенных выше примерах, иначе результат вашей формулы PPMT будет неправильным.

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

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *