Megafund REPE Modeling Test
Hi Guys - thanks in advance for any info/insight provided.
Have a modeling exam coming up for experience hire at megafund's real estate equity group (BX, Carlyle, KKR, Fortress, etc.). Can anyone shed some light on what this will entail? How granular of a model? What functionality do they want built?
I assume a waterfall with some data table sensitivities...
Appreciate it.
Best,
-Anonymous guy who posts REPE knowledge on a different account.
Will give you assumptions and rent roll to produce an unlevered cash flow. I've seen office and multifamily. Simple debt terms to calculate leverage cash flow. Simple waterfall (say 1 hurdle, 1 promote, rest pro rata). And calculate IRRs, equity multiples. Model needs to be dynamic. Some ask for written portion aka should I invest in X or not (X being the project you just modeled), what drivers would I focus on to drive growth, etc. Juat keep in mind the excel tests are typically just a check-the-box. Usually dive deeper in person, about valuation and metrics. Good luck!
Below is the modeling test I was given during my interview process. I had two hours to complete and was provided nothing more than a blank Excel worksheet.
Would be interested in your guys' feedback on how you think this stacks up to other modeling exams you've seen/taken/issue. It was by far the most granular exam I was put through during my interview process, and I interviewed at several top tier REPE shops where exams consisted of as little as building a debt schedule and waterfall to filling in the blanks on an annualized cash flow.
Modeling Exercise
All inputs below should be flexible assumptions
Development Program * 200,000 SF office building * Land purchase price: $20M ($100 per FAR) * Closing Costs: 1% of purchase price * Hard Costs: $300 psf * Soft Costs: (excluding TI’s, LC’s and Debt): 15% of hard costs * TI’s: $60 psf - paid at tenant occupancy * LC’s: $18 psf - paid six months before tenant occupancy
Construction & Lease-up * 24 Month Construction Period, beginning at land close date * Costs spent evenly over construction period * 2 Tenant Lease-up of equal size (one tenant at construction completion; one 6 months after completion) * Lease up to 95% * Rent $4.25 NNN * Free Rent: 3 months free * Annual rental bumps: 3% * Annual Operating Expenses during Lease-Up: $16 psf
Debt Assumptions * 60% LTC * Rate: 5% all-in interest rate * All equity drawn first; then debt * Use available cash flow to offset debt costs, as available
Hold Period: * 5 years after stabilization * Exit Cap Rate: 5.5% * Transaction Fees: 1.5%
Joint Venture Structure * LP invests 95% of required equity / GP invests 5% * GP receives a 20% promoted interest over a 12% IRR to the LP
Required Output * Required Project Equity, Net Profit, IRR and ROC (Return on Capital) * Required LP (after promote) Equity, Net Profit, IRR and ROC (Return on Capital)
Looks incredibly similar to both a modeling test in my MRED program and to modeling exams I've taken for jobs.
Perhaps a bit more complex than other with the promote structure - for jobs I've seen a couple "equity expects a 9% promote on original investment" with nothing in regards to IRR hurdles - but definitely not insurmountable by any means. The cash flow to offset debt costs is trickier than normal too, but again, you can just think through it.
Did you crush it?
If anyone is interested, the guy from Adventures in CRE literally took this question and modeled it out in a youtube video.
If it's just an hour its probably just annual. In one hour it's going to be VERY basic. Many of them are like 3-4 hours.
Correct - I remember interviewing with CIM and i think it was a 30 min window to model... basically gave us an NOI line item, said grow it by 3%, provided some debt and purchase assumptions so we could create a levered cash flow, and a simple, single tiered waterfall.
An easy way to see if you have the basics down... building an amo table, understanding wtf a promote is, etc.
Honestly, the best way to prepare for these tests is just find the hardest example you can find (something like the above but add in a refi scenario, another layer into the the promote, maybe a diversified tenant base (as in add a retail component with separate assumptions) and if you can figure out how to effectively model that scenario over and over in an appropriate matter of time, then you should pass all these interviews with flying colors... there really isn't any one simple method to follow that will ensure you shine bright and get every offer by stunning the interviewer with your amazing modeling skills.
The only extra piece I'll add is that you should be able to address the modeling exercise from different angles... for example, being asked "what are the returns given the following assumptions" being asked "what price would I pay to hit my return requirement of [20]% given the following assumptions"... one is asking for an IRR and one is asking for an NPV...
Also, be aware of where you're interviewing... I knew that the shop that administered the above modeling test was heavily engaged in ground-up development... If I had been interviewing with Commonwealth Partners or Pembroke, I would have focused solely on dialing in the acquisition of a stabilized asset, understanding that there's no way or reason that a core shop would be administering a development modeling test