Microsoft Office Tutorials and References

In Depth Information

**Using the PMT Function**

The Loan Analysis worksheet includes all the data you need to calculate the monthly

payment required to pay off the mortgage in 20 years at 6 percent interest compounded

monthly. You will enter the PMT function to calculate this value.

To use the PMT function to calculate the monthly payment:

◗

1.
Click cell
B10
to select it.

◗

2.
In the Function Library group on the Formulas tab, click the
Financial
button, and

then click
PMT
in the list of financial functions. The Function Arguments dialog

box opens.

◗

3.
In the Rate box, enter the cell reference
, which is the cell with the interest rate
B5

per month.

Be sure to enter the

interest rate per month for the

Rate argument and not

the annual interest rate

for any loan or

investment that has monthly

payments.

◗

4.
In the Nper box, enter the cell reference
, which is the cell with the total
B7

ber of monthly payments required to pay back the loan.

◗

5.
In the Pv box, enter the cell reference
, which is the cell with the present value
B8

of the loan. See Figure 3-37.

Figure 3-37

Function Arguments dialog box for the PMT function

interest rate per

onth

number of months

in which to pay

back the loan

loan amount

◗

6.
Click the
OK button. The value $1,432.86 is displayed in parentheses in cell B10 to

indicate a negative currency value. See Figure 3-38.