Real Estate Multifamily Acquisition Model Introduction
In this article, we will walk you through a back-of-the-envelope (“BoE”) Multifamily Acquisition Model that introduces some of the core real estate financial modeling concepts and those tested during the real estate private equity recruiting process.
Table of Contents
- How to Build an BoE Multifamily Acquisition Model?
- Acquisition Real Estate Financial Model – Excel Template
- Real Estate Acquisition Model Case Study Instructions
- Real Estate Financial Model Inputs & Drivers Section
- Real Estate Financial Model Sections
- Transaction Summary Section
- Solving the Real Estate Case Study: Answer Key
- Advanced Real Estate Financial Modeling (REFM)
How to Build an BoE Multifamily Acquisition Model?
BoE multifamily acquisition models are a common method for REPE professionals to quickly model a potential real estate investment without all the dynamic bells and whistles of a full-blown model.
The BoE Multifamily Acquisition Model contains the basic elements required to determine a range of possible IRR returns to a REPE investor, given a high-level set of assumptions about the asset’s operating forecasts, leverage and exit price.
While REPE modeling tests can vary in complexity, they will generally be more complex than a BoE model, such as monthly cash flows (vs. annual in the BoE model), dynamic timing formulas, detailed debt schedule, and an equity waterfall.
A BoE model saves time and energy, and can be a good initial test for whether an investment is worth investing more time in.
Acquisition Real Estate Financial Model – Excel Template
You’ll see an empty template and a completed template with the answers. To follow along, I recommend you work in the empty worksheet and try to recreate what you see in the walk-through, and check your work against the answer sheet at the end.
Real Estate Acquisition Model Case Study Instructions
Below is a simple multifamily acquisition scenario and a walk-through of the BoE model you might build on a first pass or some modeling tests.
With the introductions behind us, let’s dive into building a multifamily acquisition model.
Multifamily Acquisition Model Case Instructions
(Download Case Instructions PDF)
A real estate private equity firm is evaluating the acquisition of Creekstone Apartments (“Creekstone”), a multifamily property with 100 units. Build a BoE model to answer the following:
- Based on the following transaction assumptions, what are the levered IRR and multiple?
- If the minimum IRR threshold is 15.0%, what is the highest possible exit cap rate?
- What is the minimum rent premium necessary to achieve the 15.0% IRR threshold?
Historical Financials
Over the trailing twelve months (“T-12”), Creekstone achieved $1.45M in net effective rent, averaged 88% occupancy, and lost $30K of revenue to bad debt and non-revenue units. In addition, Creekstone generated $100K in total other revenue. Creekstone’s T-12 operating expenses are below:
Repairs & Maintenance $55,000 General & Administrative $37,000 Payroll $100,000 Utilities $30,000 Real Estate Taxes $255,000 Total OpEx $477,000
Transaction Assumptions
- The REPE firm acquires Creekstone for a purchase price of $15,000,000 on 12/31/2020
- The REPE firm will own the property for 5 years, and then exit at a 6.25% cap rate on 12/31/2025
Operating Performance Drivers
- Occupancy – YR1: 90%, YR2: 91%, YR3: 92%, and 93% thereafter
- Rent Growth – YR1: 0%, YR2: 2%, and 3% thereafter
- Bad Debt & Non-Revenue Units – T-12 constant percentage of Net Effective Rent
- Other Revenue Growth – YR1: 0% and 3% thereafter
- Expense Growth – YR1: 0% and 2% thereafter
Capital Expenditure Assumptions
- The REPE firm intends to implement a unit renovation business plan:
- Cost Per Unit – $5K
- Timing – YR1: 50% and YR2: 50%
- Rent Premium – the renovations are expected to provide $100 in additional monthly rent per unit immediately and fully at the start of the year they are renovated (this is a simplifying assumption)
- Defensive Capex – $100K in Year 1
Financing Assumptions
- Loan Amount – $9.75M
- Interest Rate – LIBOR + 300
- Amortization – 5%
- Origination Fee – 1%
- LIBOR – YR1: 1.5%, YR2: 1.7%, YR3: 1.9%, YR4: 2.1%, YR5: 2.3%
- Assume the loan is fully paid off at the time the property is sold
Based on the assumptions provided above, calculate the IRR and Multiple on a levered and unlevered basis.
Real Estate Financial Model Inputs & Drivers Section
Model Assumptions
The first section of the multifamily acquisition model (and real estate models in general) will be the assumptions area (inputs & drivers). Notice we have included all historical inputs and key operating, financing and transaction assumptions here:
Key Points:
- Sale price: This is blank for now, as we won’t be able to calculate the sale price until we’ve forecasted Net Operating Income (NOI)
- ROI on unit renovations: Calculated as $100 in incremental monthly rent x 12 months / $5,000 per unit renovation cost
- In-place income and expenses: We assume that in-place is T-12 for this exercise, but depending on the situation, it could be T-1, T-3, or a combination
- Effective rent per month: Calculated as net effective rent / 12 months / # of units; keep in mind, this is before vacancy losses and bad debt & non-revenue units
- Occupancy rate and vacancy loss: An occupancy rate of 88% enables us to back into a vacancy loss of $175,000 as: Vacancy loss = net effective rent – (net effective rent x occupancy rate) = $175,000
Keep in mind that historical financials should be input into the model section. From those historical financials, we can then pull the desired historical information into the “in-place” areas of the model Inputs & Drivers. From those historical financials, we will also be able to calculate the current effective rent per month as well as occupancy.
What data will you need?
A number of key assumptions will drive your model. The better information you can gather around these key inputs, the more useful your model will be. A few key assumptions worth conducting some diligence on include the Purchase Price, Sale Price, and Effective Rents. This information can be found in a number of places, including:
- Broker quotes (Purchase Price)
- Sales Comparables (Purchase Price, Sale Price)
- Market Surveys (Effective Rents)
I am struggling to understand amortization here. Can anyone kindly clarify?
Hi, Andrew,
In the context of the model described in this article, amortization simply means the amount of loan principal that is paid each year.
BB