Build an Excel spreadsheet for a purchase of $1,000,000face 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 arepurchased.

Calculations Section 2:

Amortization schedule for each interest 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 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.