Debt amortization is the systematic reduction in debt principal made over the term, or life, of the debt through periodic debt service payments. In general, five variables can determine the amortization of a debt: principal, interest rate, amortization term, debt term, and debt service payment. The principal is the amount to be amortized, or paid back. The interest rate is the percentage that, when multiplied by the principal at the beginning of the period, calculates the amount of interest. The amortization term is the number of payment periods over which the principal can be completely paid back, given a constant debt service payment. The debt term is the number of time periods over which the debt is outstanding. Although the debt term is generally the same as the amortization term, it can be shorter. In those cases, a balloon payment equal to the unamortized principal is made at the end of the debt term. The debt service payment is the combined principal and interest payment made every period over the debt term.
The timing of a payment—whether it’s at the beginning or at the end of the period—also affects the amortization of a debt. Payments made at the end of the period are called payments in arrears, or ordinary annuities. Payments made at the beginning of the period are called payments in advance, or annuities due.
Excel’s PMT function calculates the payment that, given the interest rate and the principal, completely pays off the debt principal over the amortization term.
The payment is calculated by using the PMT function, which uses the following formulas:
Payment for an ordinary annuity=Principal/Present Value factor of the ordinary annuity for i and n
Payment for an annuity due=Principal/Present Value factor of the annuity due for i and n
where i is the period interest rate and n is the number of periods.
All of the variables are defined by the terms of the contract that describes the debt instrument. Accordingly, your best source for determining these variables is the debt contract.
Note: Be consistent in the financial measurement time periods you use. If you’re building a debt amortization schedule with monthly payments, express your debt and amortization terms in months and use a monthly interest rate. If you’re building a debt amortization schedule with quarterly or yearly payments, express your debt and amortization terms in quarters or years and use a quarterly or yearly interest rate.