Financial Tools
Mortgage and loan calculators that run entirely on your device
NoteYour data stays on your device
Everything on this page, including both the calculators and the Excel export, runs inside your own browser tab, using Python compiled to WebAssembly. Your numbers are never sent to, or stored on, any server: there is no database behind this page, and no one (including me) can see what you type. Closing the tab erases everything. The downloaded Excel file is generated live by Python on your device and contains only plain values: no formulas, no macros, no tracking, and no external links other than a link back to this page.
Plug in your own numbers to build a full amortization table for a mortgage (with breakeven rent and true monthly cost vs. equity) or a general loan (car, personal, or student, including in-school deferment). Add one-time or recurring extra payments to see how much interest and time they save, then hit Export to Excel to take the whole schedule with you. The workbook’s Inputs tab lists every assumption and explains how each column is calculated.
The first load downloads a small Python runtime (roughly 15–30 seconds on a typical connection); after that it’s cached and starts quickly.
#| '!! shinylive warning !!': |
#| shinylive does not work in self-contained HTML documents.
#| Please set `embed-resources: false` in your metadata.
#| standalone: true
#| viewerHeight: 1100
## file: app.py
"""Financial Tools: mortgage & loan calculators that run in the browser.
This app is embedded in ``reports/financial_tools.qmd`` via Shinylive and runs
entirely in the visitor's browser (Pyodide): inputs never leave the tab, and
the Excel export is generated locally by Python. The math modules and this
file are byte-identical to the copies embedded in the qmd;
``tests/test_site_app_sync.py`` enforces it.
Local native run (fast iteration, same code path):
uv run shiny run apps/financial_tools/app.py
"""
from dataclasses import replace
from datetime import date
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
from matplotlib.ticker import FuncFormatter
from shiny import App, reactive, render, req, ui
try: # flat file layout inside the in-browser (Pyodide) app bundle
from core import (
AmortizingLoan,
ExtraPayment,
RecurringExtra,
add_months,
without_extras,
)
from excel import build_workbook
from mortgage import MortgageScenario
except ImportError: # repo package layout (local `shiny run`)
from financial_tools.loans.core import (
AmortizingLoan,
ExtraPayment,
RecurringExtra,
add_months,
without_extras,
)
from financial_tools.loans.excel import build_workbook
from financial_tools.loans.mortgage import MortgageScenario
PAGE_URL = "https://brennenslaney.com/financial_tools.html"
# Reference dataviz palette (validated): categorical slots 1-4 + chart ink.
SERIES = {
"principal": "#2a78d6",
"extra": "#1baf7a",
"interest": "#eda100",
"other": "#008300",
}
INK = {
"secondary": "#52514e",
"muted": "#898781",
"grid": "#e1e0d9",
"baseline": "#c3c2b7",
"surface": "#fcfcfb",
}
CONVENTION_HELP = (
"Most US loans (mortgage, auto, student) use the **nominal** convention: "
"monthly rate = quoted annual rate ÷ 12. Some lenders compound instead "
"(**effective**: monthly rate = (1 + annual rate)^(1/12) − 1), which gives a "
"slightly lower payment for the same quoted rate. To check yours: multiply a "
"statement's beginning balance by (quoted rate ÷ 12); if that equals the "
"interest charged that month, your loan is nominal."
)
USD_TICKS = FuncFormatter(lambda x, _: f"${x:,.0f}")
TODAY = date.today()
NEXT_MONTH = add_months(TODAY.replace(day=1), 1)
def _usd(x: float) -> str:
return f"${x:,.2f}"
def _usd0(x: float) -> str:
return f"${x:,.0f}"
# ---------------------------------------------------------------------------
# UI builders shared by both tabs (p = input id prefix: "m" or "l")
# ---------------------------------------------------------------------------
def _extras_panel(p: str) -> ui.AccordionPanel:
return ui.accordion_panel(
"Extra payments",
ui.markdown(
"Extra principal keeps the payment the same and pays the loan off "
"sooner. Add as many as you like."
),
ui.tags.strong("One-time"),
ui.input_date(f"{p}_ot_date", "Paid with the payment due on/after", value=NEXT_MONTH),
ui.input_numeric(f"{p}_ot_amount", "Amount ($)", 1_000, min=1, step=100),
ui.input_action_button(f"{p}_ot_add", "Add one-time", class_="btn-sm btn-primary"),
ui.output_ui(f"{p}_ot_list"),
ui.tags.hr(),
ui.tags.strong("Recurring monthly"),
ui.input_numeric(f"{p}_rc_amount", "Amount per month ($)", 100, min=1, step=25),
ui.input_date(f"{p}_rc_start", "First month", value=NEXT_MONTH),
ui.input_checkbox(f"{p}_rc_open", "Continue until payoff", True),
ui.panel_conditional(
f"!input.{p}_rc_open",
ui.input_date(f"{p}_rc_end", "Last month", value=add_months(NEXT_MONTH, 12)),
),
ui.input_action_button(f"{p}_rc_add", "Add recurring", class_="btn-sm btn-primary"),
ui.output_ui(f"{p}_rc_list"),
ui.tags.hr(),
ui.input_action_button(f"{p}_x_undo", "Remove last added", class_="btn-sm"),
ui.input_action_button(f"{p}_x_clear", "Clear all extras", class_="btn-sm"),
)
def _convention_panel(p: str) -> ui.AccordionPanel:
return ui.accordion_panel(
"Advanced: rate convention",
ui.input_radio_buttons(
f"{p}_convention",
None,
{
"nominal": "Nominal: annual rate ÷ 12 (US standard)",
"effective": "Effective: (1 + rate)^(1/12) − 1",
},
selected="nominal",
),
ui.markdown(CONVENTION_HELP),
)
def _outputs_panel(p: str) -> list:
return [
ui.output_ui(f"{p}_boxes"),
ui.layout_column_wrap(
ui.card(
ui.card_header("Where each month's money goes"),
ui.output_plot(f"{p}_money_plot"),
),
ui.card(
ui.card_header("Loan balance over time"),
ui.output_plot(f"{p}_balance_plot"),
),
width="360px",
),
ui.card(
ui.card_header(
ui.div(
"Amortization schedule",
ui.download_button(
f"{p}_download", "Export to Excel", class_="btn-sm btn-success"
),
class_="d-flex justify-content-between align-items-center w-100",
)
),
ui.output_ui(f"{p}_table"),
),
]
app_ui = ui.page_fluid(
ui.navset_card_tab(
ui.nav_panel(
"Mortgage",
ui.layout_sidebar(
ui.sidebar(
ui.input_numeric(
"m_property_value", "Property value ($)", 400_000, min=1, step=5_000
),
ui.input_numeric("m_down_payment", "Down payment ($)", 80_000, min=0, step=1_000),
ui.input_numeric(
"m_rate", "Interest rate (% per year)", 6.5, min=0, max=30, step=0.125
),
ui.input_numeric("m_years", "Term (years)", 30, min=1, max=50),
ui.input_date("m_first_payment", "First payment date", value=NEXT_MONTH),
ui.accordion(
ui.accordion_panel(
"Ownership costs",
ui.input_numeric("m_hoa", "HOA ($/month)", 0, min=0, step=10),
ui.input_numeric(
"m_tax_rate",
"Property tax (% of value per year)",
1.0,
min=0,
max=10,
step=0.05,
),
ui.input_numeric(
"m_insurance", "Home insurance ($/month)", 150, min=0, step=10
),
ui.input_numeric("m_pmi", "PMI ($/month)", 0, min=0, step=10),
ui.tags.small(
"PMI usually applies when the down payment is under 20% "
"(often $30–$70 per $100k borrowed) and drops off "
"automatically once you owe less than 80% of the value.",
class_="text-muted",
),
ui.input_numeric(
"m_rooms", "Rentable rooms (optional)", 0, min=0, max=20
),
),
_extras_panel("m"),
_convention_panel("m"),
open=False,
),
width=330,
),
*_outputs_panel("m"),
),
),
ui.nav_panel(
"General loan",
ui.layout_sidebar(
ui.sidebar(
ui.input_numeric("l_principal", "Amount borrowed ($)", 30_000, min=1, step=1_000),
ui.input_numeric(
"l_rate", "Interest rate (% per year)", 7.0, min=0, max=40, step=0.125
),
ui.input_numeric("l_term", "Repayment term (months)", 60, min=1, max=600),
ui.tags.small(
"e.g. 60 for a typical car loan, 120 for a 10-year student loan.",
class_="text-muted",
),
ui.input_date("l_first_payment", "First payment date", value=NEXT_MONTH),
ui.accordion(
ui.accordion_panel(
"In-school deferment (student loans)",
ui.input_numeric(
"l_deferment",
"Months before repayment starts",
0,
min=0,
max=120,
),
ui.input_checkbox(
"l_subsidized", "Subsidized (no interest accrues)", False
),
ui.tags.small(
"For unsubsidized loans, interest accrues while you're in "
"school and is added to the balance when repayment begins.",
class_="text-muted",
),
),
_extras_panel("l"),
_convention_panel("l"),
open=False,
),
width=330,
),
*_outputs_panel("l"),
),
),
)
)
# ---------------------------------------------------------------------------
# Chart + table helpers
# ---------------------------------------------------------------------------
def _style_axes(ax) -> None:
ax.set_facecolor("none")
for side in ("top", "right"):
ax.spines[side].set_visible(False)
for side in ("left", "bottom"):
ax.spines[side].set_color(INK["baseline"])
ax.tick_params(colors=INK["muted"], labelsize=8)
ax.yaxis.set_major_formatter(USD_TICKS)
ax.grid(axis="y", color=INK["grid"], linewidth=0.75)
ax.set_axisbelow(True)
locator = mdates.AutoDateLocator()
ax.xaxis.set_major_locator(locator)
ax.xaxis.set_major_formatter(mdates.ConciseDateFormatter(locator))
def _legend(ax) -> None:
ax.legend(frameon=False, fontsize=8, labelcolor=INK["secondary"], loc="upper right")
def _balance_fig(rows: list[dict], baseline: list[dict] | None):
fig, ax = plt.subplots(figsize=(5.5, 3.2))
fig.patch.set_alpha(0)
if baseline is not None:
ax.plot(
[r["date"] for r in baseline],
[r["ending_balance"] for r in baseline],
color=INK["muted"],
linewidth=1.5,
linestyle="--",
label="Without extra payments",
)
ax.plot(
[r["date"] for r in rows],
[r["ending_balance"] for r in rows],
color=SERIES["principal"],
linewidth=2,
label="Balance",
)
_style_axes(ax)
ax.set_ylim(bottom=0)
if baseline is not None:
_legend(ax)
fig.tight_layout()
return fig
def _money_fig(rows: list[dict], include_other: bool):
# constrained layout so the legend can sit above the plot: the stack fills
# the whole axes, so an in-axes legend would collide with the top band
fig, ax = plt.subplots(figsize=(5.5, 3.4), layout="constrained")
fig.patch.set_alpha(0)
dates = [r["date"] for r in rows]
bands = [
("Principal (equity)", SERIES["principal"], [r["scheduled_principal"] for r in rows]),
("Extra principal", SERIES["extra"], [r["extra_principal"] for r in rows]),
("Interest", SERIES["interest"], [r["interest"] for r in rows]),
]
if include_other:
bands.append(("Other costs", SERIES["other"], [r["other_expenses"] for r in rows]))
bands = [b for b in bands if any(v > 0 for v in b[2])]
ax.stackplot(
dates,
[b[2] for b in bands],
colors=[b[1] for b in bands],
labels=[b[0] for b in bands],
linewidth=0,
)
# thin surface-colored seams between stacked bands
totals = [0.0] * len(dates)
for _, _, values in bands[:-1]:
totals = [t + v for t, v in zip(totals, values)]
ax.plot(dates, totals, color=INK["surface"], linewidth=1.2)
_style_axes(ax)
ax.set_ylim(bottom=0)
fig.legend(
loc="outside upper center",
ncols=2,
frameon=False,
fontsize=8,
labelcolor=INK["secondary"],
)
return fig
def _html_table(rows: list[dict], columns: list[tuple[str, str, str]]) -> ui.TagChild:
def cell(value, kind):
if value is None:
return ""
if kind == "usd":
return _usd(value)
if kind == "pct":
return f"{value:.1%}"
return str(value)
head = ui.tags.tr(*[ui.tags.th(header) for _, header, _ in columns])
body = [
ui.tags.tr(
*[
ui.tags.td(
cell(row.get(key), kind),
style="text-align: right; white-space: nowrap;",
)
for key, _, kind in columns
]
)
for row in rows
]
return ui.div(
ui.tags.table(
ui.tags.thead(head), ui.tags.tbody(*body), class_="table table-sm table-striped"
),
style="max-height: 420px; overflow: auto;",
)
UI_COLUMNS_MORTGAGE = [
("payment_number", "#", "int"),
("date", "Date", "date"),
("payment", "P&I", "usd"),
("interest", "Interest", "usd"),
("scheduled_principal", "Principal", "usd"),
("extra_principal", "Extra", "usd"),
("other_expenses", "Other", "usd"),
("total_payment", "Total paid", "usd"),
("accounting_expense", "True cost", "usd"),
("ending_balance", "Balance", "usd"),
]
UI_COLUMNS_LOAN = [
("payment_number", "#", "int"),
("date", "Date", "date"),
("phase", "Phase", "text"),
("payment", "Payment", "usd"),
("interest", "Interest", "usd"),
("scheduled_principal", "Principal", "usd"),
("extra_principal", "Extra", "usd"),
("ending_balance", "Balance", "usd"),
]
XLSX_COLUMNS_MORTGAGE = UI_COLUMNS_MORTGAGE[:6] + [
("other_expenses", "Other expenses", "usd"),
("total_payment", "Total payment", "usd"),
("accounting_expense", "Accounting expense", "usd"),
("equity_share_of_total", "Equity share of total", "pct"),
("cumulative_interest", "Cumulative interest", "usd"),
("ending_balance", "Ending balance", "usd"),
]
XLSX_COLUMNS_LOAN = UI_COLUMNS_LOAN[:7] + [
("cumulative_interest", "Cumulative interest", "usd"),
("ending_balance", "Ending balance", "usd"),
]
SHARED_NOTES = [
"Monthly rate r: nominal convention = annual rate / 12; "
"effective = (1 + annual rate)^(1/12) - 1.",
"Monthly payment = balance x r / (1 - (1 + r)^-term) (the standard annuity formula).",
"Interest (each month) = beginning balance x r. Principal = payment - interest.",
"Extra principal reduces the balance directly; the payment stays the same, "
"so the loan ends early and the final payment shrinks.",
"Every cell in this file is a pasted value, not a live formula. To recalculate, "
"change the inputs on the web page (linked above) and export again.",
]
MORTGAGE_NOTES = [
"Loan amount = property value - down payment.",
"Other expenses = HOA + property tax rate x property value / 12 + insurance + PMI. "
"PMI is charged only while the balance is at least 80% of the original property value.",
"Accounting expense ('true cost') = interest + other expenses - the money that does "
"not build equity. Breakeven rent is the month-1 accounting expense "
"(divided by rentable rooms for the per-room figure).",
] + SHARED_NOTES
LOAN_NOTES = [
"During deferment nothing is paid; unsubsidized loans accrue interest that is added "
"to the balance when repayment begins (subsidized loans accrue nothing).",
] + SHARED_NOTES
def _extras_inputs(extras: list[ExtraPayment], recurring: list[RecurringExtra]) -> list[tuple]:
out: list[tuple] = []
for i, e in enumerate(extras, 1):
out.append((f"One-time extra payment {i}", f"{e.date.isoformat()}: {_usd(e.amount)}", "text"))
for i, r in enumerate(recurring, 1):
until = r.end_date.isoformat() if r.end_date else "payoff"
out.append(
(
f"Recurring extra payment {i}",
f"{_usd(r.amount)}/month from {r.start_date.isoformat()} until {until}",
"text",
)
)
if not out:
out.append(("Extra payments", "none", "text"))
return out
# ---------------------------------------------------------------------------
# Server
# ---------------------------------------------------------------------------
def server(input, output, session):
def _extras_state(p: str):
onetime = reactive.value(())
recurring = reactive.value(())
order: list[str] = [] # which list each add went to, for "remove last"
@reactive.effect
@reactive.event(input[f"{p}_ot_add"])
def _add_onetime():
amount = input[f"{p}_ot_amount"]()
when = input[f"{p}_ot_date"]()
req(amount, when, amount > 0)
onetime.set(onetime() + (ExtraPayment(when, float(amount)),))
order.append("ot")
@reactive.effect
@reactive.event(input[f"{p}_rc_add"])
def _add_recurring():
amount = input[f"{p}_rc_amount"]()
start = input[f"{p}_rc_start"]()
req(amount, start, amount > 0)
end = None if input[f"{p}_rc_open"]() else input[f"{p}_rc_end"]()
recurring.set(recurring() + (RecurringExtra(start, float(amount), end),))
order.append("rc")
@reactive.effect
@reactive.event(input[f"{p}_x_undo"])
def _undo():
if not order:
return
if order.pop() == "ot":
onetime.set(onetime()[:-1])
else:
recurring.set(recurring()[:-1])
@reactive.effect
@reactive.event(input[f"{p}_x_clear"])
def _clear():
order.clear()
onetime.set(())
recurring.set(())
@output(id=f"{p}_ot_list")
@render.ui
def _onetime_list():
items = [ui.tags.li(f"{e.date.isoformat()}: {_usd0(e.amount)}") for e in onetime()]
return ui.tags.ul(*items, class_="small mb-0") if items else None
@output(id=f"{p}_rc_list")
@render.ui
def _recurring_list():
items = []
for r in recurring():
until = r.end_date.isoformat() if r.end_date else "payoff"
items.append(
ui.tags.li(f"{_usd0(r.amount)}/mo, {r.start_date.isoformat()} → {until}")
)
return ui.tags.ul(*items, class_="small mb-0") if items else None
return onetime, recurring
m_onetime, m_recurring = _extras_state("m")
l_onetime, l_recurring = _extras_state("l")
def _box(title: str, value: str, note: str = "") -> ui.TagChild:
return ui.value_box(title, value, note)
# ----------------------------- Mortgage -----------------------------
@reactive.calc
def m_scenario() -> MortgageScenario:
for name in (
"m_property_value",
"m_down_payment",
"m_rate",
"m_years",
"m_hoa",
"m_tax_rate",
"m_insurance",
"m_pmi",
"m_rooms",
):
req(input[name]() is not None)
req(input.m_first_payment(), input.m_property_value() > 0)
req(0 <= input.m_down_payment() < input.m_property_value())
return MortgageScenario(
property_value=float(input.m_property_value()),
down_payment=float(input.m_down_payment()),
annual_rate=float(input.m_rate()) / 100,
term_months=int(input.m_years()) * 12,
first_payment_date=input.m_first_payment(),
rate_convention=input.m_convention(),
hoa_monthly=float(input.m_hoa()),
property_tax_rate=float(input.m_tax_rate()) / 100,
insurance_monthly=float(input.m_insurance()),
pmi_monthly=float(input.m_pmi()),
rentable_rooms=int(input.m_rooms()),
extra_payments=list(m_onetime()),
recurring_extras=list(m_recurring()),
)
@reactive.calc
def m_rows() -> list[dict]:
return m_scenario().schedule()
@output(id="m_boxes")
@render.ui
def m_boxes():
s = m_scenario().summary()
per_room = s["breakeven_rent_per_room"]
rent_note = f"{_usd(per_room)}/room" if per_room is not None else "All you boss"
pmi_note = f"PMI drops {s['pmi_drop_date']:%b %Y}" if s["pmi_drop_date"] else ""
saved = (
f"{_usd0(s['interest_saved'])} + {s['months_saved']} mo"
if s["interest_saved"] > 0
else "N/A"
)
return ui.layout_column_wrap(
_box(
"Monthly P&I",
_usd(s["monthly_payment"]),
f"on a {_usd0(s['loan_amount'])} loan",
),
_box(
"Total monthly cost",
_usd(s["total_monthly_first"]),
f"incl. {_usd(s['other_expenses_first'])} taxes/ins/HOA/PMI",
),
_box("Breakeven rent", _usd(s["breakeven_rent"]), rent_note),
_box(
"Total interest",
_usd0(s["total_interest"]),
f"over {s['months_to_payoff']} months",
),
_box("Paid off", f"{s['payoff_date']:%b %Y}", pmi_note),
_box("Saved by extra payments", saved, "vs. no extra payments"),
width="240px",
)
@output(id="m_money_plot")
@render.plot
def m_money_plot():
return _money_fig(m_rows(), include_other=True)
@output(id="m_balance_plot")
@render.plot
def m_balance_plot():
scenario = m_scenario()
baseline = None
if scenario.extra_payments or scenario.recurring_extras:
baseline = replace(scenario, extra_payments=[], recurring_extras=[]).schedule()
return _balance_fig(m_rows(), baseline)
@output(id="m_table")
@render.ui
def m_table():
return _html_table(m_rows(), UI_COLUMNS_MORTGAGE)
@render.download(filename=lambda: f"mortgage_{date.today():%Y-%m-%d}.xlsx")
def m_download():
scenario = m_scenario()
s = scenario.summary()
inputs = [
("Property value", scenario.property_value, "usd"),
("Down payment", scenario.down_payment, "usd"),
("Down payment %", s["down_payment_pct"], "pct"),
("Loan amount", scenario.loan_amount, "usd"),
("Interest rate (annual)", scenario.annual_rate, "pct"),
("Rate convention", scenario.rate_convention, "text"),
("Term (months)", scenario.term_months, "int"),
("First payment date", scenario.first_payment_date, "date"),
("HOA / month", scenario.hoa_monthly, "usd"),
("Property tax rate (annual, % of value)", scenario.property_tax_rate, "pct"),
("Home insurance / month", scenario.insurance_monthly, "usd"),
("PMI / month", scenario.pmi_monthly, "usd"),
("Rentable rooms", scenario.rentable_rooms, "int"),
] + _extras_inputs(scenario.extra_payments, scenario.recurring_extras)
summary = [
("Monthly P&I payment", s["monthly_payment"], "usd"),
("Other monthly costs (month 1)", s["other_expenses_first"], "usd"),
("Total monthly cost (month 1)", s["total_monthly_first"], "usd"),
("Breakeven rent (month 1)", s["breakeven_rent"], "usd"),
("Breakeven rent per room", s["breakeven_rent_per_room"], "usd"),
("PMI drops off", s["pmi_drop_date"], "date"),
("Total interest", s["total_interest"], "usd"),
("Total paid", s["total_paid"], "usd"),
("Payoff date", s["payoff_date"], "date"),
("Months to payoff", s["months_to_payoff"], "months"),
("Interest saved by extra payments", s["interest_saved"], "usd"),
("Months saved by extra payments", s["months_saved"], "months"),
]
yield build_workbook(
"Mortgage",
PAGE_URL,
inputs,
summary,
XLSX_COLUMNS_MORTGAGE,
m_rows(),
MORTGAGE_NOTES,
)
# ---------------------------- General loan ----------------------------
@reactive.calc
def l_loan() -> AmortizingLoan:
for name in ("l_principal", "l_rate", "l_term", "l_deferment"):
req(input[name]() is not None)
req(input.l_first_payment(), input.l_principal() > 0, input.l_term() >= 1)
return AmortizingLoan(
principal=float(input.l_principal()),
annual_rate=float(input.l_rate()) / 100,
term_months=int(input.l_term()),
first_payment_date=input.l_first_payment(),
rate_convention=input.l_convention(),
deferment_months=int(input.l_deferment()),
subsidized=bool(input.l_subsidized()),
extra_payments=list(l_onetime()),
recurring_extras=list(l_recurring()),
)
@reactive.calc
def l_rows() -> list[dict]:
return l_loan().schedule()
@output(id="l_boxes")
@render.ui
def l_boxes():
loan = l_loan()
s = loan.summary()
actual_months = s["months_to_payoff"]
baseline_months = actual_months
interest_saved = 0.0
if loan.extra_payments or loan.recurring_extras:
baseline = without_extras(loan).summary()
baseline_months = baseline["months_to_payoff"]
interest_saved = baseline["total_interest"] - s["total_interest"]
saved = (
f"{_usd0(interest_saved)} + {baseline_months - actual_months} mo"
if interest_saved > 0
else "N/A"
)
payment_note = (
f"on {_usd0(s['capitalized_principal'])} after deferment"
if loan.deferment_months and not loan.subsidized
else f"on {_usd0(loan.principal)} borrowed"
)
return ui.layout_column_wrap(
_box("Monthly payment", _usd(s["monthly_payment"]), payment_note),
_box(
"Total interest",
_usd0(s["total_interest"]),
"incl. interest accrued in deferment" if loan.deferment_months else "",
),
_box("Paid off", f"{s['payoff_date']:%b %Y}", f"{actual_months} payments"),
_box("Saved by extra payments", saved, "vs. no extra payments"),
width="240px",
)
@output(id="l_money_plot")
@render.plot
def l_money_plot():
return _money_fig(l_rows(), include_other=False)
@output(id="l_balance_plot")
@render.plot
def l_balance_plot():
loan = l_loan()
baseline = None
if loan.extra_payments or loan.recurring_extras:
baseline = without_extras(loan).schedule()
return _balance_fig(l_rows(), baseline)
@output(id="l_table")
@render.ui
def l_table():
return _html_table(l_rows(), UI_COLUMNS_LOAN)
@render.download(filename=lambda: f"loan_{date.today():%Y-%m-%d}.xlsx")
def l_download():
loan = l_loan()
s = loan.summary()
inputs = [
("Amount borrowed", loan.principal, "usd"),
("Interest rate (annual)", loan.annual_rate, "pct"),
("Rate convention", loan.rate_convention, "text"),
("Repayment term (months)", loan.term_months, "int"),
("First payment date", loan.first_payment_date, "date"),
("Deferment months", loan.deferment_months, "int"),
("Subsidized during deferment", loan.subsidized, "text"),
] + _extras_inputs(loan.extra_payments, loan.recurring_extras)
summary = [
("Monthly payment", s["monthly_payment"], "usd"),
("Balance at repayment start", s["capitalized_principal"], "usd"),
("Total interest", s["total_interest"], "usd"),
("Total paid", s["total_paid"], "usd"),
("Payoff date", s["payoff_date"], "date"),
("Months to payoff", s["months_to_payoff"], "months"),
]
yield build_workbook(
"General Loan",
PAGE_URL,
inputs,
summary,
XLSX_COLUMNS_LOAN,
l_rows(),
LOAN_NOTES,
)
app = App(app_ui, server)
## file: core.py
"""Fixed-rate loan amortization math for the Financial Tools page.
Pure standard-library code with no third-party imports. These files are embedded
verbatim in the in-browser app on ``reports/financial_tools.qmd`` (where they
run under Pyodide) and imported repo-side as ``financial_tools.loans``. Keep
them dependency-free; ``tests/test_site_app_sync.py`` enforces that the two
copies stay identical.
Ported from ``real_estate/mortgage.py`` (house-sale-analysis branch) and the
deferment logic of ``debt.py`` (mba-loans branch), generalized with a rate
convention toggle and start/stop recurring extra payments.
"""
from __future__ import annotations
import calendar
from dataclasses import dataclass, field, replace
from datetime import date
RATE_CONVENTIONS = ("nominal", "effective")
def add_months(d: date, months: int) -> date:
"""Add calendar months to a date, clamping the day (Jan 31 + 1mo -> Feb 28)."""
total = d.year * 12 + (d.month - 1) + months
year, month = divmod(total, 12)
day = min(d.day, calendar.monthrange(year, month + 1)[1])
return date(year, month + 1, day)
@dataclass(frozen=True)
class ExtraPayment:
"""One-off extra principal payment, applied at the first payment due on/after ``date``."""
date: date
amount: float
@dataclass(frozen=True)
class RecurringExtra:
"""Recurring monthly extra principal for payments due in ``start_date``..``end_date``
(inclusive; ``end_date=None`` runs until payoff). Overlapping entries stack."""
start_date: date
amount: float
end_date: date | None = None
def applies_on(self, due: date) -> bool:
return self.start_date <= due and (self.end_date is None or due <= self.end_date)
@dataclass
class AmortizingLoan:
"""A fixed-rate, monthly-pay amortizing loan (mortgage, auto, student, personal).
Args:
principal: Amount borrowed.
annual_rate: Quoted annual interest rate as a decimal (e.g. 0.065).
term_months: Repayment term in months (e.g. 360).
first_payment_date: Due date of the first regular payment.
rate_convention: "nominal" divides the annual rate by 12 (how US
servicers amortize); "effective" compounds, (1+r)^(1/12)-1.
payment_override: Servicer's actual payment if it differs from the
annuity formula by rounding. None computes it.
deferment_months: Months before the first payment during which nothing
is paid (e.g. in school). Interest accrues and capitalizes at
repayment start unless ``subsidized``.
subsidized: No interest accrues during deferment (subsidized student loans).
extra_payments: One-off extra principal payments.
recurring_extras: Monthly extra principal entries with start/stop dates.
Extra principal keeps the payment constant and shortens the tail (no
recast); the final payment shrinks to retire the balance. Extras dated
during deferment are applied at the first regular payment.
Example:
>>> loan = AmortizingLoan(300_000, 0.0375, 360, date(2019, 7, 1))
>>> round(loan.scheduled_payment, 2)
1389.35
"""
principal: float
annual_rate: float
term_months: int
first_payment_date: date
rate_convention: str = "nominal"
payment_override: float | None = None
deferment_months: int = 0
subsidized: bool = False
extra_payments: list[ExtraPayment] = field(default_factory=list)
recurring_extras: list[RecurringExtra] = field(default_factory=list)
def __post_init__(self) -> None:
if self.rate_convention not in RATE_CONVENTIONS:
raise ValueError(f"rate_convention must be one of {RATE_CONVENTIONS}")
@property
def monthly_rate(self) -> float:
if self.rate_convention == "effective":
return (1 + self.annual_rate) ** (1 / 12) - 1
return self.annual_rate / 12
@property
def capitalized_principal(self) -> float:
"""Balance when repayment starts: principal plus any deferment interest."""
if self.subsidized or self.deferment_months == 0:
return float(self.principal)
return self.principal * (1 + self.monthly_rate) ** self.deferment_months
@property
def scheduled_payment(self) -> float:
"""Monthly payment (annuity formula on the capitalized balance, unless overridden)."""
if self.payment_override is not None:
return self.payment_override
r = self.monthly_rate
balance = self.capitalized_principal
if r == 0:
return balance / self.term_months
return balance * r / (1 - (1 + r) ** -self.term_months)
def payment_date(self, payment_number: int) -> date:
"""Due date of the nth regular payment (1-indexed)."""
return add_months(self.first_payment_date, payment_number - 1)
def schedule(self) -> list[dict]:
"""Full amortization table, one row per month (deferment then repayment).
Returns:
List of row dicts with keys [payment_number, date, phase,
beginning_balance, payment, interest, scheduled_principal,
extra_principal, ending_balance, cumulative_interest]. ``interest``
is the interest accrued that month whether paid or capitalized;
during deferment ``payment`` is 0 and the balance grows (unless
subsidized).
"""
payment = self.scheduled_payment
r = self.monthly_rate
if self.capitalized_principal > 0 and payment <= self.capitalized_principal * r:
raise ValueError(
f"Payment {payment:.2f} does not cover first-month interest; "
"loan would negatively amortize."
)
rows: list[dict] = []
cumulative_interest = 0.0
row_number = 0
balance = float(self.principal)
for k in range(self.deferment_months):
row_number += 1
due = add_months(self.first_payment_date, k - self.deferment_months)
accrued = 0.0 if self.subsidized else balance * r
cumulative_interest += accrued
rows.append(
{
"payment_number": row_number,
"date": due,
"phase": "deferment",
"beginning_balance": balance,
"payment": 0.0,
"interest": accrued,
"scheduled_principal": 0.0,
"extra_principal": 0.0,
"ending_balance": balance + accrued,
"cumulative_interest": cumulative_interest,
}
)
balance += accrued
extras = sorted(self.extra_payments, key=lambda e: e.date)
extra_idx = 0
n = 0
# +24 headroom: a slightly-low payment_override can stretch past the stated term
while balance > 0.005 and n < self.term_months + 24:
n += 1
row_number += 1
due = self.payment_date(n)
interest = balance * r
scheduled_principal = min(payment - interest, balance)
extra = sum(e.amount for e in self.recurring_extras if e.applies_on(due))
while extra_idx < len(extras) and extras[extra_idx].date <= due:
extra += extras[extra_idx].amount
extra_idx += 1
extra = max(0.0, min(extra, balance - scheduled_principal))
ending = balance - scheduled_principal - extra
cumulative_interest += interest
rows.append(
{
"payment_number": row_number,
"date": due,
"phase": "repayment",
"beginning_balance": balance,
"payment": interest + scheduled_principal,
"interest": interest,
"scheduled_principal": scheduled_principal,
"extra_principal": extra,
"ending_balance": ending,
"cumulative_interest": cumulative_interest,
}
)
balance = ending
return rows
def summary(self) -> dict:
"""Key loan statistics derived from the schedule.
Returns:
Dict with: monthly_payment, capitalized_principal, total_paid,
total_interest, payoff_date, months_to_payoff (repayment months).
"""
rows = self.schedule()
repayment = [row for row in rows if row["phase"] == "repayment"]
return {
"monthly_payment": self.scheduled_payment,
"capitalized_principal": self.capitalized_principal,
"total_paid": sum(row["payment"] + row["extra_principal"] for row in rows),
"total_interest": rows[-1]["cumulative_interest"] if rows else 0.0,
"payoff_date": rows[-1]["date"] if rows else None,
"months_to_payoff": len(repayment),
}
def without_extras(loan: AmortizingLoan) -> AmortizingLoan:
"""The same loan with no extra payments (the baseline for savings math)."""
return replace(loan, extra_payments=[], recurring_extras=[])
def savings_vs_baseline(loan: AmortizingLoan) -> dict:
"""Interest and time saved by the loan's extra payments vs paying none.
Returns:
Dict with: interest_saved, months_saved (0/0.0 when there are no extras).
"""
if not loan.extra_payments and not loan.recurring_extras:
return {"interest_saved": 0.0, "months_saved": 0}
actual = loan.summary()
baseline = without_extras(loan).summary()
return {
"interest_saved": baseline["total_interest"] - actual["total_interest"],
"months_saved": baseline["months_to_payoff"] - actual["months_to_payoff"],
}
## file: mortgage.py
"""Mortgage scenario math: a home loan plus the monthly costs of owning.
Pure standard-library code, embedded in the in-browser Financial Tools app;
see the note in ``core.py``. Column semantics mirror Brennen's mortgage
sandbox workbook: "other expenses" are HOA + property taxes + insurance +
PMI (PMI drops once the balance falls under 80% of the original property
value), and "accounting expense" is everything paid that month that did not
become equity (interest + other expenses).
"""
from __future__ import annotations
from dataclasses import dataclass, field
from datetime import date
try: # repo package layout
from .core import AmortizingLoan, ExtraPayment, RecurringExtra, savings_vs_baseline
except ImportError: # flat file layout inside the in-browser app bundle
from core import AmortizingLoan, ExtraPayment, RecurringExtra, savings_vs_baseline
@dataclass
class MortgageScenario:
"""A fixed-rate mortgage described by the purchase, not just the note.
Args:
property_value: Purchase price of the home.
down_payment: Cash paid up front; the loan is the remainder.
annual_rate: Quoted annual mortgage rate as a decimal.
term_months: Loan term in months.
first_payment_date: Due date of the first monthly payment.
rate_convention: See ``AmortizingLoan``.
hoa_monthly: HOA dues per month.
property_tax_rate: Annual property tax as a fraction of property value.
insurance_monthly: Homeowners insurance per month.
pmi_monthly: PMI per month while the balance is at least 80% of the
original property value (0 if none).
rentable_rooms: Rooms you could rent out; used for per-room breakeven rent.
extra_payments / recurring_extras: Extra principal, as in ``AmortizingLoan``.
"""
property_value: float
down_payment: float
annual_rate: float
term_months: int
first_payment_date: date
rate_convention: str = "nominal"
hoa_monthly: float = 0.0
property_tax_rate: float = 0.0
insurance_monthly: float = 0.0
pmi_monthly: float = 0.0
rentable_rooms: int = 0
extra_payments: list[ExtraPayment] = field(default_factory=list)
recurring_extras: list[RecurringExtra] = field(default_factory=list)
@property
def loan_amount(self) -> float:
return self.property_value - self.down_payment
@property
def loan(self) -> AmortizingLoan:
return AmortizingLoan(
principal=self.loan_amount,
annual_rate=self.annual_rate,
term_months=self.term_months,
first_payment_date=self.first_payment_date,
rate_convention=self.rate_convention,
extra_payments=self.extra_payments,
recurring_extras=self.recurring_extras,
)
@property
def property_tax_monthly(self) -> float:
return self.property_tax_rate * self.property_value / 12
def other_expenses(self, ending_balance: float) -> float:
"""Non-loan monthly cost of owning; PMI included until LTV drops below 80%."""
pmi = self.pmi_monthly if ending_balance >= 0.8 * self.property_value else 0.0
return self.hoa_monthly + self.property_tax_monthly + self.insurance_monthly + pmi
def schedule(self) -> list[dict]:
"""Loan schedule extended with the ownership-cost columns.
Adds to each ``AmortizingLoan.schedule()`` row:
other_expenses, total_payment (P&I + extra principal + other
expenses), accounting_expense (interest + other expenses, the
month's true cost of owning), equity_share_of_pi and
equity_share_of_total (principal as a share of P&I / of total cash out).
"""
rows = self.loan.schedule()
for row in rows:
other = self.other_expenses(row["ending_balance"])
principal = row["scheduled_principal"] + row["extra_principal"]
total = row["payment"] + row["extra_principal"] + other
row["other_expenses"] = other
row["total_payment"] = total
row["accounting_expense"] = row["interest"] + other
row["equity_share_of_pi"] = principal / row["payment"] if row["payment"] else 0.0
row["equity_share_of_total"] = principal / total if total else 0.0
return rows
def pmi_drop_date(self) -> date | None:
"""Due date of the first payment with no PMI charge, or None if PMI never applies."""
if self.pmi_monthly <= 0:
return None
for row in self.schedule():
if row["ending_balance"] < 0.8 * self.property_value:
return row["date"]
return None
def breakeven_rent(self) -> float:
"""First-month accounting expense: the rent at which owning costs nothing
(interest + taxes + insurance + HOA + PMI; principal is equity, not cost)."""
rows = self.schedule()
return rows[0]["accounting_expense"] if rows else 0.0
def breakeven_rent_per_room(self) -> float | None:
"""Breakeven rent split across rentable rooms; None when there are no rooms."""
if self.rentable_rooms <= 0:
return None
return self.breakeven_rent() / self.rentable_rooms
def summary(self) -> dict:
"""Headline figures for the scenario.
Returns:
Dict with: loan_amount, down_payment_pct, monthly_payment (P&I),
other_expenses_first (month 1), total_monthly_first (P&I + other,
before extras), accounting_expense_first, breakeven_rent,
breakeven_rent_per_room, pmi_drop_date, total_interest, total_paid,
payoff_date, months_to_payoff, interest_saved, months_saved.
"""
loan = self.loan
loan_summary = loan.summary()
savings = savings_vs_baseline(loan)
rows = self.schedule()
first_other = rows[0]["other_expenses"] if rows else 0.0
return {
"loan_amount": self.loan_amount,
"down_payment_pct": (
self.down_payment / self.property_value if self.property_value else 0.0
),
"monthly_payment": loan_summary["monthly_payment"],
"other_expenses_first": first_other,
"total_monthly_first": loan_summary["monthly_payment"] + first_other,
"accounting_expense_first": rows[0]["accounting_expense"] if rows else 0.0,
"breakeven_rent": self.breakeven_rent(),
"breakeven_rent_per_room": self.breakeven_rent_per_room(),
"pmi_drop_date": self.pmi_drop_date(),
"total_interest": loan_summary["total_interest"],
"total_paid": loan_summary["total_paid"],
"payoff_date": loan_summary["payoff_date"],
"months_to_payoff": loan_summary["months_to_payoff"],
"interest_saved": savings["interest_saved"],
"months_saved": savings["months_saved"],
}
## file: excel.py
"""Excel export for the Financial Tools calculators.
Builds the whole workbook in memory with xlsxwriter (pure Python, so it also
runs under Pyodide in the visitor's browser; see the note in ``core.py``).
Every cell is a pasted value, not a live formula; the only hyperlink in the
file is the link back to the Financial Tools page on the Inputs sheet, and
nothing in the workbook references an external resource.
Value "kinds" drive number formats: text, usd, pct, date, int, num, months.
"""
from __future__ import annotations
import io
from datetime import date, datetime
import xlsxwriter
FORMATS = {
"usd": "$#,##0.00",
"pct": "0.000%",
"date": "yyyy-mm-dd",
"int": "#,##0",
"num": "#,##0.00",
"months": '#,##0 "months"',
}
def build_workbook(
tool_name: str,
page_url: str,
inputs: list[tuple[str, object, str]],
summary: list[tuple[str, object, str]],
columns: list[tuple[str, str, str]],
rows: list[dict],
notes: list[str],
) -> bytes:
"""Assemble the three-sheet workbook and return it as bytes.
Args:
tool_name: Calculator name shown on the Inputs sheet (e.g. "Mortgage").
page_url: URL of the Financial Tools page; the file's only hyperlink.
inputs: (label, value, kind) triples of every user input, exactly as used.
summary: (label, value, kind) triples of headline results.
columns: (row key, header, kind) triples defining the Amortization sheet.
rows: Schedule rows (dicts keyed by the ``columns`` keys).
notes: Plain-English lines explaining how the computed fields relate.
Returns:
The .xlsx file contents.
"""
buffer = io.BytesIO()
workbook = xlsxwriter.Workbook(buffer, {"in_memory": True})
workbook.set_properties(
{
"title": f"{tool_name} - Financial Tools",
"comments": f"Generated locally in your browser by {page_url}",
}
)
title_fmt = workbook.add_format({"bold": True, "font_size": 14})
header_fmt = workbook.add_format({"bold": True, "bottom": 1})
label_fmt = workbook.add_format({"bold": True})
value_fmts = {kind: workbook.add_format({"num_format": fmt}) for kind, fmt in FORMATS.items()}
value_fmts["text"] = workbook.add_format({})
def write_value(sheet, row_idx: int, col_idx: int, value: object, kind: str) -> None:
fmt = value_fmts.get(kind, value_fmts["text"])
if value is None:
sheet.write_blank(row_idx, col_idx, None, fmt)
elif isinstance(value, (date, datetime)):
sheet.write_datetime(row_idx, col_idx, value, value_fmts["date"])
elif isinstance(value, bool):
sheet.write_string(row_idx, col_idx, "yes" if value else "no", fmt)
elif isinstance(value, (int, float)):
sheet.write_number(row_idx, col_idx, value, fmt)
else:
sheet.write_string(row_idx, col_idx, str(value), fmt)
inputs_sheet = workbook.add_worksheet("Inputs")
inputs_sheet.set_column(0, 0, 46)
inputs_sheet.set_column(1, 1, 20)
inputs_sheet.write_string(0, 0, f"{tool_name} - Financial Tools export", title_fmt)
inputs_sheet.write_string(
1, 0, f"Generated {datetime.now():%Y-%m-%d %H:%M} locally in your browser at:"
)
inputs_sheet.write_url(2, 0, page_url, string=page_url)
r = 4
inputs_sheet.write_string(r, 0, "Inputs used for every number in this file", header_fmt)
inputs_sheet.write_blank(r, 1, None, header_fmt)
for label, value, kind in inputs:
r += 1
inputs_sheet.write_string(r, 0, label, label_fmt)
write_value(inputs_sheet, r, 1, value, kind)
r += 2
inputs_sheet.write_string(r, 0, "How the fields relate", header_fmt)
inputs_sheet.write_blank(r, 1, None, header_fmt)
for note in notes:
r += 1
inputs_sheet.write_string(r, 0, note)
summary_sheet = workbook.add_worksheet("Summary")
summary_sheet.set_column(0, 0, 34)
summary_sheet.set_column(1, 1, 20)
summary_sheet.write_string(0, 0, "Summary", title_fmt)
for i, (label, value, kind) in enumerate(summary, start=2):
summary_sheet.write_string(i, 0, label, label_fmt)
write_value(summary_sheet, i, 1, value, kind)
schedule_sheet = workbook.add_worksheet("Amortization")
for c, (_, header, kind) in enumerate(columns):
schedule_sheet.set_column(c, c, max(len(header) + 2, 12 if kind == "date" else 14))
schedule_sheet.write_string(0, c, header, header_fmt)
for i, row in enumerate(rows, start=1):
for c, (key, _, kind) in enumerate(columns):
write_value(schedule_sheet, i, c, row.get(key), kind)
schedule_sheet.freeze_panes(1, 0)
workbook.close()
return buffer.getvalue()
## file: requirements.txt
xlsxwriter