Enrollment for the FMVA™ Certification Program is now OPEN!

Debt Schedule

A repayment schedule of debt in Excel

What is a Debt Schedule?

A debt schedule lays out all of the debt a business has in a schedule based on its maturity, usually used by businesses to construct a cash flow analysis. As shown in the graphic below, interest expense in the debt schedule flows into the income statement, the closing debt balance flows onto the balance sheet, and principal repayments flow through the cash flow statement (financing activities).

The debt schedule is one of the supporting schedules that ties together the three financial statements.

 

Debt Schedule in Excel

The interest expense calculated above (row 258) flows onto the income statement as interest expense. The closing balance (row 256) flows onto the balance sheet as the total debt value under liabilities. In this example, interest expense is based off a fixed interest rate multiplied by the average debt balance for the period (opening plus closing divided by two). The example above is from CFI’s LBO Financial Modeling Course.

 

Components of a Debt Schedule in a Financial Model

When building a financial model, an analyst will almost always have to build a supporting schedule in Excel that outlines debt an interest.

Components of this schedule include:

  • Opening balance (beginning of the period)
  • Repayments (decreases)
  • Draws (increases)
  • Interest expense
  • Closing balance (end of the period)

The above items allow the debt to be tracked until maturity.  The closing balance from the schedule flows back to the balance sheet, and the interest expense flows to the income statement.

 

Type of Debt is Listed in a Debt Schedule

To construct a debt schedule, analysts need to list all debt currently outstanding by the business. The types of debt include:

  1. Loans (bank, business)
  2. Leases
  3. Bonds
  4. Debentures

 

Factors to Consider in the Construction of a Debt Schedule

Before committing to borrow money, the company needs to carefully consider its ability to repay debt and the real cost of the debt. Here is a list of factors a company needs to consider –

  1. Debt maturity – most debt is amortized and paid monthly. The longer the maturity of the debt, the lower the amount due monthly, yet the higher the total sum of the debt and interest accrued.
  2. Interest rate – The lower the interest rate, the better, but not always. A low-interest rate for a long-term debt usually results in higher interest than short-term debt with the high-interest rate. The investor needs to calculate the total interest.
  3. Floating or fixed interest – A floating interest rate will change the overall debt amount each year, while a fixed interest rate provides reliability in the calculation. Depending on the future assumption; a floating interest rate is the better choice in a low or declining interest rate environment.
  4. Ability to generate gain – No reason to take on new debt if the debtor cannot generate a steady stream of income to pay the debt off. Failure to pay a debt might result in forced liquidation and a loss of trust.

 

Why is a Debt Schedule Important?

The ability to estimate the total amount a company needs to pay once a debt matures is the main reason a debt schedule is made. Another reason for using a debt schedule includes the company’s ability to monitor the maturity of the debt and make decisions based on it, such as the possibility of refinancing the debt through a different institution/ source when the interest-rate declines.

The debt schedule report can be used as an instrument to negotiate a new line of credit for the company. Lenders will use the report and consider the risk/reward before granting new credit.

 

 

More Resources

CFI is the official provider of the global Financial Modeling & Valuation Analyst (FMVA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional resources below will be useful:

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)™ by completing CFI’s online financial modeling classes!