Welcome back. It's finally time to open Excel!!
One more thing to note, before beginning. There are three
font colors used in financial models: blue, black, and green. Blue numbers are
hard-coded, meaning the user types the number into the cell. Black numbers are
calculated based on numbers in the same sheet in which the calculation is
taking place. Green numbers are calculated based on numbers in other sheets in
the workbook. This is important to note so the calculations and assumptions are
made clear for the user and other viewers of the model.
For the multifamily development model I have been working
with, there are seven main sections: investment assumptions, operations
assumptions, budget, cash flow waterfall, stabilized cash flows, draw schedule,
and amortization schedule. The tabs that include assumptions are the two
assumptions sections and the budget; the other components take the information
in the first three sections and calculate the necessary information. For the
purposes of my blog, I will focus on the sections containing assumptions, and
briefly overview the other sections which are calculated based off of the
assumptions. *Any dollar figures here are made up purely as an example*
Investment Assumptions
This section deals with the high-level investment, from a 10,000-foot
view as opposed to a ground level view. It is broken up into financing, project
costs, equity, disposition, construction, and other assumptions. The financing
tab is extremely important, because it deals with where the majority of
funding—the debt—is coming from and how much it costs. However, the inputs on
this section mostly come from recently completed deals or the source of debt
funding, and is therefore a section that is very realistic and does not require
much deep thought. It is the first aspect of the model that I learned to complete, and serves as the basis for the rest of the underwriting process.
The Project Costs section feeds in from the budget and encompasses
all of the costs necessary for the project. The Equity section is calculated
from the investment assumptions tab. The total equity is the total uses minus
the loan amount, minus any other aspects that do not require equity, like: a
land contribution made by a partner in exchange for equity, or reserves that
the lender might require you to keep. The
“Preferred” and “Promote” cells deal with a common private equity investment
structure. Preferred returns, if present, are paid out to the investors of the
deal, while the promote is paid out to the “sponsor” of the deal, the person or
entity who puts it together and pretty much does all of the work. These factors
play into the cash flow waterfall which will be discussed later.
Other Assumptions are generally on a case-by-case basis and
not a major component of the model. The Disposition section is important for to
arrive at the final return. The “Exit Cap” is the cap rate at which the property
will be sold, and is generally assumed to be a little higher than current cap
rates for similar deals, to be conservative. This assumption can drive returns
if changed even by a quarter of a percentage point.
Lastly, the Construction section plays a big part into a
multifamily development deal because buildings can be leased while other
buildings are still under construction. Because the timing of cash flows
affects return metrics like IRR, this is an important component. In this model,
about 5% of the construction is projected to be completed per month. This can
change deal to deal, depending on density of the development and any special
circumstances associated with site development or permitting. The first move in
month and leases per month determine how quickly the project will be
“stabilized” or operating at peak occupancy. These numbers are generally
gleaned from previous developments to try to garner as realistic a number as
possible. The months to stabilization cell is calculated based on the first
move in month, leases per month, and the vacancy loss which is part of the
operations assumptions tab.
Overview of the remaining components:
- Budget: Development budget encompassing every aspect of constructing the buildings
- Waterfall: A statement which shows cash flows to the partners in the deal each year and after the property is sold, and shows the return rates for the deal
- Stabilized Cash Flows: The statement which shows every line-item that has to do with the property after it is constructed and concludes with the Net Operating Income.
- Draw Schedule: Deals with the construction of the property and where the money is coming from
- Amortization: Shows how the loan is being paid off every month.
Summary:
- Color code your financial model to be clear!
- Investment Assumptions are high-level factors in a project
- Key drivers include:
- Financing—where the money is coming from
- Disposition—how much the development can be sold for in the future
- Construction—when will the project start delivering units and reach stabilization
For my next blog post, I will discuss the Operations
Assumptions and Budget components of the model.
No comments:
Post a Comment