Microsoft Office Tutorials and References
In Depth Information
To enter values for the PMT function:
1. Click the Collapse Dialog Box button
located to the right of the Rate box.
2. Click cell E6 to enter the cell reference for the annual interest rate.
To determine the rate, you will divide the value in cell E6 by the number of payment peri-
ods in a year (cell E9).
3. Type / (the division sign), and then click cell E9 to enter the cell reference.
4. Click the Expand Dialog Box button to restore the Function Arguments dialog box.
The expression E6/E9 should now appear in the Rate box.
Next you will enter the value for the second argument, the nper argument, which is the
total number of payments that need to be made for the 30-year loan. This number is dis-
played in cell E10.
5. Click in the Nper box, and then enter E10 either by typing it directly into the reference
box or by selecting the cell from the workbook.
Finally, you will enter the pv (present value) argument. In the case of a loan, the present
value is the amount of the loan Amanda’s family is seeking. This value is stored in cell E4.
6. Click in the Pv box, and then enter E4 using the method you prefer. Figure 2-28 shows the
completed Function Arguments dialog box and illustrates how this dialog box relates to the
function that will be inserted into cell E11.
Entering the PMT function
7. Click the OK button. Excel displays the value $993.63 in cell E11. Therefore, the required
monthly payment is $993.63 for a loan of $175,000 at a 5.5% annual interest rate for 30 years.
To see how this would affect Amanda’s family budget, you will enter this information into
the Expenses portion of the worksheet.
8. Click cell A9 , type House Payment to replace the word “Rent,” and then press the Tab key.
Now you will enter a formula in cell B9 so the value House Payment is equal to the value
9. Type =E11 in cell B9, and then press the Enter key. The average total monthly expenses