WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Two-Variable Data Table in Excel

Two-Variable Data Table in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to Create a Two-Variable Data Table in Excel?

Two-variable data table helps us to analyze how the combination of two different variables impact on 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. In one variable data table, only one variable changes but here two variables change simultaneously.

Examples

Let us take some 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 loan from the bank and are in discussion with the bank manager regarding your interest rate and the repayment period. You need to analyze at 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 Rs. 18 500/-.

The initial proposal forms the bank is as bellows.

Monthly EMI

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

Create a table like this.

Create a table

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

EMI calculation

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

Go to Data, 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 dialogue box.

Two Variable Data Table Example 1-4

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

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

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

Data Table

Now click on, OK. This 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 18500 per month.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 anum for 3 years. If you can negotiate for this rate, you need to pay a monthly EMI of Rs. 18202.

Option 2: If you need some spare cash.

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

If you want to let us say 3000 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 per month.

Wow!! 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. Monthly you are investing in 4500. You need to make an analysis to know what the return on an investment after certain years is.

You are not sure when to stop investing money and what is the percentage you are expecting.

Below are the basic details to do the sensitivity analysis.

Future value

Apply 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 Data, 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 dialogue box.

Data Table dialogue box

In the ROW, input cell select give the link to the cell B2 (that contains no., years). The reason why we have selected this cell because we have created a new table, and in that table, our years in the row format, i.e., horizontally.

In the COLUMN, input cell select give the link to the cell B4 (that contains the expected return percentage). The reason why we have selected this cell because we have created a new table, and in that table, our expected percentages are in column format,i.e., vertically.

Data Table

Click on Ok this will create a scenario table for you.

create a scenario table

Look at the cells that I have highlighted. In the first attempt, we need to wait for 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.

These how we can do a sensitivity analysis by using the two-variable data table in excel.

Things to Remember

  • We cannot undo the action (Ctrl + Z) taken place by the data table. However, you can manually delete all the values from the table.
  • We cannot delete once cells at a time because it is an array formula.
  • The data table is a linked formula, so it does not require manual updating.
  • This is very helpful to look at the result when two variables changes at a time.

Recommended Articles

This has been a guide to creating Two-Variable Data Table Excel. Here we discuss how to create a Two-Variable Data Table Excel using examples and downloadable excel templates. You may also look at these useful excel tools –

  • How to use PPMT Function in Excel?
  • Excel Data Table
  • MS Excel Course
  • One-Variable Data Table Excel
  • CSV Files into Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Two-Variable Data Table Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More