Professional Pro Forma Development: From Assumptions to Exit
A professional pro forma model represents the foundation of CRE investment decision-making, projecting revenue, operating expenses, and net operating income over the holding period (typically 5–10 years) to calculate IRR, cash-on-cash returns, and exit valuation. Yet many investors rely entirely on sponsor-provided models without independent stress-testing or building their own, exposing themselves to systematic overestimation of returns and underestimation of downside risk. This guide walks through building conservative, defensible 5-year pro formas from first principles, emphasizing conservative assumptions, explicit stress-testing, and sensitivity analysis that reveals how market movements affect returns.
Standard Pro Forma Structure & Line Items
Standard CRE pro formas project Potential Gross Income (PGI), apply vacancy loss, derive Effective Gross Income (EGI), subtract operating expenses to calculate Net Operating Income (NOI), then subtract debt service and capital expenditures to calculate cash flow. Sponsors often project aggressive rent growth (3–4% annually based on optimistic forecasts). Conservative approach: use 1.5–2.5% annual rent growth based on 5-year historical submarket average (request from broker). Long-term inflation expectations support 2% as baseline conservative. Account for supply pipeline and tenant demand. By property type: retail mature markets 1.0–1.5%, office (tech hubs) 2.0–2.5%, industrial (strong logistics tailwinds) 2.5–3.0%, multifamily (market dependent) 1.5–2.5%, value-add repositioning 3–4% (riskier; limit to 2 years pre-stabilization). Example: current $18/sqft market rent, multifamily in Denver, historical growth 2.3%, assume 2.0% years 1–5, year 5 rent $19.85/sqft.
Segmented Expense Inflation & Capital Planning
Critical mistake: applying flat 2–3% expense inflation across all categories. Conservative approach segments by inflation driver. Typical CRE expense breakdown: labor 35–45%, utilities 20–25%, property taxes 15–20%, insurance 8–12%, repairs/supplies/admin 10–15%. Apply category-specific inflation: labor 3–4% annually (labor market inflation exceeds general inflation), utilities 2–3%, property taxes 2–4% (jurisdiction-dependent), insurance 6–10% through 2026, other 2–3%. Example: Year 1 $400K expenses (labor $170K 42.5%, utilities $100K 25%, property tax $70K 17.5%, insurance $40K 10%, other $20K 5%). Year 2 escalation: labor +3.5% = $176K, utilities +2.5% = $103K, property tax +3% = $72K, insurance +8% = $43K, other +2.5% = $20.5K. Year 2 total $414.5K. Conservative CapEx: budget 1–2% of NOI annually plus lump-sum reserves for aging systems. Buildings <10 years old 0.5–1.0% of value; 10–25 years 1.0–1.5%; 25–40 years 1.5–2.5%; >40 years 2.5–4.0%.
Debt Service & Exit Assumptions: Loan typically 60–70% of stabilized asset value. Use current market rate + 50 bps buffer. 5–7 year terms most common; 20–30 year amortization typical. Exit assumptions: Use market cap rate + 50 bps (assume slight cap rate expansion). Sale costs 1.5–2.0% (brokerage commission 1.0–1.25%, closing 0.5–0.75%). Model three exit scenarios: 6.5%, 7.0%, 7.5% cap rates to understand sensitivity.
Return Metrics & Stress-Testing
Key return metrics: Cash-on-Cash Year 1 (cash flow ÷ equity invested; typical 12–15% for value-add). Average cash-on-cash 5-year (average annual cash flow ÷ equity; indicates predictability). Unlevered IRR (all cash flows, no debt; typical 7–10% for stabilized). Levered IRR (equity IRR with debt financing; 12–18% for stabilized, 15–25% for value-add). Equity multiple (total proceeds ÷ equity invested; typical 2.0–4.0x for 5-year hold). Stress-test scenarios: base case (2% rent growth, 3% expense inflation, 7.0% exit cap), downside (0.5% rent growth, 4% expense inflation, 7.5% exit cap, +2% vacancy), aggressive (3% growth, 2% expenses, 6.5% exit). Downside typically reduces year 5 NOI 15–25%, equity multiple to 3.2–3.8x. Run sensitivity table: how do IRR and multiple change with different rent growth and exit cap assumptions? Property moving from 6.75% to 7.25% exit cap reduces returns 2–4% depending on rent growth.
Model Build & Common Pitfalls
Excel/Google Sheets structure: separate "Assumptions" tab (centralized; all variables in one place), "Pro Forma" tab (linked to assumptions; formulas calculate all values), "Summary" tab (outputs: cash flow, IRR, equity multiple, sensitivity tables). Key formulas: Rent growth = PriorYearRent × (1 + RentGrowthRate), EGI = PGI × (1 − VacancyRate), NOI = EGI − OpEx, Cash flow = NOI − DebtService − CapEx, Equity multiple = (SumCashFlows + ExitProceeds) ÷ EquityInvested. Pitfalls to avoid: (1) Using sponsor PGI without lease verification-request rent rolls and lease abstracts. (2) Not stress-testing debt covenants-model DSCR decline. (3) Assuming rents never decline-build recessionary scenario. (4) Using single exit cap rate-model multiple scenarios. (5) Ignoring refinance options at balloon-model refi alternatives.
Frequently Asked Questions
How conservative should rent growth be?
Use 5-year historical average from broker data. If historical 2.3%, use 2.0–2.2%. Justify any >3% with supply/demand data (low construction pipeline, high demand). Don't use optimistic projections without supporting evidence.
Should debt service escalate annually?
No. Debt service is fixed for loan term (nominal dollars). Interest rate doesn't change unless you refinance. Annual debt service payment locked at origination; cap rate at exit may change, but annual payment doesn't.
What CapEx should I budget without detailed data?
Conservative default: 1.5–2.0% of property value annually. Request property condition assessment or reserve study. If building >30 years old and assessment unavailable, use 2.0–2.5%.
How do I model value-add renovation scenarios?
Separate Year 1 (renovation) from Years 2–5 (stabilized). Year 1: higher vacancy (15–20%), lower rent (reno units start below market), higher CapEx (renovation). Years 2–5: stabilized rent growth (2.5–3% post-reno), normal vacancy (5–7%), normalized CapEx. Stabilization typically 18–24 months.