Real Estate Private Equity Excel Test Help

jt8506's picture
Rank: Baboon | 120

I have an interview next week with a real estate private equity firm for an analyst position in their acquisitions department. The HR guy said the exam would be a basic excel model (take no longer than an hour) with a list of questions to answer. Could anyone provide me with some sample excel interview models that they had to build for their company or practice models to prep for these types of interviews. Much appreciated!

Comments (37)

Best Response
Sep 27, 2011

If its not going to take you longer then an hour, i doubt its going to be a full modeling test. Most likely they are testing your familiarity with basic excel functions and real estate concepts. For example, set up a cap rate sensitivity matrix - they will give you or you will have to calc NOI, then set up a pricing table with a range of cap rates in whatever increments, very basic and easy stuff. Honestly, i could construct a test for you in under an hour, i do it for our candidates.

Are you an undergrad or experienced hire?

Sep 27, 2011

I took a test that was a condo development where you roughly had to amortize certain development costs over say a 12 month development period while drawing on a loan facility for 70% of the hard costs. Then sell off the different units based on a PSF sale price where 80% of proceeds pays down the loan (until loan is paid off), 10% pays a royalty fee and 10% gets distributed. The idea was to grasp the concepts of a loan facility, amortization, ratable sales, stabilization period, sensitivity tables etc.

Oct 26, 2016

sounds like fun. really.. haha

Oct 26, 2016

but not for an interview...

Sep 28, 2011

Thanks for the help guys! Anyone else?

Oct 26, 2016

They will typically ask you for a basic cash flow model (apartments and hotels are most common) either by giving you a handful of assumptions, or simply handing you an OM. It is pretty common for PE firms to make sure you can do a simple IRR waterfall so I would recommend knowing how to build one out.

If the fund you are interviewing with has previously done office investments (regardless of what their focus is), ARGUS is fair game.

Oct 26, 2016

I do basic waterfalls when I'm asked but I just run the cash flow through a waterfall model that I have been progressively creating for over a year now. If you gave me a blank excel sheet, I'm not entirely sure I could recreate what I have in my office. What are your thoughts on that? I'd think a lot of people are in the same boat. I created something that is flexible and easily "dumpable" when it comes to CF data and changing the prefs and promotes. I'd like to think I wouldn't have to create a waterfall model from scratch in an interview. That being said, I'd like to think a lot of things.

Thoughts?

Oct 26, 2016

From my experience, the priority for REPE modeling tests is to see how quickly you can do a back of the envelope type analysis and get all the key metrics to get a general understanding of the deal, and less about how technical you get. So the assumptions they give you are pretty straightforward and just be able to get IRR, DSCR, and basic waterfall as mentioned above.

Oct 26, 2016

And if multifamily focused I doubt they'd use ARGUS for a test, and if they do they would mention it before hand either when inviting you to interview or in the job description as a requirement.

Oct 26, 2016

I don't even know how I'd model a multifam project in Argus and I use Argus every day. Always just do the base rental income myself and take it from there within excel. Anyone who does this want to shed some light for me? What would you put into the rent roll section of argus? What if a 200 unit apartment building has 200 different units with different sf?

Oct 26, 2016

@cre123 - Agreed regarding the waterfall.

Oct 26, 2016

@cre123 - Agreed regarding the waterfall.

Oct 26, 2016

I highly doubt ARGUS will be tested. It's basically a black box that spits out cash flows and there's no way you would be expected to model something so complex as to require it anyways (e.g., an office complex with 50+ tenants). If it's a remote test there's 0% chance you will be tested on it as it's subscription based.

The test will likely consist of modeling cash flows for an office / industrial / hotel and calculating IRR and multiples. Make sure you know how to build up to unlevered and levered IRR and make it dynamic.

Rev less expenses = NOI
NOI less Capex / TI / LC = Unlev. CF
Unlev CF + debt proceeds - debt service - payoff = Lev. CF

Model this out monthly and use XIRR over the stream of cash flows. Maybe throw in some sensitivity tables for good measure and you'll be all set.

Oct 26, 2016

Agree with Mr_Coffee
Additionally, you might want to do an IRR solely for the equity shareholder.

Oct 26, 2016

I've never seen ARGUS used on one of the exams. The most common "hard part" is the equity distribution waterfall at the end. Also some people get tripped up doing the amortization schedule on the loan.

Oct 26, 2016

If you get tripped up amortizing a basic loan then you probably are in the wrong interview room at a solid REPE shop.

    • 1
Oct 26, 2016

@cre123 you're not wrong, but people do.

Sep 28, 2011

RE Capital Markets,

I'd love to have a sample test so that I can practice!

Oct 26, 2016

bump

Oct 26, 2016

Know how to model a JV acquisition and disposition for each property type and you will be fine

Oct 26, 2016

I have an excel test coming up for a REPE position in their distressed debt department. Could anybody here provide insight on what to expect and what i should know prior to taking the test?

Sep 29, 2011

generally speaking I think the above comments are correct... if you're an analyst who's not coming out of a bank, etc, it's not going to be that complicated. Just make sure you're familiar with the appropriate real estate terminology (NOI, Cap rates, etc.).... I doubt they're going to ask you to model in a profit distribution waterfall or anything like that if you haven't been in the business

Apr 26, 2016

@International Pymp I have an interview and they said that "extra credit" will be modeling the waterfall.. Do you have an example by chance? Much appreciated in advance.

Oct 25, 2016

For my interview with a RE PE firm they actually had me model a simple waterfall with a preferred return and a promote. I couldn't get all the way through it (actually thought I failed) but I showed that I could build a basic model and that's all they needed to hire me for an associate position

Oct 26, 2016

Without knowing any more specifics about the company you should focus on: NOI, Cap Rate, depending on your position you might want to familiarize yourself with legal documentation in real estate like subordination agreements, titles, deed of trust, warranty deed, promissory notes, mortgage. Get yourself a free copy of Argus Dcf (you can use it for free for a month, after that it's like a couple thousand dollars to buy) and just play around with it to familiarize yourself with the tools.

Oct 26, 2016

What sharpie said. It also depends if the fund is more focused at the asset/property level or is more "financially" oriented (REITs, Mezz, etc).

Please don't quote Patrick Bateman.

Oct 26, 2016

I can do everything I need to do in Argus and a ton in excel (have been working with them for several years)... I am more concerned with the implication of it being private equity... Are specific PE metrics or models that I will be expected to know? Or is REPE still very similar to your standard RE investment firm? The fund will be both real estate (property) oriented and financially oriented as with real estate operating co's etc.

Oct 26, 2016

Also very interested

Oct 26, 2016

Bump - Sure this would be useful to a lot of guys here...

Oct 26, 2016

Bump. Also very interested.