Функция Excel PMT с примерами формул
В учебном пособии показано, как использовать функцию PMT в Excel для расчета платежей по кредиту или инвестициям на основе процентной ставки, количества платежей и общей суммы кредита.
Прежде чем брать деньги в долг, полезно знать, как работает кредит. Благодаря финансовым функциям Excel, таким как СТАВКА, PPMT и IPMT, вычисление ежемесячного или любого другого периодического платежа по кредиту очень просто. В этом руководстве мы более подробно рассмотрим функцию PMT, подробно обсудим ее синтаксис и покажем, как создать собственный калькулятор PMT в Excel.
Что такое функция ПЛТ в Excel?
Функция Excel PMT — это финансовая функция, которая рассчитывает платеж по кредиту на основе постоянной процентной ставки, количества периодов и суммы кредита.
«PMT» означает «оплата», отсюда и название функции.
Например, если вы подаете заявку на двухлетний автокредит с годовой процентной ставкой 7% и суммой кредита 30 000 долларов, формула PMT может сказать вам, каковы будут ваши ежемесячные платежи.
Чтобы функция PMT правильно работала на ваших листах, помните о следующих фактах:
- Чтобы соответствовать общей модели денежных потоков, сумма платежа выводится как отрицательное число, поскольку это отток денежных средств.
- Значение, возвращаемое функцией PMT, включает основную сумму и проценты, но не включает какие-либо сборы, налоги или резервные платежи, которые могут быть связаны с кредитом.
- Формула PMT в Excel может вычислять платеж по кредиту для различных периодов платежей, таких как еженедельно, ежемесячно, ежеквартально или ежегодно. Этот пример показывает, как это сделать правильно.
Функция PMT доступна в Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.
Функция Excel PMT — синтаксис и основные способы использования
Функция PMT имеет следующие аргументы:
ПЛТ(ставка, кпер, pv, [fv], [type])
Где:
- Ставка (обязательно) – постоянная процентная ставка за период. Может быть представлен в виде процентов или десятичного числа.
Например, если вы делаете ежегодные платежи по кредиту с годовой процентной ставкой 10 процентов, используйте 10% или 0,1 для ставки. Если вы делаете ежемесячные платежи по одному и тому же кредиту, используйте ставку 10%/12 или 0,00833.
- Кпер (обязательно) – количество платежей по кредиту, т.е. общее количество периодов, в течение которых кредит должен быть погашен.
Например, если вы производите ежегодные платежи по кредиту сроком на 5 лет, укажите 5 в качестве кпер. Если вы делаете ежемесячные платежи по одному и тому же кредиту, то умножьте количество лет на 12 и используйте 5*12 или 60 в качестве кпер.
- Pv (обязательно) – текущая стоимость, т.е. общая сумма, которую сейчас составляют все будущие платежи. В случае кредита, это просто первоначальная сумма займа.
- Fv (необязательно) – будущая стоимость или остаток денежных средств, который вы хотите иметь после последнего платежа. Если этот параметр опущен, предполагается, что будущая стоимость кредита равна нулю (0).
- Тип (необязательно) — указывает, когда должны быть произведены платежи:
- 0 или опущен – платежи должны производиться в конце каждого периода.
- 1 – выплаты производятся в начале каждого периода.
Например, если вы одолжите 100 000 долларов США на 5 лет с годовой процентной ставкой 7%, следующая формула рассчитает годовой платеж:
=ПЛТ(7%, 5, 100000)
Чтобы найти ежемесячный платеж по тому же кредиту, используйте эту формулу:
=ПЛТ(7%/12, 5*12, 100000)
Или вы можете ввести известные компоненты кредита в отдельные ячейки и сослаться на эти ячейки в формуле PMT. С процентной ставкой в B1, нет. лет в B2 и сумму кредита в B3, формула проста:
=ПЛТ(В1, В2, В3)
Помните, что платеж возвращается как отрицательное число, потому что эта сумма будет списана (вычтена) с вашего банковского счета.
По умолчанию Excel отображает результат в формате валюты, округленный до 2 знаков после запятой, выделенный красным цветом и заключенный в круглые скобки, как показано в левой части изображения ниже. На изображении справа показан тот же результат в общем формате.
Если вы хотите, чтобы платеж был положительным числом, поставьте знак минус либо перед всей формулой PMT, либо перед аргументом pv (сумма кредита):
=-ПЛТ(В1, В2, В3)
или же
=ФМТ(B1, B2, -B3)
Кончик. Чтобы рассчитать общую сумму, уплаченную за кредит, умножьте возвращенное значение ПЛТ на количество периодов (значение nper). В нашем случае мы использовали бы это уравнение: 24 389,07 * 5 и обнаружили, что общая сумма равна 121 945,35 долларов США.
Как использовать функцию ПЛТ в Excel – примеры формул
Ниже вы найдете еще несколько примеров формулы Excel PMT, которые показывают, как рассчитать различные периодические платежи по автокредиту, жилищному кредиту, ипотечному кредиту и т.п.
Полная форма функции PMT в Excel
По большей части вы можете опустить последние два аргумента в своих формулах PMT (как мы сделали в приведенных выше примерах), потому что их значения по умолчанию охватывают наиболее типичные варианты использования:
- Fv опущен – подразумевает нулевой баланс после последнего платежа.
- Тип опущен – платежи должны производиться в конце каждого периода.
Если условия вашего кредита отличаются от условий по умолчанию, используйте полную форму формулы PMT.
В качестве примера рассчитаем сумму ежегодных платежей на основе этих входных ячеек:
- B1 – годовая процентная ставка
- B2 – срок кредита (в годах)
- B3 – сумма кредита
- B4 – будущая стоимость (остаток после последнего платежа)
- B5 – аннуитетный тип:
- 0 (регулярный аннуитет) – выплаты производятся в конце каждого года.
- 1 (аннуитетный платеж) – платежи производятся в начале периода, например, арендная плата или лизинговые платежи.
Поставьте эти ссылки на вашу формулу Excel PMT:
=ПЛТ(В1, В2, В3, В4, В5)
И у вас будет такой результат:
Расчет еженедельных, ежемесячных, ежеквартальных и полугодовых платежей
В зависимости от частоты платежей вам необходимо использовать следующие расчеты для оценивать а также Например аргументы:
- Для ставки разделите годовую процентную ставку на количество платежей в год (которое считается равным количеству периодов начисления процентов).
- Для nper умножьте количество лет на количество платежей в год.
В таблице ниже представлены подробности:
Частота платежей Nper Еженедельная годовая процентная ставка / 52 года * 52 Ежемесячная годовая процентная ставка / 12 лет * 12 Квартальная годовая процентная ставка / 4 года * 4 Полугодовая годовая процентная ставка / 2 года * 2
Например, чтобы найти сумму периодического платежа по кредиту в размере 5000 долларов США с годовой процентной ставкой 8% и сроком действия 3 года, используйте одну из приведенных ниже формул.
Еженедельный платеж:
=ПЛТ(8%/52, 3*52, 5000)
Ежемесячно оплата:
=ПЛТ(8%/12, 3*12, 5000)
Ежеквартальная оплата:
=ПЛТ(8%/4, 3*4, 5000)
Полугодовой платеж:
=ПЛТ(8%/2, 3*2, 5000)
Во всех случаях предполагается, что остаток после последнего платежа равен 0 долларов США, а платежи подлежат оплате в конце каждого периода.
На скриншоте ниже показаны результаты этих формул:
Как сделать калькулятор PMT в Excel
Прежде чем взять кредит, имеет смысл сравнить различные условия кредита, чтобы найти варианты, которые подходят вам больше всего. Для этого создадим собственный калькулятор платежей по кредиту в Excel.
- Для начала введите сумму кредита, процентную ставку и срок кредита в отдельные ячейки (B3, B4, B5 соответственно).
- Чтобы иметь возможность выбирать разные периоды и указывать, когда должны быть произведены платежи, создайте раскрывающиеся списки со следующими предопределенными параметрами (B6 и B7):
- Настройте таблицы поиска для Периоды (Е2:F6) и Платежи должны (E8:F9), как показано на скриншоте ниже. Важно, чтобы текстовые метки в таблицах поиска точно соответствовали элементам соответствующего выпадающего списка.
В ячейках рядом с раскрывающимися списками введите следующие формулы ЕСЛИОШИБКА ВПР, которые будут извлекать число из таблицы поиска, соответствующее элементу, выбранному в раскрывающемся списке.Формула для Периоды (С6):
=ЕСЛИОШИБКА(ВПР(B6, E2:F6, 2, 0), “”)
Формула для Платежи должны (С7):
=ЕСЛИОШИБКА(ВПР(B7, E8:F9, 2, 0), “”)
- Напишите формулу PMT для расчета периодического платежа на основе ваших ячеек. В нашем случае формула выглядит следующим образом:
=ЕСЛИОШИБКА(-PMT(B4/C6, B5*C6, B3, 0, C7), “”)
Пожалуйста, обратите внимание на следующие вещи:
- Аргумент fv (0) жестко запрограммирован в формуле, потому что мы всегда хотим, чтобы баланс был равен нулю после последнего платежа. Если вы хотите разрешить своим пользователям вводить любое значение в будущем, выделите отдельную ячейку ввода для аргумента fv.
- Перед функцией PMT ставится знак минус, чтобы результат отображался как положительное число.
- Функция ПЛТ заключена в ЕСЛИОШИБКА, чтобы скрыть ошибки, когда некоторые входные значения не определены.
Вышеупомянутая формула идет в B9. А в соседней ячейке (А9) выводим метку, соответствующую выбранному периоду (В6). Для этого просто соедините значение в ячейке B6 и нужный текст:
=B6&”Оплата”
- Наконец, вы можете скрыть таблицы поиска, добавить несколько последних штрихов форматирования, и ваш калькулятор Excel PMT готов к работе:
Функция Excel PMT не работает
Если ваша формула Excel PMT не работает или дает неверные результаты, это может быть вызвано следующими причинами:
- #ЧИСЛО! ошибка может возникнуть, если оценивать аргумент является отрицательным числом или Например равен 0.
- ЦЕННОСТЬ! ошибка возникает, если один или несколько аргументов являются текстовыми значениями.
- Если результат формулы ПЛТ намного выше или ниже ожидаемого, убедитесь, что вы согласуетесь с единицами измерения, указанными для оценивать а также Например аргументы, что означает, что вы правильно преобразовали годовую процентную ставку в ставку периода и количество лет в недели, месяцы или кварталы, как показано на этот пример.
Вот как вы вычисляете функцию PMT в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец рабочей книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула ПЛТ в Excel – примеры(файл .xlsx)
