Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

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

Figure 2-28

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

Payment Amount.

9.
Type
=E11
in cell B9, and then press the
Enter
key. The average total monthly expenses

are recalculated.