The Pro Forma Spreadsheet

A pro forma is a year-by-year projection of cash flows, expenses, and returns for a ten-year hold (or whatever your intended hold period). It transforms scattered assumptions into a coherent view of whether the deal delivers the returns you need.

Key takeaways

• A pro forma should model ten years: rent growth, operating expenses, debt paydown, and exit assumptions
• The base case uses conservative-but-realistic rent growth (2–3% annually), expense growth (3–5%), and an exit cap rate near current market
• Sensitivity analysis shows how returns change if rent growth is 1% lower, cap rates 50 bps higher, or occupancy 10 points lower
• The pro forma reveals break-even occupancy, annual cash flows, levered IRR, and equity multiple—all essential outputs
• Most professional investors won't bid on a deal without a pro forma showing the deal hits their required return under the base case and survives reasonable downside scenarios

The basic structure

A ten-year pro forma has these rows:

Assumption section (top of sheet):

• Purchase price, property type, number of units/rentable square feet
• Down payment, loan amount, loan rate, loan term
• Year 1 rent per unit (or per SF)
• Annual rent growth % (usually 2–3%)
• Operating expense estimates (property tax, insurance, maintenance, management, reserves)
• Annual operating expense growth % (usually 3–5%)
• Exit cap rate (year 10)

Year-by-year section (columns for years 1–10):

1. Potential Rental Income (PRI): Prior year's rents × (1 + growth rate). Year 1 is your entry assumption.
2. Vacancy loss: PRI × (1 − occupancy rate). Assume 5–10% for new acquisitions, 3–5% for stabilized properties.
3. Effective Rental Income (ERI): PRI − Vacancy loss.
4. Operating expenses: Prior year × (1 + expense growth rate). Should include all costs: property tax, insurance, maintenance, utilities (if paid by owner), management, legal/accounting, reserves.
5. Net Operating Income (NOI): ERI − Operating Expenses. This is your "property-level" profit, before debt.
6. Debt service: Principal + interest from your loan schedule. Should decrease as principal is paid off (if not interest-only).
7. Cash flow before sale: NOI − Debt Service.
8. Exit sale (year 10 only): Property value = NOI in year 10 / Exit Cap Rate. Minus remaining loan balance and selling costs (typically 5–7% of sale price). This is gross equity available.
9. Levered cash flow: Cumulative annual cash flows + equity at exit.

Summary outputs:

• Total cash invested (down payment + cash injections)
• Cumulative cash flow (sum of annual cash flows to equity)
• Year 10 sale proceeds (net of debt payoff and costs)
• Total equity gain = cash flows + sale proceeds − initial investment
• Levered IRR = annualized return on equity invested
• Equity multiple = total equity gain / initial investment

A worked example: the 10-unit apartment

Assumptions:

• Purchase price: $1,000,000
• Down payment: 25% = $250,000
• Loan: $750,000, 5.5% fixed, 30-year amortization
• Year 1 rent: $1,200/unit/month
• Rent growth: 3% annually
• Operating expenses (year 1): 35% of ERI
• Expense growth: 4% annually
• Exit cap rate (year 10): 5.0%
• Occupancy: 92% (5% year 1, stabilizing at 92%)

Year 1:

• PRI = 10 units × $1,200/month × 12 = $144,000
• Vacancy = $144,000 × 0.05 = $7,200
• ERI = $136,800
• OpEx = $136,800 × 0.35 = $47,880
• NOI = $88,920
• Debt Service (year 1): ~$47,500 (principal + interest from $750k at 5.5%)
• Cash Flow = $88,920 − $47,500 = $41,420
• Cash-on-Cash Return = $41,420 / $250,000 = 16.6%

That looks strong. But let's see years 2–10.

Year 5:

• PRI = $144,000 × (1.03^4) = $162,100
• Vacancy = $162,100 × 0.03 = $4,863 (occupancy improved to 97%)
• ERI = $157,237
• OpEx = $157,237 × 0.35 × (1.04^4) = $66,900 (expenses grew but also as % of income)
• NOI = $90,337
• Debt Service: ~$46,900 (some principal paid down, lower interest)
• Cash Flow = $43,437

Year 10:

• PRI = $144,000 × (1.03^9) = $187,700
• Vacancy = $187,700 × 0.03 = $5,631
• ERI = $182,069
• OpEx = $182,069 × 0.35 × (1.04^9) = $99,100
• NOI = $82,969
• Debt Service: ~$46,000 (further paydown, low remaining balance)
• Annual Cash Flow = $36,969

Exit (year 10):

• Sale price = $82,969 / 0.05 = $1,659,380 (using exit cap rate of 5.0%)
• Remaining loan balance: ~$650,000 (after 10 years of amortization)
• Selling costs: $1,659,380 × 0.06 = $99,563
• Net proceeds = $1,659,380 − $650,000 − $99,563 = $909,817
• Plus cumulative cash flow from years 1–9: ~$365,000
• Total equity gain = $909,817 + $365,000 − $250,000 initial = $1,024,817

Levered IRR: ~22% annually (principal paydown + appreciation + cash flow) Equity Multiple: 1,024,817 / 250,000 = 4.1x (reasonable for a ten-year hold in stable market)

This is a stylized example; real pro formas are more granular (monthly lease expiration, seasonal occupancy, capital improvements), but the principle is identical.

The critical assumptions

Rent growth: Most underwriters use 2–3% nominal rent growth, implying ~1% real (inflation-adjusted) growth. If you're assuming 5% rent growth, you're betting on above-market dynamics (population inflow, supply constraints). That's fine if you can document it, but it's a risk assumption, not a conservative one.

Operating expenses: Many first-time buyers underestimate these. Rule of thumb: budget 30–40% of ERI for multi-family, 35–50% for office or retail. If your pro forma shows 25%, you're likely underestimating maintenance reserves, property tax increases, or management costs.

Exit cap rate: This is perhaps the most consequential assumption. Many investors assume exit cap rate equals entry cap rate (5.0% if they buy at 5.0%). But cap rates move with interest rates. If rates rise, cap rates rise, and your exit sale price falls. Model exit cap rate 25–50 bps higher than current market to be conservative.

If you bought at 4.5% cap rate and assume 5.0% exit, you're factoring in cap rate compression (rates falling, values rising). That's a bet on duration and interest rate direction.

Sensitivity analysis

The pro forma base case is one scenario. The real insight comes from sensitivity tables: how does IRR change if rent growth is 1% instead of 3%? If vacancy is 10% instead of 5%? If exit cap rate is 5.5% instead of 5.0%?

A robust pro forma includes sensitivity tables for:

Rent Growth	1% Vacancy	2% Vacancy	3% Vacancy
1%	12% IRR	11% IRR	10% IRR
2%	15% IRR	14% IRR	13% IRR
3%	18% IRR	17% IRR	16% IRR

If your required return is 15%, the above table shows you can tolerate 1–2% rent growth and 1–3% vacancy and still hit target. But if rent growth falls to 0.5% or vacancy rises to 5%, you miss. This reveals which assumptions are load-bearing.

Similarly, sensitivity to exit cap rate:

Exit Cap Rate	Year 10 NOI
4.5%	$1,843,000 property value
5.0%	$1,659,000 property value
5.5%	$1,512,000 property value

A 1-percentage-point move in cap rate changes your exit value by ~$330,000, or $33,000+ per year in returned IRR. That's why cap rate assumption is so critical.

Common errors

Conflating base case with optimism: The base case should be realistic, not the "good" scenario. If you assume 4% rent growth in a market averaging 2%, you're building in optimism that should be labeled as such.

Holding exit cap rate constant: Rates and spreads change. A realistic pro forma models exit cap rate 25–50 bps higher than entry, reflecting that you're holding in a different rate environment.

Underestimating operating expense growth: Property taxes rise 3–5% annually in most jurisdictions; insurance rises 4–7%; maintenance is lumpy but averages 3–5%. A 2% operating expense growth assumption is probably too low.

Ignoring capital improvements: Most ten-year holds require some re-tenanting, roof work, parking lot reseal, or unit renovations. Budget $500–1,000 per unit over ten years.

Forgetting to include reserves: Prudent underwriting sets aside 5–10% of income for capital reserves, replacement reserves, or vacancy buffers. These come out of cash flow.

When to use the pro forma to walk away

If the base case (realistic assumptions, modest rent growth, conservative exit cap rate) shows IRR under your required return, walk away. If the base case is solid but sensitivity analysis shows you're vulnerable to a 25 bps cap rate move, tighten your bid.

If assumptions are the only thing hitting your target (worst-case occupancy scenario, exit cap rate at 4.25% in a rising-rate environment), the deal is built on hope, not economics.

Pro forma structure flowchart

Related concepts

• Net Operating Income (NOI)
• Cap Rate Explained

Next

The pro forma is only as good as its assumptions. We'll dive into the rent growth assumption—the single biggest driver of ten-year returns.