The first thing most people do when they need to test how changing interest rates affects loan payments is open Goal Seek. That’s usually the wrong move — especially if you want to see all outcomes at once, not just one. Goal Seek gives you a single answer, forces manual re-runs, and breaks silently when formulas shift. Worse? It won’t tell you if your model collapses at 6.8% or spikes unpredictably at 7.2%. You end up cross-checking numbers in scratch cells, copying values by hand, and losing track of which scenario used which assumption.
The Problem
You’re building a financing proposal for Acme Corp’s new warehouse expansion. Finance needs to show how monthly payments shift across 12 different interest rate scenarios (5.0% to 7.5%, in 0.25% increments) — all based on a $2.4M loan over 20 years. Your current approach? Manually typing each rate into cell B2, watching C5 recalculate, then copy-pasting the result into a list. By the time you hit 5.75%, you’ve lost count, misaligned two rows, and accidentally overwrote last month’s approved version.
| Method | Time for 10K rows | Accuracy | Difficulty for beginner |
|---|---|---|---|
| Manual entry + copy/paste | ~42 minutes | Low (human error likely) | Easy but exhausting |
| Goal Seek (12 iterations) | ~18 minutes | Medium (no audit trail) | Medium (dialogue box confusion) |
| What-if data table | 22 seconds (after setup) | High (formula-driven, live) | Medium (requires correct layout) |
| Power Query + parameters | ~6 minutes (first run) | Very high | Hard (requires PQ knowledge) |
Here’s what your raw input looks like right now — scattered, fragile, and hard to verify:
| Assumption | Value |
|---|---|
| Loan amount | $2,400,000 |
| Term (years) | 20 |
| Interest rate (B2) | 5.5% |
| Monthly payment (C5) | $17,293.48 |
The Solution
You don’t need macros or add-ins. Just four precise steps — and you’ll get a live, self-updating table that recalculates instantly when any input changes.
- Prepare your base formula. In cell E1, type
=PMT(B2/12, B3*12, -B1). That’s your single calculation using the original inputs (loan in B1, term in B3, rate in B2). Confirm it returns $17,293.48. - Build your input column. In F2:F13, list your 12 interest rates: 5.0%, 5.25%, 5.5%, ..., up to 7.5%. No formulas — just values. Keep them in a clean vertical column.
- Link the output row. In G1, enter
=E1. This tells Excel: “Whatever value appears in E1 — that’s the result I want to tabulate.” Don’t type the PMT formula here. Just reference it. - Create the data table. Select F1:G13 (that’s your header row + all inputs + blank output column). Press Alt → A → W → T. In the dialog: leave Row Input Cell blank, and set Column Input Cell to
$B$2. Click OK.
Done. Cells G2:G13 now auto-fill with monthly payments for each rate — no copy-paste, no manual updates. Change B2 to 6.0%, and the whole table refreshes. Change B1 to $2.6M? All 12 results update instantly.
| Interest Rate | Monthly Payment |
|---|---|
| 5.00% | $16,002.84 |
| 5.25% | $16,377.62 |
| 5.50% | $16,758.24 |
| 5.75% | $17,144.62 |
| 6.00% | $17,536.68 |
| 6.25% | $17,934.35 |
| 6.50% | $18,337.56 |
| 6.75% | $18,746.22 |
| 7.00% | $19,160.27 |
| 7.25% | $19,579.63 |
| 7.50% | $20,004.23 |
Counterintuitive tip: Your input cell (B2) must be referenced directly in the base formula — not via another cell. If you put =B2 in C1 and then use C1 inside PMT(), the data table fails. Excel traces only the literal cell address in the formula.
Going Further
You can nest two variables — say, interest rate and loan term — using a two-input data table. Place rates down column F (F2:F13), terms across row 1 (G1:L1), and reference both in your base formula: =PMT(F2/12, G1*12, -$B$1). Then select F1:L13 and use Alt+A+W+T again — this time fill both Row Input Cell ($B$3) and Column Input Cell ($B$2).
For sensitivity analysis, wrap outputs in conditional formatting: highlight payments > $19,000 in red (select G2:G13 → Home → Conditional Formatting → Highlight Cells Rules → Greater Than → 19000). Or add a helper column showing % change vs. baseline: in H2, =(G2-G4)/G4 (assuming 5.5% is your baseline in G4).
Need to lock results? Copy G2:G13 → Paste Special → Values. But keep the original table intact on another sheet — it’s far easier to regenerate than rebuild from scratch.
When NOT to Use This
Avoid data tables when your output depends on volatile functions like TODAY(), RAND(), or INDIRECT(). They’ll recalculate unpredictably and may freeze large workbooks. Also skip them if your model uses circular references — data tables don’t support iteration settings.
If you’re testing 500+ scenarios, data tables slow down. Switch to Power Query or a simple VBA loop — but only after confirming speed is actually an issue (test with 100 first). And never use them for client-facing reports unless you’ve hidden the input cell (B2) and protected the sheet — otherwise someone will change it and break everything.
One more: if your base formula refers to a closed workbook (e.g., '[Budget2024.xlsx]Sheet1'!$B$2), the table returns #REF!. Data tables only work with open, local references.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Data Table dialog | Alt → A → W → T | Works in Excel 2010+ |
| Recalculate all data tables | F9 | Press twice if values look stale |
| Select contiguous data range | Ctrl + A (twice) | First press selects current region; second expands to full used range |
| Toggle formula view | Ctrl + ` | Great for verifying data table cell references |
