• 059 01/30/2017

    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 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.


    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.


  • 058 01/16/2017

    Private equity funds are closed-end investment vehicles, which means that there is a limited window to raise funds and once this window has expired no further funds can be raised. These funds are generally formed as either a Limited Partnership (“LP”) or Limited Liability Company (“LLC”). The advantages of these structures for a private equity fund are as follows:

    1) Perhaps the biggest advantage for investors is that they are exposed to limited liability. If anything goes wrong in the investment process (bankruptcy, lawsuits, etc.), the investor risks only the capital they have committed. 

    2) LPs and LLCs are pass-through entities for federal income tax purposes.

    Illustrated Organizational Chart:

    Private Equity Fund Structure

    Raising a private equity fund requires two groups of people:

    1) Financial Sponsor (“Sponsor” in image): The team of individuals that will identify, execute and manage investments in privately-held operating businesses. This is generally comprised of a General Partner and a Management Company.

    General Partner: The entity with the legal authority to make decisions for the fund. This entity also assumes all legal liability.

    Management Company: The operating entity that employs the investment professionals responsible for allocating capital and managing investments.

    2) Investors: The individuals that will provide the capital to make those investments. Because funds are generally formed as Limited Partnerships, investors are often referred to as limited partners.

    In raising a fund, the fund founders will reach out to sources of institutional capital such as pension plans and university endowments, as well as high net worth family offices and individuals. The commitment to the fund, known as the “capital commitment,” will be made via a partnership agreement stipulating that the capital invested or resulting assets will be returned within a fixed period of time (typically 10 years). In most cases this is structured as a limited partnership agreement (LPA). The LPA will typically include the following:

    Mandate: The partnership agreement may provide parameters for acceptable investments. These restrictions could relate to scale, geography and security type, etc.

    Fund Term: This defines the time horizons available for investment and divestment.

    Management Fees: This defines the fee tied to the capital raised, or assets under management (“AUM”). The Management Company will typically earn an annual 2% fee on AUM.

    Distribution Waterfall: Distribution waterfalls define the economic relationship between the general partner (“GP”) and limited partners (“LP”). This is how the GP earns what is known as a carried interest, which is typically 20% of the proceeds after the LP has received distributions equal to the original capital invested plus a defined preferred return. Please follow this link for an example.

    For more information about leveraged buyouts please visit the Leveraged Buyout Video Series.

    Click HERE to have posts like this one sent directly to your inbox.



Models are:
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above



*Answers a, b, c, d, e, f and g are all correct.