How to create a one-variable data table in Excel
A one-variable data table in Excel allows users to test one variable.
For example, see the image below.
The image shows the particulars of a loan. We have three main variables in the data.
- The amount of loan
- The rate of interest/profit
- The tenure of the loan (until it is paid back)
Example 1: Column Input Cells
In this example, let’s see keep the interest rate as the variable.
What is the yearly payment to be made against the loan?
1. Write the PMT function to find the yearly repayment against the loan.
= PMT (B3, B4, B2)
= PMT (Interest Rate, Periods of Repayment, Amount of Loan Today)
2. Multiply this number by the number of payments to be made.
That’s the total amount to be paid against the loan over 5 years.
So how much is the interest on the loan?
3. Subtract the amount of loan from the amount of repayment.
Everything’s good and sorted.
Now, what if you want to see how the repayments change if one variable (the interest rate) changes?
Do not re-perform the entire calculation all over again. The Data Table (What-if analysis) will do it for you.
4. List down the variable (interest rate in this case) that is to be changed.
5. Create a link by referring to the targeted output for each interest rate in the corresponding column.
We want Excel to give us the repayments for different interest rates. So, we have created a link to the repayment in the original calculation.
6. Select the Inputs table (the interest rates and the corresponding column for targeted output).
7. Go to Data Tab > Forecast > What-If Analysis Tools > Data Table.
This will take you to the Data Table dialog box.
8. In the Column Input Cell box, create a reference to the ‘Interest Rate’ from the original table.
Reference is made to the Interest rate because that is the variable in our data. We want to experiment with how the changing interest rates affect repayments.
We have created a reference in the Column Input Cell box and not the Row Input Cell box. This is because our Input data is in the form of a column and not a row.
9. All set. Hit Okay and Ta-da! 😃
Excel creates a one-variable data table to calculate the repayments for different interest rates.