Two Variables Data Table in Excel

 

 

In a previous article I showed the benefits of the data tables (Data Table in Microsoft Excel) as an option of What-If analysis. This time we review a classic example of the use of a table of data for analysis of two variables.

 

The scenario is as follows. Suppose I want to apply for a personal loan from a bank, I’m doing a research with several banks and each of them has given me an interest rate as well as several different payment terms. The first case that I investigated is:

Two Variables Data Table Initial Data

Two Variables Data Table Instead of making the calculation for each option interest rate and term, adequately I will accommodate the data to form a data table. In the rows I will specify different rates of interest and in the columns possible terms.

Rows and Columns for the Data Table

 

To create the data table with two variables I will select the range B4: G11 and I go to the Data tab and click the “What-If” button to select whether the “Data Table” option. Within the dialog I must choose each of the input cells for the data table as follows:

What-If Data Table Dialog Box

When click OK the data table is generated

Two Variable Data Table Results

In this data table can observe what we expected, the higher is the term the monthly payments are reduced and the greater is the interest rate your monthly payment increases. With this information I can properly analyze and choose the option that best fits my chances of monthly payment. (Source: Excel Total)