RE Modeling Test please help

Based on the most helpful WSO content, here are some actionable tips and insights to refine your real estate model:

1. Revenue Recognition & Lease-Up Assumptions

• Residential Lease-Up: Since you’re delivering 500 units per year in Years 2–4, you’ll need to account for partial-year rent collections. For example:
  • Assume units delivered mid-year (e.g., June 30th), and calculate 6 months of rent for those units in the first year of delivery.
  • Use a lease-up schedule to reflect occupancy ramp-up (e.g., 50% occupancy in the first 6 months, 90% by the end of Year 2).

• Retail Lease-Up: Retail spaces often take longer to lease. Assume a staggered lease-up period (e.g., 50% occupancy in Year 3, 100% by Year 4).

• Year 6 Sale Timing: For Year 6, include partial rent collections up to the sale date. If the sale occurs mid-year, prorate the NOI for the months leading up to the sale.

2. Cost Allocation

• Construction Costs: A 50/50 split for vertical/horizontal costs in Years 2 and 3 is reasonable. Alternatively, you can spread costs evenly over the 4-year construction period or tie them to unit delivery milestones (e.g., 25% of costs per 500 units delivered).
• Land Cost: Typically, land acquisition costs are taken upfront in Year 1. However, if the deal structure allows for deferred payments, you can spread it over multiple years.
• Soft Costs: Allocate soft costs proportionally across the construction timeline (e.g., 25% per year over 4 years).

3. Retail Rent Calculation

• Base retail rents on the weighted average residential rent. For example:
  • Calculate the weighted average rent for 1-bedroom, 2-bedroom, and 3-bedroom units.
  • Apply a 15% discount to this figure for retail rents.
• Alternatively, if retail rents are market-driven, use market comps instead of tying them to residential rents.

4. NOI Growth & Exit Valuation

• NOI Growth: Apply the 3% annual growth rate to both residential and retail NOI. For example:
  • Year 1 NOI = Base NOI
  • Year 2 NOI = Year 1 NOI × (1 + 3%)
  • Continue compounding annually.
• Exit Valuation: Use the respective cap rates (4.5% for residential, 6.5% for retail) to calculate the sale price:
  • Residential Sale Price = Residential NOI in Year 6 / 4.5%
  • Retail Sale Price = Retail NOI in Year 6 / 6.5%
  • Deduct selling costs (2%) from the total sale proceeds.

5. Financing & Debt Service

• Interest-Only Period: For the first 48 months, calculate interest-only payments based on the loan amount and interest rate (e.g., 4% floor or reference rate + 300 bps).
• Amortization: After the IO period, switch to amortized payments over the remaining 20-year term.
• Draw Schedule: Assume equity is drawn first, followed by debt. Spread debt draws over the construction period based on cost allocation.

6. Sensitivity Table

• Common sensitivity variables include:
  • Exit cap rates (e.g., ±50 bps)
  • Construction costs (e.g., ±10%)
  • Lease-up timing (e.g., ±6 months)
  • Rent growth (e.g., ±1%)
• Ensure your sensitivity table dynamically links to your model inputs. Use Excel’s Data Table function to automate the sensitivity analysis.

7. Additional Tips

• Timeline: Use a monthly timeline for greater accuracy, especially for lease-up and cost allocation.
• Waterfall Distribution: If applicable, include a 2-tier equity waterfall to calculate returns for equity investors.
• Validation: Cross-check your model outputs (e.g., IRR, equity multiple) against industry benchmarks to ensure reasonableness.

For further practice, check out the Real Estate Modelling Test Practice thread on WSO (url: https://www.wallstreetoasis.com/forum/real-estate/real-estate-modelling…). It provides examples of case studies and key elements to include in your model.

Sources: Real Estate Development Modeling, Modelling Test - Please tear it apart :), Life in Acquisitions (Analyst/Associate), REPE Entity level investment, What is PROPER underwriting process like for development projects