Simulation 5
Build an Excel 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 received
(investment revenue). Use the general ledger accounts of
cash, discount or premium, bonds payable and interest
expense. (Similar to illustration 12-5)
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.
