In this lesson, you’ll learn what the real estate pro-forma is, why it’s important, what the key line items and calculations are, and how to make it more complex with scenarios, based on examples for office and multifamily properties.
"Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
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?
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 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.
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.
Common deductions and adjustments are ones for the Absorption & Turnover Vacancy, Concessions & Free Rent, Expense Reimbursements, and General Vacancy.
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.
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.
Reserves exist to “smooth out” the property’s cash flows as large, irregular capital costs come up.
The most common capital costs for properties are Capital Expenditures (CapEx), Tenant Improvements (TIs), and Leasing Commissions (LCs).
CapEx is for property-wide items; TIs are for tenant-specific customizations; and LCs are paid to brokers who help find new tenants.
Net Operating Income (NOI) is Effective Gross Income – Operating Expenses & Property Taxes; it’s similar to EBITDA for normal companies and is critical in valuations.
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.
Cash Flow to Equity is Adjusted NOI – Debt Service; it’s fairly close to the equity investor distributions a property can make each year.
How to Build Scenarios into a Multifamily Pro-Forma
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.
Here, the scenarios 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.