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:

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

Ans 2

Ans 1 Coupon Amount Semi Annual Bond Face Value* Semi Annual Coupon rate        30,000.00 Maturity in years 5 No of Pyaments/Year 2 Total No of Payments 10 Market Interest Rate 2.5% PVAF of 5% for 10 years 8.752063931 Present Value of coupon payments(Interest) (a)     2,62,561.92 Present Value of bond value(Principal) 0.781*1000000   (b)     7,81,000.00 Total Fair value of bond 10,43,561.92 Premium Issue proceeds-Face Value        43,561.92 Cash 10,43,561.92 Bond Payable 10,43,561.92

Ans 2

Bond Issue Value/Carrying Cost Premium on Bond Effective Interest Coupon Payment Amortisation (Premium) Closing amount of Carrying Amount Closing amount of Premium 10,43,561.92        43,561.92 26,089.05 30,000.00     -3,910.95 10,39,650.97      39,650.97 10,39,650.97        39,650.97 25,991.27 30,000.00     -4,008.73 10,35,642.24      35,642.24 10,35,642.24        35,642.24 25,891.06 30,000.00     -4,108.94 10,31,533.30      31,533.30 10,31,533.30        31,533.30 25,788.33 30,000.00     -4,211.67 10,27,321.63      27,321.63 10,27,321.63        27,321.63 25,683.04 30,000.00     -4,316.96 10,23,004.67      23,004.67 10,23,004.67        23,004.67 25,575.12 30,000.00     -4,424.88 10,18,579.79      18,579.79 10,18,579.79        18,579.79 25,464.49 30,000.00     -4,535.51 10,14,044.28      14,044.28 10,14,044.28        14,044.28 25,351.11 30,000.00     -4,648.89 10,09,395.39        9,395.39 10,09,395.39          9,395.39 25,234.88 30,000.00     -4,765.12 10,04,630.27        4,630.27 10,04,630.27          4,630.27 25,115.76 30,000.00     -4,630.27 10,00,000.00               -0.00

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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