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}} = \dfrac{({\text{PV}})(\dfrac{\text{r}}{\text{n}})}{1-(1+\dfrac{\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**= \(\displaystyle Periodic \ rate = {\frac {Annual \ rate}{number \ of \ compoundings \ per \ year}} \)

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

For example, if our annual rate is 6% and we make a payment each month, then the rate = \( {\dfrac {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:
- rate = \( {\dfrac {r}{n}} \)
- nper = \(\text{nt}\)
- Pv = Present value

### Practice Problems

- Use Excel to calculate the monthly payment on a \($45,000\) loan for a small business with an interest rate of \(7.5\%\) over 10 years. (Solution
- Use Excel to calculate a car payment compounded monthly given the following values: (Solution
- Amount of loan = \(13,500\)
- Annual interest rate = \(5.75\%\)
- Length of the loan = 6 years

- Use Excel to calculate a house payment compounded monthly given the following values: (Solution
- Amount of loan = \(245,500\)
- Annual interest rate = \(3.875\%\)
- Length of the loan = 30 years

- Use Excel to calculate the payment on a personal loan to a friend of \($150\) with an annual interest rate of \(7\%\) over six months (\(0.5\) years) with the interest calculated
**monthly**. (Solution - Use Excel to calculate the payment on a credit card balance of \($5000\) with an annual interest rate of \(19.99\%\) over five years with the interest calculated
**monthly**. (Solution - Use Excel to calculate the monthly payment on an \($800\) cell phone at \(5.25\%\) interest over two years. (Solution

## Need More Help?

- Study other Math Lessons in the Resource Center.
- Visit the Online Tutoring Resources in the Resource Center.
- Contact your Instructor.
- If you still need help, Schedule a Tutor.