Real Estate Investment Case Study Challenge #1: Solution Step 2

Introduction

This post is step two in our solution to the very first real estate investment case study challenge. This case is exemplary of a one-hour, on-site, asset-level real estate private equity interview case study. If you haven’t yet, go back to the original prompt and then the solution step one before reading this post. We strongly recommend you first try building this entire real estate LBO model before following the solution. In fact, we think it’s borderline worthless to read a model walkthrough without actually attempting the model by yourself. Finally, you can download the full solution Excel file here.

Recap of Step One

In solution step one, we translated the prompt into a well-formatted control panel. Our control panel now has every input essential to building this real estate LBO model. Now in step two of this real estate private equity interview case study challenge, we will feed these control panel inputs into our model. As we progress, keep in mind the four judgment criteria for this case study. The four judgment criteria are: (i) Simple Formulas, (ii) Style and Arrangement, (iii) Accuracy and Trust, and (iv) Correctness. You should always strive to satisfy these four criteria whenever you model, even though time constraints may force you to cut some corners.

Step Two

This second step in solving the real estate private equity interview case study challenge will focus on forecasting net operating income. After we forecast net operating income, we will finalize the rest of our real estate LBO model’s monthly levered cash flow projections. Then, we will calculate levered IRR and price our asset to hit an 11.0%.

First, Create your Monthly Timeline

1. Create a new H1 for the monthly timeline
2. Expand the print area to fit an 11-year monthly cash flow timeline. We need 11 years of monthly projections so we can accurately forecast NTM exit NOI in year 10.
3. Drag the title line out with the new print range. All of this fancy formatting will help your real estate private equity interview case study stand out as cleaner and better organized
4. Navigate back up to the control panel using ctrl+UpArrow / ctrl+DownArrow within the elevator columns (the columns with the grey X’s)
5. Add an “investment date” to our “hold period timing” inputs. This informs our timeline, which will ultimately drive our XIRR formula. Dates should always be at the end of any month
6. Load your entry date into the “MONTHLY TIMELINE” row in the column immediately after the furthest control panel content ends. This date is important because it sets our T0 date. Again, the XIRR formula requires actual dates inputs to calculate our levered IRR
7. Immediately to the right of your entry month, build an EOMONTH formula to calculate the subsequent months
8. I like to custom style (ctrl+1, tab, end, tab) my timelines as mmm-yy. Everyone knows that real estate LBO model timelines should always display the end day of any given month, so no need to show the actual day of the month. I also like to center my dates
9. Create two lines immediately below the row where you input your dates. Call them investment year & investment month. Calculate them accordingly
10. Group the rows under your CONTROL panel (shit+alt+RightArrow to group, shift+alt+LeftArrow to ungroup). Hide the rows with alt+a+h, unhide with alt+a+j

Now, Let’s Build our Net Operating Income Projections

1. Create an NOI Build-Up Section. We use this to calculate our NOI, agnostic of the hold period. Later on, we will reference the hold period agnostic NOI with a hold period observant formula
2. I like to make internal sheet references purple. This is a personal preference and is not as common as formatting hardcodes as blue, formulas as black, or external links as green font
3. Underneath my NOI build-up, I like to create a subsection where I load in my driving assumptions. This step is superfluous for such a simple case study with single occupancy and growth assumptions. However, I want to demonstrate how investors prefer to build top class real estate LBO models.
4. Load all relevant assumptions into the Assumptions area and complete your NOI build-up. Remember that capital reserves are included in economic NOI. Disagree? Take it up with Green Street Advisors, the leading real estate research outfit

Building Checks into your Real Estate LBO Model

1. Also create a section for your checks. Build these checks out as you model your NOI. The more checks, the better. Sum the result of your checks to the right of the Title line so it is immediately obvious whenever your checks return errors. If your grand checksum is ever not zero, you know something is terribly messed up
2. I prefer to make my “total checks” a COUNTIFS on anything not zero because a straight SUM formula might miss timing errors that balance to zero. Imagine your check shows +6 in one period and -6 in another period. The sum is zero, even though two periods are wrong. A SUM total check would miss this, but a COUNTIFS not zero check would not miss this error.
3. Review the checks I’ve built in the tab for ‘Step 2.’ You can quickly do this using Macabacus Lite (free forever version) and hitting ctrl+shift+[ on the global check next to the title. Navigate the precedents using the arrow keys. Can you see why these robust checks fully satisfy the judgment criterion #3 for accuracy and trust?
4. Your checks likely won’t be this robust when you’re actually modeling for a one-hour real estate private equity interview case study. You can likely get away with far fewer checks that still ensure the accuracy of your model. However, I’ve included excessive checks here to demonstrate the full correctness of each piece of the model. Each check should look completely obvious when you review it, which is why you should be worried if any check ever fails.

Notes on Our Particular Modeling Conventions

1. Kindly note that we do not occupancy-adjust our other income assumption in this model. We presume the other income figure has pre-baked utilization assumptions for all underlying items. For instance, this assumption might presume 50% utilization of garage spaces, 40% unit turn, etc. It really depends on the person modeling, but most people include utilization assumptions for their other income elsewhere in their financial models. However, an other income buildup would be overkill for this real estate private equity interview case study
2. I prefer to model expenses with negative signs. This is a point of contention with some colleagues, but I prefer my models to always show outflows as negative and inflows as positive
3. Typically, you should never show two figures with dollar signs after each other. This is a style convention that all funds use in their real estate LBO models. However, the only place I break this convention is in areas with multiple denominations stacked upon each other, such as the Assumptions section in this model

Moving Forward to Step Three

We have finished solution step two with our full NOI build-up. The third step in solving this real estate private equity interview case study will focus on finalizing all monthly levered cash flows so we can calculate our levered IRR. Once we have our levered IRR, we can solve our purchase price to the value that hits our target return of 11.0%.

Leveraged Breakdowns Delivers One-on-One Mentorship at Your Convenience

Acing your next real estate private equity interview case study requires serious preparation. We strive to bring high quality mentorship previously available only to real estate private equity insiders. Your Leveraged Breakdowns membership supports our continued development of educational materials that bridge the insider-outsider educational gap. Also, as a Leveraged Breakdowns member, you get access to hours upon hours of exclusive video content that recreates the real estate investment process with extremely realistic source materials.