Instructions: Legend: 1. Assume today is the purchase date listed below, and you purchase the bond...

Transcribed Image Text:

Instructions: Legend: 1. Assume today is the purchase date listed below, and you purchase the bond described below. The dates of the bond's remaining payments are in colum the default day count 4. Incens C1-C27, use in (CANNAC Turicaturi to calculate the unne in years between the purchase date ariu eacremaining payment. Select hasis hu leaving the third argument of the YFARFRAC command blank 3. In cells F15-F27, calculate the amount of the each of the bond's remaining payments. 4. In cells G15-G27, calculate the present value of each payment by discounting each payment at the YTM for t years. Sum these values in cell G28. 5. In cells H15-H27, calculate the payment weights (i.e., the percentage of the overall bond price that each bond payment contributes]). Sum these values in cell H28 and confirm that your weights sum to 100%. 6. In cells 115-127, multiply the time until each payment (t) by the weights from the previous step. In cell 128, sum these values to find the bond's weighted average time to payment. 7. In cells J15-J27, multiply the PV of each payment by (t^2+t). In cell J28, sum these values. 8. In cells B24-B25, report the bond's price and duration by referencing the appropriate cells from your completed cash flow table. 9. In cell B26, calculate the bond's modified duration. 10. In cell B27, calculate the bond's convexity. You can find the formula for convexity in footnote 2 on page 345 of the textbook. 11. To receive full credit, do not hard code any values anywhere in your sheet. Always use cell references when possible. You must type in or calculate Typed or calculated for you Summary Information Payment Datesars Until Payment Payment AmourPV(payment) Weight tx WeightV[payment) x (t^2 + t) 12/15/2023 Issuer Coupon Rate Walt Disney Company 0.1028 5.8% 12/15/2024 1.1028 Coupon Frequen Annual 12/15/2025 2.1028 Maturity Date 12/15/2035 12/15/2026 3.1028 Par Value $ 1,000 12/15/2027 4.1028 Yield to Maturity 6.40% 12/15/2028 5.1028 Purchase Date 1182023 12/15/2029 6.1028 12/15/2030 7.1028 Calculated Values 12/15/2031 8.1028 Price $476.86 12/15/2032 9.1028 Duration 12.10 12/15/2033 10.1028 Modified Duration 5.25 12/15/2034 11.1028 Convexity 12/15/2035 12.1028 Instructions: Legend: 1. Assume today is the purchase date listed below, and you purchase the bond described below. The dates of the bond's remaining payments are in colum the default day count 4. Incens C1-C27, use in (CANNAC Turicaturi to calculate the unne in years between the purchase date ariu eacremaining payment. Select hasis hu leaving the third argument of the YFARFRAC command blank 3. In cells F15-F27, calculate the amount of the each of the bond's remaining payments. 4. In cells G15-G27, calculate the present value of each payment by discounting each payment at the YTM for t years. Sum these values in cell G28. 5. In cells H15-H27, calculate the payment weights (i.e., the percentage of the overall bond price that each bond payment contributes]). Sum these values in cell H28 and confirm that your weights sum to 100%. 6. In cells 115-127, multiply the time until each payment (t) by the weights from the previous step. In cell 128, sum these values to find the bond's weighted average time to payment. 7. In cells J15-J27, multiply the PV of each payment by (t^2+t). In cell J28, sum these values. 8. In cells B24-B25, report the bond's price and duration by referencing the appropriate cells from your completed cash flow table. 9. In cell B26, calculate the bond's modified duration. 10. In cell B27, calculate the bond's convexity. You can find the formula for convexity in footnote 2 on page 345 of the textbook. 11. To receive full credit, do not hard code any values anywhere in your sheet. Always use cell references when possible. You must type in or calculate Typed or calculated for you Summary Information Payment Datesars Until Payment Payment AmourPV(payment) Weight tx WeightV[payment) x (t^2 + t) 12/15/2023 Issuer Coupon Rate Walt Disney Company 0.1028 5.8% 12/15/2024 1.1028 Coupon Frequen Annual 12/15/2025 2.1028 Maturity Date 12/15/2035 12/15/2026 3.1028 Par Value $ 1,000 12/15/2027 4.1028 Yield to Maturity 6.40% 12/15/2028 5.1028 Purchase Date 1182023 12/15/2029 6.1028 12/15/2030 7.1028 Calculated Values 12/15/2031 8.1028 Price $476.86 12/15/2032 9.1028 Duration 12.10 12/15/2033 10.1028 Modified Duration 5.25 12/15/2034 11.1028 Convexity 12/15/2035 12.1028