In this session, we are going to look how Excel Data Tables work within What-If analysis.
Data Tables is a way for us to interject alternative values into a calculation without the need to recreate the
calculation.
Excel through the use of a data tables will essentially take the alternatives and put them into the formula and recalculate it for us.
We have a simple table with three values ( mortgage amount, interest rate & term in months). Below we have a list of alternative interest rates.
What I want to know is how much my monthly payment is going to be, based on the three given values. I want to know what that payment would be like if a varied up the percentage rates. Also, I want to be able to change the mortgage amount or number of months on the fly if I want to.
We can recreate the formula that figures out the monthly payments 8 different times OR we can use our table from B7 to C14 range as our data table and Excel will do the rest for us.
There are two different types of data tables:
- Single variable data table - meaning there is only a single set of alternatives
- Multi variable data table - meaning there are two or more different sets of alternative values
In our example, we have a single variable data table, which has to be set up in a very specific way.
The very first column in the table has to be the list of alternative values, in our case is the percentage rate.
The second column is where the original formula is going to be placed and down bellow, it is where excel is going to recalculate all for us automatically based on the alternatives and original formula.
In the second column, the first row we are going to put the formula =-pmt(C3/12,C4,C2)
We put a negative value in front of the formula just to put the value positive.
In our case, we have to pay roughly $17k for a $180k mortgage amount for an 8% interest rate for the next 200 months.
So now we have the single variable table set up, first column alternative values, second column first row and down bellow the excel will fill next for me. Once it's set up, the rest is a very simple process.
The first step is to convert into a data table. Select the table from B7 to C14 and go to data tab > data tool section > what if analysis > data table
Since our is a single variable table, we are going to use the -Column input cell- which essentially means that we are going to take the column of alternative rates and place it into C3 to change the 8% rate where our formula is looking. And it's done.
Anytime you got a set of what-ifs, you can set up a simple table with alternatives and let excel calculate all of those alternatives for you.