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:
  1. rate = \( {\dfrac {r}{n}} \)
  2. nper = \(\text{nt}\)
  3. Pv = Present value

Practice Problems

1. 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

   x

   Solution:
   \($534.16\)
   Details:
   First, we need to do is determine the following:
   
   Number of payments per year \(= n = 12\)
   
   Number of years \(= 10\)
   
   Annual rate \(= r = 0.075 (or 7.5\%)\)
   
   Amount of loan \(= {\color{Red}Pv} = {\color{Red}45000}\) (Do not use commas in Excel except to separate each argument.)
   
   \(\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.075}{12}\text{ or }\frac{7.5\%}{12}\)
   
   Total Number of Payments \(= {\color{Magenta}nper} =\) number of years \(\times\) payments per year \(= 10 \times 12 = {\color{Magenta}120}\)
   
   Open an Excel spreadsheet. Select a cell and type:
   
   =pmt(
   
   
   
   Excel will bring up a box with "PMT(rate, nper, pv, [fv], [type])" written in it to remind you what order to enter your arguments. Continue by entering the values for rate, nper, and pv. You don’t need to enter anything for fv or type—these arguments are optional.
   
   
   
   Then hit enter. Excel will then display the payment amount which is \($534.16\). Note that it is in red and has parentheses around it indicating that it is an expense.
   
   
   )
2. Use Excel to calculate a car payment compounded monthly given the following values: (
   Solution

   x

   Solution:
   \($222.14\)
   Details:
   First, we need to do is determine the following:
   
   Number of payments per year \(= n = 12\)
   
   Number of years \(= 6\)
   
   Annual rate \(= r = 0.0575 (or 5.75\%)\)
   
   Amount of loan \(= {\color{Red}Pv} = {\color{Red}13500}\) (Do not use commas in Excel except to separate each argument.)
   
   \(\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0575}{12}\text{ or }\frac{5.75\%}{12}\)
   
   Total Number of Payments \(= {\color{Magenta}nper} =\) number of years \(\times\) payments per year \(= 6 \times 12 = {\color{Magenta}72}\)
   
   Open an Excel spreadsheet. Select a cell and type:
   
   =pmt(
   
   
   
   Excel will bring up a box with "PMT(rate, nper, pv, [fv], [type])" written in it to remind you what order to enter your arguments. Continue by entering the values for rate, nper, and pv. You don’t need to enter anything for fv or type—these arguments are optional.
   
   
   
   Then hit enter. Excel will then display the payment amount which is $222.14. Note that it is in red and has parentheses around it indicating that it is an expense.
   
   
   )
   • Amount of loan = \(13,500\)
   • Annual interest rate = \(5.75\%\)
   • Length of the loan = 6 years
3. Use Excel to calculate a house payment compounded monthly given the following values: (
   Solution

   x

   Solution:
   \($1154.43\)
   )
   • Amount of loan = \(245,500\)
   • Annual interest rate = \(3.875\%\)
   • Length of the loan = 30 years
4. 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

   x

   Solution:
   \($25.51\)
   )
5. 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

   x

   Solution:
   \($132.44\)
   )
6. Use Excel to calculate the monthly payment on an \($800\) cell phone at \(5.25\%\) interest over two years. (
   Solution

   x

   Solution:
   \($35.19\)
   )

Need More Help?

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