Where Do You Find the Debt Schedule for a Company

Debt Schedule Definition: In financial models, a Debt Agenda uses a company's cash flow projections to judge how much Debt head teacher the company can repay over time you bet the interest disbursal changes As a result.

A Debt Schedule could appear in various fiscal models: 3-statement models that project an individualistic company's business enterprise performance, merger models, and leveraged buyout (LBO) models.

However, a Debt Schedule is well-nig useful when the company has significant Debt across umteen "tranches," or types of Debt, such arsenic Term Loans, Last Unsecured Notes, and Subordinated Notes.

If a company has only 1-2 tranches of Debt surgery a minimal Debt balance relative to its Market Cap and EBITDA, a engorged Debt Schedule is seldom worthwhile – you tail use much simpler methods to project its interest expense in this guinea pig.

The Debt Schedule in an LBO Model

To illustrate these concepts, we'll use an LBO model based happening Bain Superior's $1 billion leveraged buyout of NichiiGakkan, a breast feeding-home operator in Japan.

Debt Schedules are the most useful in LBO models because in all leveraged buyouts, the acquired company must take on significant Debt.

By contrast, a troupe does not necessarily put up significant Debt in a stock M&ere;A deal or as a standalone entity.

The steps to set dormy a Debt Schedule are as follows:

Footstep 1: Calculate the Mandatory Repayments on each tranche of Debt, alias the "Amortisation" of the Debt principal.

Maltreat 2: In each period, calculate the Cash Flow Available for Debt Repayment (CFADR), also known every bit the Cash Flow Superfluous or Shortfall.

Step 3: If essential, let the company draw on its Revolver if it does not have sufficient cash flow to make its Mandatory Repayments while maintaining the Minimum Cash required to go its business.

Step 4: If the company has extra cash flow, i.e., it can well make its Mandatory Repayments patc maintaining its Minimum Cash, you may assume Optional Repayments, alias the "Cash Flow Sweep," certainly tranches of Debt.

Footmark 5: Once you have the Mandatory and Optional Repayments, you can jut out the Interest Disbursal along to each one tranche of Debt and the Interest group Income on Cash.

Step 6: Finally, you can link the Debt Agenda to the financial statements. The Worry Expense and Stake Income appear connected the company's Income Instruction, and the Debt Repayments and Additional Borrowings turn up on its Cash in on Flow Statement.

The Debt Schedule tells you how well or poorly the company can service its Debt, especially in different operational scenarios.

For instance, if in that respect's a downswing Beaver State recession, would the company shut up generate enough cash flux to pay for its Interest Expense and the Mandatory Repayments?

You pore on the pessimistic or worst-case outcomes in recognition analysis because optimistic outcomes do not benefit the lenders.

Their returns are limited to the interest rates on the Debt, and so it's irrelevant if a company outperforms its financial projections away 50%.

But if the company underperforms by 50%, lenders will enter "crisis mode."

Debt Schedule, Stone's throw 1 – Mandatory Repayments

The initial capital social system in this leveraged buyout looks like this:

The Revolver is undelineated, and only the Term Loans have Mandate Repayments and Elective Repayments (the "Cash Flow Sweep" shown below refers to the Optional Repayments):

The "spreads" above name to the interest rates on top of the 10-year Japanese government bond knuckle under.

If the 10-year relent is 1.0%, and the spread head is 3.5%, so the rate of interest in the class is 4.5%.

The "floors" nasty that if the 10-yr issue is below a certain number, much as 1.0%, it is bumped adequate 1.0% to calculate the interest rate.

Based along this information, we can set astir the Amortization for the Term Loans:

The formula is:

= –MIN(I163, $H$166 * $H$46)

H46 is the 10% annual principal repayment, and H166 is the starting Terminus Lend balance.

We need to check this amount against the Start Balance in This Period ("BoP Term Loans") in cellular telephone I163 because the company might rich person only a small balance left.

For example, if the fixed one-year quittance is $10, but the remaining Debt balance is $2, the party should repay $2 rather than $10.

Mandatory Repayments do not exist for the Subordinated Notes or Mezzanine, so we ignore them for those tranches.

Debt Schedule, Step 2 – Immediate payment Flow Available for Debt Quittance (CFADR)

Next, we can calculate the Cash Feed Available for Debt Repayment.

If this number is negative, the company volition need to take over extra aside "drawing" on the Revolver to meet its Minimal Cash in balance.

If this number is positive, the company may repay some of its Revolving door and Term Loan balance optionally.

The usual formula for this item goes like this:

= Beginning Cash + Free Cash Flow – Mandatory Debt Repayments – Borderline Cash

You can see the setup in this Debt Schedule below:

The "Amortization" line represents the company's needed principal repayments along the Term Loans.

If something boosts the cash flow available, such as the company's Beginning Cash or its Free Hard currency Menstruation, we add it, and if something reduces the cash flow available, we take off it.

Debt Schedule, Stair 3 – Revolver Draws and Repayments

The basic logic here is that if the accompany has a Cash Flow Shortfall, i.e., a Gram-negative CFADR, it should draw on the Revolver.

And if the troupe has a Cash Flow Surplus, i.e., a positive CFADR, the company should use information technology to repay any Revolver balance:

The Stand out rul is:

=IF(I155>0, –MIN(I155, I157), –I155)

We indigence the MIN function to handle the casing where the Cash in Flow Surplus exceeds the Outset Revolver.

For example, if the Start Revolver is $100, and the company's Cash Stream Surplus is $150, information technology does not puddle sense to repay $150 of the Six-shooter.

Instead, we repay only the unexpended $100, which is the minimum between $100 and $150.

Debt Schedule, Step 4 – Optional Repayments

Next, we build a formula that allows for Ex gratia Repayments of the Term Loans when there's a Hard cash Flow Surplus and some cash remains after repaying the Revolver.

The instructions in this case study state that 50% of the company's "Excess John Cash Flow" can glucinium used for this purpose.

We set up the undermentioned Excel formula to handle this encase:

In school tex, the formula is:

=IF(I155 + I158 > 0, –MIN((I155 + I158) * $I$46, SUM(I163:I164)), 0)

The number 1 part, =IF(I155 + I158 > 0, checks to see if the Johnny Cash Flowing Surplus minus the Revolver Repayments exceeds 0.

If it does not, we cannot give back anything optionally happening the Term Loans, so we countersink the Nonobligatory Repayments to 0.

If it does, then we have to compare the cash flow available * 50% to the remaining Term Loan balance after the Mandatory Repayments and repay whichever one is smaller.

We could simplify the IF check to begin with by only checking to see if the Cash in Menstruum Surplus is advantageous, arsenic the MIN(I155 + I158) part handles the comparison of Cash Flow Surplus minus the Revolver Repayments to the remaining Term Loan balance.

Debt Schedule, Step 5 – Interest Disbursement

With everything above set up, we can now calculate the Interest Disbursal on each tranche of Debt.

The formulas for the interest rates are straightforward:

We add each spread to the 10-yr government chemical bond yield OR other benchmark rate, and if there's a "floor," we take back the Soap between that floor and the benchmark rate.

And so, we calculate the Hard cash Interest Expense for the Six-gun and Term Loans based on the Beginning Balances:

We usage the Beginning Balances rather than the Average Balances to avoid circular references, which can make the model impermanent.

The Average Equalizer creates a circular reference because it's based on the Beginning and Ending Balances.

The Ending Balance depends on the Optional Repayment, but the Optional Repayment depends connected the Pursuit Expense – since the Interest Expense reduces the company's cash flow.

Therefore, the Pursuit Expense depends on the Optional Repayment, and the Optional Repayment depends connected the Interest Expense, so a circular reference point is created.

Just about LBO models and Debt Schedules put up circular references, but we choose to eliminate them by using the Offse Balances.

In addition to the Cash Interest, the Subordinated Notes and Mezzanine have Paid-in-Kind (PIK) Interest, so the Interest accrues to the loan star rather than organism compensable in cash:

The Cash Interest for both these tranches is straightforward and follows the setup above: multiply the Cash Pursuit Order by the Beginning Balance in the period.

Debt Schedule, Step 6 – Links on the Financial Statements

With the Debt Schedule complete, we can now link it to the financial statements, starting with the Interest Expense and Pursuit Income on the Income Statement:

The Johnny Cash Flow Statement here is unorthodox because it starts with Earnings Before Interest Taxes Depreciation and Amortization rather than Net.

As a result, we need to link in or calculate several items: the Cash Interest Expense, the Cash Interest Income, the Cash Taxes, and the Debt Drawdowns / (Repayments):

Symmetric though it's not the traditional CFS apparatus, the Free Cash in on Flow figuring is the same as always.

EBITDA – CapEx +/- Change in Capital – Johnny Cash Interest Expense – Cash in on Taxes +/- Other Items + Cash Sake Income is simply an alternate way to calculate FCF.

You butt see this if you use the traditional calculation (Profit + D&ere;A +/- Deferred Taxes + Non-Cash Pursuit +/- New Items +/- Change in WC – CapEx) and cross out the items that are combining weight.

The PIK Stake reduces the companion's Taxes, but this non-cash interest is not deducted to calculate FCF. It reduces the company's Cash Taxes, but nothing else.

As a resultant of these links and the lower cash flow, the company repays significantly to a lesser extent Debt and pays a high Interestingness Expense over sentence.

The Debt Agenda in this al-Qaida scenario looks significantly distinguishable from the previous iteration before the links:

The Debt Schedule: Just How Important Is it?

You should now understand how to build a Debt Schedule into an LBO model or standalone 3-statement model.

Just how principal is it in investment banking interviews or insular equity interviews?

You'ray unlikely to get detailed technical questions about IT because the Debt Schedule is more of a "mechanical" topic than a "conceptual" one.

In other language, if they neediness to check your skills, they'll give you a case study or mould test with a Debt Schedule rather than asking you verbal questions about it.

This is passing common in private equity interviews, A the Debt Agenda is always a part of LBO modeling tests, but it's not communal for internships and entry-level roles in investment banking.

Modeling tests in investment banking are usually given to more experienced candidates, such As those interviewing for lateral roles.

This tutorial is a small taste of the knowledge you'll gain in our paid courses. Breakage Into Bulwark Street uses real-spirit modeling tests and interview case studies to prepare you for investment banking and private equity interviews – and a leg up once you win your offer and start working. Get wind more near our advanced breeding by via the button on a lower floor:

Where Do You Find the Debt Schedule for a Company

Source: https://breakingintowallstreet.com/kb/leveraged-buyouts-and-lbo-models/debt-schedule/

Share this post