Two Variable Data Table in Excel

Please sign up for the course before starting the lesson.

Two Variable Data Table in Excel

A single variable data table is very useful. But the real power lies in the two variable data tables. This allows you to create a matrix of outcomes showing what the results would be at any combination of your two variables. An example explains it best.

Below we have a property example with a monthly mortgage repayment. We can see what the repayment is (cell B8) with a repayment period of 240 months (cell B6) and a interest rate of 10% (cell B7).

But what happens if interest rates are 8% but the repayment period is 120 months. And 9% with 180 months to repay. Note that we have set up a table in B11 to F16 where the left most column has different interest rates and the top row has different repayment periods. The top, left cell of the table is linked to the repayment cells (so cell B11 is linked to cell B8)

two-variable-data-tableThe next steps are important.

To see the full course contents click here.

Back to: Online Financial Modelling, Budgeting and Forecasting Course > Running What Ifs