Two-Variable Data Table in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Create a 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.

Examples

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

You can download this Two-Variable Data Table Excel Template here – Two-Variable Data Table Excel Template

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.

Monthly EMI

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

Create a table like this.

Create a table

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

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.”

What if Analysis and Data Table

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

Two Variable Data Table Example 1-4

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).

Data Table

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

Two Variable Data Table Example 1-6

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.

ParticularsAmount
SIP Amount Monthly4500
Number of Years25
Payment Per Year12
Expected Interest Rate10.50%
Future Value???

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

FV function

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.

Create a table

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

Two Variable Data Table Example 2-3

Select the table we have created.

Two Variable Data Table Example 2-4

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

What if Analysis and Data Table

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

Data Table dialogue 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.

Data Table

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

create a scenario table

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.

Video Explanation of Two Variable Data Table

 

Things to Remember

Recommended Articles

This article is a guide to creating Two-Variable Data Table Excel. Here, we discuss creating a two-variable Data Table in Excel using examples and downloadable Excel templates. You may also look at these useful Excel tools: –