In the prior post, we built the cash flow waterfalls for this preferred note case study. If you’re just coming to this article, I suggest you begin at part one (this is part four). This post will focus on the preferred equity and common equity schedules. These schedules drive almost all of the waterfall logic we built in the past post. If you wish to look under the hood, check out the full model download link at the bottom of this post. Pay close attention, because equity schedules will make frequent appearances during your REPE career. They don’t call it private equity for nothing!
Preferred Equity Schedule
The preferred equity schedule is really quite simple. We can break it out into the following steps:
- Balance, Begin – at the dawn of this investment, the balance is zero. This is the absolute start.
- Plus: Initial Contribution – The first real cash flow to occur is when your preferred investor commits $25M of capital into the JVCo
- Plus: Dividend Accrual – The PIK, or payment-in-kind, preferred coupon means any unpaid interest will increase the balance of the preferred equity. You could do this a number of ways, but the cleanest is to bifurcate the accrual of the PIK and the subsequent payment thereof. This first line shows the accrual of the PIK interest into the preferred equity balance. Make sure you get this, manipulating PIK interest is among the most common real estate private equity interview questions.
- Less: Receipt of Dividend – Now, you have to pay the PIK. Remember, the preferred waterfall schedule we built in part three shows what cash is available to pay the PIK in each period within row
- Less: Receipt of Face Value during Hold – Remember from part three that any excess cash flow after the common and preferred coupon will evenly pay down the face value of both
- Less: Receipt of Face Value at Exit – Also from part three, the first use of exit proceeds is to fully pay off any remaining balance of the preferred equity.
Common Equity Schedule
The common equity schedule is similar to the preferred equity schedule. The only difference is that the common coupon is subordinate to the preferred coupon, and the common face value repayment (sixth item) is subordinated to the return of the preferred face value. Also, the common is due a 6% PIK dividend versus the 8% due to the preferred. Everything else here works the same as the above preferred equity schedule.
Bringing it All Together
These schedules are great because they give room for extra logic. We don’t have to jam all our logic together into a single row. It is nice to have a multi-tiered equity schedule so we can best convey our logic to the next user of this model. But we have one step left, and it’s to build our cash flows to preferred and common. This is the easiest step, because we just link to parts that we have already built. Below are the cash flows for both the JV followed by the cash flows for the preferred equity and the common equity.
But regarding the cash flows, doesn’t seem like a lot, does it? But imagine building all the logic required for these cash flows all in one row in Excel. What a nightmare!
A lot of rookies try to do this, and it’s the worst mistake. Break apart your logic. Build schedules. Have backup sections. You will always thank yourself if you build extra backup versus jamming stuff into one row. Alright, that’s enough Excel for one post — see if you can replicate this all on your own within 60 minutes (probably a few hours for your first try), then I’ll see you in the next installment!
Side note: if you’re curious about my Excel formatting decisions, please check out this earlier post.
A Quick Interview Question
Modeling is integral to acing your case studies and thriving during your REPE career. But to land the job, you also need to prepare for a laundry list of potential technical questions. Fortunately, Leveraged Breakdowns has compiled that laundry list and provided clear answers to the most common real estate private equity interview questions. Check out the technical guide right here!