In this post you will find a template for the acquisition of
a fictional 160 unit multifamily property. The template was developed in
collaboration with a couple friends that are real estate professionals.
As with most of the financial models on this website, the
data contained in this workbook is entirely fictional. None of it should be
used to develop assumptions about different properties.
The components of the workbook are listed in the sequence
that an analyst would likely take in constructing this template from scratch.
The order emphasizes raw data and input-related worksheets first, and then works
towards the output pages that display the outcome. Most of the worksheets
contain some inputs, but the majority of the raw data can be found on the tab
titled “Rent Roll Analysis” and in the Trailing 12 Month Income Statement on
the tab titled “Trailing-12 & Pro Forma.”
Rent Roll Analysis: An
organized view of the raw data that will be used to develop revenue assumptions
for the projected period. It includes a list of all units at the property and
their status. The information includes the unit type, square footage of the
unit, the lease start date, current rent and whether or not the unit is
Rent Roll Summary: On
account of the amount of information contained on the “Rent Roll Analysis”
worksheet, a worksheet summarizing this information is included in the model.
Trailing-12 & Pro
Forma: This tab has two components that both require the same line items.
The advantage to having it all on one screen is that it reduces the updates
required when raw data is received. It is unlikely that income statement
data for different properties will be identical, and this format makes updating
the Trailing-12 template and the Pro Forma template a one-time adjustment
(versus having this information split on two different tabs). The two
components can be described as follows:
summarized view of the income statement data provided. This tab groups
information into totals to limit the number of variables required in developing
a pro forma and projecting the outcome.
Pro Forma: Using
multiple annualized periods of trailing 12 month income statement data, the Pro
Forma using assumptions developed to project the first year of the forecast on
the worksheet titled “Projection.”
worksheet stores several acquisition scenarios, an eleven year forecast and
calculates the returns achieved.
Amort. Schedule: The
amortization schedule pulls many assumptions related to the “New Loan” from the
“Projection” tab. Interest payments and principal payments are calculated on
this worksheet on account of the amount of space required. It would not be as
practical to fit this as a schedule on the “Projection” tab. (Think of this tab
as the supporting debt schedule you would see in an integrated financial
“Description” tab summarizes the details of the transaction.
ITEMS FOR FURTHER
Calculation of Residual Value is a big assumption that relies heavily on one input: the “Residual Cap Rate” (visible on the tab titled “Projection”). This is used to value the building at exit (Exit Year Cash Flow / Residual Cap Rate = Gross Residual Value). For a great property with no significant improvements made over the hold period, the rule of thumb is to increase the year 1 cap rate by 50 to 75 basis points (“bps”).
Revenue is generally the focus for potential margin improvement in due diligence. Costs are typically pretty well known and there is generally less room for improvement in costs (unlike an operating business). So the question becomes what can a new owner do to push up rents are add incremental fees (priority parking / storage / pet fee / etc.).
typically not included in the historical trailing twelve month data. Most
owners simply net this out of top line, but it is an important factor to
consider in developing a projection. For a solid, stable property 4% to 5% is typically
used to account for regular turnover in units.
If the property is acquired with the intention of making significant
upgrades to the property, the capital required to make those upgrades would be
included in the financial model under this line item (visible on the tab titled
(“Cap Rate”): The quotient of net operating income and property asset
Loan to Value (“LTV”)
Ratio: The sum of the loan expressed as a percentage of the purchase price
of the property. If a $100M property was acquired with $50M of debt, the LTV
would be 50%.
Loss to Lease (“LTL”):
The discount to market rent assumed by the landlord in leasing a unit.
Market Rent: The
rent the unit would command in the marketplace today. In other words, you might
have a unit that was leased several years ago. If the market had improved
significantly in the interim, the market rent might be substantially higher
than the rent currently earned on that unit.
Unleveraged IRR /
Leveraged IRR: This calculates the IRR with and without leverage. In other
words, the unleveraged IRR is the IRR achieved in the absence of the “New Loan”
as it is described in the financial model. Typically only institutional
investors contemplate making the acquisition without leverage of any kind.
*Ignore print formatting. I will upload a new version this evening.