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.
Comments (31)
Being able to build a dynamic Excel model from scratch with levered cash flows/ returns at the project and partnership levels, with sensitivities, is probably what you'll have to do. Just build one from scratch everyday and know how to calculate all/any common investment metrics and you'll be fine.
by partnership level do you mean waterfall?
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!
Thanks. Sounds similar to the modeling test I took when I interviewed at the shop I'm currently at.
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?
Commercial Real Estate Developer
what kind of REPE shops asked you to do this kind of modeling? are we talking the big ones like BX, Carlyle, Related, Tishman etc?
It is slow for me at work so I'm doing this for practice. I would be curious to compare answers with anyone that has done it. Regarding the cash flow to offset debt did you guys use the CF to fund costs in lieu of debt funding? In other words did you end up with effectively lower leverage or did you fully fund the loan and have CF to equity?
I would say this is what I have seen at several REPE shops interviews with 2-3 hour window to finish it.
was that just for a development position?
If anyone is interested, the guy from Adventures in CRE literally took this question and modeled it out in a youtube video.
Apologies if this question is a bit basic but I don't have much experience modeling with debt based on LTC:
My initial assumption would be that the costs inluded in the LTC calculation would only be land acquisition cost, transaction fees and soft/hard costs. Is it standard for lenders to include TIs, LCs, and financing fees in the total cost as well? How about operating shortfalls (Adventures in CRE has a version of this model which includes this in the total project cost as well)?
Thanks in advance!
I did this an worked through all of the kinks (I think). PM me your email if you want and I'll send it.
Thank you for the case!
* 2 Tenant Lease-up of equal size (one tenant at construction completion; one 6 months after completion)
Lease up to 95% so each tenant is leasing 95k SQF????
What level role were you applying for? Analyst? Associate?
If one can do this eyes closed, are they ready to hit the ground running at either a top dev or top PE shop (Related vs BX)
Not granular from the real estate side. Know the mechanics of the waterfall (most important) and when the timing of specific cash flows tends to happen... Also know how to tie in the debt properly and assume there may be mezz + senior.
Thanks. Monthly period modeling? The exam is only 1 hour which is why I asked about granularity.
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.
In the model, How do you suddenly make F25 and F27 become "ok" instead of "error"? The c21 " Const. Interest" should not be zero right?
Iferror function
i have a couple modeling tests from firms comparable to those in the original post, although they're all similar to the one posted above. if interested, PM me with an email address.
Sidney - could you send to me? thanks
Me too please!
Sidney, would you mind sending me a copy of the modeling tests you have?
Et et temporibus ea labore inventore aut quis. Quas ut animi qui architecto ut qui ab. Officiis unde assumenda quam est numquam iusto. Et nihil tempora iusto ab repellendus quia. Incidunt recusandae voluptatem ut qui blanditiis commodi accusamus aperiam.
Laudantium molestias iusto voluptatem et non voluptate. Veniam provident maiores eligendi nam. Itaque voluptas dignissimos iste est pariatur sed ut. Unde similique ratione a rerum.
Nisi possimus ut delectus. Voluptates maxime voluptatem ut eveniet consequatur assumenda aut. Explicabo velit placeat tempore est fugit. Error repudiandae non aliquid aut.
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...
Ipsa quam molestiae cupiditate dolores quidem. Eligendi facilis et cum molestiae. Nihil id nemo eum qui. Possimus neque non molestiae itaque modi nesciunt. Neque omnis officia ullam ipsa nemo dolorum voluptatem.
Quam minus natus modi distinctio aspernatur aut optio modi. Molestiae mollitia voluptates facilis nulla laudantium voluptas. Nulla sit nihil culpa. Fugit reprehenderit porro odio perferendis possimus occaecati mollitia. Libero sed culpa expedita delectus a possimus magni neque. Sit voluptatem aliquid id inventore voluptas.
Voluptatem quos et ut totam dolor temporibus. Sapiente velit facilis omnis non numquam iste fuga. Consequatur similique et vitae est.
Sunt qui iure dolores consequatur in molestias. Voluptas molestiae excepturi magni maxime et debitis. Nulla sed eum dolor asperiores optio fugiat qui. Pariatur pariatur enim ipsa neque excepturi aut.