Real Estate Multifamily Acquisition Financial Model

Real Estate Financial Model Sections

Operating Proforma

With the assumptions in place, the operating proforma can be modeled:

The forecasts are all driven by the T-12 Historical Financials and the assumptions inputted into the Inputs & Drivers section above.  Keep in mind the following:

• Net effective rent is driven from a per unit monthly rent assumption.  Here, we assume that renovations immediately provide the rent premium benefits. This is a common simplifying assumption, because multifamily properties typically have ~50% annual turnover, and landlords can move rents overnight. In more complex models, unit renovations and the associated rent premiums will be integrated into the model using more specific assumptions.
• Vacancy Loss is back-solved using the occupancy rate assumption from the inputs section and the net effective rent forecast in the model section.
• Net Operating income (NOI) is a key measure of operating profit for a multifamily property. The NOI in the exit year will be used to determine the sale price (using the 6.25% cap rate assumption). Note that NOI should not include capital expenditures.

Unlevered Returns

Now that NOI has been calculated, we can calculate the sale price and unlevered returns for this investment.  Although REPE investors typically use debt to finance transactions – and, as we’ll see in a subsequent step, can be a significant driver of investment returns – it is helpful to also look at unlevered returns to evaluate investments on a level playing field irrespective of financing decisions:

Here we can observe the following:

• Sale Proceeds: Calculated as the forward year NOI divided by the cap rate assumption. It should be easy to see that accuracy in forecasting the NOI is only half the story. Determining the exit cap rate for a property 5 years in advance is one of the most crucial assumptions in the analysis, and this is usually a function of how comparable properties are currently valued.
• Unlevered cash flow: The sale proceeds are not the only cash flows – you must also add NOI and deduct capital expenditures over the hold period.

The yellow box summarizes the unlevered returns profile:

• Profit: Represents simply how much cash the investor generated over the holding period, less the initial investment
• Multiple: Represents how much cash the investor generated as a multiple of the initial investment
• IRR: Factors in the hold period, such that the longer the holding period, all else equal, the lower the IRR (time value of money)
• Peak equity: Represents the amount of equity capital required to be invested in the transaction

Levered Returns 

In the next section, we forecast debt payments to arrive at levered returns – which is actually what the returns will be, since a REPE deal is usually funded with some debt.

In this example, we’ve provided a very simple capital structure with a straightforward senior loan of $9.75M, which amounts to a loan-to-value (LTV) ratio of 65% – a common leverage point for a multifamily real estate transaction.

Interest expense: Floating rate loans are usually priced off LIBOR + a spread, so the LIBOR forecast (which usually comes from a LIBOR curve) is the moving variable in the forecast to arrive at interest expense. Note that interest expense is calculated off the beginning of the period debt balance in the model.  In more complex models with cash sweeps, the approach of calculating interest expense from the beginning of period debt balances avoids a circular reference.  We have written about how to handle model circularities due to cash sweeps in a separate post about the LBO modeling test.

Origination fee: The origination fee is paid upfront.

Principal amortization: Per the inputs section, 5% of the original debt balance must be paid back to the lender each year.

Loan funding and payoff: In the first year, the cash inflow from the loan is reflected, while in the exit year, the remaining principal balance on the loan is repaid.

Levered cash flows: These are the cash flows that flow to the REPE investor, and the model calculates it as:

As with unlevered cash flows, the yellow box summarizes the returns profile, only this time it’s the actual returns to the REPE investor because it captures the impact of leverage:

• Profit, multiple, and IRR: Notice how profits are lower, while the multiple and IRR are higher than the unlevered returns. This should make sense – while the debt means lower profits in absolute dollars, the real estate private equity firm can write a much smaller equity check, such that returns are amplified. Assuming the model’s assumptions hold, the investor would generate a 17.4% levered return on this investment.
• Peak equity: Notice how the investor has to put up equity in year 0 (the acquisition date), but also expects additional equity infusions in years 1 and 2, due to capital expenditures.  The peak equity represents the total amount of equity that must be put into the investment – not just the initial check – and is factored into the total sources of funds in the sources & uses of funds schedule (see below). In this way, REPE models can differ slightly from regular LBO models, which predominantly rely on a revolving credit line and excess cash to fund cash shortfalls. The mechanics can differ deal by deal – some loans will include funding for capital expenditures, some will require the investor to set aside cash in Year 0 to cover capital expenditures. An investor may decide to spread the capital expenditures over time so that excess cash flows cover them.

Transaction Summary Section

We are now ready to complete the model.  We start with Sources and Uses (S&U) – this ubiquitous table in LBO and M&A models is a simple way to clearly see how much capital needs to be raised to make a deal happen, and how that capital will be funded. Keep in mind: the Transaction Summary section should reference the Model section whenever possible. Said another way, avoid referencing the Inputs & Drivers section, so that any errors in linking your inputs and model are not covered up.

Sources & Uses Table

Uses

It’s easier to start with Uses – enough funds need to be raised to:

• Buy the Real Estate Asset (i.e. the Property Purchase Price)
• Pay the Origination Fee
• Pay for Capital Expenditures (Capex)

Sources

To complete the Sources section, we start with the basic premise that once you’ve defined the amount of uses for capital, you then need to find sources for exactly that amount of capital.  In simple English: sources must equal uses.  The model thus references total uses into the total sources cell.  The individual components making up ‘Sources’ are:

• Debt: This is usually the biggest source of funds and can be found by summing the Loan Funding line in the Model section.
• Equity: Since the investors not only have to invest upfront in year 0, but must also write additional checks to fill shortfalls in years 1 and 2, the total peak equity can be found by summing all the negative leveraged cash flows.
• Operating cash flows: This is the plug in the S&U table and represents how much of the planned capital expenditures are not going to be funded by additional equity checks but rather through the asset’s internal cash profits.

Solving the Real Estate Case Study: Answer Key

To wrap up this real estate financial modeling tutorial, we will answer the three questions posed at the beginning of the article.

1. Based on the following transaction assumptions, the levered IRR would be 17.4%, and the multiple would be 2.2x.
2. If the minimum IRR threshold is 15.0%, the highest exit cap rate the REPE firm could exit at is approximately 6.75%.
3. To achieve a 15.0% IRR, the REPE firm would have to achieve minimum rent premiums of $50.

Advanced Real Estate Financial Modeling (REFM)

In this walk-through, we introduced the core mechanisms of a multifamily acquisition model, such as incorporating historical financials, forecasting occupancy and rent growth, building to NOI, a simplistic debt schedule, returns calculations, and sources & uses.

While this might be enough to pass a simple REPE modeling test, there are more advanced concepts likely to be tested, including:

• Monthly Cash Flows
• Dynamic Timing Formulas
• Detailed Debt Schedule
• Equity Waterfall

For more comprehensive training, we have developed a step-by-step program that contains the same instructional material trusted by leading real estate private equity firms to train their analysts and associates.

The Wall Street Prep Real Estate Financial Modeling Package includes more than 20 hours of detailed instructional material, including two interview-quality case studies: