Problem 5

Use the Excel template to build a spreadsheet for a purchase of $1,000,000 face value, 6% 5-year bond with interest payments every 6 months. Market interest rate is 5%. Include the following items:

Inputs:

Bond initial purchase amount

Stated Interest Rate

Maturity in Years

Number of payments/year

Market interest rate

Calculations section 1:

--Fair value with separate calculations for interest and principal

--Discount or premium

--Record the journal entry required when the bonds are purchased.

Calculations Section 2:

--Amortization schedule for each interest payment received (investment revenue). Use the general ledger accounts of cash, discount or premium, bonds payable and interest expense. (Similar to illustration 12-2)

Set up the spreadsheet consistent with journal entries necessary to record each interest payment received and related amortization. Also show the remaining principal and discount/premium at each interest receipt. NOTE: At the end of the bond investment term, the discount/premium account should be zero. Make sure you use as many formula as possible in the Excel spreadsheet so that your spread sheet will recalculate automatically when the inputs are changed.

Bond Valution and Accounting 4 Bond Issue Face Value 5 Stated interest Rate Es Maturity in years 7 Payments/Year Market Interest Rate 10 Interest received every 6 month 11 12 Fair value of the Hond at Issuance 13 less Bond Issue Face Value 14 Premium/ Siscount) Issuance date Interest Payment 1 Interest Payment 2 interest Payment 3 Interest Payment 4 Interest Payment Interest Payment Interest Payment 7 interest Payment Interest Payment interest Payment 10 17 Amortization Table 18 Time: 19 Investment Bands 20 Premiur balance 21 Discount balance 22 Cash 23 24 Journal Entries 25 At the issue date of the bond 26 Investment in bonds 27 Premium on bond investment 20 Discount on bond investment 29 Cash 20 31 At every interest payment date: 32 Cash 33 Discount on bond investment 34 Preinumi un bonul investiment 35 interest Revenue 36 38 At the maturity date of the bond 39 Cash 40 Investment in Bonds 43 Bond Valution and Accounting Bond Issue Face Value Stated Interest Rate Maturity in years Payments. Year Market Interest Rate linterest received every 6 month Fair Value of the Bond at Issuance Less Bond Issue Face Value Premium/ (Discount) Issuanse date Interest Payment Interest Payment 2 interest Payment 3 Interest Payment 4 Interest Payment Amortization Table Time: Investment Bonds Premium balance Discount balance Cash Journal Entries At the issue date of the bond Investment in Bonds Premium on bond investment Discount on bond investment Cash At every interest payment date! Cash Discount on bond investment Premium on bon investment Interest Revenue At the maturity date of the bond Cash Investment in Bonds terest Payment 5 Interest Payment 6 Interest Payment 7 Interest Payment 8 Interest Payment 9 Interest Payment 10