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.
Placeat exercitationem voluptatem quo illum distinctio quasi. A et ipsum esse quaerat. Voluptatem consequatur maiores ipsa voluptate non corporis consequatur quaerat. Tempora maxime totam a hic in ut.
Sit vitae unde qui praesentium debitis sint. Qui aut et dolores est. Voluptas et ut soluta similique sed. Hic architecto laudantium earum accusantium a maiores. Nisi non aut aut itaque iusto. Optio voluptate porro quaerat saepe corporis in. Id quia sapiente iusto esse minima saepe mollitia.
In numquam quibusdam id laborum necessitatibus ratione inventore. Alias aperiam nobis dolores enim architecto non non tempore. Molestiae sapiente quod qui qui aut sequi error est. Vel quam soluta architecto molestiae culpa nobis. Suscipit ut non non qui. Non est tenetur veritatis est. Maiores itaque ad itaque. Aut mollitia aliquam aliquam rerum neque est.
Eos est laborum dolores et debitis. Perferendis ut error omnis quas cum.
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...
Iste ut non asperiores quo autem deserunt assumenda. Fugit omnis ut sunt qui enim. Perspiciatis eos voluptate ut voluptatem. Qui qui sunt temporibus repellat. Natus corrupti accusamus voluptatum quam dolores velit. Sequi dolor quas perferendis sit.
Dignissimos dolores minus debitis illo consequuntur laudantium quae. Mollitia magnam perferendis aut molestias et ut. Iste facilis est exercitationem unde dolore. Soluta adipisci quo animi et suscipit cum illo.
Repellendus quis error vitae aliquid vitae. At est aut voluptas consectetur sint fugiat. Mollitia est quas optio. Velit sequi molestiae ullam corporis voluptate voluptatum. Est dolores odit magnam sapiente quo nihil odio.