Expand the Limits of Excel - Building of Three Variable Data Tables - December 2009

Data tables are one of the most useful tools for sensitivity analysis in financial modeling. They are both powerful and easy to implement.

The majority of sensitivity tables are built using one or two variable inputs. While there is no direct way of increasing the number of inputs in Excel data tables, using nested text functions can double the number of variables to be sensitized.

LEFT, RIGHT, and MID are simple text functions that allow a user to extract the required number of characters from either left, right or the middle of the cell. So how could this be useful in creating three or even four variable data tables?

RIGHT and LEFT functions serve as a mechanism that splits up text strings into shorter text strings according to the function specifications.

For instance RIGHT(2546,2) will equal to 46. Furthermore Excel is able to recognize that the result or the “46” is a number and hence able to perform further calculations with it.

Thus a single variable can be “chopped up” into two or more variables. Potentially, it is possible to double or even quadruple the number of variables to be sensitized in a data table. Of course it is important to be mindful of data proliferation and over analyzing the information.

Here is an example of how the number of dimensions in a simple two way data table can be increased to three by using RIGHT AND LEFT functions. Let us assume that we need to calculate the monthly payment required on a $150,000 loan with an interest rate of 6% interest and due for repayment in 25 years. The desired sensitivity table should take into account the impact from variance of all three inputs: the loan amount, the interest rate, and the term.

The overall set up is not too much different much from the two variable data table. Set up the calculation formula first.

Set up the data table.

Remember, three variable data tables are very similar to regular data tables so the same do’s and don’ts apply.

1. Input cells must be on the same sheet as the data table unless you create clone cells.

2. The formula cell can be on another sheet, but it must be fed directly or indirectly by the input cells

3. The same cell cannot drive the input variables and the row and column input cells; if they do, the data table won’t work properly.

Finally, highlight the whole area.

Press ALT + d + t (or ALT + a + w +t in Excel 2007) and Excel will sensitize three variables instead of two.