Monthly Debt Schedules Example

  • Peter Lynch

This post includes a monthly debt Excel template with two examples (available for download at the bottom of this post). The first it labeled “Senior Term Loan,” and the second is labeled “Subordinated Notes.”

Senior Term Loan: This example debt schedule provides the option to project interest payments at LIBOR + a fixed rate, and includes mandatory quarterly principal payments. There is a row for the cash flow sweep, but it is included as a manual input in this model (for a dynamic version in please see the leveraged buyout video series or LBO Case Study).

Subordinated Notes: This example debt schedule includes a toggle that permits a portion of the interest be paid in kind (Payment-in-Kind or PIK). This feature can be activated or deactivated for any monthly period with a simple “Y” / “N” input.

Formulas and Functions: As you explore the template, you may notice functions that have not been explained previously on ASM. For a video explanation please see the following:

  1. =SUM & =OFFSET to sum a specific number of cells.
  2. =MOD to calculate quarterly interest payments.

Template available for download: Monthly Debt Schedule

Monthly Debt Schedule