Real Estate Modeling Tests
Question about modeling tests that most RE shops have you do: while I understand that they can be specific to the industry (resi, office, industrial, etc.), is it typically building out a standard cash flow with exit and showing relevant metrics to drive your thought process on the deal? It has been awhile since I have done one of these tests and wanted to see what you guys have seen recently in the market.
Also - have most companies moved to doing these modeling tests remote? I just dont know how to leave my office for hours in a busy work day to model in person. Do companies let you do it later at night remotely or on the weekend?
Great timing! I need to take one of these as well, multifamily development....never done one before, any advice or practice tips would be greatly appreciated.
A developer in RE has never done a development model?
Please see below an example ...
An opportunity exists to acquire a hypothetical 478-unit multifamily property in suburban Maryland. The property was built in 1999 and is fully stabilized. Build a dynamic real estate acquisition, operation, disposition, and financing model to evaluate the opportunity. Please provide an annual cash flow summary formatted to print.
General Assumptions:
● Acquisition Date: 4/1/2015
● Purchase Price: $110,000,000
● Stabilized Occupancy: 95%
● Rental Rate: See T-12 Financials Provided
● Revenue Growth: 4% annually
● Operating Expenses: See T-12 Financials Provided
o Management Fee = 2.5% of Total Income
o Tax's Reassessed at Purchase: $1,600,000 Year 1, 3% growth thereafter o Replacements: $350/unit per year
● Expense Growth: 2.5% annually
● CapEx - $600/unit per year
● Hold Period: 10 Years
● Exit Cap Rate: 6.25%
● Sales Costs: 2.75%
Senior Mortgage
● 65% Loan to Cost
● Origination Fee: 45 bps
● Interest Rate: 3.5%, Fixed
● Amortization: 30 Years
Please provide a summary that includes the following metrics:
● Unlevered IRR
● Unlevered IRR Sensitivity Table (analyst choice of most relevant variables)
● Unlevered Return on Cost
● Levered IRR
n
Thank you!
Are these exams typically “build from scratch” types? Taking the BIWS course now and it seems like most of their prep already has built out templates (also could just be that I’m only a few modules in). Either way, would be helpful to know.
Most are build from scratch. I have 40+ prompts from actual RE case studies from MF to boutiques. 90% were build from scratch, about 50% were timed with less than hours alotted. Would say these posted ones i've pulled from WSO are representative of what the avg. case study looks like.
Pretty even split between multifamily and commercial. If it's commercial, it's usually 2 to 3 tenants with simplified expense reim. structures (NNN mostly).
Please see below an example...
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
Please see below a link to an old Related case study...
https://imgur.com/a/W1HFd
Please see below an example...
NOI of 1 million
Purchase at a 7% cap rate
Expense margin of 45%
Revenue Growth Yr 1: 7% Yr 2: 6% Yr 3+ 3%
Expense Growth: Yr 1: 5% Yr 2+ 3%
Acquisition closing costs 3%
$750,000 of capex, funded at closing
Leverage: 75% of purchase price (not including closing cost or capex)
Financing: 4% interest rate 30 yr amortization
Sale in year 5 at 7.25% cap rate on forward year NOI
Sale closing costs 2%
Show IRR, profit, and equity multiple
IRR and equity multiple sensitivity tables
- Sensitize exit year (3-6)
- Exit cap rate (6.25% to 8.0% in 25 basis point increments)
Please see below an example...
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)
Any shot you could provide the models to these questions? Would be extremely helpful!
+SB your way.
Let’s crowdsource the solutions to these interview problems in excel and post them to the forum’s Google Drive (not sure if that link is still active).
This would be amazing!
Culpa consequuntur dolor asperiores temporibus dolores sunt porro. Fugiat qui quia dolorem facilis. Error nemo vel soluta qui quos facere qui. Sit non veniam et labore ullam eligendi et. Est mollitia quaerat fugit ut. Voluptatem qui nobis sit et aut vitae.
Sed est quod officia corrupti voluptas est neque nostrum. Perspiciatis aperiam magnam aperiam atque voluptas tenetur voluptates. Est laboriosam laborum odit.
Fuga ut ut non tenetur error. Delectus sed dolor ipsa repellat voluptas.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...