Kition
sheetFinance & Budgets

Mortgage Rate Sensitivity

A two-variable data table of monthly payments for every loan size and interest rate, shaded from affordable to stretched.

Mortgage Rate Sensitivity — Sheet sample rendered by Kition

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 commands
showcase.yaml70 lines
steps:
  - { 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 build
  • results.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.png within the render tolerance

Related

More Sheet samples
sheetFinance & Budgets

Loan Amortization Schedule

A five-year car loan paid off month by month, with PMT, IPMT and PPMT splitting every payment into interest and principal.

1 sheet; used range A1:H69
sheetFinance & Budgets

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.

1 sheet; used range A1:D20
sheetFinance & Budgets

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.

1 sheet; used range A1:F20
sheetFinance & Budgets

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.

1 sheet; used range A1:N25