Case study

DUSKFALL underwriting platform

A real-estate underwriting model where one Python engine is the single source of truth, and a validator proves the deck, the web proforma, and the spreadsheet can never disagree.

Most real-estate deals get underwritten in a spreadsheet, pitched in a separate deck, and explored in yet another tool. The three quietly drift apart. A number changes in the model, nobody updates the deck, an investor catches the mismatch, and trust evaporates. We built this one so that cannot happen.

DUSKFALL is a Northeast astrotourism and glamping development we are underwriting for our own account. The interesting part for a reader here is not the deal. It is the engineering: one Python proforma is authoritative, a browser engine is a verified port of it, and an automated gate refuses to let any displayed number drift out of agreement.

One model is authoritative. Everything downstream is checked against it before anything ships.

The problem

The math underneath a development deal is easy to get subtly wrong. A GP/LP waterfall with a preferred return, a catch-up, a promote, and after-tax depreciation has dozens of moving parts, and a single off-by-one in the tier order compounds across a ten-year hold. Worse, once you have it right in one place, you copy the numbers into a deck and a web tool, and from that moment on they can disagree without anyone noticing. We treated that drift as a bug to be prevented mechanically, not a discipline to be remembered.

The approach

One authoritative model

A Python proforma is the single source of truth for every operating, financing, and tax assumption. A roughly 2,000-line engine (funding_scenarios.py) layers the financing structure, the full GP/LP waterfall, and the after-tax views on top of it. Every other surface reads from this, so there is exactly one place to be right. Alternative scenarios load the canonical proforma and override it in memory only, never editing the source file, and the same module feeds the PDF deck generator, so the deck, the web deck, and the model literally share one code path.

The waterfall is institutional, not a toy. The tier order is explicit in the source:

PREF_RATE          = 0.08   # 8% cumulative compounding pref, LP and GP pari-passu
LP_SHARE_OF_EQUITY = 0.80   # LP 80% / GP 20% of equity
PROMOTE_SPLIT_LP   = 0.60   # 60/40 LP/GP residual after pref + catch-up
PROMOTE_SPLIT_GP   = 0.40
CATCH_UP_TARGET_GP = 0.20   # GP catches up to 20% of cumulative promote

# Waterfall (per period, in order):
#   T1  Return of capital, pari-passu
#   T2  Pref, pari-passu by accrued unpaid balance
#   T3  GP catch-up to 20% of cumulative promote
#   T4  Residual split per promote_split

Port the engine, then prove the port

The interactive web model runs an 852-line JavaScript engine that mirrors the Python function for function: runScenario, runWaterfall, calcAfterTaxFlows, calcIRR, findBreakEvenADR, computeMarginOfSafety. It is not assumed correct. A frozen-baseline harness settles that. generate_baselines.py freezes 20 Python scenarios to JSON, then validate.html and waterfall_validate.mjs diff the JavaScript engine against those baselines cell for cell, failing if any metric diverges by more than half a percent. The README records the current status as 300 of 300 cells passing within that tolerance.

Add a consistency gate

The last stage is the one most models skip. validate_consistency.py reads the public investor deck, the web proforma, and the defaults file, reconciles roughly forty displayed numbers back to the canonical Python figures, and exits non-zero on any drift. NOI, DSCR, LP IRR, equity multiple, after-tax IRR, the capital stack, cost per key, sensitivity-grid cells, the probability-weighted return, and the Year-1 tax lines all have to match, with a half-point tolerance on rates and a thousand-dollar tolerance on NOI. If a number changes in the model and the deck is not updated, the build fails. The deck and the model cannot silently disagree, because the gate will not let them.

The investor deck hero: a dark ink-and-gold editorial layout with a transparent dome under the night sky, a Cormorant headline, and an ask box showing the limited-partner raise, base limited-partner IRR, target hold, and preferred return. — The deliverable on top of the model: a dark, editorial investor deck. Ink and gold, Cormorant over Inter, scroll reveals, a live commitment meter. Dollar figures here are adjusted for public display.

The finance, at a credibility level

The model is not a single happy-path number. It carries a full GP/LP waterfall (return of capital, an eight percent compounding preferred return, a GP catch-up to twenty percent of promote, then a residual split), after-tax modeling with one hundred percent bonus depreciation and a taxable-exit calculation, debt-service coverage by year, a tornado diagram ranking each input by its impact on levered IRR, and a two-dimensional ADR-by-occupancy IRR heatmap. Decisions are evidence-backed: the ADR anchor converges from four independent methods (a closest-comp adjustment with a metro-feeder uplift, an in-market bracket of regional comps, a hedonic build-up from the regional tier median, and a revenue-per-available-room cross-check against a national comp), and a break-even table states exactly how far ADR, occupancy, and the exit cap rate can move before the deal stops working. It argues from the downside, which is what sophisticated readers trust.

The three-case returns block from the deck. Conservative, Base, and Bull cards, each showing stabilized revenue, stabilized NOI, stabilized DSCR, LP IRR, LP equity multiple, and after-tax LP IRR. The fine print states the shared capital stack and the waterfall terms. — Three operating cases off one capital stack. Returns are shown as approximate bands and the dollar figures are adjusted, but the case math ties out internally: NOI scales with the cost, and the equity splits add up.

The result

A working underwriting platform where the model, the proforma web app, and the investor deck are provably consistent. The web model exposes sliders for every assumption, a margin-of-safety analysis over a categorized comp set, stress presets, a sensitivity heatmap, a tornado diagram, a ten-year P&L, the after-tax view, scenario save and load, and one-click Excel export. The deck is the polished surface a partner actually reads. And the gate sits underneath all of it, run as part of the build, refusing to pass when anything drifts.

The validator currently passes against the canonical figure set:

======================================================================
CONSISTENCY VALIDATOR
======================================================================
  Canonical source: Python proforma (funding_scenarios.py)
  Files checked: deck/index.html, web-proforma/index.html, defaults.json
  39 pass · 0 fail · 0 defaults.json diff
======================================================================

✓ ALL CHECKS PASS
  deck, web proforma, and defaults.json all reconcile
  to Python source-of-truth.

Most financial models are a spreadsheet plus a deck that quietly disagree. This one is engineered so they cannot. To a small-business owner that reads as careful with numbers. To an engineering leader it reads as source-of-truth architecture, regression baselines, and an automated consistency gate. Both are the point.

Skills

Financial modeling GP/LP waterfall After-tax with bonus depreciation DSCR & sensitivity Source-of-truth architecture Regression baselines Cross-document validation Investor-grade web design

Tech

Python 3.9 openpyxl Vanilla JS ES modules Plotly.js SheetJS / xlsx Node ESM test harness Cloudflare Pages Cloudflare Access OTP

Our own development project. Dollar figures adjusted for public display; methodology shown as built.

← Back to work