Функция 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)
Вместо жесткого кодирования аргументов в формуле вы можете ввести их в предопределенные ячейки и ссылаться на эти ячейки, как показано на этом снимке экрана:
Если вы предпочитаете, чтобы результат был положительным числом, поставьте знак минус либо перед всей формулой PPMT, либо перед аргументом pv (сумма кредита). Например:
=-PPMT(8%, 1, 3, 50000)
или же
=ППМТ(8%, 1, 3, -50000)
3 вещи, которые вы должны знать о функции Excel PPMT
Чтобы успешно использовать формулы PPMT в своих рабочих листах, имейте в виду следующие факты:
- Основная сумма возвращается как отрицательное число, поскольку это исходящий платеж.
- По умолчанию к результату применяется денежный формат, отрицательные числа выделены красным цветом и заключены в скобки.
- При расчете основной суммы для различных частот платежей убедитесь, что вы согласуетесь с аргументами 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, затем перетащите ее на столько ячеек, сколько необходимо, и вы получите следующий результат:
Как вы можете видеть на снимке экрана выше, общий платеж (рассчитанный с помощью функции 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 лет. Обратите внимание, что сумма всех основных платежей равна будущей стоимости инвестиции:
Функция Excel PPMT не работает
Если формула PPMT работает неправильно на вашем листе, вам могут помочь следующие советы по устранению неполадок:
- Аргумент per должен быть больше 0, но меньше или равен nper, иначе #ЧИСЛО! возникает ошибка.
- Все аргументы должны быть числовыми, иначе #ЗНАЧ! возникает ошибка.
- При расчете еженедельных, ежемесячных или ежеквартальных платежей обязательно конвертируйте годовую процентную ставку в ставку за соответствующий период, как показано в приведенных выше примерах, иначе результат вашей формулы PPMT будет неправильным.
Вот как вы используете функцию PPMT в Excel. Чтобы попрактиковаться, вы можете скачать наш Примеры формул PPMT. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
