B C D E F G H I J K L M N O P Q R S T U V W X Y Z 1 Question 4: (3 points) 2 Given the settlement date of April 29, 2021, use the info from previous problem to calculate on this spreadsheet (not a separate sheet) the bond price, duration, convexity in the specified cells (F4, F7, F12 for the US Treasury Note from the Question 3 3 and show the formulas in the orange cells. 4 Settlement date 29-Apr-21 Bond Price Formula 5 Maturity date 6 YTM Convexity Formula 7 Coupon 8 Face value 100 9 Frequency 2 Use Excel's Data Table to run a sensitivity analysis of the duration as a function of the coupon rate 10 (coupon rate=0%, 1%, 2%, 3%, 4%, 5%, 6%, 7%, 9%, 13%, 15%, 17%) and comment on the effect of coupon on the duration. 11 Modified Duration 12 Build a chart of the duration as a function of the coupon rate. 13 What is the interpretation of the modified duration? What is the interpretation of the convexity? 14 15 16 17 18 B C D E F G H I J K L M N O P Q R S T U V W X Y Z 1 Question 4: (3 points) 2 Given the settlement date of April 29, 2021, use the info from previous problem to calculate on this spreadsheet (not a separate sheet) the bond price, duration, convexity in the specified cells (F4, F7, F12 for the US Treasury Note from the Question 3 3 and show the formulas in the orange cells. 4 Settlement date 29-Apr-21 Bond Price Formula 5 Maturity date 6 YTM Convexity Formula 7 Coupon 8 Face value 100 9 Frequency 2 Use Excel's Data Table to run a sensitivity analysis of the duration as a function of the coupon rate 10 (coupon rate=0%, 1%, 2%, 3%, 4%, 5%, 6%, 7%, 9%, 13%, 15%, 17%) and comment on the effect of coupon on the duration. 11 Modified Duration 12 Build a chart of the duration as a function of the coupon rate. 13 What is the interpretation of the modified duration? What is the interpretation of the convexity? 14 15 16 17 18