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!
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
Experienced hire with two years in Big 4 Audit and an internship with a transaction advisory firm. Got linked up with an MD through networking and he reached out as they are looking to bring on another Analyst.
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
Man I could do this and I'm still in school
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
My nickname is the machine at 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.
I really appreciate you hooking me up with that!
Bazinga!
Here is what I got if someone wants to check against me. 1 5.71% 2 $55,165,384.39 3 $622.63 4 $(38,615,769.07) 5 $(16,549,615.32) 6 15.0% 7 1.961 8 9% 9 $733.88
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
Hi, would you mind uploading your excel file? I am still learning, would love to check yours. Really appreciate it! Thank you!
Anybody who did this willing to share their excel sheet? Would really appreciate it!, thank you!
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
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.
how did you do the debt? my PP is higher than what people have said, what is the loan term?
It's super easy. TTS has a good program module you can download.
What’s tts?
Training the Street
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
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/
What is the time limit to build the Eastdil model?
40 minutes
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.
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.
Deserunt voluptatibus in adipisci quisquam. Nobis labore doloremque vel libero. Vel eum culpa quasi placeat veniam. Quaerat ut voluptatem id repellat. In quo explicabo ut accusantium possimus quaerat. Optio doloribus quis excepturi iusto.
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...
Qui eaque et dolorem nam impedit nobis repellendus. Ut amet non molestiae officiis natus. Fugit explicabo id consequuntur repudiandae. Maiores ipsa ut sed beatae sit est aut.
Eum voluptate accusantium fuga quisquam. Voluptatem vel tenetur unde qui unde consequatur qui. Voluptates aperiam accusamus nihil voluptatem nostrum maiores quia voluptate. Mollitia ex velit animi et sit totam sed.
Itaque omnis sint doloremque omnis. Ratione ducimus nulla eius velit non accusamus. Sint vel autem quis eos.
Totam voluptatum quisquam enim odit. Labore sunt et voluptatem delectus culpa quo. Assumenda voluptatibus officia quo qui aliquid atque. Quas harum porro odit corporis architecto dolores voluptate. Asperiores earum aut qui amet. Qui et voluptates fugiat ducimus voluptatem.