How to Build a Real Estate Pro Forma in Excel (Step-by-Step)
Introduction
If you're learning real estate finance, building a pro forma might sound complicated. But it doesn’t have to be. A pro forma is just a projection of how much money a property is expected to bring in and spend — kind of like a detailed income statement for a building.
This walkthrough uses simple numbers and clear examples to show you how to build a Year 1 pro forma for a commercial property — the kind you'd see in retail, office, or industrial real estate. (Multifamily and hotel properties are different and thus have some distinctive line items.)
Let’s break it down line by line.
The Example
Below we have revenue and expense assumptions, enough for us to build a Year 1 pro forma.
Step 1: Potential Gross Income (PGI)
The first line of the pro forma is Potential Gross Income (PGI). This is the total rental income a property would generate if it were fully leased at market rents with 100% of tenants paying on time. It represents a best-case scenario under absolutely perfect conditions—no vacancy, no delinquencies, and no concessions. PGI is the starting point for projecting a property’s revenue, before any adjustments for real-world factors.
PGI = $10.00/SF * 100,000 SF = $1,000,000
Step 2: Vacancy and Credit Loss
Vacancy refers to the income lost when space in a property is physically unoccupied and not generating rent. Credit Loss is the income lost when tenants occupy space but fail to pay rent as agreed. While they represent different types of risk, they’re commonly grouped into a single line item in real estate pro formas because both reduce collected income and are typically estimated together as a conservative percentage of gross potential rent.
Vacancy and Credit Loss = $1,000,000 * -5% = -$50,000
Step 3: Free Rent Concession
A Free Rent Concession is a period—often one or two months—when a landlord allows a tenant to occupy the space without paying rent. It’s used as an incentive to attract tenants, especially in competitive markets or during lease-up periods. While it lowers first-year income, it can help secure long-term occupancy and reduce overall vacancy.
Free Rent Concession = -$1,000,000 / 12 * 1 = -$83,333
Step 4: Tenant Improvement (TI) Concession
A Tenant Improvement (TI) Concession is an allowance provided by the landlord to cover the cost of customizing or building out a tenant’s space—such as installing walls, flooring, or lighting. It’s used as a lease incentive, especially in office and retail, to attract or retain tenants by helping them tailor the space to their needs. TI costs are typically negotiated during lease terms and are paid by the landlord upfront or as a reimbursement.
Tenant Improvement (TI) Concession = -$1.00 * 100,000 * 1 = -$100,000
Step 5: Expense Reimbursements
In many commercial leases, tenants cover some (or all) of the property’s operating expenses — like taxes, insurance, common area maintenance (CAM), and utilities. This income is called Expense Reimbursements, and it's especially common in net leases. To calculate this, you need to calculate the expenses first, since the formula relies directly on the expense values.
In this example, due to the triple-net lease, the reimbursements are based on:
Property Taxes = $120,000
Insurance = $30,000
Repairs & Maintenance = $50,000
Utilities = $40,000
Reimbursements = $240,000
Step 5: Effective Gross Income (EGI)
This is what you realistically expect to collect — after concessions and vacancy, and including reimbursements.
EGI = PGI - Vacancy and Credit Loss - Free Rent Concession + Tenant Improvement (TI) Concession + Expense Reimbursements
EGI = $1,000,000 - $50,000 - $83,333 - $100,000 + $240,000 = $1,006,667
Step 6: Expenses
Expenses are the recurring costs needed to operate and manage a commercial property. Key line items like management fees, property taxes, insurance, and utilities keep the building running smoothly and compliant. In commercial real estate, property taxes, insurance, maintenance, and utilities are especially important because they form the basis for expense reimbursements in net leases, agreements in which tenants cover their share (called pro-rata share) of these operating costs.
The Property Management Fee is paid to the property manager or management company for handling day-to-day operations like tenant relations, maintenance coordination, and rent collection. It’s usually calculated as a percentage of Effective Gross Income (EGI) — often around 3% to 5%. This expense ensures the property runs smoothly without direct involvement from ownership.
Property Management Fee = EGI * 3.00% = $30,200
Property Taxes are annual charges imposed by the local government based on the assessed value of the real estate. They’re often the largest single line item in the operating budget and are unavoidable regardless of occupancy. Because taxes are a fixed obligation, they play a major role in the overall stability and risk profile of a property.
Property Taxes = $1.20 * 100,000 SF = $120,000
Insurance covers the cost of protecting the property from risks such as fire, natural disasters, and liability claims. Most commercial leases require property owners to carry adequate insurance coverage, and these costs are often passed through to tenants in triple-net leases. The level of coverage and premiums depend on the property’s size, location, and use.
Insurance = $0.30 * 100,000 SF = $30,000
Common Area Maintenance (CAM) expenses cover the upkeep of shared spaces such as lobbies, hallways, landscaping, parking lots, and lighting. These costs are often passed through to tenants and can include services like janitorial work, minor repairs, and snow removal. CAM ensures the property remains clean, safe, and functional for all occupants.
Common Area Maintenance (CAM) = $0.50 * 100,000 SF = $50,000
Utilities include electricity, water, sewer, trash, and sometimes gas — anything required to keep the building running day to day. Depending on lease structure, some or all of these costs may be reimbursed by tenants. These expenses can fluctuate based on building occupancy, energy efficiency, and local utility rates.
Utilities = $0.40 * 100,000 SF = $40,000
Reserves are funds set aside annually to cover large, infrequent expenses like roof replacement, structural repairs, or HVAC system upgrades. Think of them as a “rainy day fund” or “piggy bank” that protects against major financial surprises. While they don’t impact day-to-day operations, they’re essential for long-term asset planning and financial stability.
Reserves = $0.50 * 100,000 SF = $50,000
Step 7: Net Operating Income (NOI)
This is what’s left over after paying all operating expenses and setting aside reserves. It represent all property level revenues and expenses (meaning you should not have debt anywhere in this calculation). It’s one of the most important metrics in real estate — used to value the property and assess its performance.
NOI = EGI - Expenses
NOI = $1,006,667 - $320,200 = $686,467
Key Takeaways
This is a clean, simple framework for modeling income on retail, office, and industrial properties. If you’re trying to understand how deals are underwritten — whether for class, a case study, or an interview — this structure is a great starting point.
Once you’re comfortable with how PGI flows to EGI, how concessions and reimbursements work, and how expenses are built, the rest of financial modeling gets way easier.
Take the next step and start building your own pro forma with 1-on-1 support.