Financial
Sensitivity analysis table with formulas: mixed references ($A3 and B$1)
Build live 1-variable and 2-variable financial sensitivity grids (e.g., Interest Rate x Loan Term) with mixed $ references that recalculate instantly anywhere.
Build a sensitivity grid with mixed references
A formula grid makes each scenario explicit without requiring a What-If Data Table command.
Writing a single mixed-reference formula locks the row input column ($A3 for Interest Rate), locks the column header row (B$1 for Term Years), and locks fixed assumptions ($A$1 for Loan Principal)—letting one formula fill an entire 2D sensitivity grid cleanly.
| Loan $300k ($A$1) | 15 Years (B$1) | 30 Years (C$1) |
|---|---|---|
| 5.0% nominal annual rate ($A2) | $2,372.38 / mo | $1,610.46 / mo |
| 6.0% nominal annual rate ($A3) | $2,531.57 / mo | $1,798.65 / mo |
| 7.0% nominal annual rate ($A4) | $2,696.48 / mo | $1,995.91 / mo |
Two-way PMT sensitivity grid formula
Enter the shown 6% formula in B3 and copy across columns and down rows: $A3 stays on Column A, B$1 stays on Row 1, and $A$1 stays on Principal.
=ROUND(-PMT($A3 / 12, B$1 * 12, $A$1), 2)Compare 30-year monthly payment at 6% nominal annual rate
Shifting one column right from B$1 (15 yrs) to C$1 (30 yrs) cuts monthly cash outflow from $2,531.57 to $1,798.65.
=ROUND(-PMT($A3 / 12, C$1 * 12, $A$1), 2)Inputs and interpretation
The rate is nominal annual with monthly compounding; PMT assumes equal end-of-month payments, no fees and full repayment. A longer term lowers the payment but may increase total interest. Copy B3 upward to B2 for the 5% scenario and across/down for other scenarios; keep the output outside the input cells.
Try the example
Use the original values or change them to test your understanding.