Apply the PMT Function in Excel (2024)

The PMT function in Excel allows us to calculate the payment on a loan. The arguments (variables) for the function are similar to the compound interest function.

PV = Present value or amount of the loan (just one variable)

r = Annual rate

t = Number of years

n = Number of compoundings (sometimes the same as the number of payments) each year

\({\text{Payment}} = \frac{({\text{PV}})(\frac{\text{r}}{\text{n}})}{1-(1+\frac{\text{r}}{\text{n}})^{-{\text{nt}}}}\)

You do not need to remember this formula because we will use Excel to do our calculations. However, it is good to know where the arguments in the function are coming from.

The PMT function has three required arguments and two optional arguments. We will ignore the two optional arguments.

PMT(rate, nper, pv, [fv], [type])

• rate = Periodic rate = \(\frac{\text{Annual rate}}{\text{number of compoundings each year}}\)

This is the same as \(\frac{\text{r}}{\text{n}}\) in the payment equation.

See Also
RATE function - Microsoft Support

For example, if our annual rate is 6% and we make a payment each month, then the rate = \(\frac{0.06}{12}\)

• nper = total number of payments on the loan

This is the same as nt in the equation or (number of payments each year)(number of years of the loan).

• pv = present value (The amount of money being borrowed at the beginning of the loan.)


• [fv] and [type] are optional. We won’t use them in this scenario.

Video Source (08:00 mins) | Transcript

Steps for using the PMT function in excel:

• Calculate all the variables the function needs:

1. rate = \(\frac{\text{r}}{\text{n}}\)


2. nper = nt


3. Pv = Present value