Modeling Revenues: Commercial - Comprehensive Guide to Method Two Multifamily Revenue Modeling

Comparison of Revenue Modeling Methods for Multifamily Properties

• Fundamental Distinction between Method One and Method Two:

  • The primary difference between Method One and Method Two lies in the starting point of the revenue build.

  • Method One: Begins with Net Effective Rent (NER) and moves downward to calculate Total Revenue. It is often preferred for its simplicity and more straightforward approach.

  • Method Two: Begins at a higher level with Gross Potential Rent (GPR). It requires an additional step to account for Loss to Lease and Concessions before arriving at the Net Effective Rent (NER).

Structural Layout of the Multifamily Revenue Model

• Inputs and Drivers Tab:

  • Organized into hierarchical sections: Property Overview/Acquisition Assumptions, In-Place Income/Revenue Assumptions, and Capital Expenditures for Rent Premiums and Unit Mix.

  • Model-Specific Differences: While Method One focuses primarily on Effective Rent, Method Two tracks both Market Rent and Effective Rent. The Gross Potential Rent serves as the foundational row instead of Net Effective Rent.

• Model Tab Organization:

  • Features a timeline at the top.

  • Historical financials are pulled into columns $E$ (Trailing 3 Months) and $F$ (Trailing 12 Months).

  • Tracking logic differentiates between unrenovated and renovated units to apply distinct rental rates.

  • Income flow: $\text{Gross Potential Rent} \rightarrow \text{Loss to Lease} \rightarrow \text{Concessions} \rightarrow \text{Net Effective Rent} \rightarrow \text{Total Rental Income} \rightarrow \text{Total Revenue}$.

• Historical Financials Tab:

  • The P&L (Profit and Loss) output remains consistent with Method One, but the summary at the bottom changes to build toward Net Effective Rent from Gross Potential Rent.

The Tagging and Categorization Process for Method Two

To automate the pulling of historical data, a numbering/tagging system is established for the following line items:

1. Gross Potential Rent (Tag 1): Represents the theoretical maximum rent if every unit were leased at market rates.

2. Loss to Lease (Tag 2): The difference between market rents and the actual rents specified in existing leases.

3. Concessions (Tag 3): Discounts or incentives provided to tenants (e.g., one month of free rent).

4. Vacancy Loss (Tag 4): Revenue lost due to unoccupied units.

5. Non-Revenue Units (Tag 5): Units used for management, model units, or employee housing that do not generate income.

6. Bad Debt (Tag 6): Revenue that is uncollectible; this category includes the "Recovery of Bad Debt."

7. Utility Reimbursements (Tag 7): Often referred to as RUBS (Ratio Utility Billing System), including trash, water, and pest control reimbursements.

8. Other Revenue (Tag 8): Miscellaneous income sources not covered by the above categories.

Analytical Calculations: Trailing Financials and Checks

• Annualizing the Trailing Three Months ($T-3$):

  • Purpose: Captures the most recent performance while accounting for recent trends.

  • Formula: $\sum(\text{most recent 3 months}) \times 4$

• Calculating Trailing Twelve Months ($T-12$):

  • Purpose: Provides a full year of data to smooth out seasonality.

  • Formula: $\sum(\text{last 12 months})$

• Formula Validation (The Checkbox):

  • A logical check is used to ensure the calculated Total Income matches the P&L total.

  • Logic: $\text{IF}(\text{Calculated Total Income} = \text{P\&L Total Income}, \text{"OK"}, \text{"Check"})$

  • Decimals may cause false errors; the ROUND function is applied to both sides of the evaluation to reconcile small differences: $ROUND(x, 0)$.

Revenue and Growth Drivers

• Occupancy Assumptions:

  • Year 1 occupancy might be set at a specific percentage (e.g., $90\%$) based on historical values (e.g., $88\%$) and increased gradually to a stabilized rate (e.g., $93\%$) by Year 4.

• Rent Growth Categories:

  • Market Rent Growth: Can be modeled as monthly incremental growth ($\frac{\text{Annual Rate}}{12}$) because multifamily rents can reset daily due to high turnover.

  • Other Revenue Growth: Can be modeled as a step-up occurring only at the start of a new year.

  • Growth Logic Formula: $\text{IF}(\text{Current Year} \neq \text{Previous Year}, \text{Previous Value} \times (1 + \text{Growth Rate}), \text{Previous Value})$

Capital Expenditure (CapEx) and Renovation Metrics

• Business Plan Assumption: Use offensive renovations to achieve a rental premium.

• Example Variables:

  • Cost per unit: $\$6,000$

  • Monthly Rent Premium: $\$125$

  • Duration: $12\,months$

  • ROI Calculation (Annualized): $\frac{\$125 \times 12}{\$6,000} = 25\%$

• Renovation Pace Calculation:

  • If there are $150\,units$ to renovate over $12\,months$:

  • $\frac{150}{12} = 12.5 \text{ (rounded to 13) units per month}$

• Timing Triggers for Renovations:

  • Timing logic is used to ensure units are only added between the start and end dates.

  • Logic: $\text{Condition 1: } \text{Date} \ge \text{Start Date}$ AND \text{Condition 2: } \text{Date} < \text{End Date}

Tracking Units and Market Rents

• Unit Tracking:

  • Units Renovated: Previous Month Value + Units Added during current month timings.

  • Units Unrenovated: $\text{Total Units} - \text{Units Renovated}$.

• Market Rent Calculations:

  • Unrenovated Market Rent: Previous month grown by monthly rent growth: $\text{Unrenovated Market Rent}_{t-1} \times (1 + \frac{\text{Growth}}{12})$.

  • Renovated Market Rent: $\text{Unrenovated Market Rent} + \text{Rent Premium} ($\$125\).

Summary Metrics formulas

• Gross Potential Rent (GPR):

  • $(\text{Unrenovated Units} \times \text{Unrenovated Market Rent}) + (\text{Renovated Units} \times \text{Renovated Market Rent})$

• Net Effective Rent (NER):

  • $\text{GPR} - \text{Loss to Lease} - \text{Concessions}$

• Physical Occupancy Metric:

  • $1 - \frac{\text{Vacancy Loss}}{\text{Net Effective Rent}}$

• Effective Rent per Month:

  • $\frac{\text{NER}}{\text{Total Units} \times 12}$

Key Takeaways and Professional Applications

• High Turnover Dynamics: Multifamily properties allow for daily rent resets, justifying monthly incremental rent growth modeling rather than a single annual jump.

• Method Selection Nuance: In Method Two, if you grow Market Rent by $3\%$ while simultaneously reducing Loss to Lease or Concessions, your true effective rent growth will significantly exceed $3\%$. Analysts must monitor this interplay closely.

• In-Place Definitions: "In-place" varies by firm; common standards include T-3, T-12, or a hybrid. This model utilizes the T-3 for in-place assumptions.

• Cross-Sector Applicability: While built for multifamily, these logic structures apply to:

  • Student Housing: Modeling by bed rather than by unit.

  • Single Family Rentals (SFR): Modeling individual homes as units.

  • Self Storage: Modeling storage units with unique auxiliary revenue streams (e.g., insurance or packing supplies).