Problem: I want to buy a car, and I want to compare eight price points and four loan terms to calculate the monthly payment amount.
Strategy: You can solve this problem by using a data table. You set up the worksheet as follows:
- Build the model shown in A1:B4 below. Cell B4 will become the top left corner cell of your what-if table.
- In cells B5:B5, enter the four possible terms you would like to compare. In cells C4:L4, enter the possible loan amounts.
- Select the rectangular range B5:L9. The upper-left corner of this range contains the formula to calculate your monthly payment.
- Loan terms along the side, loan amounts across the top.
- Select Data, What-If Analysis, Data Table. Excel will ask you to specify a row input cell. In other words, Excel will take each cell in the top row of the table and substitute it for the row input cell. Because these cells contain prices, choose cell B1 as the row input cell.
- Next, Excel wants to know where the cells in the first column of your data table should be used. Because B5:B8 contains loan terms, specify cell B3 as the Column Input Cell. Click OK.
- Each cell in the top row gets plugged into B1.
Excel will enter an array formula for you, based on the original formula in the top-left cell of the table. It will show you the monthly prices for many combinations of terms and price points.
- The formula is replicated for each cell.
If you are looking for a monthly payment of $425, you will have to either negotiate down to a price of $21,995 with a 60-month loan, $23,995 with a 66-month loan, or choose a 72-month loan.
The formulas in the table are live. You can reenter new values in the first column and row of the table in order to zoom in on possible scenarios.
Additional Details: You can also change the formula in B4, and the table will update.