Real Estate Pro Forma in Excel: Build a Property Cash Flow Model

A single misplaced assumption in a real estate pro forma can turn a profitable deal into a seven-figure loss. Yet most analysts still build property models from scratch every time, retyping the same formulas and hoping nothing breaks. Whether you're underwriting a multifamily acquisition, a retail development, or a mixed-use value-add deal, a well-structured real estate pro forma in Excel is the foundation of every investment decision.

This guide walks you through building a complete 10-year property cash flow model — from gross potential rent through levered IRR — with working formulas you can use today.

What Is a Real Estate Pro Forma?

A real estate pro forma is a forward-looking financial projection that estimates a property's income, expenses, and investment returns over a defined hold period. Think of it as a combined Income Statement and Cash Flow Statement built specifically for a property rather than a company — unlike a three-statement financial model, which dynamically integrates income, balance sheet, and cash flow statements for corporate analysis.

Unlike corporate financial models, a real estate pro forma typically excludes income taxes and depreciation from the operating section. Instead, it focuses on cash-on-cash returns, Net Operating Income (NOI), and metrics like IRR and equity multiple that drive investment decisions.

Key Components at a Glance

Component	What It Captures	Key Metric
Gross Potential Rent (GPR)	Maximum rent if 100% occupied	$/unit/month
Vacancy & Credit Loss	Expected unleased space + bad debt	% of GPR
Other Income	Parking, laundry, late fees	$/year
Operating Expenses	Taxes, insurance, maintenance, management	$/unit/year
Net Operating Income (NOI)	GPR − Vacancy − OpEx	$ and Cap Rate
Debt Service	Mortgage principal + interest	DSCR
Cash Flow Before Tax (CFBT)	NOI − CapEx − Debt Service	Cash-on-Cash %
Reversion (Sale)	Exit price − costs − loan payoff	IRR, Equity Multiple

💡 Pro Tip: Separate your inputs from calculations. All assumptions — rent growth, vacancy rate, cap rate, loan terms — should live on a dedicated Assumptions tab. Every number in the cash flow section should be a formula referencing that tab. This makes scenario analysis trivial.

How Do You Structure a Real Estate Pro Forma Workbook?

The best pro formas follow a modular architecture that separates data entry from calculations. Here's the recommended tab structure for a professional-grade model:

graph LR
    A[Assumptions Tab] --> B[Revenue Build-Up]
    A --> C[Operating Expenses]
    B --> D[NOI Calculation]
    C --> D
    A --> E[Debt Schedule]
    D --> F[Cash Flow Waterfall]
    E --> F
    F --> G[Returns Summary]

Tab 1: Assumptions

This is where every hardcoded number lives. Include:

• Property details: Square footage, unit count, purchase price, closing costs
• Revenue assumptions: Base rent per unit, annual rent growth rate, vacancy rate, other income
• Expense assumptions: Property tax, insurance, R&M, utilities, management fee (% of EGI)
• Financing: Loan-to-value (LTV), interest rate, amortization period, loan term
• Exit assumptions: Hold period, exit cap rate, selling costs (% of gross sale price)

Tab 2: Operating Pro Forma (the core model)

This is the 10-year projection with annual columns. We'll build this step by step below.

Tab 3: Debt Schedule

Amortization table showing monthly or annual principal and interest breakdowns.

Tab 4: Returns Dashboard

Summary metrics: IRR, equity multiple, cash-on-cash yield, and a sensitivity table.

How Do You Calculate NOI in Excel?

Net Operating Income (NOI) is the single most important number in real estate analysis. It represents the property's income after all operating expenses but before debt service and capital expenditures. Here's how to build it step by step.

Step 1: Gross Potential Rent (GPR)

GPR assumes the property is 100% occupied at market rent. For a multifamily property:

=Units * Rent_Per_Unit * 12

For example, a 50-unit apartment at $1,500/month:

=50 * 1500 * 12

That gives you $900,000 in Year 1 GPR.

To project GPR across years with annual rent growth:

=Prior_Year_GPR * (1 + Rent_Growth_Rate)

Step 2: Vacancy and Credit Loss

No property stays 100% occupied. Apply a vacancy factor:

=GPR * Vacancy_Rate

⚠️ Warning: Don't underestimate vacancy. Using 3% when the submarket averages 7% is one of the fastest ways to blow up a deal. Always benchmark against comparable properties and historical submarket data from sources like CoStar or CBRE.

Step 3: Effective Gross Income (EGI)

=GPR - Vacancy_Loss + Other_Income

Other income includes parking revenue, pet fees, laundry, storage, and application fees. For our example:

=900000 - (900000 * 0.05) + 24000 = $879,000

Step 4: Operating Expenses

Model each expense line item individually. Here's a typical breakdown:

Expense Category	Year 1 Amount	Calculation Method
Property Taxes	$90,000	Assessed value × mill rate
Insurance	$18,000	Per unit or flat annual
Repairs & Maintenance	$45,000	$/unit/year × units
Utilities	$30,000	Historical + growth rate
Property Management	$43,950	5% of EGI
General & Administrative	$12,000	Flat + inflation growth
Total OpEx	$238,950

To grow expenses annually:

=Prior_Year_Expense * (1 + OpEx_Growth_Rate)

ℹ️ Note: Property management fees should be calculated as a percentage of EGI, not GPR. This aligns the manager's incentive with actual collected income and is the industry standard for institutional underwriting.

Step 5: NOI

=EGI - Total_Operating_Expenses

For Year 1: $879,000 − $238,950 = $640,050

What Is Cap Rate and How Do You Use It in Excel?

The capitalization rate (cap rate) is the ratio of NOI to property value. It's the most widely used metric for comparing commercial real estate investments.

=NOI / Property_Value

If you're buying the property for $8,000,000 with a Year 1 NOI of $640,050:

=640050 / 8000000 = 8.0%

Cap rate also works in reverse to estimate property value at exit:

=Exit_Year_NOI / Exit_Cap_Rate

This is how you calculate the reversion value (sale price) at the end of your hold period. If Year 11 NOI is projected at $820,000 and your exit cap rate assumption is 7.5%:

=820000 / 0.075 = $10,933,333

Example: If you buy at an 8.0% cap rate and sell at a 7.5% cap rate (cap rate compression), you capture value appreciation beyond just NOI growth. A 50-basis-point compression on $820K NOI adds roughly $730,000 in exit value.

How Do You Model Debt Service in Excel?

Most commercial real estate acquisitions use leverage. You need to model the loan payments accurately to calculate levered returns.

Loan Setup

Assume these terms for our example:

• Purchase Price: $8,000,000
• LTV: 70% → Loan Amount: $5,600,000
• Interest Rate: 6.5%
• Amortization: 30 years
• Loan Term: 10 years (balloon payment at maturity)

Annual Debt Service

Use Excel's PMT function for the annual payment:

=PMT(Interest_Rate, Amortization_Years, -Loan_Amount)

=PMT(0.065, 30, -5600000) = $424,657

To split interest and principal for any given year, use IPMT and PPMT:

=IPMT(0.065, Year_Number, 30, -5600000)
=PPMT(0.065, Year_Number, 30, -5600000)

Debt Service Coverage Ratio (DSCR)

Lenders require a minimum DSCR, typically 1.20x–1.25x:

=NOI / Annual_Debt_Service

=640050 / 424657 = 1.51x

💡 Pro Tip: Always check DSCR in every projection year, not just Year 1. If rent growth stalls or expenses spike, a deal that looks safe today can breach covenants in Year 3. Use conditional formatting to flag any year where DSCR drops below 1.25x.

Commercial real estate and project finance share the same DSCR discipline, but project finance models in Excel take this concept further by sculpting the entire debt repayment schedule around a target DSCR — a useful technique for complex development deals with construction risk and uneven operating cash flows.

Remaining Loan Balance

To calculate the outstanding balance at exit (for the balloon payment), use CUMPRINC:

=Loan_Amount + CUMPRINC(Interest_Rate, Amortization_Years, Loan_Amount, 1, Hold_Period_Years, 0)

Or more intuitively, use FV:

=FV(0.065, 10, -PMT(0.065, 30, -5600000), -5600000)

This gives you the remaining principal balance after 10 years of amortization. For deals with multiple tranches, floating-rate structures, or covenant-heavy credit facilities, our debt schedule credit analysis guide extends this single-loan framework with BASE rows, waterfall repayment logic, and the DSCR coverage checks that lenders actually monitor.

Building the 10-Year Cash Flow Waterfall

Now assemble everything into the core projection. Here's the complete waterfall structure:

graph TD
    A[Gross Potential Rent] --> B[Less: Vacancy & Credit Loss]
    B --> C[Plus: Other Income]
    C --> D[Effective Gross Income]
    D --> E[Less: Operating Expenses]
    E --> F[Net Operating Income NOI]
    F --> G[Less: Capital Expenditures]
    G --> H[Less: Debt Service]
    H --> I[Cash Flow Before Tax CFBT]
    I --> J[Plus: Reversion in Exit Year]
    J --> K[Total Levered Cash Flow]

Year 0: Initial Investment

Year 0 captures your equity outlay:

=-(Purchase_Price * (1 - LTV) + Closing_Costs + Initial_CapEx)

For our example:

=-(8000000 * 0.30 + 160000 + 200000) = -$2,760,000

Years 1–9: Operating Cash Flow

Each year follows the same waterfall:

CFBT = NOI - CapEx - Debt_Service

Year 10: Exit Year

In the final year, add the net reversion proceeds:

Net_Reversion = Gross_Sale_Price - Selling_Costs - Remaining_Loan_Balance
Total_Year_10 = CFBT + Net_Reversion

Complete Annual Projection Example

Line Item	Year 0	Year 1	Year 5	Year 10
Gross Potential Rent	—	$900,000	$1,048,630	$1,268,124
Less: Vacancy (5%)	—	($45,000)	($52,432)	($63,406)
Plus: Other Income	—	$24,000	$27,975	$33,843
Effective Gross Income	—	$879,000	$1,024,173	$1,238,561
Less: Operating Expenses	—	($238,950)	($270,703)	($314,524)
NOI	—	$640,050	$753,470	$924,037
Less: CapEx	—	($25,000)	($28,310)	($32,894)
Less: Debt Service	—	($424,657)	($424,657)	($424,657)
CFBT	—	$190,393	$300,503	$466,486
Net Reversion	—	—	—	$5,617,422
Total Levered Cash Flow	($2,760,000)	$190,393	$300,503	$6,083,908

ℹ️ Note: Revenue grows at 3.1% annually (2% rent growth compounded with occupancy stabilization), while expenses grow at 2.5%. This spread between revenue and expense growth is called the NOI growth spread — it's what drives value creation in a hold strategy.

How Do You Calculate IRR and Equity Multiple for Real Estate?

These are the two metrics every real estate investor looks at first.

IRR (Internal Rate of Return)

The IRR is the discount rate that makes the net present value of all cash flows equal to zero. In Excel:

=IRR(Total_Levered_Cash_Flow_Range)

Where the range includes Year 0 (negative equity) through Year 10 (CFBT + reversion). For our example, this yields approximately 16.8% levered IRR.

For irregular cash flow timing (e.g., mid-year acquisition), use XIRR instead:

=XIRR(Cash_Flow_Values, Date_Values)

⚠️ Warning: IRR assumes equal time periods between cash flows and reinvestment at the IRR rate itself. For deals with uneven cash flow timing or very high returns, XIRR gives a more accurate result. Always use XIRR for institutional-quality models.

Equity Multiple

The equity multiple tells you how many times you get your money back:

=SUM(All_Positive_Cash_Flows) / ABS(Initial_Equity)

For our model: approximately 2.3x equity multiple, meaning for every $1 invested, the investor receives $2.30 back over the hold period. In fund-structured deals with GP/LP equity splits — common in institutional real estate — these levered proceeds flow through a distribution waterfall before reaching each investor. Our guide to building a PE waterfall model in Excel covers the preferred return, GP catch-up, and carried interest tiers that govern how those returns are divided between general partners and limited partners.

Cash-on-Cash Return

This annual metric shows the yield on invested equity:

=CFBT / Initial_Equity

Year 1: $190,393 / $2,760,000 = 6.9%

graph TD
    A{What are you measuring?} --> B[Annual income yield]
    A --> C[Total return over hold period]
    A --> D[Multiple of money back]
    B --> E[Cash-on-Cash Return]
    C --> F[IRR]
    D --> G[Equity Multiple]
    E --> H[Compare to: bond yields]
    F --> I[Compare to: WACC, hurdle rate]
    G --> J[Compare to: 2.0x target]

Building a Sensitivity Table for Cap Rate and Rent Growth

No serious real estate investor relies on a single-scenario projection. A two-way sensitivity table in Excel shows how returns change across different assumptions — for a complete step-by-step walkthrough of these techniques, see our guide to sensitivity analysis in Excel. For deals with multiple interacting risk variables — vacancy, rent growth, cap rate, and interest rates all uncertain simultaneously — a Monte Carlo simulation in Excel transforms that two-way table into a probability distribution of levered IRR outcomes, letting you quantify the exact likelihood of achieving your return threshold.

Setting Up a 2-Way Data Table

Create a matrix with exit cap rates along the top and rent growth rates down the side. The intersecting cell references your IRR formula.

Example layout:

IRR Sensitivity	6.5% Cap	7.0% Cap	7.5% Cap	8.0% Cap	8.5% Cap
1.0% Rent Growth	14.2%	12.8%	11.5%	10.3%	9.2%
2.0% Rent Growth	16.1%	14.7%	13.4%	12.2%	11.1%
3.0% Rent Growth	18.0%	16.8%	15.4%	14.2%	13.1%
4.0% Rent Growth	20.1%	18.8%	17.5%	16.3%	15.2%
5.0% Rent Growth	22.2%	20.9%	19.6%	18.4%	17.3%

To build this in Excel:

1. Place the exit cap rate values in a row (e.g., C1:G1)
2. Place the rent growth values in a column (e.g., B2:B6)
3. In B1, enter the formula that references your IRR cell
4. Select the entire range B1:G6
5. Go to Data → What-If Analysis → Data Table
6. Row input cell = your exit cap rate assumption cell
7. Column input cell = your rent growth assumption cell

💡 Pro Tip: Add conditional formatting with a green-to-red color scale on your sensitivity table. This instantly shows where the deal works and where it falls apart. Use the threshold your fund requires (e.g., 15% minimum IRR) as the midpoint.

Common Mistakes in Real Estate Pro Formas

Even experienced analysts make these errors. Check your model against this list:

1. Hardcoding numbers in the cash flow section. Every value should trace back to the Assumptions tab. If you change a rent assumption and your Year 5 NOI doesn't update, something is hardcoded.

2. Forgetting to gross up property taxes post-acquisition. Many jurisdictions reassess property taxes at the purchase price. If you model taxes based on the seller's assessed value, you'll understate expenses significantly.

Example: A property with a $5M assessed value at 1.5% tax rate pays $75K/year. If you acquire it for $8M and the county reassesses, taxes jump to $120K — a $45K/year increase that directly reduces NOI. At a 7.5% cap rate, that wipes out $600K of property value.

3. Using NPV instead of XNPV. Excel's NPV function assumes cash flows start one period from today. For real estate deals with specific closing dates, XNPV handles actual dates correctly.

4. Ignoring tenant improvement (TI) and leasing commission (LC) costs. For commercial properties, these costs can run $30–$60/SF and recur at lease expiration. Omitting them inflates cash flow projections.

💡 Pro Tip: Model TI/LC costs as a reserve line item every year ($2-5/SF annually), not just at lease expiration. This smooths cash flow projections and avoids the false picture of high returns in non-rollover years followed by a cash flow cliff when leases expire.

5. Applying a single vacancy rate across all years. New acquisitions may have lease-up periods. Value-add deals might see higher vacancy during renovations. Model vacancy dynamically based on the business plan.

6. Using the wrong cap rate for exit. Your exit cap rate should generally be 25–50 basis points higher than your entry cap rate to be conservative. Assuming cap rate compression without justification is a red flag.

Frequently Asked Questions

What is the difference between NOI and cash flow in real estate?

NOI measures property-level operating performance: revenue minus operating expenses. Cash flow (CFBT) goes further by subtracting capital expenditures and debt service from NOI. NOI tells you how the property performs; cash flow tells you what the investor actually takes home. Lenders focus on NOI for DSCR; equity investors focus on cash flow for returns.

How do you calculate cap rate from NOI?

Divide the property's annual Net Operating Income by its current market value or purchase price. The formula is Cap Rate = NOI / Property Value. An $800,000 NOI on a $10 million property equals an 8.0% cap rate. Lower cap rates indicate higher prices relative to income, typically seen in core markets with lower perceived risk.

Should I use IRR or cash-on-cash return for real estate analysis?

Use both — they answer different questions. Cash-on-cash return measures annual income yield on your equity investment, useful for comparing against other income-producing assets. IRR captures the total return including appreciation and the time value of money over the entire hold period. A deal can have low cash-on-cash but high IRR if most of the return comes from exit proceeds.

How many years should a real estate pro forma project?

Match the projection period to your intended hold period, typically 5–10 years for most institutional investors. A 10-year model is standard for stabilized assets. Development deals may need 2–3 years of construction plus 5–7 years of operations. Always project at least one year beyond your exit to calculate the reversion value based on forward NOI.

What vacancy rate should I use in my pro forma?

Use the higher of your property's historical vacancy and the submarket average, then add a buffer. For stabilized multifamily in strong markets, 5–7% is typical. Office and retail may warrant 8–12% depending on market conditions. During lease-up or renovation periods, model vacancy month-by-month rather than applying a flat annual rate.

Putting It All Together

A well-built real estate pro forma is more than a spreadsheet — it's a decision-making framework. The formulas and structure covered here give you the foundation to underwrite any commercial property acquisition, from a 20-unit apartment to a Class A office tower.

The key is discipline: keep assumptions separate from calculations, validate every input against market data, and stress-test your model with sensitivity analysis before committing capital. If data preparation is a bottleneck — cleaning ERP exports or consolidating files across multiple properties — Power Query can automate that entire workflow. Tools like VeloraAI can accelerate formula building and catch errors in complex models, letting you focus on the deal analysis rather than spreadsheet mechanics.

Start with a single deal, build the model from scratch using the steps above, and refine your template with each transaction. For investors building out a multi-property portfolio, portfolio optimization with Excel's Solver extends this framework — applying mean-variance optimization to find the efficient frontier across property types, geographies, and risk profiles. That hands-on repetition is what separates analysts who understand their models from those who just fill in templates.