Real Estate Modeling Test Examples

Original Post (In Comments): https://www.wallstreetoasis.com/forums/multi-family-acquisitions-excel-…

Related Case Study Link: https://imgur.com/a/W1HFd

*(Manually converted and cleaned up so there may be variances to actual case study) 

Related's PE fund is proposing to acquire an existing apartment complex called The. Phoenix and needs to run an analysis of its investment. Related is proposing to acquire the Phoenix for a consideration of $110MM, complete lease-up of the project, refinance it after the project has stabilized, and then sell it after a 10-yr hold. Related is proposing to purchase the property with all equity.

The Phoenix is a high-end market-rate apartment complex featuring 200 apartment units. The complex is 250,000 square feet gross and has 200,000 square feet of net leasable area. Due to poor management, the property currently operates at 69% occupancy. The property's operating expenses currently run $2.0MM a year and is expected to increase 3% a year going forward. Upon completing the purchase, Related will lease up the project. Related currently projects that the net absorption of units will be 5 units a month. The in-place rents for the occupied units is $4.00 per square foot per month and is projected to increase by 3% a year going forward.  You may assume the same market rents for the lease-up of the vacant units. Please assume a 5% vacancy in your analysis.

At the end of year 2., Related will refinance the property and place debt on the property. The anticipated terms of a senior loan at that time are as follows:

1. Interest rate of 6%, annual but with monthly payments.
2. 30-year amortization.
3. 10-year term.
4. Maximum loan to value of 80%. Assume a valuation using a cap rate of 5%.
5. Debt service coverage of 1.20x.
6. Maximum debt yield of 6%.
7. Closing costs are $200,000and closing fees are 1.0%

At the end of year 10, Related will sell the property. Currently, the high-end core multifamily product is treading at a cap rate of 4%. You may assume the same cap rate in year 10. For simplicity, you may assume that there are no closing costs or fees from the disposition.

Please put together a financial analysis of the equity-free cash flows for the investment. Plea.se calculate the annual rate of return and equity multiple for Related's equity.

EXTRA CREDIT:

Please inform your interviewer before you proceed with this section of the proforma. Related's equity is split between a General Partner (90%) and a Limited Partner (10%). The General Partner and Limit ed Partner will split all cash flow contributions and distributions pari-passu until the General Partner achieves a 9% preferred return. The cash flows are then split 40% to the General Partner and 60% to the Limited Partner thereafter. Please model the cash flow waterfalls for the General Partner and Limited Partner for this investment. Please calculate the rate of return and equity multiple for the General Partner and Limited Partner.

SPECIAL INSTRUCTIONS:

If you need to make any assumptions in your model, please identify them clearly. We are evaluating your analysis on your accuracy, organization, speed, and understanding of fundamental real estate concepts.

Original Post (In Comments): https://www.wallstreetoasis.com/forums/eastdil-re-analyst-interview-com…

Build a 5 year prorforma. Assume 1% transaction costs for entrance and exit fee for debt and 1% for sale of property. Solve for entry price and entrance cap as well as all of the other data points below assuming you are required to hit a 15% IRR. Make sure everything is sensitized IE dont hard code any inputs into the model. You want to be able to see how changing growth rate from 4% to 2% changes your outputs. Also build an amortization table on separate tab. If you want to impress, build a couple sensitivity tables at the end IE use interest rate and entry cap as your sensitivities with IRR as your output. sorry my formatting is garbage. Everything is a given assumption until you get to "Solve for the following"

Assumptions
Income
Weighted Avg Rent (Per Unit) $3,048.00
Rent Growth (1-2) 4%
Rent Growth (3+) 3%
Vacancy Factor 5%
Parking Income $200,000.00
Other Misc Income $90,000.00
Income Growth Rate 3%

Units 125

Expenses
OpEx (per unit) $790
CaPex Reserve (per Unit) $200
Expense Growth Rates 3%

Debt
LTV 70%
Interest Rate (Amortizing) 6%

Sale
Exit Cap Rate 6%
No Transaction Costs

Building SF 88,600.00

Solve For the Following:

Solve for entrance price and entry cap rate assuming you require a 15% IRR to do the deal

Entrance Cap Rate
Purchase Price
Purchase Price PSF
Loan Amount
Equity
Levered IRR
Equity Multiple
Unlevered IRR
Sales Price PSF

Original Post (In Comments): https://www.wallstreetoasis.com/forums/eastdil-re-analyst-interview-com…

*A.CRE case study 

THE STONES HOTEL – FINANCIAL MODELING TECHNICAL INTERVIEW

This is a variation of a timed technical interview test previously given by a global real estate private equity firm as part of its hiring process.

Participants are given 60 minutes to complete this test.

ASSUMPTIONS – THE STONES HOTEL

• 5-year hold period
• 250 rooms
• Acquisition price of € 200k/room
• Acquisition costs are 2% of purchase price
• Debt
  • 70% loan-to-cost (LTC)
  • 5% interest rate, and requiring mandatory annual debt amortization based on
  • 25-year annual amortization period
• Occupancy
  • 60% in Year 1, 65% in Year 2, 70% in Year 3, and 75% thereafter
• Average Daily Rate (ADR)
  • € 325 in Year 1, growing at 5% in Year 2, 4% in Year 3, and 3% thereafter
• Room Revenues equal 75% of total revenues and other revenues make up the remaining 25% to get Total Revenues
• Assume the following EBITDA margins:
  • 20% in Year 1
  • 23% in Year 2
  • 25% in Year 3
  • 30% thereafter
• CAPEX reserves are 4% of total revenues
• For the exit value, assume a 13x on T12 EBITDA with 2% transaction costs

QUESTIONS – THE STONES HOTEL

• What is the unlevered and levered IRR?
• What is the Peak Equity and WDP (whole dollar profit, i.e., undiscounted profit after recovery of the original basis)?
• Please build sensitivity tables showing the results for the levered IRR and levered WDP with the following variables:
  • Exit multiple vs. hold period
  • Acquisition price vs. exit multiple
  • Loan-to-cost vs. cost of debt

Original Post (In Comments): https://www.wallstreetoasis.com/forums/megafund-repe-modeling-test

Modeling Exercise

All inputs below should be flexible assumptions

Development Program
* 200,000 SF office building
* Land purchase price: $20M ($100 per FAR)
* Closing Costs: 1% of purchase price
* Hard Costs: $300 psf
* Soft Costs: (excluding TI's, LC's and Debt): 15% of hard costs
* TI's: $60 psf - paid at tenant occupancy
* LC's: $18 psf - paid six months before tenant occupancy

Construction & Lease-up
* 24 Month Construction Period, beginning at land close date
* Costs spent evenly over construction period
* 2 Tenant Lease-up of equal size (one tenant at construction completion; one 6 months after completion)
* Lease up to 95%
* Rent $4.25 NNN
* Free Rent: 3 months free
* Annual rental bumps: 3%
* Annual Operating Expenses during Lease-Up: $16 psf

Debt Assumptions
* 60% LTC
* Rate: 5% all-in interest rate
* All equity drawn first; then debt
* Use available cash flow to offset debt costs, as available

Hold Period:
* 5 years after stabilization
* Exit Cap Rate: 5.5%
* Transaction Fees: 1.5%

Joint Venture Structure
* LP invests 95% of required equity / GP invests 5%
* GP receives a 20% promoted interest over a 12% IRR to the LP

Required Output
* Required Project Equity, Net Profit, IRR and ROC (Return on Capital)
* Required LP (after promote) Equity, Net Profit, IRR and ROC (Return on Capital)

Original Post: https://www.wallstreetoasis.com/forums/re-model-case-study-completed-pg…

Post contains MF case study template from PGIM.

Original Post: https://www.wallstreetoasis.com/forums/real-estate-modeling-test

Office Acquisition

Please model the following acquisition for an office building in the US for the following exit scenario. The model should, at the minimum, include the following:

Clearly label all drivers of the model

Project Unlevered and Levered IRR / Equity Multiple

Annual Net Operating Income (NOi) yield over the investment period

10-year quarterly cash flow with rent roll that drive the top line of the cash flow Amortization Schedule

Sources & Uses table

Land Arca: 5,000 sf

Construction GFA: 150,000 sf

Rentable / Sellable Area: 120,000 sf

Purchase Price per Construction GPA: USD 22,000 / sf Acquisition Costs: 5% of purchase price

Market Rent USD 80.0 / sf / month, as of Jan 2019

Market Rent Growth: 1% p.a. from Jan 2019 onwards

Current Occupancy: 0%

Expenses: Assume a reasonable Operating Expense Ratio above NOI and CAPEX of 0.5% of purchase price below NOI

Lease-up Schedule: Leased to 4 tenants at future market rent; 25% of the area to each tenant; 3-year lease terms; Tenancy starts 3, 6, 9 and 12 months from acquisition in June 30, 2014; Tenant renewal probability 50%. Downtime for new leases 3 is months.

Bank Loan: 50% LTC at 5.0% p.a. interest; loan term of 10 years but amortized monthly over 20 years; repay loan at exit; no early repayment penalty and no loan fees

Exit: Exit at year 6 from initial funding at Jan 31, 2019; Exit NOI Cap Rate: 3.0% (Fwd looking 1 yr)

Sensitivity Analysis:

Prepare a few tables showing the investment results (IRR, Equity Multiple, and NOI Yield) changing with respect to purchase price, exit cap rate, exit years, and loan LTC