Modeling revenues: Multifamily - Comprehensive Guide to Multifamily Revenue Modeling and Pro Forma Construction

Characteristics of Multifamily Assets

  • Lease Terminology and Duration:

    • Multifamily assets are characterized by having relatively short-term leases.

    • The standard lease duration is typically one year (12 months12\,\text{months}).

    • Variations exist including longer terms (18 to 24 months18\,\text{to}\,24\,\text{months}) or shorter terms (3 to 6 months3\,\text{to}\,6\,\text{months}).

    • In contrast, other asset classes vary significantly: Hotels have leases that may last only one night, while Office buildings often see leases ranging from 5 to 10 years5\,\text{to}\,10\,\text{years}.

  • Turnover Rates:

    • Multifamily properties typically exhibit a high annual turnover rate of approximately 50%50\%.

    • Managing this turnover is a double-edged sword: while it creates operational costs, it also allows landlords to reset rents to market rates frequently.

  • Investment Profile:

    • Safety: Multifamily is considered one of the safest real estate asset classes.

    • Market Responsiveness: Landlords can quickly adjust rents to respond to occupancy fluctuations. If occupancy is low, rents can be decreased to stimulate demand; if occupancy is high, rents can be increased.

    • Approachability: These assets are viewed as approachable because the business model (an apartment building) is intuitive to most investors.

  • Value-Add Business Plans:

    • The most common business plan involves purchasing a building and investing capital into unit renovations (e.g., upgrading floors, new appliances, paint, redoing fitness centers/common areas).

    • The high 50%50\% turnover rate is the primary mechanism that enables the execution of these renovation-led rent premium strategies.

Revenue Modeling Methodologies

There are two primary methods for building the revenue side of a multifamily financial model. The choice of method often depends on the specific firm or the location of the asset.

  • Method 1: Net Effective Rent (NER) Basis:

    • This is the preferred method for the instructor as it provides a cleaner view of rental growth without the "noise" of moving various deduction levers.

    • Formula: Net Effective Rent (NER)=Effective Rent per Month×Total Units×12 months\text{Net Effective Rent (NER)} = \text{Effective Rent per Month} \times \text{Total Units} \times 12\,\text{months}

  • Method 2: Gross Potential Rent (GPR) Basis:

    • Formula: Gross Potential Rent (GPR)=Market Rent×Total Units×12 months\text{Gross Potential Rent (GPR)} = \text{Market Rent} \times \text{Total Units} \times 12\,\text{months}

    • GPR is essentially the "sticker price" for all units in the building, before any market or tenant-specific adjustments.

Revenue Deductions and Total Rental Income

Regardless of the starting top-line number, several deductions must be accounted for to arrive at the actual collectible income:

  • Vacancy Loss: This accounts for the units that are actually vacant and not generating rent. NER assumes 100%100\% occupancy initially, then backs out this loss.

  • Non-Revenue Units: Units intended to generate no revenue, including:

    • Model Units: Fully furnished units used for tours with potential tenants.

    • Employee Units: Free units Provided to leasing agents or maintenance staff living on-site.

  • Bad Debt: Revenue lost because a tenant occupies a unit but fails to pay the rent.

  • Loss to Lease: (Method 2 Specific) The difference between the theoretical market rent and the actual rent signed on a lease, often because the market moved after the budget was set (e.g., Budgeting $1,000\$1,000 but the market drops to $900\$900).

  • Concessions: (Method 2 Specific) Temporary or short-term discounts used to induce a lease signing (e.g., "one month free" or $200\$200 off the first month). These are burned off over the lease term.

  • Calculation Sequence:

    • Total Rental Income=Net Effective Rent−Vacancy Loss−Non-Revenue Units−Bad Debt\text{Total Rental Income} = \text{Net Effective Rent} - \text{Vacancy Loss} - \text{Non-Revenue Units} - \text{Bad Debt}

Additional Revenue Streams

  • Utility Reimbursements (RUBS):

    • Stands for Ratio Utility Billing Service.

    • The landlord pays property utilities upfront and is reimbursed by tenants based on a ratio.

    • This can be a profit center if a "spread" is included in the billing.

  • Other Revenue:

    • Parking: Can be a massive revenue driver in high-rise urban areas (potentially over $200/month\$200/\text{month} per spot) but typically zero in suburban low-rise properties.

    • Pet Rent: Fees for tenants with animals.

    • Laundry: Revenue from communal washing/drying facilities.

    • Trash Fees and Other General Fees.

  • Total Revenue Equation:

    • Total Revenue=Total Rental Income+Utility Reimbursements+Other Revenue\text{Total Revenue} = \text{Total Rental Income} + \text{Utility Reimbursements} + \text{Other Revenue}

Managing Historical Financials: The "Tagging" Process

When starting a model, the first task is organizing historical P&L (Profit and Loss) data, often provided by partners in varying formats.

  • The SUMIF Formula: Used to dynamically summarize raw historical data.

    • Range: Where the formula looks for identifiers (tags).

    • Criteria: The specific tag look-for.

    • Sum Range: The actual financial values to be totaled.

  • Tagging Categories (Numerical Codes 1-6):

    1. Net Effective Rent (includes GPR, Loss to Lease, and Concessions if using Method 2 inputs).

    2. Vacancy Loss.

    3. Non-Revenue Units (Model units).

    4. Bad Debt (includes Bad Debt Recovery).

    5. Utility Reimbursements (RUBS).

    6. Other Revenue.

Performance Metrics: T3 and T12

Models typically calculate a Trailing 3 Month (T3) and Trailing 12 Month (T12) view to normalize data.

  • Seasonality: Multifamily is seasonal (e.g., summer months typically have higher rates). A T3 view might capture a seasonal peak or trough, while the T12 provides a full-year cycle.

  • Renovation Credit: If an owner renovated units in the last six months, the T3 will reflect those higher rents better than the T12, which would be dragged down by older, lower rents.

  • Calculations:

    • Effective Rent per Month=Net Effective RentTotal Units×12\text{Effective Rent per Month} = \frac{\text{Net Effective Rent}}{\text{Total Units} \times 12}

    • Physical Occupancy=(Vacancy LossNet Effective Rent)+1\text{Physical Occupancy} = \left( \frac{\text{Vacancy Loss}}{\text{Net Effective Rent}} \right) + 1 (Note: Vacancy loss is expressed as a negative number).

Unit Renovation and CapEx Assumptions

In the provided example scenario, the property (built in 2015) is being renovated in a value-add play.

  • Renovation Schedule:

    • Acquisition Date: 12/31/202012/31/2020.

    • Start Date: 03/31/202103/31/2021 (3 months3\,\text{months} post-close).

    • Duration: 12 months12\,\text{months}.

    • Units to Renovate: 150 units150\,\text{units}.

    • Pacing: 15012=13 units/month\frac{150}{12} = 13\,\text{units/month}.

  • Investment and Return:

    • Cost per Unit: $6,000\$6,000.

    • Monthly Rent Premium: $125\$125.

    • Return on Investment (ROI): 125×126,000=25%\frac{125 \times 12}{6,000} = 25\%.

    • Benchmark: An ROI between 20% and 25%20\%\,\text{and}\,25\% is generally considered acceptable. Anything higher requires additional diligence.

Modeling Implementation Logic

  • Rent Growth Calculation: The instructor recommends dividing annual rent growth by 12 (e.g., 3%/12=0.25% per month3\% / 12 = 0.25\%\,\text{per month}) rather than a single annual jump. This reflects that rents grow incrementally as units turn over throughout the year.

  • Renovated vs. Unrenovated Rent: Apply rent growth to the unrenovated rent base, then add the static rent premium ($125\$125) on top. Growing the premium itself is considered less conservative.

  • Timing Triggers: Use Excel logic to ensure renovation impacts and rent growth only occur within the defined schedule window.

  • Effective Rent Formula (Model Tab):

    • NER=(Unrenovated Units×Unrenovated Rent)+(Renovated Units×Renovated Rent)\text{NER} = (\text{Unrenovated Units} \times \text{Unrenovated Rent}) + (\text{Renovated Units} \times \text{Renovated Rent})

  • Utility & Other Revenue Growth: Use an index match to pull growth rates. Ensure growth only triggers when the year changes to avoid compounded monthly growth for non-rent items.