Page o 3 ZUUM Objective: Use Microsoft Excel to calculate the issue price of bonds issued at a discount and bonds issued at a premium, construct an amortization schedule for each of the bond issues, and make the journal entries to record the issuance of the bonds and the first two semiannual interest payments Points Possible: 10 Template: Copy the Excel template for this lab from our course's Canvas website to your desktop or student H: drive. Open the file. Problem Data: Bond Discount Problem: On January 1, 2017. Waterfall Lodge & Amusement Park issued $6,000,000 of 6% bonds, due in 10 years. The market interest rate for bonds of similar risk and maturity is 7%. Interest is paid semiannually on June 30 and December 31 each year. Bond Premium Problem: Assume that the market interest rate is 5.5% instead of 7%. Assume all other given amounts are the same as stated in the bond discount problem Required: 1. Enter the given problem data in the Bond Discount worksheet. Then use the Present Value function in Excel to calculate the issue price of the bonds To use the Present Value function, click in cell C7. Type PVC and then use cell references to enter the arguments for the PV function: The syntax of the PV function is PV(rate, nper, pmt, [1], [type]) Rate - Market interest rate, divided by 2 in this problem because interest is paid semiannually Nper Number of periods. Pmt - The amount of the semiannual cash interest payment. The formula to calculate is Face amount of the bonds x Stated interest rate - 2 FY =Fv stands for future value. When working with bonds, this is the face amount of the bonds. Page 1 of 3 0 - 20 F -Fv stands for future value. When working with bonds, this is the face amount of the bonds. Type Leave this field blank. Doing so indicates payments are made at the end of the period, which is true for bonds payable Check figure: Issue price = $5,573,627.90 Note: Excel will automatically return a negative number. To make it positive you can multiply the formula by negative I. (1) or put a minus sign in between the sign and the P(example: --PV (rate, nper, pmt, [f], [type]) (over) Vw18 DL HY 2. Use cell references and Excel formulas to construct an amortization schedule for the entire life of the bonds Note everything below the data should be a cell reference or formula containing a cell reference. (example --B4+B5" is ok, 7+4 is not) Hold your cursor over the red triangles in the corners of selected cells for help creating the formula for that cell Your schedule should look similar to Illustration 9-16 from our text: Hold your cursor over the red triangles in the corners of selected cells for help creating the formula for that cell. Your schedule should look similar to Illustration 9-16 from our text: (2) (2) Interest Expeme (4) Increase in Carrying Value Date Cash Paid Face Amount x Stated Rate Carrying Value x Market Rate 31-12) Carrying Value Prior Carrying Value +14) $ 93,205 93,433 93,670 1/1/18 6/30/18 12/31/18 $3,500 3,500 $3,728 3.737 $228 237 6/30/27 12/31/27 3.500 3.962 3.981 3.500 99,057 99,519 100,000 462 481 3. In the space below the table, enter three journal entries: January 1, 2017, to record the issuance of the bonds; - June 30, 2017, to record the first semiannual interest payment; and December 31, 2017 to record the second semiannual interest payment. Complete the Bond Premium worksheet. Use the given problem data for the bond premium problem to compute the issue price of the bonds (present value), construct an amortization schedule, and record the three journal entries. Check figure: Issue price = $6,228,408.78 (2) Your schedule should look similar to Illustration 9-17 from our text: (5) Interest Decrease in Carrying Date Cash Pald Expense Carrying Value Face Amount Carrying Value Prior Carrying Suated Rate e t Rate MEDIC U S ISSUE amortization schedule, and record the three journal entries. Check figure: Issue price - $6,228,408.78 Your schedule should look similar to Illustration 9-17 from our text (4) Decrease in Carrying Value Date Cash Pald Face Amount x Stated Rate Interest Expense Carrying Value x Market Rate 12)-3) Carrying Value Prior Carrying Value - 4 $107.439 107,162 106,877 1/1/18 6/30/18 12/31/18 $3.500 3.500 $3,223 2.215 $277 285 6/30/27 12/31/27 3.029 3.015 100,956 100,485 100,000 5. In the space below the table, enter three journal entries: -- January 1, 2017, to record the issuance of the bonds: 5. In the space below the table, enter three journal entries: -- January 1, 2017, to record the issuance of the bonds; June 30, 2017, to record the first semiannual interest payment; and December 31, 2017 to record the second semiannual interest payment 6. Save and attach in Canvas in the drop box for this lab. Make sure your name is included in the file name. Page of 2 0 - zoom + Face Interest Mar r estat per period Number of periods csc breedi t or functions Note: Hover your mouse call with a red in the hand corner to read the The data for this bond can be found in the word document instructions in the lab 2 modules in Canvas Issue price Note: Alles in the should be formulas or functions don't just entertext except the date column. You can type those in if you want Increase Carrying Value 201 I In the yellow space below, enter the journal entry for Jan 1, 2017 to record the nce of the bonds. Then make the journals to recomes two semiannual interest payment June 30 2017 Page 1 of 2 0 - ZOOM Face amount Interest payment Market interest rate per period Number of periods Blue cells can be typed in, yellow cells must be formules or functions Note: Hover your mouse to any cell with a red ads the upper right hand comer to read the hint The data for this bond can be found in the word document instructions in the lab 2 modules in Canvas Issue price Note: All cells in the table should be formulas or functions don't just entertext) except the date column. You can type those in If you wa Cash Paid for interest Date 2017 Interest Expense increase in Carrying Value Carrying Value (End of Period in the yellow space below, enter the journal entry for Jan 1, 2017 to record the issuance of the bonds. The more the journal entre to record the two se a l interest payments Account 1 2017 Page