A Debt Amortization Schedule outlines how a company will be settling its debt and interest over time. Businesses often rely on debt capital (loans) to support their growth strategy and expansions. In general, there are two types of loans you would generally see on a company’s balance sheet:

• Working capital loans (overdrafts) to support short-term inventory and working capital needs;
• Investment loans to support long-term strategic endeavors (installing a new assembly line or purchasing new equipment).

Working capital loans would sit in the short-term liabilities on the balance sheet, while investment loans sit on the long-term liabilities.

While overdrafts are usually due within a year, and most companies repay them in a single installment, investment loans typically come attached to a strict repayment schedule.

Most commonly, these have a fixed monthly installment. Initially, more of the installment covers interest on the outstanding amount, while a smaller portion of the payment goes towards settling the principal amount itself. As time progresses and the company repays more of the outstanding debt principal amount, a more minor part of the installment covers interest charges, and more goes towards the loan settlement.

We often refer to such repayment schedules as Debt Amortization Schedules.

This article will look at how to build these in Excel, using the formulas PMT, PPMT, and IPMT.

Building a Debt Amortization Schedule in Excel

For the premise of this example, let’s assume we are taking out a 22.5m loan at a 5% annual interest rate, which we will repay over 60 months.

To make it easier later, we are using numbers for the years and adding the “CY” via formatting (Use Ctrl + 1 to open the Format Cells dialog and add “CY” before your selected format).

Now that we have the summary table, the next step is to build out the amortization schedule. We need the following columns:

• Amortization Schedule (this will serve as a title for the table but will also list the dates of each installment)
• Year
• Period
• Beginning Balance
• Payment (the total installment)
• Principal (payment amount going towards settling the outstanding debt balance)

Interest (payment amount going towards the interest charge due for the period)

We start with the date we take our loan out and use the YEAR function to get the year in the second column. We will use this later to summarize the data.

Starting at Period 1 of our Debt Amortization Schedule, we link the opening balance. We then use the PMT function to calculate the total payment amount for the period. This function asks for the following:

• Rate: the interest rate per period, which is the annual interest rate of 5% divided over the number of periods in a year (12 months), as we calculate the debt amortization schedule monthly.
• Nper: the number of periods for the loan, which is 60.
• Pv: the present value of the debt facility, which we link to the beginning balance. I am locking this cell as the function uses the initial debt draw dawn amount for each period’s calculation.

To calculate the portion of the full installment that goes towards the settlement of the outstanding principal amount, we use the PPMT function:

• Rate: same as above
• Per: the current period, which we link to our Period column
• Nper: same as above
• Pv: same as above, locked again

The IPMT function calculates the interest portion of the installment and takes the same parameters as the PPMT function above.

With the beginning balance and the principal repayment, we can now calculate the ending balance. Remember that the interest payment does not decrease the outstanding debt balance.

Let’s move on to the second row of our amortization schedule. Starting with the first date, we can use the EOMONTH function to move to the end of next month. The function takes a start date and offsets it with a given number of months.

The calculation for the year remains the same, and we increment the period number by 1. We can link the beginning balance of period 2 to the ending balance of period 1 we calculated previously.

We can then copy down the rest of the parameters from period 1 because we locked the cell references when setting those up.

Now that we have all cells calculated for period 2 of the Debt Amortization Schedule, we can copy the entire row down. If our calculations are correct, we should start getting a #NUM! error after we hit the number of periods (in period 61 out of 60).

That’s our Debt Amortization Schedule.

Let’s build our summary, starting with the Debt Service. Because we left the years as formatted numbers, we can now use the SUMIFS function to summarize the principal repayments per year.

We can copy the same formula for all years because we fixed the references to the columns below.

Next, we can do the same calculation for the interest portion of the installments.

We now have an overview of the Debt Amortization Schedule and the corresponding cash flows for each year. This can help us in our financial planning and budgeting process.

Conclusion

Many lending partners would provide a repayment schedule upon request. However, the company needs to be able to model their Debt Amortization Schedule and the associated cash flows to play around with the assumptions (interest rate, term, etc.) and see how that impacts the overall profitability and liquidity of the business going forward.

Please share this article with colleagues and friends if you find it helpful.