The Real Estate Pro-Forma: Full Guide, Excel Template, Explanations, and More
- Simplification 101: Why the Real Estate Pro-Forma?
- The Real Estate Pro-Forma Excel and Guide
- Real Estate Pro-Forma: Revenue Line Items
- Base Rental Income
- Absorption & Turnover Vacancy
- Concessions & Free Rent
- Expense Reimbursements
- Potential Gross Revenue
- General Vacancy
- Effective Gross Income (EGI)
- CapEx, TI, and LC Reserves (AKA “Replacement Reserves” or “Capital Reserves”)
- Net Operating Income (NOI)
- Capital Costs Paid from Reserves
- Adjusted Net Operating Income (Adjusted NOI)
- Interest Expense on Debt
- Debt Principal Repayment
- Cash Flow to Equity Investors
If you want to understand a company, look at its financial statements – and if you want to understand a property, look at its pro-forma.
Many websites and textbooks describe the real estate pro-forma as a “cash flow projection” for a property.
While that description is accurate, it’s more useful to think of the pro-forma as a combined and simplified Income Statement and Cash Flow Statement – for a property rather than a company.
We’ll discuss the main line items on the real estate pro-forma in this article and give you an Excel template, but let’s start at the beginning with the “why.”
Why is it possible to simplify the financial statements of a property and use just a single schedule rather than a full Income Statement, Balance Sheet, and Cash Flow Statement?
Simplification 101: Why the Real Estate Pro-Forma?
It’s easiest to answer this question by pointing out that it’s possible to do the same thing for companies.
For example, in a DCF, LBO, or merger model, you could skip the full 3-statement projections and do the following instead:
- Project the company’s revenue, expenses, and taxes on its Income Statement.
- Then, create a “mini-Cash Flow Statement” and include only the key, recurring line items, such as Depreciation, the Change in Working Capital, and CapEx.
You don’t need a Balance Sheet because you can track Cash, Debt, and Equity separately below these projections, and you can estimate the Change in Working Capital with a simple percentage.
And you don’t need a full Cash Flow Statement because many of the items on it are non-recurring and, therefore, do not factor into the projections.
Properties are much simpler than large companies, so if it’s feasible to simplify the financial statements like this for companies, it’s even more feasible to simplify them for properties.
It’s also simpler because Working Capital tends to be less important for properties, and you effectively use Cash Accounting rather than Accrual Accounting in the analysis.
Also, you usually ignore income taxes because properties tend to be owned by pass-through entities such as Partnerships, S Corporations, and REITs that do not pay corporate income taxes.
Finally, “other activities” on companies’ Cash Flow Statements are often minimal for properties, and Debt and Equity Issuances and Debt Service can be handled directly on the pro-forma.
To be clear: properties still have full financial statements.
It’s just that for financial modeling, valuation, and investment analysis, you almost always skip the full statements and focus on the pro-forma instead.
The Real Estate Pro-Forma Excel and Guide
Below, we’ll walk through a pro-forma for an office/retail property with 3 tenants on different lease types (Full Service, Single Net, and Triple Net):
This is a simplified pro-forma for a “core real estate deal” intended to illustrate the concepts. We cover more complex examples and cases in our full Real Estate Financial Modeling course.
Here’s the high-level view (click the image to see a larger version):
Real Estate Pro-Forma: Revenue Line Items
You always start the pro-forma by showing the “potential” revenue of a property if it were 100% occupied and all tenants paid market rates.
Then, you make deductions and adjustments because:
- Properties are rarely 100% occupied, and you do not earn rent from vacant space.
- Not all tenants pay market rates; some might be paying lower rates, and some might be paying higher rates.
- If a tenant’s lease expires and that tenant chooses not to renew it, it will take time to find a new tenant. You will not earn rent in that downtime period.
- When a new tenant moves in, you may give it “free rent” for a few months as an incentive to agree to a multi-year lease.
- You might also earn income from “expense reimbursements,” i.e., the portion of operating expenses that a tenant is responsible for paying.
Each line item in the Revenue section corresponds to one or more of these points:
Base Rental Income
This one represents the “Potential” Rental Income of the property if it were 100% occupied and all tenants paid market rent.
Simple Calculation: If the property has 10,000 rentable square feet and the market rate is $50 per square foot per year, the Base Rental Income is $500,000.
Absorption & Turnover Vacancy
This represents the foregone rental income when a tenant leaves and it takes several months to find a new tenant.
It’s not a cash expense, but rather a loss of potential revenue.
Simple Calculation: If a tenant is renting 2,000 square feet for $50 per square foot per year, this tenant leaves, and it takes 6 months to find a new tenant, then the Absorption & Turnover Vacancy is 2,000 * $50 * (6 / 12) = $50,000.
Concessions & Free Rent
This line item corresponds to the “free rent” incentive described above.
Simple Calculation: If a new tenant moves into a 2,000-square-foot space with rent of $50 per square foot per year and receives 3 rent-free months, the “Concessions & Free Rent” line item will be 2,000 * $50 * (3 / 12) = $25,000.
This item represents the amount of property taxes, insurance, and maintenance/utilities for which the tenants are responsible.
The expenses that each tenant must pay are specified in its lease, and the terms tend to vary quite a bit.
Simple Calculation: If a tenant renting 2,000 square feet is responsible only for its share of property taxes, and property taxes for the entire 10,000-rentable-square-foot building are $50,000 per year, then this tenant must pay (2,000 / 10,000) * $50,000 = $10,000.
Potential Gross Revenue
If you sum up everything above, you’ll calculate the Potential Gross Revenue or Potential Gross Income for the property.
This number represents the income from all space in the building, ignoring permanently vacant spaces and non-rent sources.
This line item represents Potential Rental Income lost to spaces that are permanently vacant.
“Permanently vacant” means there is no tenant in the space currently, and the owner has no near-term plans to find a tenant for that space.
Simple Calculation: If this same 10,000-rentable-square-foot building has 1,000 square feet that is always empty, the General Vacancy line item would be 10,000 * 10% * $50 = $50,000.
It would appear with a negative sign on the pro-forma since it represents a deduction.
Effective Gross Income (EGI)
This is similar to “Net Sales” or “Net Revenue” for a normal company, but it’s on a cash basis.
EGI represents the cash revenue that a property generates after the adjustments for temporary and permanent vacancy and free months of rent.
It might also include adjustments for above or below-market leases, percentage rent for retail tenants, and other items.
Real Estate Pro-Forma – Property Expenses
That takes us to the end of the Revenue section, which ends with Effective Gross Income.
Most of the property’s expenses are easier to explain:
Property Management Fees
Property owners rarely manage their properties directly.
Instead, they hire 3rd party management companies to deal with tenants, collect rent, resolve problems, and set up repairs and maintenance. The fees paid to these management companies are usually a percentage of EGI.
Simple Calculation: If the management fees are 3% of EGI and EGI is $1 million, then these fees are $30,000.
These include insurance, maintenance and repairs, utilities, and other miscellaneous items.
If the property is big enough, this category might also include staff payroll, sales & marketing, janitorial, landscaping, and security services. These expenses are usually based on per-square-foot or per-square-meter amounts.
Simple Calculation: If the Operating Expenses are $10.00 per Rentable Square Foot per Year and there are 50,000 rentable square feet, then the annual Operating Expenses are $500,000.
Real Estate & Property Taxes
Nearly all local governments charge a tax on properties to fund school systems, police, and infrastructure.
Often, this tax is a small percentage (1-4%) of the property’s assessed value, and it increases each year as the assessed value goes up.
In places like Australia and the U.K., there’s also a “Stamp Duty” on property sales, which may exist along with or in place of annual property taxes.
Simple Calculation: A property’s most recent assessed value was $20 million. The state and city charge property taxes for a total of 3% of the property’s value, so the taxes here are $600K.
Capital Costs on the Real Estate Pro-Forma: Capital Expenditures, Tenant Improvements, and Leasing Commissions
“Capital costs” refer to spending on items that will provide a benefit for many years to come.
The main categories are Capital Expenditures, Tenant Improvements, and Leasing Commissions.
In the simple real estate pro-forma above, we group these items into a single line (“CapEx, TIs, and LCs”), but we calculate them separately and show them on separate lines in more complex models.
Capital Expenditures (CapEx) represent spending on items that are not specific to individual tenants. For example, CapEx might include spending on a new roof, elevator, or on the air conditioning or heating system in the building.
CapEx may vary greatly from year to year, depending on the property’s age and condition.
Simple Calculation: If CapEx per Gross Square Foot is $5 and there are 50,000 Gross Square Feet in the property, CapEx is $250K.
Tenant Improvements (TIs) represent spending on items that are specific to individual tenants. Owners often pay for these items to customize the space and make it easier for tenants to move in. Examples might include additional walls and doors in an office suite.
Simple Calculation: If the TIs per Rentable Square Foot for a tenant are $50 and the tenant is renting 10,000 square feet, the TIs will be $500,000 upon initial move-in.
Those TIs will not recur each year – only when a tenant renews a lease, or a new lease begins.
Leasing Commissions (LCs) are payments made to commercial real estate brokerage and leasing companies to find new tenants and to get existing tenants to renew their leases (as some negotiation may be required).
LCs are almost always based on a percentage of the total lease value over the term of the lease.
Simple Calculation: A tenant signs a 5-year lease for 10,000 square feet that initially starts at $50 per rentable square foot and then increases to $52, $54, $56, and $58 over the term.
The Leasing Commissions will be 5% of the total lease value over these 5 years.
Therefore, the LCs will be 5% * ($50 + $52 + $54 + $56 + $58) * 10,000 = $135,000.
As with TIs, LCs are incurred only when a new tenant moves in, or an existing one renews.
NOTE: We simplified this calculation in the Excel example here and didn’t account for lease escalations, but in a more complex version, we would.
CapEx, TI, and LC Reserves (AKA “Replacement Reserves” or “Capital Reserves”)
From the descriptions above, you can immediately detect one big issue with these capital costs: they’re often lumpy.
A property might have $200K in capital costs one year, then $0 the next year, then $800K, then $300K, and then $200K.
This pattern makes the property’s cash flows fluctuate significantly, and it makes it difficult for owners to plan their long-term strategy.
So, nearly all properties use “Reserves” that aim to smooth out these fluctuations.
In the example directly above, the owners might estimate $1.5 million in capital costs over 5 years.
Based on that, they might then allocate $300K per year into these Reserves.
When capital costs are owed, the owners draw on the Reserves to pay for them.
If the Reserves cannot cover everything in one year, the remaining required funding will come out of the property’s cash flow for that year.
Here’s how it works over the first 3 years in the example above:
- Year 1: Allocate $300K; spend $200K; Ending Reserves of $100K.
- Year 2: Allocate $300K; spend $0; Ending Reserves of $400K.
- Year 3: Allocate $300K; spend $800K (with $100K funded from cash flows since the Reserves are only up to $700K); Ending Reserves of $0.
There is significant disagreement over where the Reserve Allocations should be shown.
Some people argue that they should not affect the property’s Net Operating Income (see below) because they are not true “cash costs” when they are allocated.
Therefore, this group argues, the Reserves should be deducted after the NOI line item.
Other people argue that you must deduct the Reserve Allocations before the NOI line item to reflect the true cost of owning the property.
We agree more with the second line of reasoning, so in this article and the case studies in our Real Estate Course, we assume that the Reserve Allocations reduce NOI.
Net Operating Income (NOI)
This item is similar to EBITDA for normal companies – a capital structure-neutral measure of core-business cash flow – but it’s not the same.
For example, if you deduct the Reserve Allocations, as we do, then NOI partially reflects capital costs.
EBITDA, by contrast, never reflects capital costs because it excludes CapEx and D&A.
NOI is critical because properties are often valued based on their projected NOI divided by a selected “Capitalization Rate” (Cap Rate) or “Yield.”
For example, if a property’s projected NOI is $5 million and Cap Rates for similar properties in the area are 5%, this property might be worth $5 million / 5% = $100 million.
If your NOI figures are off, then your valuation will be off as well.
Capital Costs Paid from Reserves
This line item represents the capital costs that are covered by the Reserves in a given year.
If the Reserves are insufficient, this amount will not completely offset the capital costs – and that will reduce the property’s cash flow:
Adjusted Net Operating Income (Adjusted NOI)
This is the item shown above: the property’s NOI minus the capital costs not covered by the Reserves.
Even if you disagree with our treatment of the Reserves in NOI, you’d still have to deduct the Reserve Allocations in this section – so Adjusted NOI ends up being the same.
Adjusted NOI is similar to Unlevered Free Cash Flow for normal companies because it represents the cash flow generated by core-business operations after capital costs and re-investment, ignoring capital structure (i.e., interest expense).
But it’s not quite the same because Adjusted NOI excludes income taxes, the Change in Working Capital, and several other items that go into Unlevered FCF.
Interest Expense on Debt
Nearly all property acquisitions and developments are funded partially by Debt, so you will almost always see the Interest Expense on that Debt on the pro-forma.
You could use the IPMT function in Excel to calculate the Interest Expense, or you could calculate it manually based on Interest Rate * Debt Balance.
Debt Principal Repayment
This one is the other component of Debt Service: repaying the principal over time.
You could use the PPMT function, the counterpart of IPMT, to calculate this one, or you could do it manually based on percentages; it just depends on the terms of the Debt and your model.
Cash Flow to Equity Investors
This is the final line on the typical real estate pro-forma, and it represents the amount available for distribution to the equity investors (the “owners”) in the property.
Companies often avoid dividends and accumulate large cash balances, but properties tend to distribute their annual cash flow unless they need it for something major.
Additional Items and the Real Estate Pro-Forma for Multifamily and Hotel Properties
In this article, we’ve focused on a real estate pro-forma for a mixed office/retail property.
This same pro-forma also applies to industrial properties because lease terms, revenue, and expenses are similar.
However, it would be slightly different for a multifamily (apartment) property, and it would be significantly different for a hotel.
Multifamily leases are shorter, simpler, and more similar to each other, and Tenant Improvements and Leasing Commissions tend to be far less significant as a result.
Tenants are often responsible for Utilities, but not much else, so Expense Reimbursements might be labeled just “Utility Reimbursements.”
And since individuals rather than companies are the tenants, there is a substantial risk of non-payment. That explains why you’ll see “Bad Debt” as a deduction in the Revenue section.
Hotels are so different that we’d probably need a separate article to explain the hotel pro-forma fully.
But in short, it’s more like the Income Statement of a real company, with revenue in categories such as Rooms and Food & Beverage, and Fixed vs. Variable Expenses.
Hotels require far more staff to operate since they’re more like “real companies,” so expense categories like Sales & Marketing and General & Administrative are much bigger.
A few other, more advanced items on the real estate pro-forma include Loss to Lease and Percentage Rent.
Loss to Lease represents the difference between in-place rents and market rents for tenants who are on below-market leases.
For example, if the market rent is $50 per square foot per year, but a tenant occupying 5,000 square feet is paying $45 per square foot per year, the Loss to Lease would be ($50 – $45) * 5,000, or $25,000 (and shown with a negative sign on the pro-forma).
You can determine what the proper rates might be by conducting comparative market analysis.
Percentage Rent is for retail tenants who pay a percentage of their monthly gross sales to the property owners, in addition to the traditional fixed rent.
Retailers might agree to this term in exchange for lower base rent.
With Debt Service, there is non-cash interest for properties, just as there is for companies (it’s also called “Accrued” or “Paid-in-Kind” or “PIK” interest).
And in some cases, the full loan proceeds will not be distributed upfront, but rather “held back” and distributed when certain milestones are met.
That creates differences in the Interest and Principal Repayment calculations and the returns for lenders.
What’s NOT on the Real Estate Pro-Forma: Taxes and Depreciation
Before wrapping up, I want to address two topics that generate a lot of questions:
“1) Why does your real estate pro-forma not include income taxes?
2) And what about Depreciation? Isn’t it huge in real estate?”
You typically exclude income taxes because, as mentioned above, properties are often owned by pass-through entities.
If you form a partnership to acquire apartment buildings, each partner will pay taxes individually, but the partnership as a whole will not pay corporate-level taxes.
Each partner’s taxes and rates will vary greatly based on their circumstances, so there’s no point in including these on the pro-forma.
Depreciation does not appear on the pro-forma because it is a non-cash expense that exists to “smooth out” CapEx and, partially, for tax purposes.
But since the pro-forma excludes taxes, there’s no point in including an item like Depreciation that is relevant mostly for tax purposes.
If we were modeling a taxable entity, such as a C corporation that owned properties, then we might worry about income taxes and Depreciation in the pro-forma.
The Real Estate Pro-Forma: What Next?
Like any financial model, the pro-forma is simply a tool.
If you use it correctly and come up with reasonable assumptions, it can tell you if a deal is potentially viable or completely implausible.
But it cannot tell you whether or not you’ll earn an out-sized return on a property acquisition or development.
“Real life” comes into play in a major way there; a deal might look great on paper, but if the property manager is incompetent and toxic rats invade the property, all bets are off.
However, a properly constructed pro-forma can help you evaluate the risks of a deal and see what might happen if something goes wrong, and how you might reduce those risks.
Coming up next, we’ll go through a series of real estate case studies that demonstrate how to use the pro-forma to make investment decisions, as you might in real estate private equity.
Yes, we’ve released real estate case studies in the past, but the ones coming up are newer, better, and more representative of real-life exercises.
And if you want more before then, there’s the full Real Estate Financial Modeling course.
Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews
Read below or Add a comment
What are the necessary pro-forma elements in regard to developing a residential (most single-family homes with some multifamily structures and some commercial/retail spaces. Land as already been acquired, but this is needed for our business plan to lenders to fund the development. First-time developer here, is there a model available for this?
So on these technical posts / Excel-based articles, we can only answer questions related to the actual content here. At a high-level, though, single-family home development tends to be structured differently, often more based on development and pre-sale “phases,” with less detail on each individual unit in the lot. So you will tend to see more complicated funding structures and timelines, with equity and debt draws occurring at different times.
We do have 2 pre-sold condo development models in the Real Estate Modeling course (one easier/shorter and one more complex with a more detailed waterfall).
I’m looking for something like this only for a residential brokerage company.
We do not have anything for residential brokerage companies. We post free samples of the material we have and nothing more.
Residential Brokerages usually value properties using comparable sales, no need for any of this unless it’s an independent residential/multifamily investor.
Hi, is there any way to calculate acquisition price or purchase price when it’s not given in acquisition model, how is it calculated from IRR or cap rate? Please explain
Purchase prices are typically based on forward NOI for the property and prevailing Cap Rates in the area for similar properties (NOI / Cap Rate). So you need that data to estimate the price.
Can you help me with one of my assignment.? where can I send you my excel sheet?
Sorry, but we don’t provide homework help on this site.
Thank you for the great proforms and narrative!.
It appears to be very comprehensive and extremely well laid out. A great resource. Thank you so much!
I am looking for a template to simplify cash flow projections for several buildings and then consolidating them. At the moment I have a massive workbook that has grown to become a bit of a beast. Do you have any suggestions?
I can’t really say or recommend something specific here without seeing your files. But you normally don’t want to track individual tenants across multiple buildings separately. You can probably consolidate it by simplifying some of the assumptions, such as by saying that a certain % of overall leased space expires each year and tenant turnover will result in XX% less in rental income in those periods.
I’ve applied for a Real Estate PE Internship at KKR and I was finally contacted by the asset management team of have some interviews. I think both teams are working closely together but I was wondering what is the value of an Asset Management internship within KKR knowing that I want to do PE and already have 2 PE experience. Does the KKR brandname worth it ?
Thank you for the answer…
I’d say it’s still worthwhile, but mostly because of the KKR brand name. The work experience itself probably won’t add much.
May I know what is Exit Cap Rate? What is the formula you used to calculate exit cap rate?
It’s based on what similar properties in the area have sold for. Cap Rate = Forward NOI / Property Selling Price. Higher Cap Rates mean lower valuations and vice versa.
Thank you so much!
nice article and great template. I was wondering if you have any experience with running House Price Index scenarios to make best and worst case assumptions. If you have any tips, I’d love to hear.
Sorry, we do not.
Hi Brian, may I know why ‘item ‘capex, TIs, LCs” is calculated twice, one in expenses, antoher in adjustment after NOI. Thank you so much.
They are different. The one before NOI is for the “reserves,” AKA the annual allocation to a balance that will be drawn when CapEx, TIs, and LCs are actually incurred and require cash payments. The one below NOI is for the payment of those items, paid for by as much of the reserves as possible.
This is great. I am looking for something similar investment in aircraft class and the returns perspective to different lenders.
Can you share, please?
Sorry, we don’t cover that industry.
Thanks for the excellent article and template.
Do you feel it’s worthwhile doing an analysis like this with all 50 tenants individually included within an office building to assess the pro forma with more granular detail?
It’s rarely worth doing granular analysis for 50 tenants because rental income will not be “lumpy” at that level. It would be worthwhile only if, for example, 25 tenants had lease expiration dates in Year 1, there was nothing in Years 2-3, and then the next 25 tenants all had expiration dates in Year 4.
Excellent series of in depth inputs on real estate investments.
Very general question, Which are some of the reasons or parameters property developers compare among available plots/options and decide on one specific plot or property to go ahead and invest in it.
pro-forma must be one of the useful tool but are there any other parameters/data which builds confidence or supports decision taking.
Thanks in advance.
Take care, Stay safe
Location, location, location. The value of land depends almost entirely on what else is around it and whether the area is growing quickly or losing population and commercial activity.
thank you for the insight into your program. I like it.
I am going to enhance my use of Excel, and then I will be back.
thank you again!
Thank you for this, Is there any commercial software or ERP that offers this Real Estate Pro-Form?
There are various software solutions for real estate, the most famous one being ARGUS. But the problem is that all these solutions are less ubiquitous than Excel; everyone knows and uses spreadsheets, but only small percentages of people use one specific software product. So, we tend to stick with Excel for most pro-formas, except for very complex ones with multiple lease breaks and potential renewals for each tenant in the holding period, where ARGUS does a better job.
Thank you for this, I found it extremely helpful in a deal I’m analyzing. I do have a question regarding the spreadsheet’s functionality. Suppose I want to add tenants and make them all NNN. Do I just delete Tenant 1 and 2 and then copy and paste Tenant 3 a few more times into the spreadsheet?
Hi Steven. Were you able to accomplish this? I have tried the copy and paste and the references get all messed up.
This is great – thank you very much for the hard work and effort you put into this article and the accompanying spreadsheet
Thanks for reading!
Thank you Brian.
Thanks for reading!