Investor Excel Formulas — Advanced
This page upgrades your toolkit: disciplined underwriting blocks, promote waterfalls, scenario engines, spatial/DID hooks, and portfolio diagnostics. Each module explains why it matters, when to deploy, and how to build it in Excel.
1) Underwriting Blocks (inputs → drivers → outputs)
Why: clean separation makes reviews faster and prevents circular refs.
When: anytime you prepare an IC memo.
Steps: workbook structure
- Inputs sheet: price, rents, growth, vacancy, OpEx %, capex, debt terms, exit cap. Add Source and Date columns.
- Drivers sheet: indexation, downtime logic, TI/LC timing, debt schedule (IPMT/PPMT), tax as needed.
- Outputs sheet: NOI, FCF, DSCR, equity CF, NPV/XNPV, IRR/XIRR, EM, sensitivities.
Debt service
=PMT(Rate/12, TermMonths, -LoanAmt)
=IPMT(Rate/12, t, TermMonths, -LoanAmt)
=PPMT(Rate/12, t, TermMonths, -LoanAmt)Exit value (wrap in LAMBDA)
=LAMBDA(noi, cap, sale, (noi/cap)*(1-sale))Name it TV() in Name Manager. Call: =TV(NOI_Y6, ExitCap, SaleCosts)
IC snapshot: Cap @ entry/exit, unlevered vs levered returns, DSCR path, refi feasibility, top 3 drivers.
2) Promote Waterfall (pref + hurdles + catch-up)
Why: aligns GP/LP incentives; you must show math clearly.
When: JV deals, development promotes, or performance fees.
Steps: minimal but audit-ready
- Compute equity cash flows by period (before promote).
- Accrue preferred return (monthly):
=LET(cap,EquityContrib, r, PrefRate/12, m, MonthsElapsed, cap*(1+r)^m - cap)- Return of capital, then pref, then apply split tiers (e.g., 70/30 → 60/40 → 50/50) with flags that switch once IRR hurdles are met.
- Optional lookback/clawback at exit for LP target assurance.
Tip: Put each tier’s result in a separate row and subtotal; avoid hidden macros—ICs prefer visible logic.
3) Scenario Engine (clean & auditable)
Why: committees compare Base / Down / Up quickly; your model should switch assumptions without manual edits.
Steps
- Create a small table with columns Base, Down, Up for key drivers (rent growth, vacancy, exit cap, rate).
- Use a selector cell
Sel(1..3) and:=CHOOSE(Sel, BaseCol, DownCol, UpCol) - For grid sensitivities (cap × growth) show a 2-way table; for programmatic reads:
=INDEX(OutputTable, MATCH(RowKey, RowLabels, 0), MATCH(ColKey, ColLabels, 0))
Deliverable: one-page tornado or 2-way cap/growth table next to returns. Make drivers traceable to the Inputs sheet.
4) Portfolio Diagnostics (dispersion, exposures, correlation)
Why: risk lives in concentrations and co-movement.
By sector/region
=SUMIFS(NOIs, Sector, H2, Region, H3)
=COUNTIFS(Sector,"Industrial",Rating,"BBB+")Dispersion & tails
=STDEV.S(range) =VAR.S(range)
=PERCENTILE.INC(range,0.1) =PERCENTILE.INC(range,0.9)Correlation & beta-style slope
=CORREL(series_x, series_y)
=SLOPE(y_range, x_range)Red-flag filter
=FILTER(Assets, Assets[DSCR]<1.20)
Step-by-step: normalize series (same dates), label units, freeze panes; show stdev and 10–90% bands next to any chart.
5) QA & Auditability (what reviewers expect)
- Inputs sheet with Source/Date columns and a short Assumption Register.
- Version ID (YYYY-MM-DD) and a three-line change log.
- Color-code inputs (blue) vs formulas (black); avoid hard-numbers in formulas.
- One-page IC pack: thesis, 3–5 drivers, returns (unlev/lev), DSCR path, refi test, top risks/mitigations.