FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

PMT Function

Updated on December 12, 2023
Article byTanuj Kumar
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

PMT Function in Excel

The PMT function is an advanced Excel formula and one of the financial functionsFinancial FunctionsExcel is known for making complex formulas easy to use and apply. Most needed functions are 1.Future Value 2.FVSchedule 3.Present Value 4.Net Present Value 5.XNPV 6.PMT 7.PPMT 8.Internal Rate of Return 9.Modified Internal Rate of Return 10.XIRR 11.NPER 12.RATE 13.EFFECT 14.NOMINAL 15.SLNread more used to calculate the monthly payment amount against the simple loan amount. You have to provide the function of basic information, including loan amount, interest rate, and payment duration, and the function will calculate the payment as a result. The payment amount calculated by this PMT Excel formula returns the amount without taxes, reserve payments, and fees (sometimes associated with loans).

PMT Function Formula

PMT Formula

Explanation

Five parameters are used in this PMT Excel function. In which three are compulsory and two are optional.

Compulsory Parameter:

  1. Rate: Rate represents the interest rate for the loan amount. If you are making a monthly payment in this PMT Excel function, then you should convert the rate at a monthly rate and the nper in a month too.
  2. Nper: Nper is the total number of installments for the loan amount. For example, considering the 5 years terms means 5*12=60.
  3. Pv: Pv is the total loan amount or present value.

Optional Parameter:

  1. [Fv]: It is also called the future value. It is optional in this PMT Excel. If not passed in this function, it will be considered zero.
  2. [Type]: It can be omitted from PMT fn and used as 1 in case payments are due at the beginning of the period and considered as 0 in case the payments are due at the end of the period.

How to Use the PMT Function in Excel? (with Examples)

This function is very simple. Let us now see how to use this PMT Excel function with the help of some examples.

You can download this PMT Function Excel Template here – PMT Function Excel Template

Example #1

Suppose the loan amount is 25,000, the interest rate is 10% annual, and the period is 5 years.

Here, the number of payments will be =5*12=60 payments in total.

In this PMT Excel, we have considered C4/12 because a 10% rate is annual. By dividing by 12, we get the monthly rate. Here, the future value and type are considered zero.

PMT Function (Example-1)

The output will be:

PMT Function (Example-1-1)
  • Use the negative sign before this PMT Excel to convert it into a positive value.
PMT Function (Example-1-2)

Then the output will be $531.18.

  • You can also change the currency type to change the format of the cell.

Select the cell, convert the format, and select the currency type per your requirements.

PMT Function (Example-1-3)

Example #2

We can use it to calculate the payments on a Canadian mortgage.

Suppose the loan amount is 25000, the annual interest rate is 10%, compounded semiannually, and the period is 5 years.

Here, the annual rate/2+1 is the semiannual interest in the annual rate, and the rate is 10/2=5% every 6 months. The rate is a power of (1/6) for monthly and semiannual payments.

PMT Function (Example-2)

Example #3

The PMT formula in Excel can be used as an automatic loan calculator.

In the previous example, we calculated the monthly payment by providing the loan amount, interest rate, and number of payments. In an automatic loan calculator, we are using the annual rate, time, and frequency of payments.

Before understanding the automatic loan calculator, create a list for calculating the number of payments. 

PMT Function (Example-2-1)

Using the list, we can calculate the numbers of payments within a year by using a simple VLOOKUP from the list, which is 26 bi-weekly.

For the total number of payments, multiple it from the total number of years,=3*26=78.

Here, we again took the annual rate of 5% and the loan amount as 25000.

Then the PMT function in Excel looks like: =-PMT(C30/E31,E32,C33) and output will be $346.74.

PMT Function (Example-3)

Things to Remember

Below are the few error details that can come in the PMT formula in Excel if the wrong argument is passed in the functions.

#1 – Error handling #NUM!: If the Nper value is 0, it will throw a #NUM! Error.

PMT Function (Example-4)

#2 – Error handling #VALUE!: The result will be a #VALUE! Error when any non-numeric value has been passed in the PMT function formula.

PMT Function (Example-4-1)

In this function, a monthly amount and rate should be considered in a month.

  1. It would help if you converted the monthly or quarterly rate per your requirement.
  2. The payment amount calculated by this PMT Excel function returns the amount without taxes, reserve payments, and fees (sometimes associated with loans).

Recommended Articles

This article is a guide to PMT Function in Excel. Here, we discuss the PMT formula and how to use the PMT function, along with practical examples and downloadable Excel templates.