Introduction
This is part three in a real estate private equity case study series in which I take you through my underwriting process. In part one, we decided to analyze a $350M 4.0% 2029 notes issuance by Life Storage Inc. (NYSE: LSI), a self storage REIT. In part two, we went on a hunt for free pricing bond pricing data, then covered some key terms. In this post, we are going to spin up our Excel model. By the way, if you’re looking for a live video real estate private equity guide to see how i model in real-time, perhaps check out the REPE Starter Kit.
Input the Basics
Let’s begin by entering the basics into an Excel spreadsheet. This is how you should begin any real estate private equity equity case study. What do we know about this bond? Right now, we have what’s in the LSI 10-K and what’s on the FINRA market data page (note: I pulled this info on 5/5/20). Step one is to punch all the data we have so far into an Excel. I’ve done that, and it looks like this:
How I Chose What Went Into This Control Panel
Some beginners might look at the control panel above and feel overwhelmed, like they have to memorize this as a template. If that isn’t you, good, you can skip this bit. But if you look at the control panel I built and think “how the heck did he come up with all those inputs? ” then keep reading. Whenever I’m about to model an investment opportunity, I only care about two things:
- When and how does my cash go out?
- When and how does my cash come back in?
Those two questions reflect the sum total of any REPE investment model. All other outputs we build, such as capital stacks, sources and uses, debt summaries, and supply-demand analyses, exist for one purpose: to explain why and how cash is moving in and out. Cash flow is your true north when modeling investments for REPE. I hardly ever know how a model will look when I get started, I just grab any data that might impact how my cash will go out or come back in then work from there.
Time to Build the Model
Final step for this post, let’s actually build the monthly cash flow model. For our first question, how will cash go out, let’s assume we buy the entire issuance at its last trade price of $103.70. We will make our assumptions more realistic later in this series, but let’s first lay a solid foundation. At a par value of $100, the $103.70 pricing implies the entire $350M issuance is worth $362.950M. So our first cash flow will be an outflow of that amount. Right now, we will have no other cash outflows, so let’s move onto our second question.
When and how will cash come back in? We will receive semi-annual coupon payments on June 15th and December 15th of each year, per page 8 of their most recent 10-K. At maturity on June 15, 2029, we will receive the full principal at $100 par (aka $350M). That’s it, nothing else. How does this look in Excel? Take a look at the annual summary of my monthly model:
Recap of the Cash Flows and Next Steps
Everything above was built with logic and formulas that link to the control panel, and should be self-explanatory. If not, I’m happy to answer any questions in the forum. Summarizing yet again, we have three cash flow forecasts:
- We buy the bonds
- We receive semiannual interest (this annual view shows as a lump sum, but the monthly backend flows it in on the proper dates)
- We receive our full principal at maturity.
This solves to a paltry 3.55% IRR, which should definitely catch your eye. As you know from Breaking Down REPE and our real estate private equity guide for technical interviews, opportunistic funds target 18% returns. REPE investors don’t target 3.55% IRRs. So, in the next post, I’ll highlight some mental math tricks that megafund investors would use before they even got to building this model. These tricks allow us to quickly screen opportunities before we commit resources to building models. After that, we’ll then look to figure out what market price would make this opportunity an attractive investment.