Data room / Run 1, stages 3 to 5: scenarios, model, verdict

Model source

Code, 2,013 words

run-1/models/equity_flow_model.py Open the file as written

#!/usr/bin/env python3
"""Equity Flow — Stage 4 quant model.

Single-deal economics across an HPA x hold-period grid, per ASSUMPTIONS.md.
Writes markdown tables to stdout (redirect to RESULTS.md).
"""

# ---------------- parameters (see ASSUMPTIONS.md) ----------------
V0 = 625_000.0            # enrollment value
MONTHLY = 1_500.0         # disbursement
TERM_M = 84               # contracted months
COMMIT = MONTHLY * TERM_M # 126,000
FEE = 0.025 * COMMIT      # 3,150 netted from first disbursements
MTG0 = 350_000.0          # first mortgage
MTG_RATE = 0.035 / 12
MTG_N = 360
SELL_COST = 0.07
BASE_SHARE = 0.25
RATCHET = 0.01
CAP_SHARE = 0.50
HELOC_RATE = 0.085 / 12
REFI_RATE = 0.07
HURDLE = 0.08

HPAS = [-0.03, -0.02, -0.01, 0.0, 0.01, 0.02, 0.03, 0.04, 0.05, 0.06, 0.07, 0.08]
HOLDS = [1, 2, 3, 5, 7, 10, 12, 15, 20, 25, 30]


def mtg_payment(p, r, n):
    return p * r / (1 - (1 + r) ** -n)


MTG_PMT = mtg_payment(MTG0, MTG_RATE, MTG_N)


def mtg_balance(months):
    if months >= MTG_N:
        return 0.0
    r = MTG_RATE
    return MTG0 * (1 + r) ** months - MTG_PMT * (((1 + r) ** months - 1) / r)


def share_at(years):
    if years <= 7:
        return BASE_SHARE
    return min(BASE_SHARE + RATCHET * (years - 7), CAP_SHARE)


def value_at_month(g_annual, m):
    return V0 * (1 + g_annual) ** (m / 12.0)


def disbursed_months(g, exit_m, pause_on):
    """List of months (1-indexed) in which a disbursement occurs."""
    months = []
    for m in range(1, min(exit_m, TERM_M) + 1):
        if pause_on:
            last_check = (m - 1) // 3 * 3  # quarterly checks at months 0,3,6...
            if value_at_month(g, last_check) < V0 - 1e-9:
                continue
        months.append(m)
    return months


def irr_monthly(flows):
    """flows: list of (month, amount). Grid-scan for sign changes, then
    bisect; return the root nearest zero (the economically meaningful IRR
    when a t=0 fee inflow creates multiple roots)."""
    if not flows:
        return None
    def npv(r):
        return sum(a / (1 + r) ** m for m, a in flows)
    grid = [-0.45, -0.3, -0.2, -0.1, -0.05, -0.02, -0.01, -0.005, -0.002,
            -0.001, -0.0005, -0.0001, 0.0001, 0.0005, 0.001, 0.002, 0.005,
            0.01, 0.02, 0.03, 0.05, 0.08, 0.12, 0.2, 0.35, 0.6, 1.0, 2.0]
    best = None
    prev_r = prev_v = None
    for r in grid:
        v = npv(r)
        if prev_v is not None and prev_v * v <= 0:
            lo, hi = prev_r, r
            for _ in range(200):
                mid = (lo + hi) / 2
                if npv(mid) * npv(lo) <= 0:
                    hi = mid
                else:
                    lo = mid
            root = (lo + hi) / 2
            if best is None or abs(root) < abs(best):
                best = root
        prev_r, prev_v = r, v
    return None if best is None else (1 + best) ** 12 - 1


def deal(g, T, pause_on=True):
    exit_m = T * 12
    dm = disbursed_months(g, exit_m, pause_on)
    gross = MONTHLY * len(dm)
    fee_taken = min(FEE, gross)
    net_received = gross - fee_taken
    price = V0 * (1 + g) ** T
    appreciation = max(0.0, price - V0)
    share = share_at(T)
    claim = gross + share * appreciation
    escrow = max(0.0, price * (1 - SELL_COST) - mtg_balance(exit_m))
    settle = min(claim, escrow)
    homeowner_close = escrow - settle

    # homeowner flows: receives (net of fee front-loaded), pays settlement
    ho_flows, remaining_fee = [], fee_taken
    for m in dm:
        amt = MONTHLY
        if remaining_fee > 0:
            take = min(remaining_fee, amt)
            amt -= take
            remaining_fee -= take
        if amt:
            ho_flows.append((m, amt))
    ho_flows.append((exit_m, -settle))
    ho_apr = irr_monthly(ho_flows)

    # investor flows (gross deal level)
    inv_flows = [(0, FEE if gross >= FEE else 0.0)]
    inv_flows += [(m, -MONTHLY) for m in dm]
    inv_flows.append((exit_m, settle))
    inv_irr = irr_monthly(inv_flows)
    inv_mult = (settle + (FEE if gross >= FEE else 0.0)) / gross if gross else None

    # HELOC benchmark: same net draws, 8.5% accrual, payoff at exit
    bal = 0.0
    draw_iter = {m: a for m, a in ho_flows if a > 0}
    for m in range(1, exit_m + 1):
        bal *= (1 + HELOC_RATE)
        bal += draw_iter.get(m, 0.0)
    heloc_payoff = bal
    heloc_interest = heloc_payoff - net_received

    ef_cost = settle - net_received  # dollar net cost of EF to homeowner

    # cash-out refi benchmark: refi 350K+122.85K at 7%/30yr at month 0,
    # cost through exit = extra interest paid vs old loan
    refi_p = MTG0 + 122_850.0
    refi_pmt = mtg_payment(refi_p, REFI_RATE / 12, 360)
    def bal_at(p, r, pmt, m):
        return p * (1 + r) ** m - pmt * (((1 + r) ** m - 1) / r)
    old_paid = MTG_PMT * exit_m
    old_prin = MTG0 - mtg_balance(exit_m)
    new_paid = refi_pmt * min(exit_m, 360)
    new_bal = max(0.0, bal_at(refi_p, REFI_RATE / 12, refi_pmt, min(exit_m, 360)))
    new_prin = refi_p - new_bal
    refi_cost = (new_paid - new_prin) - (old_paid - old_prin)  # extra interest, incl. on the cash

    return dict(gross=gross, net_received=net_received, price=price,
                share=share, claim=claim, escrow=escrow, settle=settle,
                homeowner_close=homeowner_close, ho_apr=ho_apr,
                inv_irr=inv_irr, inv_mult=inv_mult, ef_cost=ef_cost,
                heloc_interest=heloc_interest, heloc_payoff=heloc_payoff,
                refi_cost=refi_cost, months_paid=len(dm))


def pct(x, na="n/a"):
    return na if x is None else f"{100*x:.1f}%"


def k(x):
    return f"${x/1000:,.1f}K" if abs(x) < 1e6 else f"${x/1e6:,.2f}M"


def grid_table(metric, fmt, pause_on=True, note=""):
    hdr = "| HPA \\ Hold | " + " | ".join(f"{t}y" for t in HOLDS) + " |"
    sep = "|---" * (len(HOLDS) + 1) + "|"
    lines = [hdr, sep]
    for g in HPAS:
        row = [f"| **{g*100:+.0f}%** "]
        for T in HOLDS:
            d = deal(g, T, pause_on)
            row.append(f"| {fmt(d[metric])} ")
        lines.append("".join(row) + "|")
    if note:
        lines.append("")
        lines.append(note)
    return "\n".join(lines)


def main():
    print("# Stage 4 Quant Model — RESULTS")
    print()
    print("Generated by `equity_flow_model.py` per `ASSUMPTIONS.md`. Branch P")
    print("(pause operates) governs negative-HPA cells unless marked Branch N.")
    print("Positive-HPA cells are branch-invariant. All figures single-deal,")
    print("gross, pre-tax, pre-fund-fees (see assumptions 17, 21).")
    print()

    print("## Table 1 — Investor gross IRR (annualized)")
    print()
    print(grid_table("inv_irr", pct))
    print()

    print("## Table 2 — Investor multiple on gross disbursed (incl. fee)")
    print()
    print(grid_table("inv_mult", lambda x: "n/a" if x is None else f"{x:.2f}x"))
    print()

    print("## Table 3 — Homeowner APR-equivalent (effective cost of cash received)")
    print()
    print(grid_table("ho_apr", pct))
    print()

    print("## Table 4 — Homeowner net dollar cost (settlement minus net cash received)")
    print()
    print(grid_table("ef_cost", k))
    print()

    print("## Table 5 — EF settlement minus HELOC payoff (positive = EF costs more at exit)")
    print()
    def heloc_delta(d):
        return d
    hdr = "| HPA \\ Hold | " + " | ".join(f"{t}y" for t in HOLDS) + " |"
    sep = "|---" * (len(HOLDS) + 1) + "|"
    print(hdr); print(sep)
    for g in HPAS:
        cells = []
        for T in HOLDS:
            d = deal(g, T)
            delta = d["settle"] - d["heloc_payoff"]
            cells.append(k(delta))
        print(f"| **{g*100:+.0f}%** | " + " | ".join(cells) + " |")
    print()

    print("## Table 6 — Cash-out refi extra-interest benchmark (cost of same cash via refi, destroying the 3.5% lock)")
    print()
    print(grid_table("refi_cost", k,
          note="_Extra interest vs. keeping the old loan, on a $472.9K refi at 7.0%._"))
    print()

    # summary analyses
    print("## Summary A — Where the customer does worse than the obvious alternative")
    print()
    worse_heloc, worse_nothing = [], []
    for g in HPAS:
        for T in HOLDS:
            d = deal(g, T)
            if d["net_received"] <= 0:
                continue
            if d["settle"] > d["heloc_payoff"]:
                worse_heloc.append((g, T, d["settle"] - d["heloc_payoff"], d["ho_apr"]))
            if d["ho_apr"] is not None and d["ho_apr"] > 0.15:
                worse_nothing.append((g, T, d["ho_apr"]))
    print(f"- **Worse than a matched-draw 8.5% HELOC:** {len(worse_heloc)} cells.")
    print("  Per-HPA pattern (longest hold at which EF still costs more than the HELOC):")
    for g in HPAS:
        holds_worse = [T for T in HOLDS if deal(g, T)["settle"] > deal(g, T)["heloc_payoff"]]
        if holds_worse:
            print(f"    - {g*100:+.0f}% HPA: worse through {max(holds_worse)}y"
                  + (" (all holds)" if len(holds_worse) == len(HOLDS) else ""))
    print("  Worst dollar cells:")
    for g, T, delta, apr in sorted(worse_heloc, key=lambda x: -x[2])[:6]:
        print(f"    - {g*100:+.0f}% HPA, {T}y hold: EF costs {k(delta)} more than the HELOC (APR-eq {pct(apr)})")
    print("- **Cheaper than the HELOC:** long holds at low-to-trend HPA — the")
    print("  cells where the fund earns below its cost of capital (the")
    print("  pricing-identity: consumer-fair cells are fund-unfair cells and")
    print("  vice versa; investor crossexam Dispute 3).")
    print(f"- **Economically indefensible vs. doing nothing (>15% APR-eq):** {len(worse_nothing)} cells,")
    print("  concentrated in holds ≤ 3 years at HPA ≥ +3% — exits driven by the")
    print("  life events (job loss, divorce, death) the marketing says the")
    print("  product accommodates:")
    for g, T, apr in sorted(worse_nothing, key=lambda x: -x[2])[:6]:
        print(f"    - {g*100:+.0f}% HPA, {T}y hold: {pct(apr)}")
    print("- **Cash-out refi comparison:** the refi benchmark (Table 6) is more")
    print("  expensive than EF in most long-hold cells — the rate-lock-destruction")
    print("  premise is real — but for holds ≤ 5 years the refi's extra interest")
    print("  is smaller than EF's cost in appreciating markets.")
    print()

    print("## Summary B — Where investor returns go negative or sub-hurdle")
    print()
    neg, sub = [], []
    for g in HPAS:
        for T in HOLDS:
            d = deal(g, T)
            if d["inv_irr"] is None:
                continue
            if d["inv_irr"] < 0:
                neg.append((g, T, d["inv_irr"], d["settle"], d["gross"]))
            elif d["inv_irr"] < HURDLE:
                sub.append((g, T, d["inv_irr"]))
    print(f"- **Negative gross IRR cells (Branch P):** {len(neg)}. With the pause")
    print("  operating, deployment freezes at ~$4.5K in declining markets, so the")
    print("  non-recourse cap never bites on this grid; deeper drawdowns (stress")
    print("  table) are where sale-exit principal losses live. Note the paused-deal")
    print("  IRRs are dominated by the $3,150 fee charged on PROJECTED")
    print("  disbursements against only $4,500 deployed — an artifact the spec's")
    print("  fee mechanics create (investor A15).")
    for g, T, irr, s, gr in neg[:6]:
        print(f"    - {g*100:+.0f}% HPA, {T}y: IRR {pct(irr)} (settle {k(s)} on {k(gr)} deployed)")
    print(f"- **Positive but below the 8% hurdle:** {len(sub)} cells. Full list by row:")
    from collections import defaultdict
    byrow = defaultdict(list)
    for g, T, irr in sub:
        byrow[g].append((T, irr))
    for g in sorted(byrow):
        cells = ", ".join(f"{T}y {pct(i)}" for T, i in byrow[g])
        print(f"    - {g*100:+.0f}% HPA: {cells}")
    d37 = deal(0.03, 7); d325 = deal(0.03, 25)
    print(f"  At trend HPA (+3%): year-7 exit {pct(d37['inv_irr'])} gross, decaying to")
    print(f"  {pct(d325['inv_irr'])} by year 25. The marketed ~11% requires above-trend")
    print("  HPA AND the year-7 exit the underwriting selects against.")
    print()

    print("### Branch N (pause inoperative — spec §20 behavior): negative-HPA rows")
    print()
    hdr = "| HPA \\ Hold | " + " | ".join(f"{t}y" for t in [1, 2, 3, 5, 7, 10]) + " |"
    print(hdr)
    print("|---" * 7 + "|")
    for g in [-0.03, -0.02, -0.01]:
        cells = []
        for T in [1, 2, 3, 5, 7, 10]:
            d = deal(g, T, pause_on=False)
            cells.append(f"{pct(d['inv_irr'])} / {d['inv_mult']:.2f}x")
        print(f"| **{g*100:+.0f}%** | " + " | ".join(cells) + " |")
    print()
    print("_Branch N shows the loss profile if disbursements continue through_")
    print("_declines (as the spec's own §20 example assumes): 0% nominal IRR for_")
    print("_years (capital returned, no gain), turning negative only where the_")
    print("_escrow cap binds at deep drawdowns — see the stress table._")
    print()

    print("## Stress table — fully deployed vintage, sale at drawdown from enrollment")
    print()
    print("| Drawdown | Sale price | Escrow avail. | Claim | Recovery on $126K |")
    print("|---|---|---|---|---|")
    for dd in [-0.10, -0.20, -0.27, -0.30, -0.35, -0.45]:
        price = V0 * (1 + dd)
        escrow = max(0.0, price * (1 - SELL_COST) - mtg_balance(84))
        rec = min(escrow, COMMIT) / COMMIT
        print(f"| {dd*100:.0f}% | {k(price)} | {k(escrow)} | {k(COMMIT)} | {rec:.2f}x |")
    print()
    print("_Reproduces the Stage 2 concession arithmetic: breakeven drawdown ≈_")
    print("_−27% from enrollment; at sand-state severity (−45%) a SALE exit_")
    print("_recovers ≈ 0.17x — an 83% principal loss with no foreclosure needed_")
    print("_(actuarial crossexam R1)._")
    print()

    print("## Key single-cell reconciliations")
    print()
    d = deal(0.05, 7)
    print(f"- Base case (+5%, 7y): investor IRR {pct(d['inv_irr'])} incl. the t=0 fee, multiple {d['inv_mult']:.2f}x —")
    print("  consistent with the spec's ~11%/1.50x (11.7% ex-fee, investor B2; the")
    print("  fee adds ~1pp at deal level, but is double-promised to platform opex).")
    d = deal(0.03, 7)
    print(f"- Trend HPA (+3%, 7y): {pct(d['inv_irr'])} gross incl. fee (7.2% ex-fee, investor B2) —")
    print("  at/below the 8% hurdle before any fund fees, servicing, pause drag, or")
    print("  credit losses; the claimed 11% is an above-trend-HPA artifact.")
    d = deal(0.05, 25)
    print(f"- Ultra-long hold (+5%, 25y): homeowner pays {k(d['settle'])} on")
    print(f"  {k(d['net_received'])} received (APR-eq {pct(d['ho_apr'])}); investor multiple")
    print(f"  {d['inv_mult']:.2f}x but IRR only {pct(d['inv_irr'])} — the ratchet grows the")
    print("  multiple, not the IRR (actuarial 5.1).")
    d = deal(0.03, 25)
    print(f"- Spec §21 analogue (+3%, 25y): settlement {k(d['settle'])}, share {d['share']*100:.0f}%,")
    print(f"  homeowner APR-eq {pct(d['ho_apr'])}, investor IRR {pct(d['inv_irr'])}.")
    print()
    print("END OF RESULTS")


if __name__ == "__main__":
    main()