Watch Me Solve a REPE Technical Interview Modeling Test (Updated Nov 2021)
What better way to spend some free time over the Thanksgiving holiday, than to record myself completing a real estate private equity technical interview exercise! While I’ve offered help with the real estate technical interview in the past, I’ve never just grabbed an actual technical interview case study and walked our readers through how I would complete it. So, in this video, I spend an hour and 15 minutes doing just that. Follow along by downloading the companion Excel file.
Note: The file for download has been updated to v1.3. The latest version includes several small error fixes and a third worksheet that includes an optional Operating Shortfall reserve. See version notes at the end of this post.
Are you an Accelerator member? See capstone courses 16, 17, and 18 of the core curriculum for detailed instruction on how to build models similar to that built in this Watch Me Build. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
If you’re reading this, you’ll probably also find value in our ‘How to Prepare for a Real Estate Technical Interview‘ post.
Source of the Technical Interview Case Study (Modeling Exercise)
So, I was lurking on WSO earlier this week, and I came across a thread from someone requesting help to prepare for a real estate technical interview. Well, about four comments into the thread, a contributor (Yakehito) shared a ‘Modeling Exercise’ he’d been given as part of a top tier REPE company’s interview process. According to Yakehito, he/she had been provided with nothing more than a blank Excel file, a two-hour window, and a list of assumptions. You can find them here (as well as re-posted below).
So reading through the assumptions, I thought “our readers would probably find it useful if I were to do this exercise and record myself while I do it.” So that is exactly what I’ve done. Below, you’ll find the assumptions from the aforementioned thread, together with a video I recorded (warning: it’s long and boring!) and the finished product for you to download and follow along as you watch.
And while I’m on the subject, if you haven’t already you should really check out WSO’s Real Estate Forum. It’s a great place to engage with other CRE professionals.
Modeling Exercise Assumptions
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 (I’m assuming monthly)
- 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%
- All cash flows are distributed 95/5 until the LP has achieved a 12% IRR
- Any excess cash flow thereafter is distributed 20% to the GP and 80% 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)
What This Is and Is Not
The finished model that comes out of this exercise is not meant to be used for actual deals and likely contains errors – I knocked it out in less than two hours. And while I performed error checks along the way, I have not thoroughly audited it for errors.
With that said, for those preparing for a real estate technical interview, I think watching and listening to how I would approach this exercise will be really helpful for you as you craft your own strategy and develop your own techniques. I’ve said this before, but I learn the most when I’m looking over someone else’s shoulder watching them model, and then implementing what I learned from them into my own modeling. So hopefully, you’ll find educational value in this exercise.
Video – Watch Me Tackle a Real Estate Modeling Exercise in Excel
Follow Along using the Excel File from the Video
To get the most out of this video, I highly recommend you download the Excel file I use in the video. In that Excel file, you’ll find four tabs.
- Versions. The versions tab alerts you to any changes that have been made to the file since I first recorded this tutorial
- Modeling Exercise Complete. This tab contains the complete file from the tutorial. I’ve only made minor corrections since recording the video. See Version Notes below.
- Modeling Exercise Template. Use this blank worksheet to follow along.
- Alt_With_Operating_Shortfall. Since recording this tutorial, a member of our Accelerator program asked about adding an Operating Shortfall reserve to the development budget. This fourth worksheet includes that addition (red font cells have been changed from the original).
To make this ‘Watch Me Build’ file accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – similar real estate course modules sell for $100 – $300+). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Quick Note for Accelerator Members – Adding an Operating Shortfall Reserve
Are you an Accelerator member? Read further. If not, consider joining the Accelerator.
In the Accelerator forum, one of our members recently asked how to add an operating shortfall reserve to this model, such that the loan would pick up any operating shortfall during lease-up. In response to that question, I recorded a short video showing her how to add an operating shortfall reserve to this model.
If you’re an Accelerator Advanced member, you can find this video exercise as part of the ‘Additional Case Studies and Technical Interview Exams’ course in the ‘Career Advancement in Real Estate Endorsement.’
Now it’s important to point out that I purposely didn’t include operating shortfall in the development budget here. That is because modeling an operating shortfall reserve here would have added a level of complexity that the case didn’t expressly call for. And I did my best to keep this model as simple as possible.
But as a result of not including operating shortfall in the development budget, cell C26 (i.e. Equity as part of Total Development Sources) is less than cell D84 (i.e. total equity contributions over the hold period). This is intentional on my part – not an error – but it’s atypical for a merchant-build development model to not include operating shortfall in the development budget.
Version Notes
v1.3
- Revised formula in row 29 to calculate Debt Service based on current loan balance (row 29), rather than max loan balance (cell C25)
v1.2
- Removed unnecessary external links
- Updated Version tab
v1.1
- Fixed issue where Total Contributions (cell B84) was labeled “Total Distributions”
- Fixed issue where Error Check formula was inadvertently written with ROUNDUP() instead of ROUND() function (cell C117)
- Added a tab with optional Operating Shortfall included in Development Budget (see Alt_With_Operating_Shortfall tab)
- Added Versions tab
v1.0
- Initial release of this tutorial
