Spreadsheet Assignment for Bonds Payable Amortization Schedule
Assume a $1,000,000 10 year Bond with a stated annual interest rate at 10%. The cash payments are made yearly for each of the 10 years. For the Discount assume a 12% market or effective interest rate. For the Premium assume an 8% market or effective interest rate. Reminder: Bond Sold at Par: Stated Rate = Market Rate Bond Sold at Discount: Stated Rate < Market Rate Bond Sole at Premium: Stated Rate > Market Rate Prepare an Excel Spreadsheet that completes a bond amortization schedule using the effective interest method for a 10 year bond. Assume cash interest payments are made at the end of each full year. The spreadsheet should be able to handle a bond sold at par, a discount, or a premium. Have totals at the bottom of the amortization schedule for the total cash payments and the total interest expense. Remember: To calculate the Annual bond payment you multiply the Face Value times the stated or contract rate. To calculate the selling price of the bond you need to calculate the present value (PV) of all the future cash payments (PV of interest payments plus the PV of the bond payoff amount at the maturity date.) (Use the market interest rate for getting the present value.) The columns in your amortization schedule should be:
Col 1
Col 2
Col 3
Period
Payment FV * CR
Int Exp CV * MR
Amort Col 1 - Col 2
Col 4 Discount (Premium) Balance Prev Bal - Col3
Col 5 Carry Value (CV) FV - Col 4
Once you have prepared the amortization schedule, use the schedule to make journal entries for the issue date, the cash payment for the end of the third year, and for the maturity or payoff date. Hand in the Excel file through WebCT.