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.

Simulation 5 (30 points) REQUIRED: Complete the attached excel spread sheet simulation problem. GRADE RUBRIC: All students in your group will receive the same grade unless someone doesn't fully participate. PARTICIPATION GRADE: Identify any members of your group who did not fully participate in the assignment. If someone did not fully participate, identify the student and assign either 0 or 50% to their effort. Their score will be reduced accordingly. Failure to follow any instructions may result in lost points on this assignment. Simulation 5 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