Comparing Loans in Excel With a Two-Variable Data Table
A data table fills 28 payment scenarios from one PMT formula, and it is the least-used feature in Excel for the amount of work it saves. Here is the exact setup, the corner cell rule that trips everyone, and the calculation setting that stops it freezing your workbook.
One Formula, Twenty-Eight Answers
A two-variable data table takes a single formula and recalculates it across a grid of two inputs. Seven interest rates down the side, four terms across the top, and Excel fills twenty-eight payment figures from one PMT.
It lives under Data, What-If Analysis, Data Table, and almost nobody uses it. The setup is fiddly in exactly one respect, and once you know that part it takes thirty seconds.
Try the mortgage calculatorCheck any single cell of the table against a full amortisation before trusting the grid.Build the model first
Swipe sideways to compare columns.
| Cell | Contents |
|---|---|
| B1 | Loan amount: 320000 |
| B2 | Annual rate: 6.75% |
| B3 | Term in years: 30 |
| B4 | Monthly payment: =PMT(B2/12, B3*12, -B1) |
| B5 | Total interest: =B4*B3*12-B1 |
The data table will overwrite B2 and B3 repeatedly with each combination and record what B4 returns. So the model has to be a real, working model driven by those two cells. Any value typed directly into the payment formula will not respond.
Lay out the grid
Swipe sideways to compare columns.
| D | E | F | G | H | |
|---|---|---|---|---|---|
| 8 | =B4 | 15 | 20 | 25 | 30 |
| 9 | 6.00% | ||||
| 10 | 6.25% | ||||
| 11 | 6.50% | ||||
| 12 | 6.75% |
Then select the whole block including the headers and the corner, D8 to H15, and go to Data, What-If Analysis, Data Table. Row input cell is B3, the term, because the terms run across the row. Column input cell is B2, the rate, because the rates run down the column. Press OK.
What comes back
Swipe sideways to compare columns.
| Rate | 15 years | 20 years | 25 years | 30 years |
|---|---|---|---|---|
| 6.00% | $2,700 | $2,293 | $2,062 | $1,919 |
| 6.25% | $2,744 | $2,339 | $2,111 | $1,970 |
| 6.50% | $2,788 | $2,386 | $2,161 | $2,023 |
| 6.75% | $2,832 | $2,433 | $2,211 | $2,076 |
| 7.00% | $2,876 | $2,481 | $2,262 | $2,129 |
| 7.25% | $2,921 | $2,529 | $2,313 | $2,183 |
| 7.50% | $2,966 | $2,578 | $2,365 | $2,237 |
Now change the corner cell to =B5 and rerun, and the same grid returns total interest instead. This is where the exercise earns its keep, because the payment table and the interest table tell opposite stories.
Swipe sideways to compare columns.
| Rate | 15 years | 20 years | 25 years | 30 years |
|---|---|---|---|---|
| 6.00% | $166,062 | $230,219 | $298,529 | $370,682 |
| 6.50% | $181,758 | $252,600 | $328,199 | $408,142 |
| 7.00% | $197,725 | $275,430 | $358,508 | $446,428 |
| 7.50% | $213,959 | $298,696 | $389,432 | $485,495 |
The setting that stops your workbook freezing
Data tables recalculate every time anything in the workbook changes, and they recalculate every cell in the grid. A few small tables are fine. Several large ones in a model with heavy formulas will make the file unusable.
Go to Formulas, Calculation Options, and choose Automatic Except for Data Tables. Everything else recalculates normally, and the tables refresh only when you press F9. This is the correct setting for any workbook containing more than one data table.
The rules a data table imposes
- The input cells must be on the same sheet as the table. A data table cannot point at a rate cell on another tab, which is the most common reason the command greys out.
- The output is an array formula. You cannot edit or delete individual cells inside the grid; you delete the whole block or nothing.
- One variable, not two, means a simpler layout: inputs in a column, formula one row up and one column right, and only the column input cell filled in.
- The corner cell is visible and ugly. Format it with a custom number format of ;;; to hide its contents while keeping the reference intact.
- Values pasted from a data table are just numbers. If you want a static snapshot for a report, copy and paste as values into a separate range.
When to use something else
Swipe sideways to compare columns.
| Question | Tool |
|---|---|
| What does the answer look like across a range of two inputs? | Two-variable data table |
| What input gives me exactly this answer? | Goal Seek |
| What combination is best subject to constraints? | Solver |
| I want a live grid that recalculates without F9 | A formula grid with mixed references |
| I need more than two inputs varied | Scenario Manager, or a table of scenarios in rows |
The last alternative is worth knowing. For PMT specifically you can skip the data table entirely: put =PMT($D9/12, E$8*12, -$B$1) in E9 and copy across and down. Mixed references do the same job, recalculate normally, and can be edited cell by cell. Data tables earn their place when the underlying model is more than one formula deep.
Try the auto loan calculatorRun the same rate and term comparison on a shorter-term loan where the trade-offs differ.What the table does not include
- Fees. PMT works on the note rate, so a loan with $6,000 of points and one with none look identical in the grid. Compare on APR, or add a fee row to the model and compute an effective rate.
- Escrow. Taxes and insurance are usually collected alongside the payment and are not part of the loan. The grid understates what leaves your account each month.
- Whether you qualify. A 15-year payment of $2,832 has to pass a debt-to-income test that a 30-year payment of $2,076 might not.
- Rate availability. Not every rate is offered at every term, and shorter terms usually price below longer ones, so the top-left corner of a real quote sheet is better than this grid suggests.
- What you would do with the difference. Taking the 30-year loan and investing the $756 monthly difference is a real alternative, and whether it beats the 15-year depends on a return assumption the grid has no view on.
- Prepayment. A 30-year loan paid on a 15-year schedule costs about the same interest as a 15-year loan and keeps the lower required payment as an option.
Why does my data table show the same value in every cell?
The corner cell does not reference the output. It must contain =B4, pointing at the cell your model computes, not a typed number and not a copy of the PMT formula. That reference is how Excel knows what to recalculate.
Why is the Data Table command greyed out?
Usually because your input cells are on a different sheet from the table. Data tables require the inputs and the grid on the same worksheet. It is also greyed out if the selection does not include both the header row and the header column.
Which is the row input cell and which is the column input cell?
The row input cell corresponds to the values running across the top row; the column input cell corresponds to the values running down the left column. Terms across the top means the term cell is the row input. Getting these the wrong way round produces plausible-looking nonsense, so check one cell by hand.
Why has my workbook become slow?
Data tables recalculate on every change to the workbook. Set Formulas, Calculation Options to Automatic Except for Data Tables, then refresh with F9 when you want the grid updated. On a model with several tables this is the difference between instant and unusable.
Can I delete one cell inside the table?
No. The results are a single array, so Excel refuses partial edits. Delete the entire result range and rerun the command, or convert the results to values first with copy and paste special.
Can I vary three inputs?
Not in one data table, which is limited to two. Either run several two-variable tables at different values of the third input, or lay the scenarios out as rows with one column per input and one formula column. The row approach is less elegant and far easier to audit.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team