e t D u i n v D C al n e t BY KEITH R. HERRMANN

BY KEITH R. HERRMANN

Calc

ul

ati

n

g Debt Se

rvi

ce

Wh

e

n Paymen

t

Freq

u

e

n

cie

s D

iff

e

r

• • • • • • • • • • • • • • • • • •

L

ike 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 provid-ed the right answer, but the city wantprovid-ed 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 finan-cial advisor had supplied.The level of stress increased as the hours wore on,until the eureka moment finally came.As a for-mer 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 calculat-ing 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 illustra-tive purposes only.) In our example, the interest rate is 8 per-cent, 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 neg-ative 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 nega-tive. 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 peri-od, or Type can be set at 1 if you pay at the beginning of a given period. For more complete descriptions of the parame-ters, 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 = $0 and Type = 0 (i.e., there is no cash balance at the end of the loan and all payments occur at the end of the period).

Now let’s make things slightly more complicated. Rather than having annual payments, we will say the interest and principal payments are both semi-annu-al. PMT can still be used, but we need to make sure we are consistent about the units used for specifying the interest rate and the number of periods. If we are making semi-annual payments on the 10-year loan at 8 percent interest, then we use 8%/2 for rate and 10*2 for the number of periods. Exhibit 2 displays how to modify the PMT function to achieve the desired result. This modifies the PMT function, dividing the interest rate by two, multiplying the number of periods by two, and multiplying two.If the PMT answer were multiplied by two,the answer pro-vided would be the semi-annual debt service rather than the annual debt service. As shown in Exhibit 2, the average annu-al debt service in this scenario is $7,358,175.The totannu-al cost of the loan is $73,581,750. The average annual debt service in this scenario is $93,299 less per year than the corresponding answer in Exhibit 1.This means that the total cost of the loan is less when the payment frequency is semi-annual than when it is annual.

In addition to annual and semi-annual scenarios, PMT can be used when other payment frequency options are used

With differing payment

frequen-cies, the Excel payment function

cannot be used to accurately

calculate average annual debt

service. Instead, it is necessary

to set up a customized model.

(e.g., quarterly, bi-monthly, monthly, or bi-weekly). Following the example shown in Exhibit 2 and making similar modifi-cations, we can use the PMT function to calculate average annual debt service under any of these scenarios. For exam-ple, if we are making monthly payments for both principal and interest, then we would use 8%/12 for rate, 10*12 for the number of periods, and multiply the PMT answer by 12. (To calculate payments for the other payment frequencies men-tioned above, make the following substitutions in rate, period, and payment in lieu of the 12 we used for monthly: for quar-terly use 4, for bi-monthly use 6, and for bi-weekly use 26.)

Many debt payments are monthly; for example, in lease or installment financing. If the principal and interest payments for this loan were monthly, then the modified PMT function would return $7,279,656. The total cost of the loan when the payment frequency is monthly is $72,796,556. The average annual debt service in this scenario is $171,818 less per year than the corresponding answer in Exhibit 1, and it also repre-sents a savings over the semi-annual scenario.The point is that making more frequent payments results in a lower total amount paid.

BEYOND THE PAYMENT FUNCTION

For the Durham bond issue,the interest was to be paid semi-annually, and the principal was to be repaid annually. With these differing payment frequencies,the PMT function cannot be used to accurately calculate average

annual debt service.Instead,it is necessary to set up a customized model. Exhibits 3 and 4 depict a model that can be used to calculate average annual debt service for this scenario. The average annual debt service, in cell C25 of Exhibit 3, is $7,200,000.The total cost of the loan is $72

million. The average annual debt service in this scenario is $251,474 less per year than the corresponding answer in Exhibit 1.

Exhibit 3 shows the model,and the cells are populated with numerical results. Exhibit 4 shows the formulas behind the numbers in Exhibit 3.What follows is a brief description of the purpose of each row in the model. Row seven contains the interest rate. Row eight contains the annual repayment of the principal on a straight line basis. (This model is for a $50 mil-lion loan to be paid off over 10 years, and the annual princi-pal repayment is $5 million.) Row nine shows the outstanding balance of the principal each year as it is paid off over the term of the loan. Row 10 multiplies the interest rate by the annual principal repayment, and it is the calculation of the interest payments. Row 11 calculates the first of each year’s semi-annual interest payments, and row 12 calculates the sec-ond annual interest payment.Row 13 sums the two semi-annual interest payments. (Annual interest equals the sum of two semi-annual interest payments). Row 14 calculates cumu-lative interest, row 15 calculates total debt service (equal to principal plus interest), and row16 calculates cumulative debt service.

In addition to the payment frequency issue, there are other significant differences between the mathematical equation used by PMT and the assumptions in Exhibits 3 and 4. As already noted, PMT calculates payments based on constant (level) debt service over the term of the loan. What this means is that, simi-lar to what is often found in a car loan or a home mortgage, PMT assumes the total debt service payments remain the same for each period and that the amounts of principal and interest included in each payment will change. Exhibit 1:Annual Interest and Annual Principal

Interest Rate 8.0%

Total Number of Periods (Term) 10

Principal Amount $50,000,000

Average Annual Debt Service $7,451,474

Formula in Cell B8 =-PMT(B4,B5,B6)

Note: B4, B5, and B6 are input cells.

Exhibit 2: Semi-annual Interest and Semi-annual Principal

Interest Rate 8.0%

Total Number of Periods (Term) 10

Principal Amount $50,000,000

Average Annual Debt Service $7,358,175

Formula in Cell B8 =-PMT((B4/2),(B5*2),B6)*2

Note: B4, B5, and B6 are input cells.

Making more frequent payments

results in a lower total amount

paid.

The amount of principal will be lower and the amount of interest will be higher for the initial payments. Moving for-ward, as each subsequent payment is made, the total will remain constant while the amount of principle repaid will gradually increase and the amount of interest paid will grad-ually decrease.

For the Durham bond issue, the principal was to be repaid on a straight line basis over 10 years.The principle for this $50 million loan would be repaid in 10 equal installments of $5 million per year. The interest is calculated based on the out-standing loan balance for any given period. Thus, for the first year of the loan, the interest is 8 percent of $50 million, or $4 million. It is to be paid in two semi-annual installments of $2 million (two payments each equal to 4 percent of $50 mil-lion). The debt service for the first year is $4 million interest payment plus $5 million principal repayment, for a total of $9 million. For the second year, the outstanding principal had been reduced to $45 million ($50 million less the first annual $5 million repayment),and the interest is 8 percent of $45 mil-lion, or $3.6 million (two payments of $1.8 million each).The

total debt service for the second year, $3.6 million interest plus $5 million principal, is $8.6 million. In a similar fashion, the interest payments decrease by $400,000 every year until the tenth and final year,when the total debt service is $5.4 mil-lion (equal to $400,000 interest on the outstanding balance of $5 million plus $5 million principal repayment). Notice that the annual principal repayments remain constant for all 10 years, while the amount of interest is reduced by $400,000 per year until the loan is finally paid off.This is not level debt serv-ice because the total payments are not constant for all peri-ods. Moreover, this is different from the level debt service assumption in PMT where the total payments are constant for all periods.

At the outset,Durham’s financial analyst said that in this sce-nario (8 percent annual interest rate, 10-year term, $50 million principal, semi-annual interest payments, and annual princi-pal repayments), the total cost of this loan is $72 million, with an average annual debt service of $7.2 million per year for 10 years.In its attempt to verify this analysis,the city first used the unmodified PMT function in Exhibit 1, which returned an Exhibit 3: 10-Year Debt Service Calculator

Year Interest Annual Principal Cumulative Calculation First Interest Second Interest Total Interest Cumulative Total Debt Cumulative

Rate Repayment Principal Payment Payment Interest Service Debt Service

1 8.0% $5,000,000 $50,000,000 $400,000 $2,000,000 $2,000,000 $4,000,000 $22,000,000 $9,000,000 $72,000,000 2 8.0% 5,000,000 45,000,000 400,000 1,800,000 1,800,000 3,600,000 18,000,000 8,600,000 63,000,000 3 8.0% 5,000,000 40,000,000 400,000 1,600,000 1,600,000 3,200,000 14,400,000 8,200,000 54,400,000 4 8.0% 5,000,000 35,000,000 400,000 1,400,000 1,400,000 2,800,000 11,200,000 7,800,000 46,200,000 5 8.0% 5,000,000 30,000,000 400,000 1,200,000 1,200,000 2,400,000 8,400,000 7,400,000 38,400,000 6 8.0% 5,000,000 25,000,000 400,000 1,000,000 1,000,000 2,000,000 6,000,000 7,000,000 31,000,000 7 8.0% 5,000,000 20,000,000 400,000 800,000 800,000 1,600,000 4,000,000 6,600,000 24,000,000 8 8.0% 5,000,000 15,000,000 400,000 600,000 600,000 1,200,000 2,400,000 6,200,000 17,400,000 9 8.0% 5,000,000 10,000,000 400,000 400,000 400,000 800,000 1,200,000 5,800,000 11,200,000 10 8.0% 5,000,000 5,000,000 400,000 200,000 200,000 400,000 400,000 5,400,000 5,400,000 Assumptions Principal Amount $50,000,000 Cost of Issuance $0 Total Financing $50,000,000 Interest Rate 8.0%

average annual debt service of $7,451,474, and that result was $251,474 (3.5 percent) too high. Modifying the PMT function as shown in Exhibit 2 returned an average annual debt service of $7,358,175, which was still $158,175 (2.2 percent) too high. Once the model in Exhibits 3 and 4 was developed, the city was able to verify that the financial analyst’s number, $7.2 million, was correct.

CONCLUSIONS

When evaluating a new debt issuance,it can be a good idea to run several scenarios using different payment frequencies. As the exhibits demonstrate, the payment frequency affects the total cost of the loan.When you are analyzing prospective deals, it might be beneficial to see how scheduling payments of large amounts like this would affect the net present value of the available funds. (An additional factor not discussed in this article is that payments will vary depending on whether the payments are made at the beginning or end of the period.)

PMT can be used to calculate average annual debt service for many types of payment frequencies, but it should not be used when the payment frequencies for the principal and interest are not the same or when there is not an assumption of level debt service. Sometimes there is a need to calculate debt service payments in a scenario that does not fit into PMT; a customized approach is required instead.The tips provided in this article will help you evaluate financing options under a wider variety of debt service assumptions and payment fre-quency scenarios.❙

KEITH R. HERRMANN is deputy finance officer for the City of Durham, North Carolina (www.durhamnc.gov).The City of Durham is currently rated triple-A by all three national credit rating agencies (Moody’s, Standard and Poor’s, and Fitch). Herrmann holds an MBA from the Yale School of Management and degrees from the University of Pennsylvania, King’s College (University of London), and the Sorbonne (Université de Paris). He can be contacted at [email protected].

Exhibit 4: 10-Year Debt Service Calculator Formulas

Year Interest Annual Cumulative Calculation First Second Total Cumulative Total Cumulative

Rate Principal Principal Interest Interest Interest Interest Debt Debt

Repayment Payment Payment Service Service

1 =+$C$23 =+C21/10 =SUM(C8:L8) =+C7*C8 =SUM($C$10:$L$10)/2 =SUM($C$10:$L$10)/2 =SUM(C11:C12) =SUM(C13:$L$13) =+C13+C8 =SUM(C15:$L$15) 2 =+$C$23 x=+C8 =C9-C8 =+D7*D8 =+C11-C10/2 =+C11-C10/2 =SUM(D11:D12) =SUM(D13:$L$13) =+D13+D8 =SUM(D15:$L$15) 3 =+$C$23 =+D8 =D9-D8 =+E7*E8 =+D11-D10/2 =+D11-D10/2 =SUM(E11:E12) =SUM(E13:$L$13) =+E13+E8 =SUM(E15:$L$15) 4 =+$C$23 =+E8 =E9-E8 =+F7*F8 =+E11-E10/2 =+E11-E10/2 =SUM(F11:F12) =SUM(F13:$L$13) =+F13+F8 =SUM(F15:$L$15) 5 =+$C$23 =+F8 =F9-F8 =+G7*G8 =+F11-F10/2 =+F11-F10/2 =SUM(G11:G12) =SUM(G13:$L$13) =+G13+G8 =SUM(G15:$L$15) 6 =+$C$23 =+G8 =G9-G8 =+H7*H8 =+G11-G10/2 =+G11-G10/2 =SUM(H11:H12) =SUM(H13:$L$13) =+H13+H8 =SUM(H15:$L$15) 7 =+$C$23 =+H8 =H9-H8 =+I7*I8 =+H11-H10/2 =+H11-H10/2 =SUM(I11:I12) =SUM(I13:$L$13) =+I13+I8 =SUM(I15:$L$15) 8 =+$C$23 =+I8 =I9-I8 =+J7*J8 =+I11-I10/2 =+I11-I10/2 =SUM(J11:J12) =SUM(J13:$L$13) =+J13+J8 =SUM(J15:$L$15) 9 =+$C$23 =+J8 =J9-J8 =+K7*K8 =+J11-J10/2 =+J11-J10/2 =SUM(K11:K12) =SUM(K13:$L$13) =+K13+K8 =SUM(K15:$L$15) 10 =+$C$23 =+K8 =K9-K8 =+L7*L8 =+K11-K10/2 =+K11-K10/2 =SUM(L11:L12) =SUM(L13:$L$13) =+L13+L8 =SUM(L15:$L$15)

Assumptions

Principal Amount $50,000,000

Cost of Issuance $0

Total Financing =SUM(C19:C20)

Interest Rate 8.0%