- Please discuss the pros and cons of each option?

Table 20.1 and the first worksheet, named Cost Components, show a simplified presentation of up-front costs for a capital construction project. These costs are set out on a separate worksheet because estimates may change. Other worksheets then link to those assumptions, which can be adjusted when necessary. The Cost Components worksheet also contains an estimate of the tax-exempt interest rate at the time when debt is likely to be issued. Because market interest rates fluctuate, most analysts use rates somewhat higher than current borrowing costs, providing a little cushion against changing rates. Finally, the worksheet contains an estimate of the underwriting fees and other costs of issuance anticipated in a bond transaction. Many jurisdictions and authorities issue bonds with level debt service, like payments on a fixed-rate home mortgage. This makes budgeting easy and reassures rating agencies, which usually prefer either level debt service or front-loaded debt service and are wary of back-loaded debt-service structures. Excel's TVM functions make it simple to calculate annual debt service for a level debt structure. The PMT function gives a quick and accurate estimate of the total principal and interest payment required to retire debt on a given schedule.

TABLE 20.1; information from Table 20.1

A major urban center is planning to issue a municipal bond for the construction of a stadium:

-The construction cost is $100,000,000-The design cost are estimated to be $10 million

-The cost of issuance is 1% of the total (construction & design), or $1,100,000

-The interest rate is 5.875%, based on the economic and financial conditions of the city

-The term of the bond is 20 years-The city must decide between using:

-Level debt service with semi-annual payments; or-Level principal payments made annually.

Table 20.2

The second worksheet, named Level Debt, restate the relevant assumptions picked up directly from the first worksheet and then use the PMT function to calculate the annual payment. To make this calculation, the PMT function requires the interest rate (rate), the number of years (nper), and the initial outlays (PV). If you look carefully at the cell containing the calculation of the annual payment, you will see the PMT function introduced with a minus sign. When Excel is asked to calculate a TVM

Table 20.2 Level Principal Payment

Principal Amount, $111,100,000

Interest Rate, 5.875%Terms in Years, 20

Annual Principal Payment, $5,555,000

Annual Interest Payment is Variable, see Table 20.3 on page 194 for detail on annual interest paid and total annual debt service

Total Interest Paid Over Bond Life, $68,534,813Total Debt Service Over Bond Life, $179,634,813

Table 20.3

The spreadsheet tab labeled Level Principal shows how to set up a cash flow table with level principal payments, which result in front-loaded payments of total annual debt service. Statutes in some jurisdictions require this kind of debt structure by mandating equal principal payments each year

Table 20.3 Level Principal Payment

Principal Amount, $111,100,000

Interest Rate, 5.875%

Terms in Years, 20

Annual Principal Payment, $5,555,000

Annual Interest Payment is Variable, see Table 20.3 on page 194 for detail on annual interest paid and total annual debt service

Total Interest Paid Over Bond Life, $68,534,813

Total Debt Service Over Bond Life, $179,634,813

formula using two-dollar values (in this case, PV and PMT), the answer always carries a sign different from the dollar variable used as an input. The added minus sign restates the answer as a positive number, which makes better sense to most people who will read the analysis. The total payment is composed of both principal and interest, and as with mortgage or car payments, the interest portion is always higher in earlier years, with the principal portion of the payment growing as the maturity date of the debt approaches. Also, typical tax-exempt bond structures pay interest twice a year and principal once a year. To simplify the discussion, we only calculate annual debt payment in this text.