Debt schedule template
A debt schedule tracks a loan year by year: the beginning balance, less repayments, gives the ending balance, and interest is charged on what is owed. This one covers a term loan with a required repayment, a cash sweep and a revolver.
- Ending balance
Ending = beginning − required repayment − cash sweep
Each year starts where the last one ended.
- Interest
Interest = beginning balance × interest rate
Charging it on the beginning balance keeps the schedule free of circular references.
- Cash sweep
Sweep = (cash flow − after-tax interest − required repayment) × sweep %
Cash left after the required payment goes to extra repayment, up to the balance still owed.
- Revolver
Draw = required repayment − cash left, when cash is short
The required payment is always made. A short year borrows on the revolver, and later spare cash repays it before any sweep.
Inputs
Worked example
| Yr 1 | Yr 2 | Yr 3 | Yr 4 | Yr 5 | |
|---|---|---|---|---|---|
| Beginning balance | 500.0 | 410.0 | 314.6 | 213.5 | 106.3 |
| Required repayment | 25.0 | 25.0 | 25.0 | 25.0 | 25.0 |
| Cash sweep | 65.0 | 70.4 | 76.1 | 82.2 | 81.3 |
| Ending balance | 410.0 | 314.6 | 213.5 | 106.3 | 0.0 |
| Interest expense | 40.0 | 32.8 | 25.2 | 17.1 | 8.5 |
- Year 1, step by step
- Interest500.0 × 8%
- $40.0m
- Interest after the tax saving40.0 × (1 − 25%)
- $30.0m
- Cash left for repayment120.0 − 30.0
- $90.0m
- Required repayment500.0 × 5%
- $25.0m
- Cash sweep(90.0 − 25.0) × 100%
- $65.0m
- Ending term loan500.0 − 25.0 − 65.0
- $410.0m
- Over five years
- Total interest
- $123.5m
- Debt repaid in
- Year 5
How to build a debt schedule
Four steps, repeated for each year.
1. Set the loan terms
The amount, the interest rate and the required repayment each year, usually a percent of the original loan.
2. Charge interest
Multiply the beginning balance by the rate. Interest saves tax, so the cash cost is interest times one minus the tax rate.
3. Find the cash left
Take the cash flow before debt service and subtract the after-tax interest. That is the cash available to repay.
4. Pay the required amount, then sweep
Pay the required repayment first, drawing on the revolver if the cash is short. Spare cash repays the revolver, then the sweep percent of what remains goes to extra repayment. Carry both balances into the next year.
Talk through the paydown out loud
Knowing the mechanics is half of it. In an LBO interview you explain where the cash goes and how the debt falls. Practice the walk-through with an AI interviewer.
Questions people ask
What is a cash sweep?
A rule that sends spare cash to repay debt after the required payment. At 100% all spare cash goes to the loan. At 0% only the required amount is paid.
Should interest use the beginning or the average balance?
The beginning balance is simpler and has no circular reference. The average balance is closer to how interest builds up, but then interest depends on the repayment, which depends on cash left after interest, so the model needs iterative calculation.
What happens when the cash cannot cover the required repayment?
The company still pays it, by drawing on its revolver, the credit line banks lend alongside a term loan. The revolver charges interest and is repaid first from later spare cash, before any sweep. Try a cash flow of 50 in the calculator to see it.
How do I add a second loan?
Copy the block for each loan. Repay the most senior loan first and let each one take what is left of the cash.
Is the Excel file free, and does it work in Google Sheets?
It is free with no sign-up and has no macros. Blue cells are inputs and every other cell is a formula. It opens in Google Sheets.