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

382 The PMT Function
You need to convert the relative cell reference B4 to the absolute refer- ence $B$4 so that Excel adjusts neither the row number nor the column letter when you copy the PMT formula down the rows and across the columns of the table. Because the term in B3 (which is then brought for- ward to cell B6) is an annual period, but you want to know the monthly loan payment, you need to convert the yearly periods to monthly peri- ods by multiplying the value in cell B4 by 12.
12. Click the Pv text box, click A7 to insert this cell reference in this text box, and then press F4 three times to convert the relative reference A7 to the mixed reference $A7 (column absolute, row relative).
You need to convert the relative cell reference A7 to the mixed reference $A7 so that Excel won’t adjust the column letter when you copy the PMT formula across each column of the table, but will adjust the row number when you copy the formula down across its rows.
13. Click OK to insert the formula =PMT(B$6/12,$B$4*12,$A7) in cell B7. Now you’re ready to copy this original PMT formula down and then over
to fill in the entire Loan Payments table.
14. Drag the Fill handle on cell B7 down until you extend the fill range to cell B16 and then release the mouse button.
After you’ve copied the original PMT formula down to cell B16, you’re ready to copy it to the right to G16.
15. Drag the Fill handle to the right until you extend the fill range B7:B16 to cell G16 and then release the mouse button.
After copying the original formula with the Fill handle, be sure to widen columns B through G sufficiently to display their results. (You can do this in one step by dragging through the headers of these columns and then double-clicking the right border of column G.)
After you’ve created a loan table like this, you can then change the begin- ning principal or interest rate, as well as the term to see what the payments would be under various other scenarios. You can also turn on the Manual Recalculation so that you can control when the Loan Payments table is recal- culated.
For information on how to switch to manual recalculation and use this mode to control when formulas are recalculated, see Book III, Chapter 1. For infor- mation on how to protect the worksheet so that users can input new values only into the three input cells (B2, B3, and B4) to change the starting loan amount, interest rate, and the term of the loan, see Book IV, Chapter 1.