all of the instructions and information are included in the pictures but i will upload again so the information is listed twice
Step Instructions 1 Start Excel. Download and open the workbook named: Brooks_Chapter_6_2_Start. In cell D16, by using cell references, calculate the call price of the bond at the end of year 10. Use the relevant cells from the Given Data section. N 3 3 Note: The output of the expression you typed in this cell is expected as a positive number. In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 and absolute references to the relevant cells from the Given Data section. Note: The output of the expression you typed in this cell is expected as a positive number. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called on the call date. Use cells C16, D16, and absolute references to the relevant cells from the Given Data section. 4 5 6 7 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on the call date at the end of year 10. In cell range E17:G36, by using cell references, calculate the prices of the callable and noncallable bonds and the call premium on each call date after year 10. Copy the content from cell E 16:G16 down the columns to row 36. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 8 9 1 2 ADVANCED PROBLEM 6-2 FOR SPREADSHEET APPLICATION 3 Callable bond and call premium. Edward has just purchased a callable bond and wants to determine the bond's potential payoff price if it is called at any time during its callable life. The callable bond is a twenty-year semiannual bond that an issuer can call starting at year ten. It is callable every six months on the coupon payment date. The call price is declining and starts out as one extra year's coupon (a full annual coupon payment). It reduces each period (every six months by 1/20th of the annual coupon payment) untilat the bond's maturitythere is no call premium price. If the original bond is an 8% bond (with 4% coupons paid semiannually), a par value of $1,000 and an original yield to maturity of 6.5%, what is the bond price today at each potential callable date (coupon payment date) given no change in its original yield to maturity? Edward wants to compare the callable bond price at each callable date with that of a noncallable bond that is identical in every way other than the call feature (same yield to maturity, same coupon rate, and same maturity date). The difference between the callable bond price and the noncallable bond price is the call premium if called on that date. Find the call premium for each potential call date. 4 5 6 Given Data 7 8 9 Par Value Annual Coupon Rate Yield to Maturity Number of Payments per Year $1,000.00 8.0% 6.5% 10 11 2 12 13 Solution 14 Call Date (Years) 10.0 Call Price Price If Called on Date Price of Noncallable Bond Call Premium 15 16 13 Solution 14 Call Price Price If Called on Date Price of Noncallable Bond Call Premium 15 16 17 18 19 20 21 22 23 24 25 Call Date (Years) 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0 14.5 15.0 15.5 16.0 16.5 17.0 17.5 18.0 18.5 19.0 19.5 20.0 26 27 28 29 30 31 32 33 34 35 36 37 39 Points Requirements 1. Start Excel. 40 0 In cell D16, by using cell references, calculate the call price of the bond at the end of year 10. Use the relevant cells from the Given Data section. 2. 1 41 Note: The output of the expression you typed in this cell is expected as a positive number. In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 3. and absolute references to the relevant cells from the Given Data section. 1 42 1 43 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called date. Use cells C16, D16, and absolute references to the relevant cells from the Given Da section. 5. On the 1 44 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not 6. callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. 1 45 Note: The output of the function you typed in this cell is expected as a positive number. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on 7. the call date at the end of year 10. 1 46 In cell range E17:G36. by using cell references, calculate the prices of the callable and noncallable bonds and the call In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 3. and absolute references to the relevant cells from the Given Data section. 1 42 1 43 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called 5. on the call date. Use cells C16, D16, and absolute references to the relevant cells from the Given Data section. 1 44 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. 6. 1 45 1 46 Note: The output of the function you typed in this cell is expected as a positive number. 7. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on the call date at the end of year 10. 8. In cell range E17:G36, by using cell references, calculate the prices of the callable and noncallable bonds and the call premium on each call date after year 10. Copy the content from cell E16:G16 down the columns to row 36. 9. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 47 48 0 Step Instructions 1 Start Excel. Download and open the workbook named: Brooks_Chapter_6_2_Start. In cell D16, by using cell references, calculate the call price of the bond at the end of year 10. Use the relevant cells from the Given Data section. N 3 3 Note: The output of the expression you typed in this cell is expected as a positive number. In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 and absolute references to the relevant cells from the Given Data section. Note: The output of the expression you typed in this cell is expected as a positive number. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called on the call date. Use cells C16, D16, and absolute references to the relevant cells from the Given Data section. 4 5 6 7 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on the call date at the end of year 10. In cell range E17:G36, by using cell references, calculate the prices of the callable and noncallable bonds and the call premium on each call date after year 10. Copy the content from cell E 16:G16 down the columns to row 36. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 8 9 1 2 ADVANCED PROBLEM 6-2 FOR SPREADSHEET APPLICATION 3 Callable bond and call premium. Edward has just purchased a callable bond and wants to determine the bond's potential payoff price if it is called at any time during its callable life. The callable bond is a twenty-year semiannual bond that an issuer can call starting at year ten. It is callable every six months on the coupon payment date. The call price is declining and starts out as one extra year's coupon (a full annual coupon payment). It reduces each period (every six months by 1/20th of the annual coupon payment) untilat the bond's maturitythere is no call premium price. If the original bond is an 8% bond (with 4% coupons paid semiannually), a par value of $1,000 and an original yield to maturity of 6.5%, what is the bond price today at each potential callable date (coupon payment date) given no change in its original yield to maturity? Edward wants to compare the callable bond price at each callable date with that of a noncallable bond that is identical in every way other than the call feature (same yield to maturity, same coupon rate, and same maturity date). The difference between the callable bond price and the noncallable bond price is the call premium if called on that date. Find the call premium for each potential call date. 4 5 6 Given Data 7 8 9 Par Value Annual Coupon Rate Yield to Maturity Number of Payments per Year $1,000.00 8.0% 6.5% 10 11 2 12 13 Solution 14 Call Date (Years) 10.0 Call Price Price If Called on Date Price of Noncallable Bond Call Premium 15 16 13 Solution 14 Call Price Price If Called on Date Price of Noncallable Bond Call Premium 15 16 17 18 19 20 21 22 23 24 25 Call Date (Years) 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0 14.5 15.0 15.5 16.0 16.5 17.0 17.5 18.0 18.5 19.0 19.5 20.0 26 27 28 29 30 31 32 33 34 35 36 37 39 Points Requirements 1. Start Excel. 40 0 In cell D16, by using cell references, calculate the call price of the bond at the end of year 10. Use the relevant cells from the Given Data section. 2. 1 41 Note: The output of the expression you typed in this cell is expected as a positive number. In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 3. and absolute references to the relevant cells from the Given Data section. 1 42 1 43 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called date. Use cells C16, D16, and absolute references to the relevant cells from the Given Da section. 5. On the 1 44 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not 6. callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. 1 45 Note: The output of the function you typed in this cell is expected as a positive number. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on 7. the call date at the end of year 10. 1 46 In cell range E17:G36. by using cell references, calculate the prices of the callable and noncallable bonds and the call In cell D17, by using cell references, calculate the call price of the bond at the end of year 10.5. Use the call price in cell D16 3. and absolute references to the relevant cells from the Given Data section. 1 42 1 43 Note: The output of the expression you typed in this cell is expected as a positive number. 4. In cell range D18:D36, by using cell references, calculate the call prices of the bonds at the end of years 11 through 20. Copy the content from cell D17 down the columns to row 36. In cell E16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are called 5. on the call date. Use cells C16, D16, and absolute references to the relevant cells from the Given Data section. 1 44 Note: The output of the function you typed in this cell is expected as a positive number. In cell F16, by using cell references in the Excel PV function, calculate the price of the bond today as if the bonds are not callable. Use cell C16 and absolute references to the relevant cells from the Given Data section. 6. 1 45 1 46 Note: The output of the function you typed in this cell is expected as a positive number. 7. In cell G16, by using cell references, calculate the call premium between the prices of the callable and noncallable bonds on the call date at the end of year 10. 8. In cell range E17:G36, by using cell references, calculate the prices of the callable and noncallable bonds and the call premium on each call date after year 10. Copy the content from cell E16:G16 down the columns to row 36. 9. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 47 48 0
