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.
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 commandssteps:
- { 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 buildresults.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.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.
Project Appraisal (NPV & IRR)
Two capital projects compared on NPV, IRR, MIRR, profitability index and payback, with a verdict for each.
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.
Depreciation Schedule
One machine depreciated four ways, with SLN, SYD, DDB and VDB side by side and the book value each leaves behind.