Mortgage Rate Sensitivity
A two-variable data table of monthly payments for every loan size and interest rate, shaded from affordable to stretched.
The model is one PMT formula fed by three inputs. `data.dataTable` substitutes six loan sizes (the column input) and six rates (the row input) into it and writes the 36 payments as values, the way Excel's What-If data table does. A colour scale runs from green to red, payments over the household's $2,400 comfort limit are outlined in bold, and the cell that matches the current inputs is outlined by an expression rule.
Steps
50 commandssteps:
- { command: range.setValues, params: { range: A1, values: [["Mortgage Payment Sensitivity"], ["Monthly principal and interest for a 30-year fixed loan"]] } }
- command: range.setValues
params:
range: A4
values:
- [Loan amount, 350000]
- [Interest rate, 0.065]
- [Term (years), 30]
- [Monthly payment, "=PMT(B5/12,B6*12,-B4)"]
- [Comfort limit, 2400]
- command: range.setValues
params:
range: D3
values:
- [null, Interest rate]
- ["=B7", 0.055, 0.06, 0.065, 0.07, 0.075, 0.08]
- [250000]
- [300000]
- [350000]
- [400000]
- [450000]
- [500000]
- { command: data.dataTable, params: { range: "D4:J10", rowInput: B5, columnInput: B4 }, as: table }
- { command: home.fontSize, params: { range: A1, size: 20 } }
- { command: home.bold, params: { range: A1 } }
- { command: home.fontColor, params: { range: A1, color: "#1e3a8a" } }
- { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
- { command: home.fontColor, params: { range: "B4:B6", color: "#1d4ed8" } }
- { command: home.fontColor, params: { range: "B8", color: "#1d4ed8" } }
- { command: home.numberFormat, params: { range: "B4", format: "$$#,##0" } }
- { command: home.numberFormat, params: { range: "B5", format: "0.00%" } }
- { command: home.numberFormat, params: { range: "B7:B8", format: "$$#,##0.00" } }
- { command: home.bold, params: { range: "A7:B7" } }
- { command: home.fillColor, params: { range: "A4:B8", color: "#eff6ff" } }
- { command: home.fillColor, params: { range: "A7:B7", color: "#dbeafe" } }
- { command: home.borders, params: { range: "A4:B8", preset: outside, color: "#93c5fd" } }
- { command: home.mergeCenter, params: { range: "E3:J3" } }
- { command: home.fillColor, params: { range: "E3:J4", color: "#1e3a8a" } }
- { command: home.fontColor, params: { range: "E3:J4", color: "#ffffff" } }
- { command: home.bold, params: { range: "E3:J4" } }
- { command: home.numberFormat, params: { range: "E4:J4", format: "0.0%" } }
- { command: home.alignCenter, params: { range: "E4:J4" } }
- { command: home.fillColor, params: { range: "D5:D10", color: "#1e3a8a" } }
- { command: home.fontColor, params: { range: "D5:D10", color: "#ffffff" } }
- { command: home.bold, params: { range: "D5:D10" } }
- { command: home.numberFormat, params: { range: "D5:D10", format: "$$#,##0" } }
- { command: home.numberFormat, params: { range: "D4", format: "\"Loan\";\"Loan\"" } }
- { command: home.fillColor, params: { range: "D4", color: "#172554" } }
- { command: home.fontColor, params: { range: "D4", color: "#bfdbfe" } }
- { command: home.italic, params: { range: "D4" } }
- { command: home.numberFormat, params: { range: "E5:J10", format: "#,##0" } }
- { command: home.alignCenter, params: { range: "E5:J10" } }
- { command: home.conditionalFormat, params: { range: "E5:J10", rule: { type: colorScale, colors: ["#bbf7d0", "#fef9c3", "#fecaca"] } } }
- { command: home.conditionalFormat, params: { range: "E5:J10", rule: { type: expression, formula: "=E5>$B$8", style: { font: { bold: true } } } } }
- { command: home.conditionalFormat, params: { range: "E5:J10", rule: { type: expression, formula: "=AND(E$4=$B$5,$D5=$B$4)", style: { font: { bold: true, underline: Single, color: { Rgb: 1981066 } }, borders: { left: { style: Medium, color: { Rgb: 1981066 } }, right: { style: Medium, color: { Rgb: 1981066 } }, top: { style: Medium, color: { Rgb: 1981066 } }, bottom: { style: Medium, color: { Rgb: 1981066 } } } } } } }
- { command: home.borders, params: { range: "E5:J10", preset: inside, color: "#ffffff" } }
- { command: range.setValues, params: { range: D12, values: [["Bold: above the comfort limit. Outlined: today's inputs. Values written by the What-If data table."]] } }
- { command: home.italic, params: { range: "D12" } }
- { command: home.fontColor, params: { range: "D12", color: "#6b7280" } }
- { command: home.fontSize, params: { range: "D12", size: 9 } }
- { command: home.columnWidth, params: { cols: "A:A", width: 112 } }
- { command: home.columnWidth, params: { cols: "B:B", width: 84 } }
- { command: home.columnWidth, params: { cols: "C:C", width: 24 } }
- { command: home.columnWidth, params: { cols: "D:D", width: 80 } }
- { command: home.columnWidth, params: { cols: "E:J", width: 62 } }
- { command: home.rowHeight, params: { rows: "5:10", height: 22 } }
- { command: home.alignMiddle, params: { range: "D5:J10" } }
- { command: sheet.read, params: { range: "E5:J10" }, as: payments }
- { command: sheet.read, params: { range: "B7" }, as: current }Checks
5 checks on every buildresults.table= {"mode":"twoVariable","static":true,"cells":36}results.current.values.0.0= {"approx":2212.238082,"tol":0.0001}results.payments.values= [[{"approx":1419.472503,"tol":0.0001},{"approx":1498.876313,"tol":0.0001},{"approx":1580.170059,"tol":0.0001},{"approx":1663.256238,"tol":0.0001},{"approx":1…- Diagnostics: none, the kit's own check finds nothing wrong
- The saved document opens again, and its thumbnail matches
thumb.pngwithin the render tolerance
Related
More Sheet samplesLoan Amortization Schedule
A five-year car loan paid off month by month, with PMT, IPMT and PPMT splitting every payment into interest and principal.
Mortgage Comparison
Three ways to finance the same house side by side, from the monthly bill to the interest paid over the life of the loan.
Break-Even Analysis
How many coffee drippers a small pottery has to sell each month to cover its costs, with the revenue and cost lines crossing on a chart.
12-Month Cash Flow Forecast
A bakery's year of cash in and cash out, month by month, with running balances, a colour scale and a warning when cash dips under the buffer.