Debt Characteristics

The first step in building our debt schedule is to enter assumptions related to the characteristics of any debt and preferred stock financing. These assumptions include interest rates, conversion prices for convertible securities, and scheduled amortization for debt requiring periodic repayment of principal.

 

Create Financial Models 10x Faster with Macabacus

Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking and productivity tools to help you accelerate financial modeling and presentations.

Start your Free Trial
 

Debt Maturities

Revolving credit facilities have no required amortization over the term of the facility, and preferred stock has no specified maturity date or mandatory periodic repayment, so we have excluded these instruments from the required repayment schedule. Expressing debt amortization as percentages of original face value in this schedule, rather than specifying a maturity year for each instrument, affords greater flexibility. For example, the former approach better accommodates debt that has 5% scheduled amortization for four years, followed by a bullet repayment of 80% in the fifth year.

Interest Rates

Floating interest rates are more common for senior debt than subordinated debt and preferred stock. In our model, we assume that the revolving and senior credit facilities have floating rates expressed as spreads to LIBOR, and that subordinated debt instruments and preferred stock pay fixed interest. Our interest rate assumptions are illustrative, and may not reflect current market conditions.

Circuit Breaker / Average Interest

Interest is normally computed on average balances. As we continue to build our debt schedule, the model will become circular because interest expense and preferred dividends reduce net income, which reduces cash flow available for debt repayment, which affects period-end debt balances, which affects average debt balances, which affects interest expense, and so on. Circularity may prevent the model from recovering from an anomaly, resulting in #VALUE! and/or other Excel errors. We can eliminate circularity, and thereby allow the model to correct itself, by toggling off average interest with a “circuit breaker.” With average interest turned off, interest expense and income are computed using beginning-of-period balances. After the model is restored and errors are corrected, you can switch the circuit breaker back to the average interest. We will link our circuit breaker to the rest of the model in
Step 9.

Cash Sweep

If the borrower has excess cash and the terms of the debt provide for early repayment at the borrower’s option, the borrower may use excess cash to periodically repay debt ahead of schedule. This is called “cash sweep.” A revolving credit facility always provides for early repayment, while bonds and preferred stock do not. Other debt instruments may or may not provide for unlimited cash sweep, and some may provide for limited cash sweep (e.g. only up to 50% of the outstanding balance may be repaid early). We have set up our cash sweep assumptions as percentages, rather than binary Yes/No inputs, to support limited cash sweep, but you would only enter 100% or 0% here—effectively, binary inputs—unless you knew that the debt terms provided for limited cash sweep.

Discover more topics

Build an operating model
In this tutorial, we will walk through how to build a general industry business operating model.
Read more
Build an M&A model
In this section, we demonstrate how to model a merger of two public companies in Excel.
Read more
Build an LBO model
In this tutorial, we will walk you through building an LBO model in Excel.
Read more
Asset and Stock Deals
The first step in purchase price allocation, or PPA, is to determine the purchase price.
Read more