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?

Private Equity Interview Course

  • 2,447 questions across 203 private equity funds. Crowdsourced from over 500,000 mem.
  • 9 Detailed LBO Modeling Tests and 15+ hours of video solutions.
  • Trusted by over 1,000 aspiring private equity professionals just like you.

Comments (52)

Jul 25, 2018

20 min is brutal

Jul 25, 2018

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.

Jul 25, 2018

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

    • 2
Jul 25, 2018

~18 minutes? You're one of those guys that doesn't use a mouse I presume? Kudos. Color me impressed.

Jul 25, 2018

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Jul 25, 2018

Honest question - at what point in your career did you find yourself able to model out anything that was thrown your way?

    • 1
Funniest
Jul 25, 2018

18 months of 12 hour days.

    • 2
Jul 25, 2018

you can build one sensitivity table showing IRR/EM/Profit. that would save more time.

Jul 25, 2018

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

Jul 25, 2018

Cake

Jul 25, 2018

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?

Jul 25, 2018

Its different for each firm. You would likely be expected to take a test though.

    • 2
Jul 25, 2018

Gotcha. I was thinking firms like HFF/CB/JLL etc.

Jul 25, 2018

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.

    • 1
Aug 3, 2018

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.

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Jul 25, 2018

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.

Jul 25, 2018

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.

Jul 25, 2018

Well, yeah, but I only had 20min. Bare bones.

Jul 25, 2018

yeah, looking back, I didn't do sale on forward NOI....

    • 1
Jul 25, 2018

Can someone upload the file?

Jul 25, 2018

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.

    • 1
Jul 26, 2018

This may be a dumb question, but how did you get rent revenue?

Most Helpful
Jul 25, 2018

Not dumb at all. You know NOI is $1M, and that Expenses is 45% of Rev. So I set NOI=Rev-Exp, and Exp=Rev*0.45. Then I used Goal Seek to find Rev
By the way - In the screenshot J8 is NOI and J4 is Rev

    • 3
    • 1
Jul 26, 2018

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
Link to My Model

    • 1
Jul 27, 2018

This is the route I would have taken as well.

Aug 3, 2018

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

Dec 27, 2018

That's my understanding too, but i think your K8 cell is off a little with my number. I got $19,197,940

Jul 27, 2018

Is it typical to hardcode your sensitivity table or make it dynamic in a test like this?

Jul 25, 2018

never ever hardcode a sensitivity table

    • 1
Jul 27, 2018

That's what I thought but I saw a few people who did. Thanks.

Jul 27, 2018

while we're on the topic, anyone want to do a REPE webinar for us? email me [email protected]

cc @investREanalyst

WSO's COO (Chief Operating Orangutan) | My Linkedin

    • 1
Jul 25, 2018

Can you give a hint at which firm it is? and is it for an analyst or associate position?

Jul 29, 2018

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

Array

    • 1
Jul 25, 2018

Anyone care to upload an excel version?

Jul 27, 2018

https://www.dropbox.com/s/1284tw58rjjgne0/ACRE%20M...
I did three attempts with different formatting/approaches. The third one was quick and dirty and was completed in about 12 minutes.

    • 1
Jul 31, 2018

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.

Jul 27, 2018

If you upload your excel file I could help you.

Aug 28, 2018

Probably a dumb question but why did everyone schedule annual debt service as opposed to monthly?

    • 1