Real Estate PE Technical Interview Question - Case Study
Recently I was invited to an interview where I was asked on the spot to build a real estate financial model with the following assumptions:
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)
I was given 20 minutes to complete. Anyone want to take a crack at it?
20 min is brutal
20 mins is definitely harsh. It took me 5 minutes to read through the assumptions and begin to develop a logical approach. How did you fare, I hope the interviewer did not expect clean formatting, etc.
Missing a bunch of bells and whistles and, like mentioned above, the formatting is trash but was able to put it together in ~18 minutes
~18 minutes? You're one of those guys that doesn't use a mouse I presume? Kudos. Color me impressed.
Honest question - at what point in your career did you find yourself able to model out anything that was thrown your way?
18 months of 12 hour days.
you can build one sensitivity table showing IRR/EM/Profit. that would save more time.
how would you do this? I understood it as you need to show the two variables of cap rate and sale year and derive both IRR and EM sensitivities
Cake
This is off topic, and I don't mean to change the topic, but would an interview for analyst for a brokerage (say investment sales or DSF), need to do an excel test? And if so, would it be from scratch like this or about how diffucult would it be?
Its different for each firm. You would likely be expected to take a test though.
Gotcha. I was thinking firms like HFF/CB/JLL etc.
2nd year analyst on an I Sales team in NYC and was given a modeling test during the interview process. If you in the hunt for a RE capital markets position it's likely you'll encounter at least one test for every 3/4 interviews you go to. A lot of these teams are lean and extremely deal focused so they need to you contribute something day one. If you can't...there's another kid that can.
I work at primary multi family shop (buy-side). I got a basic set of assumptions a blank worksheet and needed to find the debt and equity yield in 30 minutes.
I don't have time to model out this post right now, but when I have free time I'll time myself and post an excel doc with my answer.
Got about the same as investREanalyst but our Net Proceeds are different. Took me longer than 20min because I tried getting cute w/ including IRR and multiple in same data table and it took me way too long. I am stubborn.
Also, I would recommend using blue fonts for hard code, black for formulas, and green for reference to other tabs (amort table). more of a traditional PE way to do things.
Well, yeah, but I only had 20min. Bare bones.
yeah, looking back, I didn't do sale on forward NOI....
I've been smashing my head against this comparing my Net Sale(same as JB1500) to yours and am only just making it down this far in the comments...
Can someone upload the file?
https://drive.google.com/file/d/1JrpOQ8aGed6knP_-SqJqqjEEUJxAAO_B/view?…</a">20Min REPE Test
I cleaned up my above screenshot a bit for visual purposes, but didn't mess w/ any formulas. For the most part bare bone formulas. Open to any criticism.
This may be a dumb question, but how did you get rent revenue?
I did my model with the understanding that the $1MM NOI is in-place, so year 1 of your cash flow has revenues grown by 7% and expenses grown by 5%. You all appear to be using $1MM NOI in year one then using the growth percentages in year 2.
https://ibb.co/k6xqyo
https://www.dropbox.com/s/4eerrckg2j1c7yp/Model%20Test.xlsx?dl=0</a">Link to My Model
This is the route I would have taken as well.
sorry if stupid question but how do you get your data table to show both IRR & MOIC in same cell?
edit: nvm sorry just saw you linked to excel - thanks for this!
edit2: quick qs but why isnt closing costs picked up in the sum formula in cell K18?
Many thanks
That's my understanding too, but i think your K8 cell is off a little with my number. I got $19,197,940
If the sale is in year 5, why don't you calculate the sale price based off the YEAR 6 NOI? If you could explain this, I would really appreciate it. I interpreted this case to mean that the property was sold in year 5 and forward looking NOI would be the year following year 5.
Is it typical to hardcode your sensitivity table or make it dynamic in a test like this?
never ever hardcode a sensitivity table
That's what I thought but I saw a few people who did. Thanks.
while we're on the topic, anyone want to do a REPE webinar for us? email me WallStreetOasis.com>[email protected]
cc @investREanalyst"
Can you give a hint at which firm it is? and is it for an analyst or associate position?
I went on this interview as well. I’m very good at building these things but yea he gave 20 mins. Totally nuts. He interrupted me multiple times to push me along
Anyone care to upload an excel version?
https://www.dropbox.com/s/1284tw58rjjgne0/ACRE%20Modeling%20%231.xlsx?d…
I did three attempts with different formatting/approaches. The third one was quick and dirty and was completed in about 12 minutes.
Can someone please explain how they sensitized for hold periods? I can make the model so that the exit year is dynamic but when I plop it into the sensitivity w/ exit cap it comes out with some wacky IRR's.
If you upload your excel file I could help you.
Quis molestias adipisci quisquam et voluptas. Nihil deserunt dignissimos labore et vel ut tenetur neque. Excepturi quia velit quisquam qui sequi et ratione nihil. Eligendi repellendus doloribus sunt odit quidem sed velit dolore. Fugit commodi maxime optio tempora sunt. Et facilis sint sit.
Aliquam deleniti quia aut et in. Saepe blanditiis architecto numquam suscipit. Laboriosam quia sed et vitae. Quam numquam quae quasi cum.
Et recusandae cum harum voluptas. Rerum vitae est et culpa magni ex. Provident est fugiat laboriosam quas optio doloremque qui. Doloremque ut ratione magnam nihil. Ad nisi voluptas maxime distinctio quod delectus nihil voluptas. Dignissimos debitis rerum nulla similique et delectus. Quos doloribus at at earum.
Ut aperiam eum modi at. Est inventore enim non harum atque. Eaque harum ea animi laudantium aut enim similique.
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...
Ut animi quis numquam excepturi et commodi. Ipsam quae modi hic. Omnis reiciendis dolorem enim ut voluptas rerum. Cupiditate incidunt provident repellat nulla et. Eius sint enim est dolores voluptatum. A at voluptas et dolor mollitia quia exercitationem qui.
Quaerat quae facilis corrupti tempora consequatur sed cum. Quas quia possimus possimus. Ipsam reprehenderit ipsum sed asperiores. Consequuntur ratione consequatur provident dolor. Qui omnis sed eligendi est voluptatibus.
Nihil iste sed quo sed adipisci. Illum error voluptas incidunt sed voluptatem. Sed necessitatibus quis ex aut suscipit. Cumque natus enim ut eum est beatae. Velit ut est veritatis quis aperiam quisquam quia ab. Blanditiis quidem et distinctio vel vero rerum placeat.
Possimus sit rerum ducimus illum est sit unde. Molestiae sed voluptates voluptate earum sint et cumque. Accusamus qui eveniet delectus quos et.
Qui nemo vel dolorem consequatur et ipsum provident. Cupiditate ea voluptatem rerum at quaerat. Neque enim est odit velit quia amet quo. Explicabo enim tenetur rerum exercitationem.