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

380 The PMT Function
that contains a table using the PMT function to calculate loan payments for a range of interest rates (from 2.75 percent to 4.00 percent) and principals ($150,000 to $159,000). The table uses the initial principal that you enter
in cell B2, copies it to cell A7, and then increases it by $1,000 in the range A8:A16. The table uses the initial interest rate that you enter in cell B3, copies to cell B6, and then increases this initial rate by 1/4 of a percent in the range C6:G6. The term in years in cell B4 is a constant factor that is used in the entire loan payment table.
To get an idea of how easy it is to build this type of loan payment table with the PMT function, follow these steps for creating it in a new worksheet:
1. Enter the titles Loan Payments in cell A1, Principal in cell A2, Interest Rate in cell A3, and Term (in years) in cell A4.
2. Enter $150,000 in cell B2, enter 2.75% in cell B3, and enter 30 in cell B4. These are the starting values with which you build the Loan Payments
table.
3. Position the cell pointer in B6 and then build the formula =B3.
By creating a linking formula that brings forward the starting interest rate value in B3 with the formula, you ensure that the interest rate value in B6 will immediately reflect any change that you make in cell B3.
Figure 4-3:
Loan Payments table using the PMT function to calculate various loan payments.