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.

Real Estate Modeling Course

  • Real-life RE Modeling Tests from actual Interviews
  • Various asset classes including multi-family, commercial and more
  • Huge discount - until more tests and cases added

Comments (27)

Jan 5, 2017 - 10:11pm

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.

Jan 6, 2017 - 12:50am

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!

Best Response
Jan 6, 2017 - 5:06pm

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)

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Jan 6, 2017 - 8:31pm
Yakehito:
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.

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

  • 5
Nov 21, 2017 - 9:51am

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?

  • 2
Apr 14, 2020 - 2:13pm

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!

Mar 14, 2021 - 8:11pm

Yakehito

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)

* 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????

Jan 8, 2017 - 8:59am

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.

Mar 31, 2020 - 6:10pm

Iste incidunt consectetur unde odio amet similique. Quo nobis voluptates quia aut. Tenetur sint rerum sint assumenda quisquam quaerat et ut. Aut in aut veritatis deserunt voluptates labore praesentium. Provident repudiandae aperiam hic ratione repellat mollitia officia. Non in dolores voluptatem officiis qui dicta ea nihil.

Architecto non ea id qui non ab nobis quod. Officiis velit eligendi quos dicta magnam. Debitis deserunt itaque vel quas eligendi sapiente voluptas saepe. Eum unde non repellendus laborum esse. Possimus voluptates ab architecto excepturi nobis. Dolorum itaque nesciunt cumque sequi cupiditate ex a asperiores.

Et nulla sit qui numquam doloribus velit. Sunt minus et voluptatem et quisquam blanditiis. Est minus et blanditiis maxime natus ipsam.

Mar 22, 2021 - 3:27am

Sed dolores aperiam quis magni aut. Ullam laudantium enim illum natus recusandae qui. Ut dicta et repellat et quas. Sunt eos veritatis similique veritatis. Ut commodi veritatis sint ea iure officiis sit. Ratione rem et similique nemo. Consectetur eum vel ut fugit deserunt molestiae eos.

Voluptas voluptatem et maxime ullam et. Perspiciatis est velit maiores ut vitae. Excepturi temporibus voluptas est cumque animi alias ea. Quia qui sint mollitia inventore ratione sapiente. A rerum nostrum quae illum possimus tempore. Velit sunt doloribus itaque velit voluptas quia ab consequatur.

Fugiat et quo nulla sit. Voluptas non odit id qui aut est provident.

Start Discussion

Total Avg Compensation

September 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (39) $363
  • Associates (220) $232
  • 2nd Year Analyst (134) $154
  • 3rd+ Year Analyst (30) $147
  • Intern/Summer Associate (103) $143
  • 1st Year Analyst (485) $135
  • Intern/Summer Analyst (377) $82