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

Cash Flow facts Premium 6% Stated Rate Market rate 5% Payment Amount PV Factors at 2.50%, 10 Period PV Value (Amount X Factor) Periodic Interest $30,000 8.752064 $262,562 Maturity Value $1,000,000 0.781198 $781,198 $1,043,760 Period Ending Bonds Payable Unamortized Premium Net Book Value (Bonds Payable + Unamortized Premium) Interest Expense (cash paid less premium amortization) 01-01-X1 $1,000,000 $43,760 $1,043,760 0 06-30-X1 $1,000,000 $39,384 $1,039,384 $25,624 12-31-X1 $1,000,000 $35,008 $1,035,008 $25,624 06-30-X2 $1,000,000 $30,632 $1,030,632 $25,624 12-31-X2 $1,000,000 $26,256 $1,026,256 $25,624 06-30-X3 $1,000,000 $21,880 $1,021,880 $25,624 12-31-X3 $1,000,000 $17,504 $1,017,504 $25,624 06-30-X4 $1,000,000 $13,128 $1,013,128 $25,624 12-31-X4 $1,000,000 $8,752 $1,008,752 $25,624 06-30-X5 $1,000,000 $4,376 $1,004,376 $25,624 12-31-X5 $1,000,000 $0 $1,000,000 $25,624 Journal Entries Date Particulars Dr Amt. Cr. Amt 01-01-X1 Cash A/c                                                    Dr. 1043760       To Premium on Bonds Payable A/c $43,760       To Bonds Payable A/c $1,000,000 periodically Interest Exp A/c                                     Dr. $25,624 Premium on Bonds Payable A/c      Dr. $4,376        To Cash A/c $30,000 (To record interest payment (This Entry occurs on every interest payment dat at 6 months intervals) and amortization of premium 12-31-X5 Bonds payable                                        Dr. $1,000,000    To Cash A/c $1,000,000 (To record payment of face value at maturity) On any given financial statement date, Bonds Payable is reported on the balance sheet as a liability, along with the unamortized Premium balance (known as an “adjunct” account). To illustrate, the balance sheet disclosures would appear as follows on December 31, 20X3 and 20X4: Long term Liabilities (20X3) Bonds Payable $1,000,000 Plus: Unamortized premium on Bonds Payable $17,504 $1,017,504 Long term Liabilities (20X4) Bonds Payable $1,000,000 Plus: Unamortized premium on Bonds Payable $8,752 $1,008,752

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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