Kition
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.

Mortgage Comparison — Sheet sample rendered by Kition

A 30-year and a 15-year loan with 20 % down, and a 30-year loan with 10 % down that adds mortgage insurance. PMT gives each monthly principal and interest, an IF adds insurance only below 20 % down, and the lifetime interest row shows what the lower monthly bill costs. The cheapest figure in each summary row is picked out by an expression rule, and a stacked column chart breaks every monthly bill into its parts.

Steps

36 commands
showcase.yaml57 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Mortgage Comparison"], ["One house at $485,000, three ways to pay for it"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - [Loan terms, 30-yr fixed, 15-yr fixed, 30-yr low down]
        - [Home price, 485000, "=B5", "=B5"]
        - [Down payment %, 0.2, 0.2, 0.1]
        - [Down payment, "=B5*B6", "=C5*C6", "=D5*D6"]
        - [Loan amount, "=B5-B7", "=C5-C7", "=D5-D7"]
        - [Interest rate, 0.0635, 0.056, 0.0655]
        - [Term (years), 30, 15, 30]
        - [null, null, null, null]
        - [Monthly cost, "=B4", "=C4", "=D4"]
        - [Principal & interest, "=PMT(B9/12,B10*12,-B8)", "=PMT(C9/12,C10*12,-C8)", "=PMT(D9/12,D10*12,-D8)"]
        - [Mortgage insurance, "=IF(B6<20%,B8*0.55%/12,0)", "=IF(C6<20%,C8*0.55%/12,0)", "=IF(D6<20%,D8*0.55%/12,0)"]
        - [Property tax, "=B5*1.1%/12", "=C5*1.1%/12", "=D5*1.1%/12"]
        - [Home insurance, 140, 140, 140]
        - [Total per month, "=SUM(B13:B16)", "=SUM(C13:C16)", "=SUM(D13:D16)"]
        - [null, null, null, null]
        - [Interest over the term, "=B13*B10*12-B8", "=C13*C10*12-C8", "=D13*D10*12-D8"]
        - [Total paid incl. down, "=B7+B13*B10*12", "=C7+C13*C10*12", "=D7+D13*D10*12"]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#14532d" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:D4", color: "#14532d" } }
  - { command: home.fillColor, params: { range: "A12:D12", color: "#14532d" } }
  - { command: home.fontColor, params: { range: "A4:D4", color: "#ffffff" } }
  - { command: home.fontColor, params: { range: "A12:D12", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:D4" } }
  - { command: home.bold, params: { range: "A12:D12" } }
  - { command: home.alignRight, params: { range: "B4:D4" } }
  - { command: home.alignRight, params: { range: "B12:D12" } }
  - { command: home.fontColor, params: { range: "B5:D6", color: "#1d4ed8" } }
  - { command: home.fontColor, params: { range: "B9:D10", color: "#1d4ed8" } }
  - { command: home.fontColor, params: { range: "B16:D16", color: "#1d4ed8" } }
  - { command: home.numberFormat, params: { range: "B5:D8", format: "$$#,##0" } }
  - { command: home.numberFormat, params: { range: "B6:D6", format: "0%" } }
  - { command: home.numberFormat, params: { range: "B9:D9", format: "0.00%" } }
  - { command: home.numberFormat, params: { range: "B13:D17", format: "$$#,##0.00" } }
  - { command: home.numberFormat, params: { range: "B19:D20", format: "$$#,##0" } }
  - { command: home.borders, params: { range: "A5:D10", preset: insideHorizontal, color: "#d1d5db" } }
  - { command: home.borders, params: { range: "A13:D16", preset: insideHorizontal, color: "#d1d5db" } }
  - { command: home.bold, params: { range: "A17:D17" } }
  - { command: home.borders, params: { range: "A17:D17", preset: topDoubleBottom } }
  - { command: home.fillColor, params: { range: "A19:D20", color: "#f3f4f6" } }
  - { command: home.bold, params: { range: "A19:A20" } }
  - { command: home.conditionalFormat, params: { range: "B17:D17", rule: { type: expression, formula: "=B17=MIN($B$17:$D$17)", preset: greenFillDarkGreenText } } }
  - { command: home.conditionalFormat, params: { range: "B19:D20", rule: { type: expression, formula: "=B19=MIN($B19:$D19)", preset: greenFillDarkGreenText } } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 150 } }
  - { command: home.columnWidth, params: { cols: "B:D", width: 104 } }
  - { command: insert.chart, params: { range: "A12:D16", type: column, subtype: stacked }, as: chart }
  - { command: chart.switchRowColumn, params: {} }
  - { command: chart.set, params: { title: What each monthly bill is made of, legend: bottom, at: F4, width: 430, height: 330 } }
  - { command: sheet.read, params: { range: "B13:D20" }, as: costs }

Checks

7 checks on every build
  • results.costs.values.0 = [{"approx":2414.2745372,"tol":0.000001},{"approx":3190.9106177,"tol":0.000001},{"approx":2773.3457687,"tol":0.000001}]
  • results.costs.values.1 = [0,0,{"approx":200.0625,"tol":0.000001}]
  • results.costs.values.4 = [{"approx":2998.8578705,"tol":0.000001},{"approx":3775.493951,"tol":0.000001},{"approx":3557.9916021,"tol":0.000001}]
  • results.costs.values.6 = [{"approx":481138.8333786,"tol":0.0001},{"approx":186363.9111784,"tol":0.0001},{"approx":561904.4767486,"tol":0.0001}]
  • results.costs.values.7 = [{"approx":966138.8333786,"tol":0.0001},{"approx":671363.9111784,"tol":0.0001},{"approx":1046904.4767486,"tol":0.0001}]
  • 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

Project Appraisal (NPV & IRR)

Two capital projects compared on NPV, IRR, MIRR, profitability index and payback, with a verdict for each.

1 sheet; used range A1:H20
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

Depreciation Schedule

One machine depreciated four ways, with SLN, SYD, DDB and VDB side by side and the book value each leaves behind.

1 sheet; used range A1:H19