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

Market rate of interest is less than the coupon rate of interest. The bond has been issued at premium.

Half Yearly interest = 0.03*1000000 = $30000

Market Value of the bond

= $30000 * PVIFA ( 2.5%, 10) + $1000000 * PVIF (2.5%, 10)

= $30000 * 8.7521 + $1000000 * 0.7812 = $1043760

Premium = $1043760 - $1000000 = $ 43760

Journal Entry when the bonds were issued:

Cash Dr. $1043760

Bond Premium Cr. $43760

Bond Payable Cr. $1000000

Amortization Schedule

Note: Journal for each period has been given in the heading

For example for the first payment of interest, the journal should be:

Interest expense Dr. $ 26094

Bond Premium Dr. $ 3906

Cash Cr. $30000

Date Interest Payment @ 3% OF Face Value Interest Expense 2.5% of previous book value in G Amortization of bond Premium Credit Balance in the Bond Premium Account Credit Balance in the bonds Payable Account Book value/Carrying Value of the bond C - B F + E Credit Cash debit interest expense Debit bond premium $ $ $ $ $ $ Jan-01 43760 1000000 1043760 Jun-30 30000 26094 -3906 39854 1000000 1039854 Dec-31 30000 25996 -4004 35850 1000000 1035850 Jun-30 30000 25896 -4104 31746 1000000 1031746 Dec-31 30000 25794 -4206 27540 1000000 1027540 Jun-30 30000 25689 -4311 23229 1000000 1023229 Dec-31 30000 25581 -4419 18810 1000000 1018810 Jun-30 30000 25470 -4530 14280 1000000 1014280 Dec-31 30000 25357 -4643 9637 1000000 1009637 Jun-30 30000 25241 -4759 4878 1000000 1004878 Dec-31 30000 25122 -4878 0 1000000 1000000 Total 300000 256240 -43760

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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