MBA's Guide to Microsoft Excel

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 peri- ods 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 pay- ments 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 princi- pal, 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 instru- ment. Accordingly, your best source for determining these variables is the debt contract.

Chapter Contents:

• EasyRefresher: Amortizing Debt
• Using the Debt Amortization Starter Workbooks
• Understanding the Fixed Rate, Ordinary Annuity Amortization Starter Workbook
• Understanding the Fixed Rate, Annuity Due Amortization Starter Workbook
• Understanding the Variable Rate, Ordinary Annuity Amortization Starter Workbook
• Understanding the Variable Rate, Annuity Due Amortization Starter Workbook
• Customizing the Debt Amortization Starter Workbooks