Leveraged Breakdowns

Live Case Study #1, Part Five: Finish Our Model


This series follows a live case study in which we underwrite the potential acquisition of a $350M 4.0% 2029 notes issuance by Life Storage Inc. (NYSE: LSI), a self storage REIT. If you haven’t yet, I suggest you begin at part one. This post picks up where part four left off: how to figure out the price at which this investment would be actionable. Pay close attention, because the concepts we work through here are often tested in real estate private equity interview questions.

Refresh our Model

You should make sure to refresh your models periodically. If working on a live transaction, do so daily, otherwise every few days is fine. So let’s go ahead and refresh our model before diving into the rest of this case study. Whether you’re a junior analyst or deep into your REPE career, it’s always a good habit to ensure your data is fresh.

So first, let’s go back to the real time pricing page we found in part two and grab the latest information. The latest we had was $103.70 as of 5/5/20. Today is 5/17/20, and the price is now $102.77, so no major change in value. But let’s go ahead and update the pricing section of our control panel for this new information, as shown below:

LSI Bond Math

Add the Haircut

Next, we need to figure out what price would make these bonds actionable. All that means is we’re going to build in haircut functionality on top of the current price. So, basically, we’re figuring out what percentage reduction in price would result in a 20% IRR, since we’re investing out of an opportunistic fund. We can either goal-seek the answer, or guess then type the number in and see if we’re right. I like guessing and typing the answer in myself because it helps build my sixth sense for guesstimating returns.

Model the Haircut

First, I’m going to rename our “pricing” section to “current pricing.” Then, underneath it, I’m going to add a “haircut” section to drive my reduction against market pricing. Note, I will also move the “value of outstanding principal” to the haircut section and drive it off the post-haircut figure so our cash flows update accordingly. Now all we need to do is guess the haircut.

Guess the Haircut

In this model, we will derive value from two events: (i) receipt of coupons, and (ii) receipt of principal. Our IRR will represent the sum of these two events.

As discussed in part four current yield helps us guess (i) the return from the receipt of coupons. In other words, at a 4.00% fixed rate, a $20 price would imply a 20% current yield. So our absolute floor would be an 80.5% haircut from $102.77 to $20. But we’re also going to get a return from (ii) receipt of principal at the end. In fact, if we were to pay $20 today and get $100 back in nine years, that alone would give us a ~20% IRR. Let’s say we only need a ~10% current yield, which would mean a price of $40 (or a 60% haircut). The resulting value accretion would be about a 10% IRR as well ($40 to $100 in 9 years). If we plug that in, we get to a 17.72% IRR.

OK, so we were close, but the primary miss is that current yield does not factor in the diminishing time-value of coupons as cash flows extend further into the future. But we were close, and this quick math would certainly work in an interview setting. If we tweak the haircut to solve for a 20.0% IRR, we arrive at a 65.0% haircut. See below for the final result of the model, both the control panel and the annual cash flows (built off a monthly cash flow backup, not shown).

Updated Control Panel

Updated Control Panel

Updated Annual Cash Flows

Updated Annual Cash Flows

Next Steps

It does not take an expert with an REPE career to understand this is an unlikely investment for an opportunistic fund. Further, no bondholder would accept a 65% haircut to pricing. If these notes were actually distressed enough to warrant a 65% haircut, you probably would not expect to receive full par value at exit. Regardless, this lesson has prepared you for real estate private equity interview questions covering quick mental math and designing readable control panels and cash flow outputs. To close us out, the next section is going to cover the logic behind my fancy formatting.

Member Exclusive: Download Complete Model

Leave a Comment

Scroll to Top