Two-Variable Data Table in Excel

A two-variable data table helps us analyze how combining two variables impacts the overall data table. The word itself suggests two variables involved in this data table. In simple terms, when the two variables change, what is the impact on the result? For example, in the one-variable data table, only one variable changes. But here, two variables change simultaneously.

Let us take examples to see how we can create a two-variable data table in Excel.

Example #1

Assume that you are taking a bank loan and discussing your interest rate and the repayment period with the bank manager. You need to analyze the different interest rates. And, at different repayment periods, what is the monthly EMI amount you need to pay?

Also, assume that you are a salaried person, and after all your monthly commitments, you can save a maximum of ₹18,500/-.

The initial proposal forms the bank as below.

At 22% p.a. interest rate, the monthly EMI for 3 years is ₹19,095.

Create a table like this.

Now, to cell F8, give a link to cell B5 (which contains EMI calculation).

Select the data table that we have created for creating scenarios.

Go to the "Data" tab, then select "What-if Analysis" and "Data Table."

Now, click on “Data Table.” It will open up the below dialog box.

We have arranged our new tables like different interest rates vertically and other years horizontally.

In our original calculation, the interest rate is in cell B4, and the number of years is in cell B2.

Therefore, for the row input cell,give a link to B2 (that contains years. In our table, years are there horizontally). For the column input cell,please provide a link to B4 (that has the interest rate and in our table interest rate is there vertically).

Now, click on "OK." It would create a scenario table instantly.

So now, you have all the scenarios in front of you. Your monthly savings is ₹18,500 per month.

Option 1: If you do not want some spare cash.

You need to negotiate with the bank for an interest rate of 18.5% per annum for 3 years. You need to pay a monthly EMI of rupees if you can negotiate for this rate. ₹18,202.

Option 2: If you need some spare cash.

In this volatile world, you need some cash all the time. So, cannot spend all the ₹18,500 savings money for your salary.

If you want to let us say ₹3,000 per month as spare cash, you need to negotiate with the banker for a maximum of 15.5% for 3.5 years. In this case, you need to pay a monthly EMI of ₹15,499.

Such a useful tool we have in Excel. We can analyze and choose the plan or idea according to our wishes.

Example #2

Assume you are investing money in mutual funds through SIP planning. For example, monthly, you are investing ₹4,500. It would help if you analyzed to know what the return on an investment after certain years is.

You are unsure when to stop investing money and what percentage you expect.

Below are the basic details to do the sensitivity analysis.

Apply the FV function to know the future value after 25 years of investment.

Ok, the future value of your investment after 25 years is 65 lakhs.

Now you need to know at different years and at different rates what would be the return on investment. Create a table like this.

Now give a link to the cell F4 from B5 (which contains the future value for our original investment).

Select the table we have created.

Go to the "Data" tab, then select "What-if Analysis" and "Data Table."

Now, click on "Data Table." It will open up the below dialog box.

In the ROW, input cell select links to cell B2 (that contains no., years). We have selected this cell because we have created a new table. In that table, our years are in the row format horizontally.

In the COLUMN, input cell select links to cell B4 (which contains the expected return percentage). We have selected this cell because we have created a new table. In that table, our expected percentages are in column format, vertically.

Click on "OK." It will create a scenario table for you.

Look at the cells that we have highlighted. In the first attempt, we need to wait 25 years to get the sum of ₹65 lakh at a 10.5% return. However, at a 13% return rate, we get that amount in 22 years. Similarly, at a 15% return rate, we get that amount in just 20 years.

Like this, we can do a sensitivity analysis using the two-variable data table in Excel.

• We cannot undo the action (Ctrl + Z) taken by the Data Table. However, you can manually delete all the values from the table.
• We cannot delete one cell at a time because it is an array formula.
• The Data Table is a linked formula that does not require manual updating.
• It is helpful to look at the result when two variables change simultaneously.