Introduction
This is the third and final step in solving the inaugural real estate investment case study challenge. This solution teaches you how to build a typical real estate private equity model used in one-hour on-site case studies. This is a basic model that you should be able to build from scratch without referencing anything but memory. Do not just follow along with this solution, actually try to build everything from scratch even if it’s extremely difficult the first few times. You may download the full solution here.
Recap of Step Two
In solution step two, we built net operating income into our real estate private equity model. We also addressed the most important concern of the real estate investment case study. Can you guess what that is? If you’re guessing the check section, you’re correct. A model without robust checks is just a bunch of random numbers to the outside reviewer. Always save your superiors time and energy by building their checks for them. Save them even more time by summing every check together into the very top of your model!
Step Three
Now, we are going to build our hold period observant monthly cash flow projection model. We will create an entirely new section for our monthly cash flow model. We will put in our purchase control, calculate our gross asset value, and build our debt projections. We will combine this information to calculate the levered IRR using the XIRR formula.
Create the New Monthly Cash Flow Section
- Create a hold-period observant cash flow section. We’ll need to create some additional timeline formulas to adhere to our hold period assumptions
- I like to use binary flags for my timing considerations. I do this to stick to the classic “don’t repeat yourself” paradigm of modeling. Instead of building an IF statement every time I need to turn something on or off during the hold period, it’s better to just multiply by a one or a zero to include or exclude a particular cash flow item
- Now, let’s build our unlevered cash flow items.
Bring in our Unlevered Cash Flow Items
- First, create a new control panel section for purchase price. Enter any dummy figure you like, purchase price is the last item we actually solve for. Note, I’ve included some additional informative metrics in my new purchase price section
- Once you’ve set up your purchase price control, link to it in your monthly cash flows. Make sure your purchase price in the monthly cash flows is a negative outflow
- Go ahead and link up your hold-period NOI below the purchase price. We’ve already calculated this
- Finally, we need to calculate our GAV to forecast the reversion value. Do so in a new GAV build-up section. Remember to include your cap rate inflation and to cap NTM NOI
- Sum all of these items together to calculate your unlevered free cash flow
- Let’s take this unlevered free cash flow and calculate our unlevered IRR and unlevered multiple on cash. Keep in mind these numbers will be entirely meaningless until we have finished our levered cash flow projections and price the asset to an 11.0% levered return
Finish our Cash Flow Projections with all Levered Cash Flow Items
- Now, we need to build our levered cash flow projections. Create a new section for your monthly debt backup. First, we will create the outline for our debt schedule
- A debt schedule exists to calculate your beginning and ending balance of debt in each period. Thus, the only lines in our debt schedule are items that increase or decrease our debt balance. For this real estate private equity interview case study, those cash flow items are issuance, amortization, and paydown
- I prefer to keep my primary debt schedule clean, feeding it with additional backup calculations from elsewhere as necessary
- Obviously, our T0 debt balance is zero because we haven’t issued debt yet. To calculate our initial issuance, go back up to the control section and create a calculation to imply your debt issuance given your purchase price and LTV. Make sure to format this figure in black since it is a straight formula and not a hardcoded input in itself
- Notice how the next period’s beginning balance matches the prior period’s ending balance. This type of schedule is called a corkscrew model and is commonly found in real estate LBO model elements such as debt build-ups, waterfall calculations, and cash flow sweeps
- To calculate our Amortization and Interest, we need to first pull the 1MO LIBOR curve. Chatham Financial lets you download the latest LIBOR futures. Bring the relevant sheet into your model and link to it in your control section. You’ll notice I’ve built out the leverage section a bit to accommodate the different pieces of functionality we’re now relying on
- If you’ve ever modeled fixed-rate debt, you can simply use the PPMT function to model the paydown in each period. With floating-rate debt, it gets a bit tricky and can depend on the structure of the individual loan
- Working around the complexities of floating-rate amortization, we’ve simply assumed a fixed amortization payment based off the T0 all-in interest rate. We calculate this fixed payment using the PPMT function
- Kindly note, more intense debt modelers might create their own separate tab for their debt backup. They might model a floating amort based on the LIBOR curve. We are not going to dive into that level of granularity for this one hour real estate private equity interview case study
- To calculate interest, index-match the LIBOR forecast for each month. Notice how my LIBOR tab includes an EOMONTH formula on the Chatham dates to align the dates of our model with Chatham’s source data. This is standard practice
- Note, I calculate monthly interest on the beginning balance. This implies you pay your amortization at the end of the month. Bankers might be used to running mid-period interest, which is a more aggressive stance that lowers interest expense in periods with amortization
- Load all of the relevant debt backup into a new monthly cash flow section for your levered cash flows. Sum everything up, and repeat your formulas to now calculate your LIRR and LMOC
- Create a new section in your control panel that links to your returns metrics. Price your asset until you hit an 11.0% levered IRR. Now you’ve solved the case!
Want to watch us build this in realtime? Support Leveraged Breakdowns with a membership!
Your Leveraged Breakdowns membership supports the continued development of real estate private equity educational materials for outsiders by insiders. We want to teach you the ins and outs of the industry so you can perform at your best during any real estate private equity interview. Further, membership grants access to hours upon hours of exclusive video walkthroughs. Our flagship course, breaking down real estate private equity, teaches you how to build a full-scale real estate private equity model from scratch with extremely realistic source materials.
Final Observations after Completing this Solution
Do I have to build such a clean model for an on-site case study?
At the end of all this, you might be wondering how you can process this much information in an hour. First, you can cut a few corners on each of the four judgment criteria. Your case doesn’t need to be this crisp. Of course, your first priority is to finish the real estate investment case study within a strict time frame. However, it won’t be a very good real estate private equity model if you aren’t at least aware of these judgment criteria while solving your case study.
Why we like to keep it simple
Notice how our monthly cash flow is only a few lines. But look down at all of the logic beneath the monthly cash flows. Imagine if all of this logic was rolled up into these few lines. Your real estate investment case study would take a long time to review! Also, imagine there are no checks. How could anyone quickly tell your real estate private equity model is correct without an exhaustive full audit? As an analyst, you never want to put your associate or vice president in that position. If you make your models easy to audit and review, your colleagues will appreciate your work product tremendously. The same goes for your interviewers!
Play with your Control Panel
Take a moment to look at the control section of your real estate investment case study. Everything in blue is a control you can tweak. You can easily tweak any assumption that drives your real estate private equity model.
This is what a model is! Your model should never reflect just one static circumstance. Instead, you should be able to modify each important variable in realtime. If you’re ever asked technical questions, they’re essentially checking whether you have a high-level grasp on how tweaking any of these inputs might impact an investment. On that note, below are some real estate private equity interview questions to test your understanding.
Challenge yourself with these real estate interview questions, use the model as an answer key
Challenge your understanding of real estate private equity investing with these questions. Grab the relevant variables from your model, but see if you can’t answer these questions on pen and paper.
- What is the optimal hold period for this investment?
- How might a shortened I/O period impact your pricing?
- What is your next buyer’s NTM cash-on-cash yield assuming the same growth projections and 50% LTV at a 5.0% fixed cost of debt without amortization?
- What IRR is your next buyer likely to achieve? This one isn’t a pen-and-paper question and will require Excel.