Eastdil RE Analyst Interview Coming Up - Excel Test

So I have an interview coming up with Eastdil for an Analyst position and was told I will need to be able to create a full DCF, with levered/unlevered IRR, and a full amortization schedule. Was hoping that someone might have some materials either specific to Eastdil or anything else that would help me get some practice with something comparable to what I might expect to see.

Also, any additional advice or insight would be greatly appreciated. Thanks in advance!

Real Estate Modeling Course

  • Real-life RE Modeling Tests from actual Interviews
  • Various asset classes including multi-family, commercial and more
  • Huge discount - until more tests and cases added

Comments (39)

Mar 21, 2018 - 12:05am

I've got a model for ya, but first I wanna know how you got the interview and if you're an experience hire, fresh from Undergrad, or a summer analyst

Fuckin my way thru nyc one chick at a time
  • 1
  • 1
Best Response
Mar 21, 2018 - 12:30am

Copying and pasting an old modeling test to preserve the anonymity of the company that provided it. 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

Fuckin my way thru nyc one chick at a time
  • 13
Mar 21, 2018 - 5:32pm

RE modeling is highly complex and all the best modelers climb to the top of RE much faster than others. I will look out for your name in bisnow in the next 5 years or so

Fuckin my way thru nyc one chick at a time
  • 2
  • 2
Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Mar 21, 2018 - 10:41pm
Ronclue:

Man I could do this and I'm still in school

Hopefully going to put something together for this tonight or in the morning. Would definitely appreciate The Machine belting out a quick pro forma for me to compare mine to, if you are willing.

Mar 24, 2018 - 11:03am

I got pretty close to you. The biggest difference I think is that I'm capping exit year + 1 NOI (Year 6) for a sale in year 5.

1) 5.77%
2)$59.4 MM PP
3)$671 / SF
4) $41.6 MM Loan
5) $17.8 MM Equity
6) 15% Levered IRR
7) 1.94x
8) 8.11% Unlevered IRR
9) $767 / SF

  • 1
Jul 13, 2018 - 11:41am

Does the Parking Income and Misc Income assume 100% occupancy or 95% occupancy? If it assumes 100% occupancy, you'd have Vacancy Loss be 5% of Potential Gross Income + Other Income, otherwise Vacancy Loss is just 5% of Potential Gross Income.

EDIT: I guess this doesn't really matter for a practice and would differ in a real world scenario where some ancillary income would be affected by occupancy and others would not

Jul 13, 2018 - 3:13pm

1) Entrance Cap Rate 5.86%

2) Purchase Price $58,602,292

3) Purchase Price PSF $661 PSF

4) Loan Amount $41,021,604

5) Equity 30.49% - $17,990,904

6) Levered IRR 15.03%

7) Equity Multiple 1.95X

8) Unlevered IRR 9.49%

9) Sales Price PSF $790 PSF

10) Sales Price $70,024,148

FYI I took vacancy loss as a portion of other income and parking as well as potential gross income (not just PGI), I didn't recapture CapEx reserves on sale, and my CapEx is below the line in regards to NOI.

Jul 12, 2018 - 2:07am

I had something close to IRRelevant until I realized I didn't deduct CapEx from FCF. I "guestimated" PP and backfilled all other #'s from there, and then once model was set up I did a Goal Seek, setting unL IRR to 15% and search for PP (have to set PP to values or else it won't work) Attached model as well (hope this works)

1) Entrance Cap Rate 5.29%

2) Purchase Price $59,566,254

3) Purchase Price PSF $672.31

4) Loan Amount $41,696,378

5) Equity $17,869,876

6) Levered IRR 15.00%

7) Equity Multiple 1.97 x

8) Unlevered IRR 8.42%

9) Sales Price PSF $770.48

10) Sale Price $68,264,767

WSO Test - Updated

*Edit: Realized I didn't take into account CapEx Reserves.... so I updated model & numbers. Not sure if I did it correctly

Jul 12, 2018 - 10:35am

Adventures In CRE has some pretty good "Watch Me Build" videos for various models. I've been building them alongside the videos which has been pretty helpful in terms of internalizing the steps of the process. Might be a good way to develop familiarity prior to testing yourself with case studies.
Here's the link to their Excel library: https://www.adventuresincre.com/library-real-estate-excel-models/

Jan 23, 2019 - 9:43am

Hi all, I know this is an old thread but hopefully someone might reply. Thanks for the uploads. I am still unsure around having to back out both the entry price and the entry cap rate. The models that have been uploaded seem to have guesstimated one of these and then goalseeked for the other.

Because the 15% relates to the Levered IRR, I cant see how to back this out without guesstimating either the entry price or entry cap?

Any help is much appreciated.

Jan 23, 2019 - 3:41pm

Entry Price and Entry Cap is algebra to find the other....

Purchase Price/ NOI = Entry Cap.

If you know the income of the property the sale cap you back into an IRR using goal seek based on Sale Price (NOI/Sales Cap). The only way to achieve an IRR if you know the sale and cash flows is for purchase price to move. Find it using Goal Seek and then you get your Entry Cap.

Purchase Price/ NOI = Entry Cap.

Start Discussion

Total Avg Compensation

June 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (35) $364
  • Associates (202) $234
  • 2nd Year Analyst (115) $151
  • Intern/Summer Associate (97) $145
  • 3rd+ Year Analyst (27) $145
  • 1st Year Analyst (420) $131
  • Intern/Summer Analyst (338) $82