Build an Excel spreadsheet for a $1,000,000, 6% 5-year bond with interest paymen

Question

Build an Excel spreadsheet for a $1,000,000, 6% 5-year bond with interest payments every 6 months. Market interest rate is 5%. Include the following items:

Inputs:
Bond issue 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 issued.

~~~~~~~~ Calculations Section 2: ~~~~~~~~ DONT FORGET THIS PLS ~~~~~~~~~~~~

Amortization schedule for each interest payment. Use the general ledger accounts of cash, discount or premium, bonds payable and interest expense.

Set up the spreadsheet consistent with journal entries necessary to record each interest payment and related amortization. Also show the remaining principal and discount/premium at each interest payment. NOTE: At the end of the loan term, the discount/premium account should be zero.

Explanation / Answer

Bond Fair Value Amount (in $) Bond issue amount 1000000.00 Stated Interest Rate 0.06 Maturity in Years 5.00 Number of payments/year 2.00 Market interest rate 0.05 Value of Bond 1043760.32 Less: Bond issue amount 1000000.00 Premium Amount 43760.32 Amount (in $) Particulars Debit Credit Cash 1043760.32      Bonds premium 43760.32      Bonds payable 1000000.00 Amount (in $) Particulars Debit Credit Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Interest Expense 25623.97 Premium on Bonds payable 4376.03          Cash 30000.00 Bonds Payable 1000000.00             Cash 1000000.00

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.