Debt Schedule — Excel Model Template
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:
Step 1. Debt Tranches Table and Financing Assumptions
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:
- Revolving Credit Facility (i.e. Revolver)
- Senior Debt
- Subordinated Debt
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.
- Revolver = 0.0x * $100m EBITDA = $0m
- Senior Debt = 3.0x * $100m EBITDA = $300m
- Subordinated Debt = 1.0x * $100m EBITDA = $100m
Since the total leverage multiple is 4.0x, the total amount of debt is $400m.
- Total Debt = $300m Senior Debt + $100m Subordinated Debt = $400m Total Debt
Step 2. Interest Rate Pricing and Interest Expense Calculation
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.
- Revolver Interest Rate = 1.2% + 4.0% = 5.2%
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.
- Senior Debt Interest Rate = 2.0% + 4.0% = 6.0%
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.
- Subordinated Debt Interest Rate = 10.0%
Step 3. Mandatory Loan Repayment Percentage Assumptions
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 mandatory repayment is based on the original principal amount, not the beginning balance
- The ending debt balance cannot dip below zero, as that would mean that the borrower paid back more than the initial principal owed.
The Excel formula for mandatory repayment is as follows:
- Mandatory Repayment = -MIN (Original Principal * % Amortization, Original Principal)
Step 4. Financing Fees Assumption
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.
Step 5. Optional Repayment (“Cash Sweep”)
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:
- Optional Repayment = -MIN (SUM of Beginning Balance and Mandatory Repayment), Cash Available for Optional Paydown) * % Cash Sweep
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.
Step 6. Operating Assumptions and Financial Forecast
Next, for the financial forecast, we’ll use the following operating assumptions to drive our model.
- EBITDA = $100m in Year 1 – Increase by +$5m / Year
- Tax Rate = 30.0%
- D&A and CapEx = $10m / Year
- Increase in NWC = -$2m / Year
- Beginning Cash Balance = $50m
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.
- Positive Balance – If the company has “excess cash” to service more debt, it can use the excess funds for optional repayment of debt prior to the maturity date – i.e. the “cash sweep” – or pay down the outstanding revolver balance, if applicable. The company may also retain any excess cash.
- Negative Balance – If the FCF amount is negative, the company has insufficient cash and must draw down on its revolver (i.e. borrow cash from the credit line).
For example, if we track the flow of funds in Year 1, the following changes occur:
- Free Cash Flow (Pre-Debt Repayment) = $42m
- Less: $15m in Mandatory Repayment
- Cash Available for Revolver Repayment = $27m
- Less: $14m in Optional Repayment
- Net Change in Cash = $14m
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.
Step 7. Debt Schedule Build
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.
- Calculating the total debt balance is straightforward, as you just add up the ending balances of each tranche for each period.
- Interest expense is calculated using the average debt balances – i.e. the average between the beginning and ending balance.
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.