Page 111 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 94
94 Part II: Using Formulas and Functions

3. Create a formula in cell A7 that dynamically copies the initial principal value
entered in cell B2 to this cell.
To create a dynamic link between the value in one cell and another cell in the
same worksheet, type = to start the formula, followed by the reference of the cell
with which you want the value linked.
4. Create a formula in cell A8 that adds 1000 to the value in cell A7 and then use the
Fill handle to copy this formula down the rows to the cell range A9:A16.
5. Create a formula in cell B6 that dynamically copies the initial interest rate
entered in cell B3 to this cell.
6. Create a formula in cell C6 that adds 0.25% to the value in cell B6, reduce the
number of decimal places to two, and then use the Fill handle to copy this for-
mula across the columns to the cell range D6:G6.
7. Position the cell cursor in cell B7 to build the original PMT formula and then
click the Insert Function button on the Formula bar.
8. Type loan payment in the Search for Function text box in the Insert Function
dialog box and then press Enter to search for the PMT function.
9. With the PMT function highlighted in the Select a Function list box, select OK
to close the Insert Function dialog box and open the Function Arguments
dialog box.
10. Move the Function Arguments dialog box to the right, out of the way.
The first argument to specify is the Rate argument, for which you will select cell
B6 that is linked to the starting interest rate in cell B3. Note that you must con-
sider what part of this cell reference, if any, should be adjusted when you copy
this formula down the rows of this table (from 7 through 16) and then across the
columns (from B through G) and adjust its reference accordingly.
Note that when you copy the PMT formula down the rows of the table, you defi-
nitely do not want Excel to adjust the row number because all of the interest
rates are in row 6, meaning that this part of the cell reference must be absolute.
When you then copy the PMT formula to columns on the right, you do, however,
need the column letter to be adjusted in order to pick up the quarter percentage
point entered into the succeeding columns in row 6, so you do need to keep this
part of the cell reference relative. Therefore, for this argument, you need to con-
vert B6 from its completely relative reference to the mixed cell reference, B$6
(column adjusted but not the row).
In addition, because the starting interest rate you enter into cell B3 and bring for-
ward to cell B6 is a yearly rate and the PMT function calculates the monthly pay-
ment, you need to divide this yearly interest amount by 12 so that the Rate
argument represents a monthly portion.
11. With the insertion point in the Rate text box, click cell B6 to select and enter its
reference, and then press F4 twice to convert it to the mixed reference, B$6,
before you type /12 before you press Tab.
B$6/12 now appears in the Rate argument text box.
The Nper argument picks up the single loan period value entered in cell B4.
Because this value acts a constant in the table, all the loan payments calculated
by the PMT function refer to it. Therefore, this reference has to be completely
absolute, $B$4, so that Excel adjusts neither its column letter nor its row number
in any of the copies. In addition, because this period is in years (30 years to
begin with), you need to multiple it by 12 so that the PMT function is dealing
with the total number of months.
12. With the insertion point in the Nper text box, click cell B4, and then press F4
once to convert its reference to $B$4. Type *12 before you press Tab.