Failed Real Estate Acq Assoc Candidate Case Study

Lowdoggo's picture
Rank: Monkey | 34

I recently sat for a second round interview for the role above with a very large US-based developer's London office. It was shortly followed by the recruiter "expediating the process" by sending me a case study that they have all of their hires do. I returned my product and received radio silence for a week. Upon initiating follow-up, I received feedback from the recruiter stating that "the team felt that the modeling was not quite up to associate level." I'm a recent MBA grad with limited analyst experience, but I felt my math was good. I'm fairly certain I won't get the role now, so will someone please critique the hell out of my submission so I can be better prepared later?

Remove spaces:

Here's the prompt:
www.dropbox.co m/s/ndxl0s6cong2l7a/Test%20Overview.docx?dl=0

Here are the provided cashflows:
www.dropbox.co m/s/qld7r3qwxkraeot/Test%20Cashflows.xlsx?dl=0

Here is my response:
www.dropbox.co m/s/3lkgw99rm4bn5rn/Test%20Submission.xlsx?dl=0

Comments (25)

Jun 12, 2019

i can't seem to open any of the links, but i'm happy to take a look at and see. i work for a private equity shop, and have u/w quite a bit (used to work for a high profile brokerage team and did a lot of big ticket deals)

    • 1
Jun 12, 2019

You do realize that the link is cut in half, right? Have you used DropBox before?

    • 1
Jun 12, 2019

Simply copy/paste the entire link to a web browser and remove the space in "c om." (My account is too new to link the full address.)

    • 1
Jun 12, 2019

If you are willing to share your feedback with me over message I'd appreciate it. Going to take a stab at the test myself.

Jun 12, 2019

Sadly the only feedback I've received so far is what is in the original post.

Jun 12, 2019

i'm curious, how long did they give you to put this together? i haven't dug into it yet to check the calculations but it looks like you didn't skimp on effort and seems like you responded to all the points.

Jun 12, 2019

I was given maximum one week. It's the calculations I'm more worried about. Structure 1 was classic waterfall through pref and hurdles, structure 2 was worded a bit strangely, structure 3 used multiples for hurdles which was new to me, but my result "felt" right. Then again, my experience is pretty limited, so I'm wary of hubris here.

Jun 12, 2019

Something that was strange to me: the chart axes are specified. If you sensitize around the provided 180,000,000 exit, the net cash flow IRR's plot outside of the chart from the get-go. That seems really weird to me from a case study stand point. Any time I was given a value in a case study and told to sensitize, I always did assumptions above and below that amount. If you do that here, the optimistic values plot way outside of the chart.

Jun 12, 2019

Side note: to get the exact formatting I used, open in Excel desktop -- dropbox's reader is distorting things here and there.

Jun 13, 2019

I've only taken a quick glance (will take a better look later) but the first thing I seen was that the IRR tests on each tier are not hitting the exact value. Off the top of my head I think instead of (1+Structure1Pref)^(1/12)-1) you would need to get something along the lines of the below;

(1+Structure1Pref)^(x/y)-1)
Where;
x = days since last accrued payment
y = days in year (accounting for leap years)

Or something along those lines, someone feel free to correct me.

Your formatting is pretty good and easy to follow.

    • 2
Jun 13, 2019

That's correct. For x, you'd subtract the two dates. An XIRR check would prove this out too.

    • 1
Jun 13, 2019

You also could use the days function in excel to accomplish the same thing:

=Days(end date, beginning date)/360 *Hurdle Rate

I also feel like hurdle IRRs don't always tie out exactly when using XIRR? Just a thought....

    • 1
Jun 13, 2019

*Correction: it would tie out with 365 days. It depends on how interest is compounded, which I don't think is spelled out for the sake of the case study, but would be a think on a live deal.

    • 1
Jul 4, 2019

Thanks very much for this -- I implemented your suggestion in this way:
((1+Structure1Pref)^((H2-G2)/(DATE(YEAR(H2),12,31)-DATE(YEAR(H2),1,1)+1))-1)

My Preferred IRR is coming out to 7.99% instead of exactly 8. Any ideas what may be missing?

Jul 8, 2019

I'm not sure, I know I've ran into this problem in the past and fixed it but it's buried somewhere on one of my old models and can't seem to find it (disappointed I haven't kept this fix running with my newer models!). If I manage to find it I'll post it here.

Jun 13, 2019

beyond me but appreciate the candor in sharing. would be interested to see everyone else's thoughts. and see what additional feedback the firm gives you.

    • 1
Jun 13, 2019

Only thought I have is maybe putting the assumptions on the actual waterfall pages, having built-in tests, and putting in sums/totals on the left side for the waterfall.

If I want to print those tabs, and just those tabs, I have no idea what the assumptions/hurdles are and I want to see sums/bottom line IRRs and multiples. Also, we all know you think there are no errors, but everyone likes to see a "check" line-- it gives them a warm fuzzy feeling.

Again, these are very nit comments, and different people have different styles, but if you forced a nit-pick, those would be my comments.

    • 1
Jun 13, 2019

Looks like you did a good job conceptually but you solutions were not precise.

    • 1
Most Helpful
Jun 13, 2019

I think all the above are interesting points. With that being said, I think you are very ahead of the curve as far as where other analysts are. In a prior life I worked with an analyst who was fresh out of his MSRE from a top school...thought he know everything...and struggled consistently with any type of waterfall modelling. the format and layout is one thing, but you'd be surprised how many analyst level people don't understand the concepts behind the math they are using.

Definitely echo the point about making sure your test return %'s tie out EXACTLY to the hurdle. I have mine tie out 100% so that if you enter in a cell your test value = input value excel should return TRUE.

Some advice I would have is how you laid out the waterfalls. It did take a bit of reviewing before I finally understood the flow from A-Z. This can ding you hard on these tests, because the grader mentally checks out soon as he had to work to understand the math and results.
Instead, I've found that the below logic and illustration resonates well with a 3rd party who doesnt know your model/template from working in it every day. I find that this way allows you to quickly and easily identify when you start promoting, when the pref is paid off, etc...

1) Capital Contributions
2) NCF
3)Distributions
4) Sale Price
5) Total NCF

6) for your PREF...
- beginning pref balance (either compounded and accrued or simple)
- preferred return $ (i.e. your rate of return * the beg bal)
- distributions
- ending balance

7) Return of Equity...
- beg balance
- contirbutions
- distirbutions
- ending balance

8) IRR/MOC hurdles...
- beginning balance
- cumulative distribution needed to reach hurdle
- actual distribution for that period
- ending balance

9) RESIDUAL SPLITS...
- simply a line for remaining cash flow, and all investors below that showing their split based on the %.

AGAIN - all in all keep your head up, i think you did good for your level. Let's also remember this is a job interview, and lies are very often told. Could be a million and 1 reasons why they went a different direction -- that's just the reason they gave you...

    • 11
Jul 4, 2019

How do you normally account for acrrual of preference over time to have it tie out exactly? I have implemented some of the advice (see above) for very careful accrual that more accurately counts days, but XIRR reflects just shy of the required 8% (7.993).

Jul 5, 2019

late to this...but for any compounding...ill typically use the effect formula in excel. this takes the annual rate, incorporates the compounding schedule, and returns the equivalent percentage. I found this is the most accurate and easy way to tie out the numbers exactly. Once you have the effective rate, I make the formula based on number of days...meaning take the rate / 365 (make sure it's the exact number of days in the year you are calculating) * number of days between periods. I also like to have my waterfalls built out this way incase another deal comes by and someone says "we want it modeled out to show quarterly distributions per how it's outlined in the OA.". Changing my model from monthly CF to qtly CF is easy, because my formula just calculates a larger number of days between dates.

hope that makes sense.

some things I've found happen a lot when seeing other company models on deals is the coming leap here having an impact...but that's getting a little too cute IMO.

Most Controversial
Jun 13, 2019

Unless I'm missing something, I think this is a shitty prompt TBH for several reasons:

  1. As a junior, you'd be more responsible for structuring asset-level deals/promotes, which would 99% of the time involve some skin in the game and PP returns up to the first hurdle. Would very much doubt that they'd be having a new analyst try to model out internal partnership distributions/true CI.
  2. Somewhat related to point above, but I don't know of any firm that routinely does 0% GP money up front. Most LP's want you to have skin in the game. So this is a little bit odd to me the way they structured it.
  3. Surprised that someone would want you to include a data table in something like this. To me, it only muddies the waters. This screams to me that whoever came up with the prompt doesn't really know a whole lot about excel/UW and just wants to use buzz words to make them look smart.

But it is what it is I guess.

With respect to your model, think the only things I noticed after brief review were:
1. As others mentioned, your IRR checks don't tie. This is important and probably why they dinged you after cursory review.
2. Not sure if anyone else saw this, but structure 2 looks wrong to me. Unless I'm missing something, not sure how your deal level IRR could be 19.89% and both your Ordinary and Carried interest IRR's are both below deal level IRR.
3. Why do you have your exit coming from 2 different places in the model? (i.e., you have capital return sensitivity as a different input than proceeds from exit at top of the first tab. Don't see a reason for this, but let me know if there is something I'm missing). EDIT: I re-read the prompt and see why you did this, but again, think this is redundant so I question the competence of the prompt writer.

Other than that, pretty good attempt though.

    • 7
Jun 13, 2019

Hate to be that guy but this shit is so stupid and is a poor indication of how successful a candidate would be.

There are internal models everywhere that would take legit one day to master.

So idiotic.

    • 4
Jul 4, 2019
Comment
Jul 8, 2019