I Owe You One

I'm not going to sugarcoat it... modeling the debt schedule is intimidating.

In fact, even as a financial modeling instructor, I often have to brace myself before building this schedule.

Mentally, I'm getting ready for multiple tranches, back-and-forth links, and even some circularity.

Nonetheless, mastering the debt schedule is essential if you're working in Private Equity or FP&A:

• In Private Equity: the debt schedule is the cornerstone of your LBO Model and helps predict returns.
• In FP&A: the debt schedule helps predict cash flow based on changes in business performance (aka scenarios).

Let's dive in today...

Why Companies Raise Debt

Zooming out first...

Companies raise debt because it provides access to cash (and often times, a lot of cash) without diluting shareholders.

(Every time you raise equity, like in a "Series A," the existing shareholder group usually experiences some level of dilution).

This cash allows the company to:

1. Manage its working capital (e.g., funding a shortfall in day-to-day cash management).
2. Invest in growth (e.g., new equipment or perhaps an acquisition).
3. Distribute a dividend (typically in Private Equity, called a "dividend recap" (topic for another day)).

However, debt also comes at cost, which is three-fold:

1. Interest expense: Debt capital isn't free. You have to pay interest on the amount borrowed.
2. Liquidation preference: In a sale (or downside scenario), the debtholders are paid back first, before equity.
3. Compliance: If the company can't meet its covenant obligations, the bank can demand repayment, restrict further borrowing, or foreclose on assets pledged as collateral.

Given the stakes at play, this delicate balance makes modeling debt accurately a critical skill.

Types of Debt

There are a ton of different types of debt out there, but here's what I typically see in most models (especially middle-market):

• Revolver: Basically a giant credit card that allows companies to borrow and repay as needed for short-term liquidity.
• Senior Debt: Typically the lowest-cost debt, secured by company assets and paid first in a liquidation scenario (note, the Revolver and Senior Debt often come as a package deal from a single bank).
• Mezzanine Debt: Higher risk and return, often blending debt with equity warrants, and subordinated to senior debt. Sometimes called a "stretch piece" because it's just "a little extra debt on top of the senior," mezz typically has "paid-in-kind" or "PIK" interest that increases the principal balance over time, and is often provided by a different bank.
• Seller Notes: Debt financing provided by the seller in an acquisition, often with more flexible terms. Effectively, instead of receiving all their cash upfront, the Seller agrees to get paid over time (say 5 years) plus interest. This is a way for the Seller to increase their overall proceeds, but it comes with time and performance risk (I'll typically see this in smaller acquisitions like a dental office).

Debt Covenants

Like I mentioned above, "covenants" are like a "financial health check" that allows the bank to keep track of company performance.

These are contractual conditions borrowers must meet. The ones I see most often are:

• Fixed Charge Coverage Ratio: Ensures the company can cover fixed obligations like interest, principal, and lease costs.
• Leverage Ratio: Limits the amount of debt a company can raise relative to its EBITDA (i.e., Total Debt cannot exceed 4.0x EBITDA in any given quarter).

Although debt covenants are typically reported quarterly (via a "compliance certificate"), I like to run the calculation in my model every single month, because it helps me stay ahead of any potential issues:

Financial Modeling: The Core Components of a Debt Schedule

In your model, a debt schedule is most-effective when it contains a "cash flow sweep."

In other words, to the extent there is excess cash in the business (based on a minimum you specify), the excess cash goes to pay down the outstanding debt in order of priority.

If there's not enough cash to meet the minimum, then the company "draws" (aka borrows) from the Revolver (to satisfy the minimum).

Here’s the structure I use:

• Cash Flow Sweep
• Revolver Schedule (Priority 1)
• Senior Schedule (Priority 2)
• Mezz Schedule (Priority 3)
• Other Debt (Priority 4)

This cash flow sweep enables me to see how quickly the company can pay down its debt obligations over time based on changes in company performance, while also stress-testing if it can meet the covenant obligations.

This is the core principal of building an LBO Model, because the amount of debt (or lack thereof) affects the "Returns Schedule," which tells the private equity firm how well the equity holders will perform on the deal.

In FP&A, while you might not be flowing directly into a returns schedule, it's still helpful to know how quickly the company can pay down debt because (1) it promotes overall stability and (2) increases shareholder value.

Managing Circularity in Debt Schedules

In advanced LBO Models, interest expense is often calculated by using the average of the beginning and ending debt balances. Using the average creates a circular reference because:

• Interest expense determines the amount of cash available, which...
• ...determines how much debt can be paid down, which...
• ...changes the debt balance, which...
• ...changes the interest expense
• 🔄️ So it creates a circle.

However, Excel is smart enough to handle circularity, so there are a few things you can do:

• Use a Circularity Breaker: With iterative calculations turned on, you create an ON/OFF "breaker" (usually a named range like "Circ_Break") in Excel to temporarily set the interest expense to zero. It works like this:
  • =If ( Circ_Break="ON", make interest expense $0, else run the calculation )
  • This temporarily "shorts" your interest expense to $0, and will help you get rid of those nasty #NAME or #DIV/0 errors
• Simplify with Beginning Balances only: Alternatively, calculate interest expense on the beginningdebt balance only (not the average). While technically less accurate, this avoids circularity and improves model stability.
  • I generally prefer this approach because I believe the incremental accuracy gained is not worth the added model complexity and risk, however, it's a "must know" if you're working in private equity

While circularity is generally a "no no" in modeling, both approaches have their place depending on your audience and the complexity of the model (and I teach both methods, because if you know how to handle circularity, it's much less scary).

Integrating the Debt Schedule

Let's not forget, this massive schedule still has to connect back to our three statement model (like I said at the beginning, this schedule takes a lot of work 😅)

Thankfully, this is the "easy part."

Here's how you link it back to the rest of the model:

• Interest Expense: all of it goes to the Income Statement (which affects the Operating Cash Flow in the Statement of Cash Flows)
• Ending Balances: all link to the Balance Sheet (which affects the Financing Cash Flow in the Statement of Cash Flows)

Debt Schedule: The Punchline

When managed properly, debt can fund company growth and acquisitions without requiring incremental equity capital or diluting existing equity holders.

It's the same reason you can buy a $1,000,000 house with a down payment of only $100,000... debt provides access to things that otherwise might not be affordable.

But, it comes at a cost.

When tracked and managed properly, the debt can be paid down over time and the net result is a win-win:

• Borrower increases the value of their asset
• Lender makes a return on their investment

However, if too much debt is raised (or performance changes materially), it can crush you.

So, make sure your financial model is carefully managing your debt balances. It makes all the difference.

That's it for today. See you next time.
—Chris

p.s., if you enjoyed this post, then please consider checking out my Financial Modeling Courses.  As featured by Wharton Online, Wall Street Prep, and LinkedIn Learning, you'll learn to build the exact models I use with Investment Banks, FP&A Teams, and Private Equity Firms 👉 Click here to learn more.