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.
Click here to download the Excel template.*
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 vacant.
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:
Trailing-12: A 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.”
Projection: This 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 statement model.)
Description: The “Description” tab summarizes the details of the transaction.
ITEMS FOR FURTHER CONSIDERATION:
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.).
Vacancy is 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.
Acquisition Reserves: 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 “Projection”).
Capitalization Rate (“Cap Rate”): The quotient of net operating income and property asset value.
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.