Here’s the thing, though – the financial world is a fluid place, with interest rates changing moment-by-moment, and lenders offering loans that span differing lengths of time. As a consequence, you’re likely to be offered an array of loan choices. To make the decision easier, it would be great to have a table of information that shows how the monthly payment varies as the term and interest rate vary.

In a nutshell, this what Data Tables do. And here’s how we build one.

First, let’s add a blank table to our workbook with the options we’re interested in. Our range of interest rates will be added as a column, and the range of term lengths (in years) as a row:

As you can see, we’re assuming that the bank will offers us terms of 15, 20, 25 or 30 years, and that interest rates will range from 5% to 7%, stepping up .125% each time. Since Excel’s default is to show numbers with two decimal places (and we want to see three), we will need to bump up the decimal view by taking a quick trip to the Formatting Palette, expanding the “Number” pane, and clicking the “Increase Decimal” button once:

Now that I have my table, we simply do the following actions.

First, set the upper-left corner of the table (the cell between the “15” and the “5.000%”) to equal the “payment” cell from the mortgage calculation (that’s cell B5).

Second, select the entire table with the mouse, and then go to the “Data” menu and select “Table…”. The Table dialog will appear.
 

 Third, tell Excel which row input cell and column input cells we want the table to use. In this case, “row” is the loan’s term and “column “is the interest rate, so we click on cell B3 (just to the right of “Term (Years)”) for “row” and cell B4 (just to the right of “Interest Rate”) for “column”.

When the table is created, Excel will exchange the default values (30, 6%) for each of the values in the table (15 – 30, 5% - 7%).

Finally, click “OK”. Excel will crunch the numbers and present us with a new Data Table: 

The numbers are great, but the formatting could use a touch more work. Let’s select the data in the table itself (leaving out the row and column headers), go to the “Format” menu and select “Cells…”. The “Format Cells” dialog will appear.

Click on “Currency” and then select the fourth style from the top (it reads, “($1234.10)” in red).

Once we click OK, we will see something that looks like this:

Perfect.

Now, we have a table that shows, at a glance, how much the loan payment will be for each of 68 (!) distinct combinations of interest rates and terms. With this, we can quickly assess our options, and determine which combination of interest rate and loan term is most attractive.

Data Tables, as you might imagine, can be applied to all manner of problems. It’s pretty simple to envision a similar Data Table that calculates the total amount of interest paid over the life of a loan, or a table that computes the total amount of time to pay off the loan, based on payments that exceed the principal amount. You get the idea.

Another nifty thing about Data Tables is that they’re updated dynamically. That means we can change some of the values in the original calculation, and the table will update with new results. For example, we could change the amount of the loan could change from $250,000 to, say, $100,000, and the table will change to show the payment amount for each of the 68 combinations – no extra work needed!

So that’s Data Tables – just another under-appreciated feature in Excel. (And for those that are curious, I’ve made my spreadsheet from this example available as a download – just click here).

Happy calculating!