insert Page Layout Tell me what you want to do File Home Formulas Data Review View Help Shan AutoSumA O Cut h Copy X Fal == . Calib Wap Tat Normal Calculation 11 A A Good Neutral Genera Bad Input Conditional Fomat a Formatting Table Insert Delte E u A. Merge & Center Check Cell Unked Cell Sort & Find B S% Explanatony Note = st Painter Clear Select- Aigoment Cigboard Fort Nunber Styles Cale Fation x c10 c. D A G H N P Q XY7 Bood Settlement Date 5/14/2019 Maturity Date 5/14/2024 ate Face Value $100 Frequency Basis Yield Bond orice 99.1528 8.21% 7.71 % 101 Price change Percentage change 14 30318 bond price 2.0492% Duration 20 12. Predicted % change due to duration Predicted % change due to comvexity Total predicted % change in bond price 2.0238 % 0.0252 % 2.0489 % Column 1 Columns 3 x4 1/(2 2 " Prica ) Convexity Column 2 Column 3 Calumn 4 Column5 Period (t) Cash Flow (CF) 1/1+(v/2) )^ (t+ 2 ) tt+1]CF 7.09 20.43 0.851361 24.00 0.817790 48.00 39.25 62.84 0.785544 80.00 168.00 121.77 0.724815 155.96 0.696235 224.00 192.61 288.00 25127 an om 7981.16 0.002521 20.12338 4 5 Part Part Sheets eads The data of a bond issued by XYZ company is given in the Part II worksheet. The bond matures in 5 years, has a coupon rate of 8% and a yield to maturity of 8.21%, and pays interest semiannually 1. Please compute the bond prices in cells B10 and B11 corresponding to the yield of 8.21%, and 7.71%, respectively. Then compute the change in bond price and percentage change in bond price in cells B12 and B13 to maturity 2. Please calculate Macaulay's duration of this bond in cell B15. Excel has a built-in function of computing Macaulay's duration. Perhaps I should show this to you earlier. However, if I do that, no one will pay attention to the process of computing Macaulay's duration -duration(settlement, maturity, coupon, yld, frequency, basis) L t(t+1)C, 3. Please calculate its convexity in years. You can use the formula: 22 x Price (1+ 2 where y is the yield to maturity of 8.21%, and bond price is from cell B10. First create 1 columns for these variables, t (Period), C,(Cash Flow or CF), (PV Discount +2 (1 2 1 Factor), t(t+1 C,, and t(t+ 1)C;[- -]. Then sum up t(t+ 1)C;[ y+2 ] for 10 (1+ 2 (1 2 periods (in cell E33), and multiply it by 22 y Price You wil get the convexity of this bond in cell G33 (in years)