Page 399 - Excel 2013 All-in-One For Dummies

The PMT Function 381
4. Position the cell pointer in cell C6 and then build the formula =B6+.25%.
By adding 1/4 of a percent to the interest rate to the value in B6 with the formula =B6+.25% in C6 rather than creating a series with the AutoFill handle, you ensure that the interest rate value in cell C6 will always be 1/4 of a percent larger than any interest rate value entered in cell B6.
5. Drag the Fill handle in cell C6 to extend the selection to the right to cell G6 and then release the mouse button.
6. Position the cell pointer in cell A7 and then build the formula =B2. Again, by using the formula =B2 to bring the initial principal forward to
cell A7, you ensure that cell A7 always has the same value as cell B2.
7. Position the cell pointer in A8 active and then build the formula =A7+1000.
Here too, you use the formula =A7+1000 rather than create a series with the AutoFill feature so that the principal value in A8 will always be $1,000 greater than any value placed in cell A7.
8. Drag the Fill handle in cell A8 down until you extend the selection to cell A16 and then release the mouse button.
9. In cell B7, click the Insert Function button on the Formula bar, select Financial from the Or Select a Category drop-down list, and then double- click the PMT function in the Select a Function list box.
The Function Arguments dialog box that opens allows you to specify the rate, nper, and pv arguments. Be sure to move the Function Arguments dialog box to the right so that no part of it obscures the data in columns A and B of your worksheet before proceeding with the following steps for filling in the arguments.
10. Click cell B6 to insert B6 in the Rate text box and then press F4 twice to convert the relative reference B6 to the mixed reference B$6 (column relative, row absolute) before you type /12.
You convert the relative cell reference B6 to the mixed reference B$6
so that Excel does not adjust the row number when you copy the PMT formula down each row of the table, but it does adjust the column letter when you copy the formula across its columns. Because the initial inter- est rate entered in B3 (and then brought forward to cell B6) is an annual interest rate, but you want to know the monthly loan payment, you need to convert the annual rate to a monthly rate by dividing the value in cell B6 by 12.
11. Click the Nper text box, click cell B4 to insert this cell reference in this text box, and then press F4 once to convert the relative reference B4 to the absolute reference $B$4 before you type *12.
Book III Chapter 4
Financial Formulas