A SPREADSHEET PROGRAM FOR BOND DISCOUNT AND PREMIUM AMORTIZATION
By Harry Howe
Current GAAP requires that any bond discount or premium be systematically amortized to income. While the APB expressed a clear preference for the effective interest method, the practitioner may use other methods if the results obtained are not materially different from those which would result from the interest method. The effective interest method enjoys the strongest theoretical foundation but is also the most computationally intensive, especially when the bond holder and issuer have different fiscal years. The purpose of the program presented in this article is to provide an easy method for evaluating the materiality impact using alternative methods of allocating bond discount or premium over the life of the bond.
Example
To illustrate the use of the spreadsheet program, assume an accountant has to account for a bond issued at a discount with the following characteristics:
Face value $1,000,000
Fair value $945,000
Term-years 15
Number of payments per year 1
Stated interest rate 8.5%
Entering Bond Information
To operate the macro, simultaneously depress the Ctrl+Q keys. A dialog box will ask for the face value of the bond (the amount due at maturity), the market value of the bond (issue price), its term in years, the number of payments made to bondholders per year (i.e., 1 if annual, 2 if semiannual, etc.) and the stated (contractual) rate of interest. Enter the interest rate in decimal format (i.e., the 8.5% interest rate would be keyed in as .085).
Using the Table
The first boxed range in the Table shows all the basic facts for the bond, allowing the accountant to verify the accuracy. The next boxed range shows the periodic amount of amortization using straight-line methods and the periodic interest expense (the sum of the cash payment and the amortization expense). For the example shown, the difference between straight-line amortization ($3,666.67) and the amount of first-year amortization using the effective interest method ($1,845.45) is $1,821.22. The accountant can decide whether this difference would be material. For bonds issued at smaller (and, generally speaking, more realistic) discounts, the difference between straight-line and effective interest methods may be immaterial. If the bond described above were issued at a 1.5% discount (instead of the 5.5% shown) the difference between the two methods drops to less than $500, or about six-tenths of one percent of the straight-line amount.
Except for the five user-supplied facts about the bond, all other cells in the worksheet contain formulas or labels. The documentation worksheet describes the computations for each of these formulas.
Using the Graph
To view the information amortization calculations in a graph, use the keystroke sequence / g v (Graph View). The Esc button will return you to the worksheet.
Correcting Input
If the wrong facts are provided for the bond, simply complete the entry cycle and start over again. Occasionally the user may supply incorrect information that causes the macro to attempt impossible calculations and to lock up. This might happen if the user supplied percentages in the wrong format (e.g., 8.5 instead of .085 for 8.5%) or switched the interest and term values. The Ctrl+Break combination will terminate the macro execution and you can start over again.
Worksheet Description and Documentation
The spreadsheet is organized into four tabbed worksheets--the main calculation table, the graph, the macros, and the documentation. The spreadsheet demonstrates several programming techniques that are readily adaptable to similar modeling problems, such as computing a loan reduction schedule or comparing the effect of various depreciation methods. Flexible range names and a "slide copy" technique automatically create a worksheet with the correct size (number of periods) for a given bond problem. The program requires no adjustment to calculate applicable amortization amounts for a single annual-payment bond of five years (n=5) or a monthly level payment note of 30 years (n=360). This feature saves time and eliminates mistakes. Other techniques include GetNumber macro commands, which control user input and use of the @IRR function to compute the effective interest rate. *
Harry Howe, PhD, is an assistant professor at SUNY Geneseo, N.Y. The author expresses his appreciation of the help of Dr. Jeffrey W. Lippitt at Siena College in designing the Excel version.
The spreadsheet programs mentioned in this article are available for download from The CPA Journal website (www.cpaj.com).
THE SPREADSHEET DEMONSTRATES several techniques, such as computing a loan reduction schedule or
comparing the effect of various depreciation methods.
Editors:
Douglas R. Carmichael, PhD, CFE, CPA
Baruch College
John F. Burke, CPA
The CPA Journal
The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.