Suppose we want to see how much money we have at the end of 20 years using different interest rates. We could repeatedly change the “Return p.a.” cell (B7) and try to remember what we saw at each change in the percentage, or…
…we can use the amazing What-If Analysis: Data Table feature.
All we need to do is build our logic prototype as we have here, then use What-If Analysis to build a matrix of calculations for each interest rate we are interested in.
Begin by creating a list of interest rates you wish to calculate against.
Next, select the cell to the right of the list of interest rates that is 1 row above the first listed rate.
This cell will hold the prototype formula we created earlier in cell B7.
Since we have already created the FV formula, we will simply reference it in this cell.
The idea is that we want to repeat the prototype FV formula down this column, swapping out the percentage rate at each row with the rate listed on that same row.
Instead of creating these long, complicated formulas that use a mixture of relative and absolute references (we know how much everyone enjoys those), we can perform the following steps:
- Highlight the range of cells that encompasses the prototype formula, the listed interest rates, and the cells that will display the results (cells F2 through G19).
- Select Data (tab) -> Forecast (group) -> What-If Analysis -> Data Table.
In the Data Table dialog box, we must decide whether to use the Row Input Cell or Column Input Cell as the changing range.
As our interest rates are listed in a column, we will populate the Column Input Cell field.
What we place in that field is a reference to the address of the cell that holds the original percentage rate used by the prototype formula, cell B5.
Click OK and we have our table of results for each listed interest rate.
The feature essentially says, “For every reference of cell B5 in the prototype formula, replace it with the value(s) of the cells in the left column of the highlighted range.”