Calculating Interest and Excel Functions:

Apply the PMT Function in Excel

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.

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.

As you watch the following video, follow along in excel to start practicing using this function.

Video Source (08:00 mins) | Transcript

Steps for using the PMT function in excel:

Calculate all the variables the function needs:

rate = $\frac{\text{r}}{\text{n}}$

nper = 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.
Use Excel to calculate a car payment compounded monthly given the following values:
- 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:
- 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.
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.
Use Excel to calculate the monthly payment on an $800 cell phone at 5.25% interest over two years.

Solutions

$534.16 (Written Solution)

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 = Pv = 45000 (Do not use commas in Excel except to separate each argument.)

$\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 = nper = number of years × payments per year = 10 × 12 = 120

Open an Excel spreadsheet. Click in a cell then 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.
$222.14 (Written Solution)

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 = Pv = 13500 (Do not use commas in Excel except to separate each argument.)

$\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 = nper = number of years × payments per year = 6 × 12 = 72

Open an Excel spreadsheet. Click in a cell then 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.
$1154.43
$25.51
$132.44
$35.19