1) Underwriting Structure (inputs → drivers → outputs)

Keep underwriting auditable by splitting the workbook into three layers:

1. Inputs: assumptions (rents, indexation, opex %, capex schedule, debt terms), with sources and dates.
2. Drivers: deterministic math (indexation, vacancy/downtime logic, TI/LC amortization, debt schedules).
3. Outputs: NOI, FCF, DSCR/ICR, equity cash flows, IRR/MOIC, sensitivities.

Tip: Put your assumptions in a single table and reference them with XLOOKUP/INDEX+MATCH to avoid hard-links across tabs. Use LET to name sub-steps inside long formulas.

2) Debt & Covenants: DSCR/ICR, refinancing, and cov-lite traps

• Amortization & floating rate: build rate paths (base + spread), use IPMT/PPMT by period.
• DSCR (debt service coverage): test base and stressed NOI versus scheduled debt service.
• Refi test: at maturity, reprice debt with market all-in rate and new amort/n; solve PMT for feasibility.
• Covenants: DSCR minima, LTV triggers, cash sweeps; model cure mechanics (cash reserve / prepay).

Core formulas

DebtService_t = IPMT(rate_t/12, t, n_total, -Principal_0) + PPMT(rate_t/12, t, n_total, -Principal_0)
DSCR_t        = NOI_t / DebtService_t
NewPayment    = PMT( NewRate/12, NewTermMonths, -OutstandingAtMaturity )

Quick “what-if” (rate shock):

=FILTER(Schedule, Schedule[DSCR_Shocked] < 1.20)

Practical checks

• Use ICR for floating-only phases; switch to DSCR once amortization begins.
• Refi feasibility must pass: DSCR ≥ covenant, LTV ≤ max, exit cap not heroic.
• Track interest reserve burn and rate caps/floors explicitly.

3) Promote Waterfalls: IRR hurdles, catch-ups, and lookbacks

Start simple (preferred return + split), then layer hurdles.

1. Tier 0: Return of Capital
2. Tier 1: Preferred return (e.g., 8% simple/compounding)
3. Tier 2+: Promote tiers (e.g., 70/30 → 60/40 → 50/50) with catch-up logic

Preferred accrual example (monthly):

=LET(
  cap, Inputs[EquityContributed],
  r, Inputs[PrefRate]/12,
  months, N,
  cap*(1+r)^months - cap
)

IRR hurdle detection: compute LP IRR by tiered cash flows; compare to threshold; expose flags to switch splits.

Lookback option: at exit, recompute to ensure LP hit target before splitting promote; show clawback amount if needed.

4) Lease Modeling: rollover cliffs, indexation, downtime & TI/LC

• Rollover ladder: build a period-by-period table with SEQUENCE dates; mark expiries; compute downtime and re-tenancy probability.
• Indexation: CPI-linked steps; cap/floor; INDEX into a CPI curve or use constant for base case.
• TI/LC: expense timing (signing vs handover), amortization policy (straight line to lease end).

Active expiring leases in next 12 months:

=FILTER(Leases, (Leases[EndDate] <= EOMONTH(TODAY(),12)) * (Leases[Status]="Active"))

Rent step parser example:

=VALUE(TEXTAFTER(TEXTBEFORE([@Step], "%"), "+"))/100

5) Capex & Cap-Stack: timing, contingency, and WACC reality checks

• Capex curves: S-curve or tranche schedule; include contingency and escalation explicitly.
• Cap-stack: equity, senior, mezz/PIK; blended cost vs WACC for sanity.
• Exit math: Value = NOI_exit / ExitCap minus sale costs; triangulate with market comps.

Exit value function (wrap in LAMBDA):

=LAMBDA(noi, cap, (noi / cap) * (1 - SaleCosts))

6) Scenario & Monte Carlo: correlated shocks the practical way

Real portfolios move together. You don’t need full matrix algebra to approximate correlation—use a small factor model:

1. Create a MarketFactor series with RANDARRAY() (fixed seed for demos) and a few Idiosyncratic series.
2. Shock each driver = beta * MarketFactor + epsilon * Idio. Keep betas >0 for cap rate, <0 for rents in risk-off regimes.
3. Run 1,000–5,000 draws; summarize worst-case DSCR and P(Loss).

Example (one-period draw):

=LET(
  n, 1000,
  mkt, RANDARRAY(n,1, -2, 2),         /* market shock in σ units */
  id1, RANDARRAY(n,1, -1, 1),
  beta_rent, -0.6,  beta_cap, 0.8,
  rentShock,  beta_rent*mkt + 0.4*id1,
  capShock,   beta_cap*mkt  + 0.3*id1,
  /* apply to base assumptions */
  Rent_t = BaseRent*(1 + rentShock*AssumedSigmaRent),
  Cap_t  = BaseCapRate*(1 + capShock*AssumedSigmaCap),
  HSTACK(Rent_t, Cap_t)
)

Use BYROW / MAP to roll these into period cash flows; aggregate equity cash flows and compute distribution of IRR/DSCR. Save a seed so runs are reproducible.

7) Spatial & DID Methods: turning location and policy into numbers

Spatial exposure (portfolio-level): compute accessibility (travel times to nodes), power-grid proximity for data centers, and hazard overlays (flood/heat). Export summary tables per asset and cluster.

• Hedonics: separate structural vs neighborhood effects; beware spatial autocorrelation.
• Spatial spillovers: SDM/SAR for rent/value propagation; report marginal effects.
• DID/event study: measure policy/infrastructure impacts (e.g., floodplain remap, transit opening). Include parallel trends, placebo, and anticipation tests.

Deliverable: a one-slide map + a table of marginal effects with uncertainty bands, then a sheet listing assets with the largest predicted impact.

8) AI-Assisted Diligence: from documents to sheet-ready fields

1. Triage: split PDFs into sections; extract tables; tag items (rent steps, covenants, termination rights).
2. Validation: cross-check AI fields with a baseline ruleset (e.g., DSCR formula, rent step bounds, date logic).
3. Schema: standardize to columns you’ll reuse: Date | Source | Section | Field | Value | Range | Confidence.
4. Handoff: paste schema into Excel; formulas reference the table—not the narrative.

Tip: keep an “Assumption Register” table the IC can scan in 30 seconds.

9) Deliverables: one-page IC pack + workbook hygiene

• IC one-pager: thesis, 3–5 drivers, base/Down-25bps/Up-25bps sensitivities, covenant headroom, top risks/mitigations.
• Workbook hygiene: color-code inputs/links, freeze panes, name ranges, version ID (YYYY-MM-DD), and a change log.
• Outputs that matter: DSCR path, refi feasibility, unlevered vs levered divergence, and promote economics at base & stressed exits.

Next steps: In a companion workbook, I’ll package: Inputs, Leases, Capex, Debt, Waterfall, Scenarios, MC-Factor (correlated shocks), and IC-OnePager. Add a “File” block here later to attach the template.