Calculating debt service when payment frequencies differ.

• Author: Herrmann, Keith R.

[ILLUSTRATION OMITTED]

Like many localities, the City of Durham, North Carolina, uses debt to provide long-term financing for projects. When it was time, recently, to calculate the amount of debt service Durham would be obligated to pay as a result of issuing bonds, the city's financial advisor had already provided the right answer, but the city wanted to confirm that this number was correct. Initially, there were problems in verifying her analysis.

Using the payment function in Excel to calculate average annual debt service yielded a different answer than the financial advisor had supplied. The level of stress increased as the hours wore on, until the eureka moment finally came. As a former boss once said, "Through stress comes understanding."

THE PAYMENT FUNCTION

To start with the basics, total debt service is the sum of all the interest payments and all the principal repayments on a loan. Average annual debt service is total debt service divided by the number of years for the loan. The frequency of the periodic payments is typically one year (annual), six months (semi-annual), or one month. For the sake of clarity, the examples that follow will be for simple interest, which is calculated on only the principal amount or the portion of the principal that remains unpaid.

If the frequency of the payments of interest and principal are annual, then there is a function in Excel that is ideally suited for calculating the average annual debt service payment: the payment function (PMT). PMT calculates the payment for a loan based on constant payments and a constant interest rate. The syntax to be used is: PMT (interest rate, number of periods, principal amount). Exhibit 1 is a template that displays the use of the PMT function. (The numbers in this example are for illustrative purposes only) In our example, the interest rate is 8 percent, the term of the loan is 10 years, and the principal amount is $50 million. Cells B4, B5, and B6 in Exhibit 1 are the input cells for this data. The PMT function is in cell B8. The content of cell B8 is -PMT(B4, B5, B6). Be sure to include a negative sign before the PMT function: The convention in Excel is to distinguish between an amount received and a payment made by designating one as positive and the other as negative. Many people find it more intuitive to make both positive, which is accomplished by inserting a negative sign where indicated. This formula will return the average annual debt service of $7,451,474 per year. Because the term of the loan is 10 years, this means that the total cost of the loan is $74,514,744, or 10 times the average annual debt service.

PMT includes additional features. There is a parameter that can be used to calculate loan payments if there is a future value (FV) or cash balance you want to attain after the last payment is made. There is also a parameter, called Type, to indicate when payments are due. The Type parameter can be either omitted or set at 0 if you pay at the end of a given period, or Type can be set at 1 if you pay at the beginning of a given period. For more complete descriptions of the parameters, see the help provided for PMT in the financial functions reference section of Excel. All the examples in this article omit values for FV and Type, assuming the default settings of FV =...