A Debt Schedule is used to keep track of all outstanding debt balances and related payments, namely mandatory principal amortization and interest expense.
Not only does the debt schedule estimate the debt capacity of a company, but it can also serve as a tool to anticipate upcoming cash shortfalls that would require additional funding.
Table of Contents
The purpose behind modeling the debt schedule is to forecast the balances of outstanding debt securities and the amount of interest expense coming due in each period.
For a company raising debt financing, it is essential to determine the impact of the new debt on its free cash flows (FCFs) and credit metrics.
The parties involved in a lending arrangement – or more specifically, the borrower and lender(s) – are entering a contractual legal agreement. In exchange for capital from the lender(s), the borrowers agree to terms such as:
Loan agreements are legally-binding contracts with specific requirements that must be followed. For example, paying a lender with lower priority ahead of a senior lender is a clear violation unless explicit approval was provided.
If a company defaults on a debt obligation and undergoes liquidation, the seniority of each creditor determines the order in which lenders would receive proceeds (i.e. recovery).
The required rate of return is higher for non-senior lenders lower in the capital structure as these lenders require more compensation for undertaking incremental risk.
The two distinct types of debt structures are as follows.
Senior debt lenders such as banks tend to be more risk-averse while prioritizing capital preservation (i.e. downside protection), whereas subordinated debt investors are typically more yield-oriented.
The revolving credit facility – i.e. the “revolver” – is a flexible form of short-term financing in which the borrower can draw down (i.e. obtain more debt) or repay as needed once the borrower has enough cash.
However, if the borrower has an outstanding revolver balance, all discretionary debt repayments must go towards paying down the revolver balance.
There are two major aspects in a typical credit agreement that reduce the outstanding debt over time:
Now that we’ve listed out the steps to building out a debt schedule, we can move on to an example modeling exercise in Excel. For access to the template, fill out the form below:
The first step to modeling a debt schedule is to create a table outlining each of the different tranches of debt along with their respective lending terms.
Here, our company has three different tranches of debt within its capital structure:
In the first column (D), we have “x EBITDA”, which refers to how much debt was raised in that particular tranche relative to EBITDA – i.e. “turns” of EBITDA.
For the sake of simplicity, we’ll be referencing our next twelve months (NTM) EBITDA figure for our debt amounts.
For instance, our company raised 3.0x EBITDA, so we multiply our Year 1 EBITDA of $100m – i.e. the next fiscal year – by 3.0x to get $300m in senior debt capital.
Since the total leverage multiple is 4.0x, the total amount of debt is $400m.
The next two columns following the “$ Amount” section are “Pricing” and “% Floor”, which we’ll be using the determine the interest expense burden associated with each debt tranche.
For the revolver, the pricing is “LIBOR + 400”, which means the interest expense is the rate of LIBOR plus 400 basis points (bps) – i.e. one-hundredth of a percent.
With that said, to convert basis points to percentage form, we just divide by 10,000.
For the senior debt tranche, there is an interest rate “floor”, which protects the lenders from falling interest rates (and their yield).
Our formula uses the “MAX” function in Excel to ensure that LIBOR does NOT fall below 2.0% (or 200 basis points).
If LIBOR does indeed drop below 200 bps, the interest rate is calculated as follows.
Note that LIBOR is currently in the process of being phased out by the end of 2021.
When it comes to interest rate pricing, floating interest rates are more common for senior debt than subordinated debt.
For sub-debt, a fixed rate is far more common – with the occasional PIK interest element for riskier securities or deals with a substantial amount of debt involved.
The “% Amort.” column refers to the required repayment of the debt principal per the original lending agreement – for our scenario, this is applicable for just the senior debt (i.e. 5% annual mandatory amortization).
When modeling out the mandatory amortization, two important considerations to keep in mind are:
The Excel formula for mandatory repayment is as follows:
The financing fees are the costs associated with raising debt capital, which are not treated as a one-time outflow but are instead expensed on the income statement under accrual accounting as a result of the matching principle.
To calculate the total financing fees, we multiply each % fee assumption by the amount raised in each tranche and then add them all up.
But to calculate the annualized financing fees, which is the amount expensed on the income statement and what impacts the free cash flow (FCF), we divide each total amount of fees in the debt tranche by the term length.
If our company has excess cash on hand and the lending terms do not restrict early repayment, the borrower can use the excess cash for discretionary debt repayments ahead of the original schedule – which is a feature often called a “cash sweep.”
The formula for modeling the optional repayment line is:
In our illustrative example, the only tranche with the optional cash sweep feature is the senior debt, which we entered as 50% in our debt assumptions earlier.
This means that half (50%) of the discretionary, excess FCF of the company is used to repay outstanding senior debt.
Next, for the financial forecast, we’ll use the following operating assumptions to drive our model.
Once we calculate the free cash flow (FCF) up until the point at which “Mandatory Debt Repayment” is to be paid, we add up each of the mandatory amortization amounts and link it back to our financial forecast section.
From the total amount of free cash flow available to pay down debt, we first subtract the mandatory amortization amount.
For example, if we track the flow of funds in Year 1, the following changes occur:
The net change in cash of $14m is then added to the beginning cash balance of $50m to get $64m as the ending cash balance in Year 1.
In the final section of our debt schedule, we’ll calculate the ending debt balances for each tranche, as well as the total interest expense.
But prior to doing so, we must link back the missing section of the financial forecast into our debt schedule section, as shown below in the roll-forward schedules for each tranche of debt.
Note that a circular reference is introduced into our model since interest expense reduces net income and net income reduces the free cash flow (FCF) available for debt repayment.
And then, FCF affects the end-of-period debt balances and thus the interest expense for each period.
As a result, we must create a circuit breaker (i.e. the cell named “Circ”), which is a toggle switch that can cut off the circularity in the case of errors.
If the circuit breaker is set to “1”, the average balance is used in the interest expense calculation, whereas if the circuit breaker is switched to “0”, the formula will output zero in the interest expense calculations.
From Year 1 to Year 5, we can see how the total debt outstanding has declined from $371m to $233m, so the ending debt outstanding at the end of the projection period is 58.2% of the initial amount of debt raised.
Enroll in The Premium Package: Learn Financial Statement Modeling, DCF, M&A, LBO and Comps. The same training program used at top investment banks.
