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.
Never use Year_N NOI for exit valuation. Always Year_N+1 NOI / exit cap.
Never confuse levered and unlevered IRR. Both ship; both labeled.
Never use deprecated numpy.irr. Use numpy_financial.irr.
Never hardcode market rents — they come from the user's rent roll or comp set.
Never imply the model gives a buy/sell recommendation. It presents math; humans decide.
If the user has ARGUS, generate an export-to-ARGUS schema rather than a competing model.
1---2name: cre-underwriting3description: "Generate an institutional-grade commercial real estate underwriting model — input schema (T-12 income, T-3 trailing, rent roll, debt terms, exit assumptions), calc engine (Cap Rate, NOI, Cash-on-Cash, IRR, DSCR, Debt Yield, ROI, Equity Multiple, levered & unlevered returns)."4version: "1.0.1"5category: analysis6platforms:7 - CLAUDE_CODE8metadata:9 source: https://skillmd.com/skills/tinh2/cre-underwriting10---1112# Commercial Real Estate Underwriting Generator1314You 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.1516The 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.1718============================================================19=== PRE-FLIGHT ===20============================================================2122Gather and verify before generating:2324- [ ] **Asset type identified.** The model differs significantly:25 - **Multifamily** — unit mix, in-place vs market rent, T-12 with rent roll, loss-to-lease, vacancy, concessions26 - **Office** — rent roll with WALT, TI/LC reserves, vacancy assumption from CoStar comps27 - **Retail** — anchor vs in-line tenants, % rent clauses, CAM recoveries28 - **Industrial** — flat NNN structure, expansion options, build-to-suit credit29 - **Hospitality** — RevPAR / ADR / Occupancy, FF&E reserve30 - **Self-storage** — economic vs physical occupancy, ECRI cadence31 - **Mixed-use** — segmented proforma per use type, combined exit32- [ ] **Capital stack assumption.** All-cash, single mortgage, A/B note, mezz, preferred equity — drives Phase 4 (Waterfall).33- [ ] **Output format.** Excel workbook (openpyxl), Python module with API, OR both (recommend both — Python for repeatability, Excel for LP delivery).34- [ ] **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.3536Recovery:3738- If asset type unclear, default to multifamily (the most common deal type — 40%+ of US CRE transaction volume).39- 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.4041============================================================42=== PHASE 1: INPUT SCHEMA ===43============================================================4445Generate `inputs.py` defining the deal inputs as a strict Pydantic schema. Fields by section:4647**Property**4849- name, address, asset_type, year_built, year_renovated, sq_ft (NRA), unit_count, parking_count, submarket5051**Acquisition**5253- purchase_price, closing_costs_pct (default 1.5%), due_diligence_costs, financing_costs, capex_at_close, working_capital, total_basis (derived)5455**Income (T-12 actual + Y1 underwritten)**5657- gross_potential_rent, vacancy_pct (physical), credit_loss_pct, concessions, other_income (parking, fees, RUBS, laundry), effective_gross_income (derived)5859**Operating Expenses (Y1 underwritten)**6061- real_estate_taxes (post-reassessment if relevant), insurance, utilities, repairs_maintenance, marketing, payroll, mgmt_fee_pct, replacement_reserves_per_unit, total_opex (derived), expense_ratio (derived)6263**Net Operating Income** (derived: EGI − OpEx)6465**Debt**6667- ltv_pct OR loan_amount (mutually exclusive), interest_rate, amortization_years, term_years, io_period_years (default 0), origination_fee_pct, dscr_required_min (default 1.20x), debt_yield_required_min (default 8.0%)6869**Exit**7071- hold_period_years (default 5 or 7), exit_cap_rate (typically +25-75 bps over entry cap), cost_of_sale_pct (default 2.0%), terminal_value (derived)7273**Growth Assumptions (10-year vectors)**7475- rent_growth_pct[], expense_growth_pct[], other_income_growth_pct[]7677**Partnership** (if syndication)7879- 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)8081VALIDATION: Schema validates against a sample multifamily deal (10-unit, $1.5M purchase) without errors. All derived fields recompute correctly from primary fields.8283FALLBACK: If user has a custom field, add via `extra_fields: dict` rather than hardcoding.8485============================================================86=== PHASE 2: CORE CALCULATION ENGINE ===87============================================================8889Generate `calc.py` with these formulas (cite each so the user can audit):9091```python92# Cap Rate = NOI / Purchase Price93# Source: Appraisal Institute, "The Appraisal of Real Estate" 15th ed.9495# Cash-on-Cash = (NOI - Debt Service) / Total Equity Invested96# Year 1; should be > LP pref to make sense for value-add deals9798# DSCR = NOI / Annual Debt Service99# Lender minimum typically 1.20x-1.25x (multifamily), 1.30x+ (other)100101# Debt Yield = NOI / Loan Amount102# Lender minimum typically 7.5-9% — cap-rate-independent stress test103104# Loan Constant = Annual Debt Service / Loan Amount105# For amortizing loan: use PMT formula106107# Annual Debt Service:108# IO period: loan_amount * interest_rate109# Amortizing: numpy_financial.pmt(rate/12, am_months, -loan) * 12110111# Unlevered IRR: numpy_financial.irr([- total_basis, ncf_yr1, ..., ncf_yrN + sale_proceeds])112# Levered IRR: numpy_financial.irr([- total_equity, cfat_yr1, ..., cfat_yrN + net_sale_to_equity])113114# Equity Multiple = Sum(Distributions to Equity) / Total Equity Invested115116# Terminal Value = Year_N+1_NOI / Exit Cap Rate117# Net Sale Proceeds = Terminal Value - Cost of Sale - Loan Balance at Exit118```119120The engine MUST:121122- Use `numpy_financial` for IRR/PMT/NPV (NOT the pure-numpy versions — they're deprecated).123- Compute LEVERED and UNLEVERED separately. Many junior models conflate these.124- 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.125- Handle a value-add scenario where NOI grows non-linearly (e.g., rent bumps after renovation).126- Compute breakeven occupancy: `Breakeven_Occ = (OpEx + Debt Service) / GPR`.127- Compute debt sizing test: if `loan_amount` is None, size to MIN(LTV constraint, DSCR constraint, Debt Yield constraint).128129VALIDATION: 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.130131============================================================132=== PHASE 3: 10-YEAR PROFORMA ===133============================================================134135Generate the full 10-year cash flow waterfall:136137| Line | Year 1 | Year 2 | ... | Year N (exit) |138| ----------------------------- | ------ | ------ | --- | ------------- |139| Gross Potential Rent | 1.20M | grown | | |140| (-) Vacancy | (60K) | | | |141| (-) Concessions | (10K) | | | |142| (+) Other Income | 80K | | | |143| **Effective Gross Income** | 1.21M | | | |144| (-) Operating Expenses | (480K) | | | |145| **Net Operating Income** | 730K | | | |146| (-) Capital Reserves | (15K) | | | |147| **NOI after Reserves** | 715K | | | |148| (-) Debt Service | (450K) | | | |149| **Cash Flow After Debt** | 265K | | | |150| (+) Sale Proceeds net of debt | | | | + 5.2M |151| **Cash Flow to Equity** | 265K | | | 5.46M |152153Plus a Sources & Uses table at acquisition and a Sources & Uses at exit.154155VALIDATION: Row totals reconcile (EGI − OpEx = NOI). Year N+1 NOI used for exit valuation, not Year N.156157============================================================158=== PHASE 4: WATERFALL (for syndication deals) ===159============================================================160161If GP/LP partnership is configured, generate the waterfall.162163Standard CRE waterfall (American or European — default European, which is simpler and LP-friendly):164165```166Tier 1: Return of Capital — 100% to LP until LP has received back original equity167Tier 2: Preferred Return — 100% to LP until LP IRR = preferred rate (typically 8%)168Tier 3: First Promote — 70/30 (LP/GP) until LP IRR = 12% (or configured threshold)169Tier 4: Second Promote — 60/40 until LP IRR = 18%170Tier 5: Final Promote — 50/50 above171```172173Output per LP and per GP:174175- Equity invested, distributions received, levered IRR, equity multiple, % of total profit176177VALIDATION: Sum of (LP + GP) distributions = total distributable cash flow. GP carry only kicks in after LP IRR hurdle met.178179FALLBACK: If single-investor deal, skip this phase entirely.180181============================================================182=== PHASE 5: SENSITIVITY TABLES ===183============================================================184185Generate three 2D sensitivities (the deal-killers):1861871. **Exit Cap × Rent Growth** → Levered IRR1882. **Entry Cap × Loan Constant** → Cash-on-Cash Year 11893. **Vacancy × OpEx Growth** → DSCR Year 1190191Each output as both a pandas DataFrame heatmap AND an Excel sheet with conditional formatting.192193VALIDATION: Center cell of each sensitivity equals the base-case output.194195============================================================196=== PHASE 6: INVESTMENT MEMO ===197============================================================198199Generate `memo.md` (markdown) with these sections:2002011. **Executive Summary** (3 sentences: asset, basis per unit, headline returns)2022. **Returns Summary Table** (Y1 cap, stabilized cap, Y1 CoC, levered IRR, equity multiple, DSCR Y1)2033. **Sources & Uses** at acquisition2044. **Capital Stack diagram** (text-based)2055. **Underwriting Assumptions Highlights** (rent growth, expense growth, exit cap)2066. **Sensitivity Summary** (best case / base case / downside)2077. **Risks & Mitigants** (3-5 items, populated from heuristics: high LTV → refi risk; aggressive rent growth → stabilization risk; etc.)2088. **Recommendation** (with a clearly-marked placeholder for the underwriter — model doesn't recommend, it presents)209210VALIDATION: Memo renders without dangling markdown. All numbers tie to the proforma.211212FALLBACK: If user wants PDF, add a step to convert via `pandoc` or `weasyprint`.213214============================================================215=== SELF-REVIEW ===216============================================================217218Score 1–5:219220- **Complete**: All 6 phases present? Both levered and unlevered IRR computed? Waterfall if applicable?221- **Robust**: Handles divide-by-zero (cap rate when NOI < 0), partial first year, IO period, value-add NOI ramp?222- **Clean**: Excel output formatted with proper number formats ($, %, x for multipliers)? Tabs labeled? Print-area set?223- **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.)224225If any < 4:226227- Most common gap: using current-year NOI instead of forward-year NOI for the exit valuation. Fix and re-run sensitivity.228229============================================================230=== LEARNINGS CAPTURE ===231============================================================232233Append to `~/.claude/skills/cre-underwriting/LEARNINGS.md`:234235## <YYYY-MM-DD> — <asset type, deal size, capital stack>236237- **What worked:** <pattern that produced clean output>238- **What was awkward:** <retry or manual fix needed>239- **Suggested patch:** <concrete improvement>240- **Verdict:** [Smooth / Minor friction / Major friction]241242============================================================243=== STRICT RULES ===244============================================================245246- Never use Year_N NOI for exit valuation. Always Year_N+1 NOI / exit cap.247- Never confuse levered and unlevered IRR. Both ship; both labeled.248- Never use deprecated `numpy.irr`. Use `numpy_financial.irr`.249- Never hardcode market rents — they come from the user's rent roll or comp set.250- Never imply the model gives a buy/sell recommendation. It presents math; humans decide.251- If the user has ARGUS, generate an export-to-ARGUS schema rather than a competing model.
Run npx get-skill add tinh2/cre-underwriting in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.
Generate an institutional-grade commercial real estate underwriting model — input schema (T-12 income, T-3 trailing, rent roll, debt terms, exit assumptions), calc engine (Cap Rate, NOI, Cash-on-Cash, IRR, DSCR, Debt Yield, ROI, Equity Multiple, levered & unlevered returns). It is listed under AI & ML on SkillMD.
This skill has not completed SkillMD's automated safety review yet. Independent scanners report: SkillSpector: PASS, Skill Scanner: PASS. Capability flags: docs only. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.
This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.
Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.
tinh2 (@tinh2) published this skill. Their other Agent Skills are listed on their SkillMD profile.