Skip to main content

How to Build a Real Estate Pro Forma in Excel from a Blank Sheet

10 min readUpdated July 22, 2026By Justin Acciavatti

 

 

A real estate pro forma is a cash flow forecast: assumptions about income, expenses, debt, and the eventual sale flow through a year by year waterfall to produce net operating income, levered cash flow, and investor returns.

This guide builds a 10-year commercial real estate pro forma from a blank Excel sheet, step by step: the Assumptions tab, the cash flow waterfall, debt service, the property sale, and the three return metrics that decide whether the deal is worth doing.

 

The Foundation: Structuring Your Model for Success

Before writing a single formula, we must structure our workbook. The cardinal rule of professional financial modeling is separation of inputs and outputs. All your assumptions should live on one tab, making the model easy to audit, update, and sensitize.

Our workbook will have two tabs:

  1. Assumptions: Where all our inputs and variables will live.
  2. Annual Summary: Where our 10-year cash flow waterfall and return calculations will be performed.

 

Step 1: Create the "Assumptions" Tab

This tab is the control panel for your entire model. Open a blank sheet and name it "Assumptions." Create the following sections, using clear labels and color-coding for input cells (e.g., blue text on a light gray background) to denote cells that are meant to be changed.

A. Core Deal Assumptions

  • Analysis Start Date: Jan. 1, 2026
  • Property Name: Example Value-Add Property
  • Hold Period (Years): 10
  • Purchase Price: $10,000,000
  • Exit Capitalization (Cap) Rate: 6.50%

B. Property & Income Assumptions

  • Gross Leasable Area (GLA) in SF: 50,000
  • Average In-Place Rent / SF / Yr: $20.00
  • General Vacancy & Credit Loss: 7.0%
  • Annual Rental Growth (Yrs 2-10): 3.0%

C. Operating Expense (OpEx) Assumptions (Enter these as a $/SF or as a % of Effective Gross Income)

  • Property Taxes: $3.50 / SF
  • Insurance: $0.75 / SF
  • Repairs & Maintenance: $1.25 / SF
  • Property Management Fee: 4.0% of EGI
  • Utilities (Landlord): $0.50 / SF
  • General & Administrative: $0.25 / SF
  • Annual OpEx Growth Rate: 2.5%

D. Financing Assumptions

  • Loan-to-Value (LTV): 65.0%
  • Interest Rate: 6.00%
  • Amortization Period (Years): 30

E. Capital Expenditures (CapEx) Assumptions

  • Initial Renovation Budget (Year 0): $500,000
  • CapEx Reserve / SF / Yr (Yrs 1-10): $0.50

Your Assumptions tab should now be a clean, organized dashboard for the entire investment thesis.

 

Step 2: Build the Annual Cash Flow Waterfall

Create a new tab named "Annual Summary." This is where the model comes to life. Set up your columns with "Year 0," "Year 1," "Year 2,"... up to "Year 10," and a final "Year 11" column that we will use to calculate the sale price.

Let's build the waterfall, row by row. Every number in this section should be a formula referencing your "Assumptions" tab. Do not hardcode numbers here.

Potential Gross Income (PGI) This is the total rental income if the property were 100% occupied.

  • Year 1 Formula: =Assumptions!B4 * Assumptions!B5 (GLA * Initial Rent/SF)
  • Year 2 Formula: =Year 1 PGI * (1 + Assumptions!B7) (Previous Year's PGI * (1 + Rental Growth))
  • Drag this formula across through Year 11.

(-) General Vacancy & Credit Loss

  • Formula: =PGI * Assumptions!B6 (Multiply PGI by the vacancy rate)
  • Drag this across for all years.

= Effective Gross Income (EGI)

  • Formula: =PGI - Vacancy
  • Drag this across.

Operating Expenses (OpEx) For each expense line item, we will reference the assumption and then grow it by the OpEx growth rate.

  • Property Taxes (Year 1): =Assumptions!C3 * Assumptions!B4
  • Property Taxes (Year 2): =Year 1 Property Taxes * (1 + Assumptions!C9)
  • Repeat this structure for Insurance, R&M, Utilities, and G&A.
  • Property Management Fee: Calculate this as a percentage of EGI for each year (=EGI * Assumptions!C6).

= Total Operating Expenses

  • Formula: =SUM(All OpEx line items)
  • Drag this across.

= Net Operating Income (NOI) This is the single most important profitability metric for a property before considering financing.

  • Formula: =EGI - Total Operating Expenses
  • Drag this across for all years. This is your unlevered cash flow from operations.

 

Every number in this section should be a formula referencing your Assumptions tab.

 

Step 3: Modeling Debt and Capital Costs ("Below the Line")

Now we account for financing and capital costs to determine the cash flow available to equity investors.

(-) Annual Debt Service First, go to your Assumptions tab and calculate the total loan amount: =Purchase Price * LTV. Then calculate the annual payment using the PMT function: =PMT(Interest Rate, Amortization Period, -Loan Amount).

  • Annual Debt Service Formula (on Annual Summary tab): Refer to your calculated annual payment cell on the Assumptions tab. Use an IF statement so it only applies during the hold period. =IF(Year > 0 and Year <= Hold Period, Assumptions!$D$6, 0)

(-) Capital Expenditures (CapEx) We have two types of CapEx.

  • Initial Renovation (Year 0): In the Year 0 column, link directly to the renovation budget on the Assumptions tab.
  • Recurring Reserves (Yrs 1-10): =Assumptions!E3 * Assumptions!B4 (Reserve/SF * GLA). Drag this across for Years 1-10.

= Cash Flow Before Tax (CFBT) This is the levered cash flow: the actual cash distributed to investors each year.

  • Formula: =NOI - Annual Debt Service - Recurring CapEx
  • Drag this across for Years 1-10.

 

Step 4: Modeling the Property Sale (Reversion Cash Flow)

At the end of the hold period (Year 10), we sell the property. The sale price is based on the Net Operating Income the property is expected to generate in the year after we sell it (Year 11).

Net Sale Price Calculation

  1. Gross Sale Price: In a separate section below your waterfall, calculate this. The formula is ='Annual Summary'!NOI for Year 11 / Assumptions!B6 (Year 11 NOI / Exit Cap Rate).
  2. Costs of Sale (e.g., 2%): Multiply the Gross Sale Price by your assumed selling cost percentage.
  3. Net Sale Price: =Gross Sale Price - Costs of Sale.

Reversion Cash Flow Calculation This is the final cash event in Year 10.

  1. Loan Payoff: Use the CUMPRINC function to determine the total principal paid over the 10-year hold period. The remaining loan balance is Initial Loan Amount - Principal Paid.
  2. Net Reversion Cash Flow: In the Year 10 column, the formula is =Net Sale Price - Loan Payoff.

 

 

Step 5: Calculating Investor Returns

You now have all the cash flows needed to measure the investment's performance.

First, create a final "Total Levered Cash Flow" line. This is the CFBT for years 1-9. For Year 0, it's the Purchase Price - Loan Amount + Initial Renovation Costs. For Year 10, it's the CFBT + Net Reversion Cash Flow.

1. Internal Rate of Return (IRR)

  • Levered IRR: Use the IRR function on the "Total Levered Cash Flow" line for Years 0-10. =IRR(range_of_cash_flows)
  • What it means: The annualized rate of return on the equity investment.

2. Equity Multiple (EM)

  • Formula: =SUM(Positive Levered Cash Flows for Yrs 1-10) / ABS(Year 0 Initial Equity Investment)
  • What it means: For every $1 of equity invested, how many dollars are returned. An EM of 2.5x means you got your original dollar back, plus another $1.50.

3. Cash-on-Cash Return

  • Formula (for each year): =CFBT / Initial Equity Investment
  • What it means: The cash income received each year as a percentage of the initial cash invested.

 

When to Build vs. When to Buy

Building your own model is the best way to learn the mechanics, and the version you just built will serve for study, screening, and simple deals.

However, if you are a professional analyzing a live, high-stakes transaction, you are operating under a deadline. The primary risks of a self-built model are subtle formula errors, flawed logic, and the time cost of debugging.

Our pre-built models are professionally audited and designed to save you dozens of hours, letting you focus on the deal itself, not on spreadsheet mechanics.

 

Conclusion: From Spreadsheet to Strategy

You have now built a dynamic real estate pro forma from the ground up. The model lets you test assumptions, quantify risk, and defend every number in your underwriting.

Change the exit cap rate, adjust the rental growth, or model a different financing structure on the "Assumptions" tab, and watch your entire 10-year forecast and return metrics update instantly.

Take the Next Step:

 

 

Frequently Asked Questions

What is a real estate pro forma?

A real estate pro forma is a forecast that translates a property's physical attributes and market assumptions into projected cash flow, returns, and risk across a defined hold period. It is built as an assumptions tab feeding a year by year cash flow waterfall. Ten years is the common hold period for an institutional model.

How do you calculate NOI in a pro forma?

Net operating income is effective gross income minus total operating expenses. Effective gross income is potential gross income less vacancy and credit loss, and operating expenses cover taxes, insurance, repairs, management, utilities, and administration. NOI is measured before any financing. That last point is why NOI is the standard basis for comparing properties carrying different debt.

What is the difference between NOI and cash flow before tax?

Net operating income is unlevered: it stops before financing and capital costs. Cash flow before tax subtracts annual debt service and recurring capital expenditure reserves from NOI, leaving the levered cash actually available to equity investors each year. A pro forma needs both, because the two answer different questions about the same deal.

How is the sale price calculated in a real estate pro forma?

The sale price is the net operating income of the year after the sale divided by the exit cap rate, so a ten year hold is priced off Year 11 NOI. Subtracting selling costs gives the net sale price, and subtracting the remaining loan balance gives the reversion cash flow. Pricing off the last year you own the asset understates what a buyer is actually purchasing.