Refer to the information in S10-7 and prepare a worksheet that uses formulas to reproduce the bond discount amortization schedule shown for simplified effective-interest amortization in Chapter Supplement 10C. Display both the completed spreadsheet and a “formulas revealed” (Ctrl ~) version of it.
Info S10-7
Assume the authors of a popular introductory accounting text have hired you to create spreadsheets that will calculate bond discount amortization schedules like those shown in this chapter. As usual, you e-mail your friend Owen for some guidance. Much to your disappointment, you receive an auto-reply message from Owen indicating that he’s gone skiing in New Zealand. After a bit of panicking, you realize you can refer to Owen’s previous e-mail messages for spreadsheet advice that will help you complete this task. From his advice for Chapter 9, you decide to create a data input section for the stated interest rate, market interest rate, face value, issue price, and years to maturity. The spreadsheet file also will have a separate amortization schedule worksheet that contains only formulas, references to the cells in the data input section, and references to other cells in the amortization schedule. All amounts will be rounded to the nearest dollar (using the Round function in Excel), which means the discount amortization in the final year might be off a few dollars (unless you use the IF function in Excel to eliminate any remaining discount in the final year of the bond’s life, in the same way that Owen showed in Chapter 9 for declining-balance depreciation).
Required:
Prepare a worksheet that uses formulas to reproduce the straight-line bond discount amortization schedule shown in Chapter Supplement 10 A. Display both the completed spreadsheet and a “formulas revealed” (Ctrl ~) version of it.