Skip to main content

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

15 min readUpdated July 27, 2026By TILT Analytics

 

 
A broker pro forma shows a $10 million purchase, 65% leverage, 3% annual rent growth, and a 12% levered IRR. The property tax bill is based on the seller's current basis, the exit is priced off an optimistic cap rate, and the spreadsheet still looks precise. That is how a deal clears a hurdle rate before anyone has tested the assumptions.

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: 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.

 

How Should You Structure a Real Estate Pro Forma Workbook?

Use one Assumptions tab for inputs and one Annual Summary tab for the waterfall and returns. This separation makes the professional financial model auditable and easier to 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.
For the formulas below, use this explicit cell map on the Assumptions tab: B4 GLA, B5 initial rent, B6 vacancy and credit loss, B7 rent growth, B8 hold period, B9 purchase price, B10 exit cap rate, B11 fixed OpEx growth, B12 management fee, B13 LTV, B14 interest rate, B15 amortization period, B16 initial renovation, B17 recurring reserve, B18 selling costs, B19 annual debt service, and B20:B24 property taxes, insurance, repairs and maintenance, utilities, and G&A per SF. Keep labels beside these cells so an auditor can trace every formula.

The equations below use named line items such as PGI, EGI, and NOI. They are mathematical formulas unless an Excel function and fully defined range are shown. They are not presented as copy-paste-ready cell formulas.

 

How Do You Create the Assumptions Tab?

The Assumptions tab is the control panel for the model. Label each input clearly and mark cells intended to change, such as with blue text on a light gray background.

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% (illustrative teaching input)

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% (illustrative teaching input)
  • Annual Rental Growth (Yrs 2-10): 3.0% (illustrative teaching input)

C. Operating Expense (OpEx) Assumptions (Enter these as a $/SF or as a % of Effective Gross Income. Do not treat these sample values as market benchmarks.)

  • 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% (illustrative teaching input)

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
  • Selling Costs: 2.0% of gross sale price (illustrative teaching input)

The Assumptions tab now holds every input the investment thesis rests on.

 

How Do You Build the Annual Cash Flow Waterfall?

Create an Annual Summary tab with Year 0 through Year 10 and a Year 11 column for the terminal NOI. Reference the Assumptions tab for every input and do not hardcode operating assumptions in the waterfall.

Let's build the waterfall, row by row. Every number in this section should reference the Assumptions tab.

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

  • Mathematical formula for Year 1: PGI_1 = GLA x initial rent, using GLA = Assumptions!B4 and initial rent = Assumptions!B5.
  • Mathematical formula for Year 2 onward: PGI_y = PGI_(y-1) x (1 + rent growth), using rent growth = Assumptions!B7.
  • Apply the equation across through Year 11.

(-) General Vacancy & Credit Loss

  • Mathematical formula: Vacancy_y = PGI_y x vacancy rate, using vacancy rate = Assumptions!B6. This is a deduction from PGI, not an additional expense.
  • Apply the equation across for all years.

= Effective Gross Income (EGI)

  • Mathematical formula: EGI_y = PGI_y - Vacancy_y
  • Apply the equation across.

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

  • Property taxes, Year 1: Property taxes_1 = property tax/SF x GLA, using property tax/SF = Assumptions!B20 and GLA = Assumptions!B4.
  • Property taxes, Year 2 onward: Property taxes_y = Property taxes_(y-1) x (1 + OpEx growth), using OpEx growth = Assumptions!B11.
  • Repeat this structure for Insurance, R&M, Utilities, and G&A.
  • Property management fee: Management fee_y = EGI_y x management fee rate, using management fee rate = Assumptions!B12.

= Total Operating Expenses

  • Mathematical formula: Total OpEx_y = property taxes_y + insurance_y + R&M_y + utilities_y + G&A_y + management fee_y
  • Apply the equation across.

= Net Operating Income (NOI) This is the property's operating profitability before any financing.

  • Mathematical formula: NOI_y = EGI_y - Total OpEx_y
  • Apply the equation across for all years. NOI is a property-level, unlevered operating measure. It is not taxable income, GAAP net income, or a complete liquidity measure, and definitions can vary by property and reporting convention.

Check the first year before building the rest. With the teaching inputs above, Year 1 PGI is 50,000 x $20.00 = $1,000,000. Vacancy and credit loss is $1,000,000 x 7.0% = $70,000, so EGI is $930,000. Fixed OpEx is ($3.50 + $0.75 + $1.25 + $0.50 + $0.25) x 50,000 = $312,500. The management fee is $930,000 x 4.0% = $37,200. Year 1 NOI is therefore $930,000 - $312,500 - $37,200 = $580,300. If the spreadsheet does not produce these values, stop and fix the inputs or references before proceeding.

Tax and accounting boundary: Keep the underwriting cash-flow convention separate from a tax return and financial statements. Mortgage principal is debt repayment, not an operating expense. Mortgage interest, depreciation, repairs, improvements, property taxes, and sale-related taxes can have different treatment depending on the property, entity, jurisdiction, basis, and accounting method. Improvements are generally capitalized rather than treated as ordinary repairs for tax purposes, and recurring reserves are a modeling convention that should not be assumed to equal a book or tax expense. Consult the applicable tax and accounting professionals before using this model for reporting.

 

Every number in this section should reference your Assumptions tab.

 

How Do You Model Debt and Capital Costs?

Subtract annual debt service and recurring reserves from NOI to produce the modeled cash flow before tax. This teaching case uses annual, end-of-period payments, but a live loan may pay monthly, include interest-only periods, float, require escrows, or charge fees.

(-) Annual Debt Service First, calculate the total loan amount: =Assumptions!B9 * Assumptions!B13. With the sample inputs, that is $10,000,000 x 65.0% = $6,500,000. Define the Excel named range LoanAmount as that loan amount. For annual end-of-period payments, use =PMT(Assumptions!B14, Assumptions!B15, -LoanAmount), which returns $472,217.92 for this case. PMT includes principal and interest, not taxes, reserves, or loan fees. If payments are monthly, use the mathematical form Debt service = -PMT(annual rate / 12, amortization years x 12, LoanAmount).

  • Mathematical formula: Debt service_y = Assumptions!B19 when 0 < y <= Assumptions!B8; otherwise Debt service_y = 0.

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

  • Initial renovation, Year 0: Link to Assumptions!B16 and treat it as a cash outflow in the total levered cash-flow line.
  • Recurring reserves, Years 1-10: Recurring reserves_y = reserve/SF x GLA, using reserve/SF = Assumptions!B17 and GLA = Assumptions!B4.

= Cash Flow Before Tax (CFBT) This is the model's levered cash-flow line before income taxes, distributions, and sale proceeds.

  • Mathematical formula: CFBT_y = NOI_y - Debt service_y - Recurring reserves_y
  • Apply the equation across for Years 1-10. With Year 1 NOI of $580,300, annual debt service of $472,217.92, and recurring reserve of $25,000, Year 1 CFBT is $83,082.08. This is a modeled pre-income-tax cash-flow line, not taxable income and not a promise that all cash will be distributed.

Lending checks: LTV is loan amount / value, so the sample 65.0% is an input, not a universal lending standard. DSCR is commonly calculated as NOI / total debt service, but lenders may adjust NOI, reserves, payment terms, and the stress rate. Review market vacancy and expenses, test lower NOI and higher rates, and compare the result with the actual lender's requirements. There is no single 2026 LTV, interest rate, amortization period, or DSCR threshold that applies to every property, lender, market, or loan product.

 

How Do You Model the Property Sale?

Price the sale using Year 11 NOI divided by the exit cap rate, then subtract selling costs and the remaining loan balance. This is a terminal-value convention, not a tax calculation.

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: Gross sale price = Year 11 NOI / exit cap rate, using exit cap rate = Assumptions!B10.
  2. Costs of sale: Costs of sale = Gross sale price x selling-cost rate, using selling-cost rate = Assumptions!B18. The sample 2.0% is illustrative, not a universal transaction cost.
  3. Net sale price: Net sale price = Gross sale price - Costs of sale.

For this teaching case, Year 11 PGI is $1,000,000 x 1.03^10 = $1,343,916.38, Year 11 EGI is $1,249,842.23, and Year 11 NOI is $799,822.12. At a 6.50% exit cap, gross sale price is $799,822.12 / 6.50% = $12,300,495.74. At the illustrative 2.0% selling-cost assumption, net sale price is $12,054,485.83.
Reversion Cash Flow Calculation This is the final cash event in Year 10.

  1. Loan payoff: Define L0 = Assumptions!B2 x Assumptions!B13, annual interest rate as Assumptions!B14, amortization periods as Assumptions!B15, and hold period as Assumptions!B8. Then use Loan balance = L0 - ABS(CUMPRINC(Assumptions!B14, Assumptions!B15, L0, 1, Assumptions!B8, 0)). Check the sign returned by your Excel version and keep the balance nonnegative.
  2. Net reversion cash flow: Net reversion cash flow = Net sale price - Loan balance.

Under the sample annual-payment convention, the loan balance after 10 payments is $5,416,302.39, so net reversion before any sale tax or other closing adjustments is $12,054,485.83 - $5,416,302.39 = $6,638,183.44. Do not treat this sale calculation as a tax calculation.

 

 

How Do You Calculate Investor Returns?

Use one Total Levered Cash Flow line for Year 0 through Year 10, then calculate levered IRR, equity multiple, and cash-on-cash return from that line with clearly stated denominators.

First, create a final "Total Levered Cash Flow" line. This is the CFBT for years 1-9. For Year 0, it is the negative initial equity contribution: Initial equity = purchase price - loan amount + initial renovation costs. With the sample inputs, that is -$4,000,000. For Year 10, it is CFBT + Net Reversion Cash Flow. Include acquisition costs, financing fees, additional equity contributions, and distributions if they apply to the deal.
1. Internal Rate of Return (IRR)

  • Levered IRR: Define the Excel named range TotalLeveredCashFlow as the Year 0 through Year 10 cells on the Total Levered Cash Flow line. Then use =IRR(TotalLeveredCashFlow).
  • What it means: The annualized rate of return on the equity investment.

2. Equity Multiple (EM)

  • Mathematical formula: EM = sum of positive Total Levered Cash Flow values for Years 1-10 / absolute value of Year 0 equity. If the deal requires later equity contributions, include total contributions in the denominator instead.
  • What it means: For every $1 of equity contributed, how many dollars are returned. An EM of 2.5x means $2.50 returned for each $1.00 contributed, including the original dollar.

3. Cash-on-Cash Return

  • Mathematical formula: Cash-on-cash_y = CFBT_y / absolute value of Year 0 equity.
  • What it means: The modeled pre-income-tax operating cash flow for that year as a percentage of the initial equity. State the denominator and whether reserves, additional contributions, or sale proceeds are included.

 

Should You Build This Yourself or Buy It?

Build it yourself to learn the mechanics, but use a reviewed model or professional tool when a live transaction depends on the result and the cost of a formula error exceeds the cost of the tool.

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.

On a live transaction with a deadline, the risks of a self-built model are subtle formula errors, flawed logic, and the time cost of debugging.

TILT's pre-built models provide a starting point for the assumptions, operating cash flow, debt, reversion, and return calculations described here.

 

What Does the Finished Model Give You?

The finished model lets you test assumptions, quantify risk, and defend every number in your underwriting because the forecast and return metrics recalculate from a controlled assumptions tab.

Change the exit cap rate, the rental growth, or the financing structure on the "Assumptions" tab, and the 10-year forecast and the return metrics recalculate.
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. The hold period should match the investment plan, financing, business plan, and exit strategy. Ten years is only the teaching horizon used in this article.

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 the property-level costs included by the model. NOI is measured before financing. Because NOI is unlevered, it can help compare property operations across different debt structures, but definitions and included expenses should be stated.

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

Net operating income is unlevered: it stops before financing and capital costs. In this model, cash flow before tax subtracts annual debt service and recurring capital expenditure reserves from NOI, producing a modeled levered cash-flow line before income taxes. 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?

A common terminal-value convention is the net operating income of the year after the sale divided by the exit cap rate, so this ten year teaching case 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. Use the terminal NOI convention, timing, cap rate, and sale costs supported by the valuation assignment and transaction assumptions.