The purpose of this note is to illustrate how spreadsheets can be used to teach bond accounting to students in introductory accounting courses. Since most students find it very difficult to master the amortization tables, accounting instructors who teach the principles course will find this approach very useful. It does not require them to teach their students how to use tables related to present and future values of single amounts or annuities and related concepts. The only relevant aspect of present value tables for understanding bond accounting is the computation of proceeds from bond issue.

The proposed method uses the built-in present value formula of ExcelŽ to accomplish this. Once the desired parameters related to interest rates, face value and loan period are supplied, a series of macros automatically builds the amortization table. The table can be used as a springboard to teach the economic and accounting aspects of bonds. One other advantage of using spreadsheets is that there is no need to confine the parameters of examples or student assignment to what is available in the present value and future value tables. Any combination of interest rates and maturity periods can be used. This enables instructors to use real life examples.

Illustration

I will use the following example to demonstrate the spreadsheet approach:

"ABC Corporation issued bonds worth $1,000,000 face value on January 1, 1990. Semi-annual interest payments are due on July 1 and January 1. The bonds were scheduled to mature on January 1, 2000."

The discussion is confined to the effective rate of interest method since this is the only acceptable method under the generally accepted accounting principles (GAAP). Two cases are discussed. In the first case, market rate of interest is below the coupon rate. In the second case, market rate of interest is above the coupon rate. For ease of exposition, the first date of coupon payment is exactly six months after the bond issue date.

Case I: Issuing Bonds Above Par Value

Suppose the market rate of interest is 3% and the coupon rate is 4%. Since the market rate of interest is below the coupon rate, bonds will be issued at premium or above par value.

Panel A of Table 1 is the input area. The student supplies the following values: face value, coupon rate, market rate, issue date and maturity date. Panel B uses an extensive series of macros to create the amortization table.

The readers will notice that some columns of the table are different than the traditional amortization table found in accounting texts. The changes are designed to make the economics of bond financing more transparent. For this reason, I use the term "outstanding loan" in place of "carrying value" or "book value." The change reflects the true nature of the amount. The second change is that "amortized premium" has been substituted with "Excess Payment To Be Subtracted From Outstanding Loan." APB Opinion NO. 25, which governs the current bond accounting, does not require a separate premium account. It only says that unamortized premium should be added to the face value of bonds. If the traditional approach is preferred, the excess payment can be termed as "amortized premium." However, my experience shows that students find it easy to grasp the idea that any payment over and above interest should be applied towards principal. Thus, the heading of the column "Excess Payment To Be Subtracted From Outstanding Loan" is self-explanatory. On the other hand, the concept of amortized premium is not very intuitive. The amounts in the last column are obtained after subtracting excess payment from the beginning-of-period outstanding loan.

How does the new amortization table help students understand the economics of bond financing? Though there can be alternative approaches, I start out by explaining that the company borrowed $1,085,884 at the prevailing market rate of interest which is three percent. In the first period, the interest expense for six months is $16,288 (= 1,085,884 x .03 x 0.5). However, the company paid $20,000 (=1,000,000 x .04 x 0.5) to bondholders. Since the amount paid to bondholders is greater than the interest expense, the excess payment is considered a repayment of principal and is subtracted from the outstanding loan. As a result, the outstanding loan decreases by $3,712 (=20,000-16,288). The second period begins with the loan outstanding at the end of first period. Again, since the company is paying more amount than the interest, the excess payment goes towards principal and reduces the outstanding loan amount.

The rest of the table can be explained in the same manner as described above. In the final period coupon and face value payments are made which equal the sum of interest expense and outstanding loan. Notice that in the entire explanation there is no mention of present value and future value concepts. Invariably though, some students ask how did I get the loan amount. At this point I introduce the present value concept by saying that a dollar received in future (say, one year later) is worth less than a dollar received today because it can earn interest. Then I give a simple example of a bond with two year maturity and compute the present value of coupon payments and payment of face value at maturity and sum them up to get proceeds from the bond. The discussion is usually short and does not take more than 15-20 minutes.

Case II: Issuing Bonds Below Par Value

Continuing with the same illustration, now suppose the market rate of interest is 4% and the coupon rate is 3%. Since the market rate of interest is above the coupon rate, bonds will be issued at discount or below par value.

Panel A of Table 2 is the input area. Panel B of Table 2 presents the amortization table. The columns of this table are the same as in Table 1 except the second to last column now reads "Unpaid Interest To Be Added To The Outstanding Loan." The amounts in the last column are obtained after adding unpaid interest to the beginning-of-period outstanding loan.

Again, to see how the proposed table helps students understand bond accounting, start out by explaining that the company borrowed $918,206 at 4% interest rate. In the first period, the interest expense for six months is $18,364 (= 918,206 x .04 x 0.5). However, the company paid only $15,000 to bondholders. Since the company has not paid the full interest amount, the shortfall ($3,364 = 18,364 - 15,000) is added to the outstanding loan amount. As a result, the outstanding loan increases by $3,364 and is now $921,570 (= 918,206 + 18,364) at the end of the first period. We start out the second period with the loan outstanding at the end of the first period. In the second period, the interest expense for six months is $18,431. Since the company is not paying the entire interest amount, the unpaid interest ($3,431 = 18,431 - 15,000) is again added to the outstanding loan amount. The rest of the table can be explained in a similar fashion.

Conclusion

This note illustrates how the Excel spreadsheets can be utilized to teach bond accounting. The spreadsheet approach is superior to the conventional approach of using present value and future value tables for two reasons. First, any combination of interest rates and maturity periods can be used. Second, it avoids the need to teach students the use of present value and future value tables and related concepts so the instructors can focus on the economic and accounting aspects of bond financing.

References

Financial Accounting Standards Board, 2002, Accounting Standards- Original Pronouncements, Vol. III, John Wiley & Sons NY.

Lawerance, K.D., J.J. Wild, and B. Chiapetta, Fundamental Accounting Principles, 16th Edition, McGraw-Hill Irwin.


back to top

Table 1 Case I: Issuing Bonds Above Par Value Amoritzation Table

Panel A: Input Area
Face Value			1,000,000
Coupon Value			4%
Market Rate			3%
Issue Date			1/1/1990
Maturity Date			1/1/2000
Panel B: Amortization Table
Date	Period	Beginning-of-Period Outstanding Loans	Amount Paid to Bondholders	Interest Expense	Excess Payment to be Subtracted from Outstanding Loan	End-of-Period Outstanding Loan
-	(1)	(2)	(3)	(4)	(5)	(6)
1/1/1990	0	-	-	-	-	1,085,884
7/1/1990	1	1,085,884	20, 000	16,288	3,712	1,082,172
1/1/1991	2	1,082,172	20, 000	16,233	3,767	1,078,405
7/1/1991	3	1,078,405	20, 000	16,176	3,824	1,074,581
1/1/1992	4	1,074,581	20, 000	16,119	3,881	1,070,700
7/1/1992	5	1,070,700	20, 000	16,061	3,939	1,066,761
1/1/1993	6	1,066,761	20, 000	16,001	3,999	1,062,762
7/1/1993	7	1,062,762	20, 000	15,941	4,059	1,058,703
1/1/1994	8	1,058,703	20, 000	15,881	4,119	1,054,584
7/1/1994	9	1,054,584	20, 000	15,819	4,181	1,050,403
1/1/1995	10	1,050,403	20, 000	15,756	4,244	1,046,159
7/1/1995	11	1,046,159	20, 000	15,692	4,308	1,041,851
1/1/1996	12	1,041,851	20, 000	15,628	4,372	1,037,479
7/1/1996	13	1,037,479	20, 000	15,562	4,438	1,033,041
1/1/1997	14	1,033,041	20, 000	15,496	4,504	1,028,537
7/1/1997	15	1,028,537	20, 000	15,428	4,572	1,023,965
1/1/1998	16	1,023,965	20, 000	15,359	4,641	1,019,324
7/1/1998	17	1,019,324	20, 000	15,290	4,710	1,014,614
1/1/1999	18	1,014,614	20, 000	15,219	4,781	1,009,833
7/1/1999	19	1,009,833	20, 000	15,147	4,853	1,004,980
1/1/2000	20	1,004,980	20, 000	15,075	4,980	1,000,000

See the following link for a working version of this spreadsheet (please enable macros): Arya_table 1

back to top

Table 2 Case II: Issuing Bonds Below Par Value Amoritzation Table

Panel A: Input Area
Face Value			1,000,000
Coupon Value			4%
Market Rate			3%
Issue Date			1/1/1990
Maturity Date			1/1/2000
Panel B: Amortization Table
Date	Period	Beginning-of-Period Outstanding Loans	Amount Paid to Bondholders	Interest Expense	Unpaid Interest to be Added to Outstanding Loan	End-of-Period Outstanding Loan
-	(1)	(2)	(3)	(4)	(5)	(6)
1/1/1990	0	-	-	-	-	918,206
7/1/1990	1	918,206	15, 000	18,364	3,364	921,570
1/1/1991	2	921,570	15, 000	18,431	3,431	925,001
7/1/1991	3	925,001	15, 000	18,500	3,500	928,501
1/1/1992	4	928,501	15, 000	18,570	3,570	932,071
7/1/1992	5	932,071	15, 000	18,641	3,641	935,712
1/1/1993	6	935,712	15, 000	18,714	3,714	939,426
7/1/1993	7	939,426	15, 000	18,789	3,789	943,215
1/1/1994	8	943,215	15, 000	18,864	3,864	947,079
7/1/1994	9	947,079	15, 000	18,942	3,942	951,021
1/1/1995	10	951,021	15, 000	19,020	4,020	955,041
7/1/1995	11	955,041	15, 000	19,101	4,101	959,142
1/1/1996	12	959,142	15, 000	19,183	4,183	963,325
7/1/1996	13	963,325	15, 000	19,267	4,267	967,592
1/1/1997	14	967,592	15, 000	19,352	4,352	971,944
7/1/1997	15	971,944	15, 000	19,439	4,439	976,383
1/1/1998	16	976,383	15, 000	19,528	4,528	980,911
7/1/1998	17	980,911	15, 000	19,618	4,618	985,529
1/1/1999	18	985,529	15, 000	19,711	4,711	990,240
7/1/1999	19	990,240	15, 000	19,805	4,805	995,045
1/1/2000	20	995,045	15, 000	19,901	4,955	1,000,000

See the following link for a working version of this spreadsheet (please enable macros): Arya_table 2