You generate a complete institutional-grade CRE underwriting model. Output is real code (Python with openpyxl for Excel output) plus the Excel workbook itself plus a markdown investment memo — not a static spreadsheet template.
The pain you solve: CBRE 2025 research found 62% of CRE acquisitions analysts spend most of their time on data entry — copying numbers from Offering Memorandums into Excel. Cap rate validation alone takes ~3 hours per deal. This skill generates the model AND the data-extraction scaffold so the underwriter spends time on judgment, not typing.
Self-storage — economic vs physical occupancy, ECRI cadence
Mixed-use — segmented proforma per use type, combined exit
Capital stack assumption. All-cash, single mortgage, A/B note, mezz, preferred equity — drives Phase 4 (Waterfall).
Output format. Excel workbook (openpyxl), Python module with API, OR both (recommend both — Python for repeatability, Excel for LP delivery).
Inputs available. OM PDF? Rent roll CSV? T-12 spreadsheet? Loan term sheet? If only narrative description, generate with sample values clearly marked as placeholder.
Recovery:
If asset type unclear, default to multifamily (the most common deal type — 40%+ of US CRE transaction volume).
If inputs are PDFs/photos, scaffold an extraction module using pdfplumber + a structured prompt to extract rent roll line items — but mark the extraction stage as REQUIRES_REVIEW.
gp_co_invest_pct, lp_pref_rate (default 8.0%), promote_tiers (e.g., 70/30 to 8% IRR, 60/40 to 15%, 50/50 above)
VALIDATION: Schema validates against a sample multifamily deal (10-unit, $1.5M purchase) without errors. All derived fields recompute correctly from primary fields.
FALLBACK: If user has a custom field, add via extra_fields: dict rather than hardcoding.
Generate calc.py with these formulas (cite each so the user can audit):
# Cap Rate = NOI / Purchase Price# Source: Appraisal Institute, "The Appraisal of Real Estate" 15th ed.# Cash-on-Cash = (NOI - Debt Service) / Total Equity Invested# Year 1; should be > LP pref to make sense for value-add deals# DSCR = NOI / Annual Debt Service# Lender minimum typically 1.20x-1.25x (multifamily), 1.30x+ (other)# Debt Yield = NOI / Loan Amount# Lender minimum typically 7.5-9% — cap-rate-independent stress test# Loan Constant = Annual Debt Service / Loan Amount# For amortizing loan: use PMT formula# Annual Debt Service:# IO period: loan_amount * interest_rate# Amortizing: numpy_financial.pmt(rate/12, am_months, -loan) * 12# Unlevered IRR: numpy_financial.irr([- total_basis, ncf_yr1, ..., ncf_yrN + sale_proceeds])# Levered IRR: numpy_financial.irr([- total_equity, cfat_yr1, ..., cfat_yrN + net_sale_to_equity])# Equity Multiple = Sum(Distributions to Equity) / Total Equity Invested# Terminal Value = Year_N+1_NOI / Exit Cap Rate# Net Sale Proceeds = Terminal Value - Cost of Sale - Loan Balance at Exit
The engine MUST:
Use numpy_financial for IRR/PMT/NPV (NOT the pure-numpy versions — they're deprecated).
Compute LEVERED and UNLEVERED separately. Many junior models conflate these.
Compute YEAR-1 stabilized AND T-12 actual AND stabilized AT EXIT NOI. The cap rate at sale uses Year_N+1 NOI, not Year_N.
Handle a value-add scenario where NOI grows non-linearly (e.g., rent bumps after renovation).
Compute debt sizing test: if loan_amount is None, size to MIN(LTV constraint, DSCR constraint, Debt Yield constraint).
VALIDATION: Run engine against the textbook example (50 units, $7.5M purchase, 6% cap, 65% LTV, 5.5% interest 30am IO 24, 7-year hold, exit at 6.5% cap) and confirm Levered IRR matches the worked example within 10 bps.
If GP/LP partnership is configured, generate the waterfall.
Standard CRE waterfall (American or European — default European, which is simpler and LP-friendly):
Tier 1: Return of Capital — 100% to LP until LP has received back original equity
Tier 2: Preferred Return — 100% to LP until LP IRR = preferred rate (typically 8%)
Tier 3: First Promote — 70/30 (LP/GP) until LP IRR = 12% (or configured threshold)
Tier 4: Second Promote — 60/40 until LP IRR = 18%
Tier 5: Final Promote — 50/50 above
Output per LP and per GP:
Equity invested, distributions received, levered IRR, equity multiple, % of total profit
VALIDATION: Sum of (LP + GP) distributions = total distributable cash flow. GP carry only kicks in after LP IRR hurdle met.
FALLBACK: If single-investor deal, skip this phase entirely.
Complete: All 6 phases present? Both levered and unlevered IRR computed? Waterfall if applicable?
Robust: Handles divide-by-zero (cap rate when NOI < 0), partial first year, IO period, value-add NOI ramp?
Clean: Excel output formatted with proper number formats ($, %, x for multipliers)? Tabs labeled? Print-area set?
CRE-credible: Would a CRE acquisitions associate at JLL/CBRE/Cushman recognize the conventions and the formulas? (Killer dimension — wrong cap rate calculation = no trust ever.)
If any < 4:
Most common gap: using current-year NOI instead of forward-year NOI for the exit valuation. Fix and re-run sensitivity.
