Real Estate Pro-Forma: Calculations, Examples, and Scenarios (22:31)

Table of Contents:

• 1:23: Part 1: Why the Real Estate Pro-Forma?
• 2:29: Part 2: Simple Real Estate Pro-Forma Excel & Calculations
• 12:34: Part 3: How to Build Scenarios into a Pro-Forma (Multifamily Example)
• 18:09: Part 4: Differences for Other Property Types and More Advanced Items
• 20:03: Recap and Summary

Why the Real Estate Pro-Forma?

Just as you need to understand the financial statements to grasp financial modeling and valuation for normal companies, you must understand the Pro-Forma if you want to understand real estate.

The Real Estate Pro-Forma is a simplified and combined Income Statement and Cash Flow Statement for properties, with a few modifications – such as no Income Taxes and no Depreciation in most cases.

Properties do have financial statements, but for modeling and valuation purposes, we can simplify and just project the Pro-Forma – as we often do when valuing companies with a DCF and projecting only their cash flows.

The Shape of the Real Estate Pro-Forma and Simple Calculations

You always start with Potential Revenue, if the property were 100% occupied and all tenants paid market rates, and then make deductions.

Next, you list the operating expenses required to run the property’s day-to-day operations.

Then, you list the “capital costs” (similar to CapEx and the Change in Working Capital for normal companies) that correspond to long-term items that will last for more than 1 year.

Finally, you show the Debt Service (Interest and Principal Repayments) and the Cash Flow to Equity at the bottom.

Here’s a simple example of the real estate pro-forma and each section on it:

Revenue and Effective Gross Income

Base Rental Income at the top represents this “potential revenue” with 100% occupancy and full market rents paid by tenants.

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.

Common deductions and adjustments are ones for the Absorption & Turnover Vacancy, Concessions & Free Rent, Expense Reimbursements, and General Vacancy.

Absorption & Turnover Vacancy is for the months of downtime when a tenant leaves, and it takes time to find a new tenant. It’s not an expense, but rather “foregone rental income.”

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 is used for when a new tenant moves in, or an existing tenant renews, and you grant “free months of rent” as an incentive.

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.

Expense Reimbursements are an addition to revenue and represent tenants’ proportional share of property taxes, insurance, and maintenance/utilities.

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.

General Vacancy is for space that’s “permanently vacant,” AKA there is no tenant and no plans for one anytime soon.

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.

As in the screenshot above, t would appear with a negative sign on the pro-forma since it represents a deduction from “potential revenue.”

Effective Gross Income sums up all these adjustments and is similar to Net Revenue or Net Sales for normal companies, but on a Cash basis rather than an accrual basis.

Operating Expenses

Common operating expenses include property management fees, utilities, maintenance, insurance, sales & marketing, general & administrative, property taxes, and reserves.

Some are projected based on a % of Effective Gross Income, some are based on $ per square foot or $ per square meter figures, and some are percentages of the property’s value.

Property Management Fees exist because 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.

Simple Calculation: If the management fees are 3% of EGI and EGI is $1 million, then these fees are $30,000.

Other Operating Expenses might include insurance, maintenance and repairs, and utilities; if the property is big enough, this category might also include staff payroll, sales & marketing, janitorial, landscaping, and security services.

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.

Property Taxes are levied by nearly all local governments worldwide to fund school systems, police, and infrastructure.

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.

Reserves exist to “smooth out” the property’s cash flows as large, irregular capital costs come up.

For example, if you allocate $200K per year to the Reserves over 5 years when there are no capital costs in Years 1-2, $600K of capital costs in Year 3, 0 in Year 4, and $400K in Year 5, you can use the Reserves to cover the Year 3 and Year 5 costs without dipping into the property’s cash flows.

If the Reserves are insufficient to fund these capital costs, then they will be funded with some of the property’s annual cash flow instead. Here’s an example:

NOTE: There is significant disagreement over where these Reserves should show up.

Some people argue that they should be below the NOI line and therefore not affect NOI, while others argue that Net Operating Income must reflect Reserve Additions to account for the true cost of running the property.

We tend to follow the latter approach, so we do that in all the models here; this approach is also more conservative and favored by lenders.

Capital Costs

“Capital costs” refer to spending on items that will provide benefits for many years to come.

The most common capital costs for properties are Capital Expenditures (CapEx), Tenant Improvements (TIs), and Leasing Commissions (LCs).

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.

CapEx represents items that are not specific to one tenant, such as a new roof, elevator, air conditioning, heating system, etc. (usually a $ per square foot or square meter figure).

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) are items that are specific to individual tenants, paid as incentives to those tenants (additional walls, doors, etc.).

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 paid to brokerage companies and agents to find new tenants, or to negotiate with existing tenants and get them to renew.

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.

Net Operating Income and Cash Flow

Toward the bottom of the Pro-Forma are important metrics for valuing the property and determining its performance in a deal.

Net Operating Income (NOI) is Effective Gross Income – Operating Expenses & Property Taxes.

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.

Adjusted NOI is NOI – Net Capital Costs; it’s similar to Unlevered FCF for normal companies since it’s core-business cash flow after capital costs, ignoring capital structure.

But it’s not quite the same as Unlevered FCF because Adjusted NOI excludes income taxes, the Change in Working Capital, and several other items that go into Unlevered FCF.

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.

Cash Flow to Equity is Adjusted NOI – Debt Service; it’s fairly close to the equity investor distributions a property can make each year.

The main items in “Debt Service” are Cash Interest paid on Debt and Principal Repayments. You might calculate these with the IPMT or PPMT functions in Excel, or you could do it manually, depending on the terms.

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.

Differences in the Real Estate-Pro Forma for a Multifamily Property

In this tutorial, 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, there would be some minor differences for a multifamily property (i.e., an apartment building where tenants rent units) because:

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

There may also be a new line item for Parking Income, which is what it sounds like.

We show “Loss to Lease,” to represent the difference between market rents and in-place rents, as well, but that is not specific to multifamily – it could appear on almost any pro-forma.

Here’s an example multifamily pro-forma, with the key differences highlighted:

In addition to these new features and line items, this model can also become more complex with the addition of scenarios.

How to Build Scenarios into a Real Estate Pro-Forma for a Multifamily Property

All investing is probabilistic, so you need to consider what happens if the deal goes very well, average, or very poorly.

Typically, you create Base, Upside, and Downside cases with differences in Rent, Vacancy, Bad Debt, Expenses, TIs, and LCs.

In credit analysis, you focus on the Base, Downside and Extreme Downside cases since the upside is extremely limited for lenders.

Everything must be connected in these scenarios – if there’s a recession, rents will fall, the vacancy rate will rise, and TIs and LCs will also rise because it will be more difficult to find tenants.

In our multifamily example here, the Base Case represents steady, uninterrupted growth in Market Rents (3-5%), the same 3% Vacancy Rate, the same 3% Bad Debt, 2-4% Expense Growth, and TIs grow at 2-4% with LCs remaining at 3% of Effective Rent.

The Downside Case represents a mild recession over ~2 years, so Market Rents fall, Vacancy and Bad Debt rise to ~6%, Expenses fall, TIs grow at 10%, and LCs jump to 8% of Effective Rent.

And the Extreme Downside Case is similar but has even worse numbers, based on the most severe recession from the past few decades.

You can see how some of the assumptions differ in these scenarios below:

Taken together, all these differences create substantially different outcomes for Effective Gross Income (EGI) and Net Operating Income (NOI) in the final year of the model:

• Base Case:
  • EGI: $9.7 million
  • NOI: $6.3 million
• Downside Case:
  • EGI: $8.6 million
  • NOI: $5.6 million
• Extreme Downside Case:
  • EGI: $8.1 million
  • NOI: $5.2 million

The scenarios also tell us that the proposed financing for this deal, with 85% leverage, won’t work because some of the lenders lose money in the Extreme Downside Case, and the equity investors also get wiped out.

We can see that by looking at some of the sensitivities for the returns to different investor groups here:

Real Estate Pro-Forma for a Hotel

The Pro-Forma for a hotel is quite different and much more like the Income Statement of a normal company, with Revenue split into Rooms and Food & Beverage and Expenses split into Fixed vs. Variable categories.

Sales & Marketing and G&A may be much bigger, there will be management compensation and bonuses, and NOI margins tend to be lower than they are for other property types such as multifamily.

Here’s what it might look like down to the NOI and Adjusted NOI line items: