Please show Excel formula

D E F A11 fx Market Data B 2 Consider the following newly issued bonds: 3 4 Inputs Juan Rojo, Incorporated 10-Year Bond McAllister Avionics 9-Year Bond 5 Settlement Date 01-01-2020 01-01-2020 6 Maturity Date 01-01-2030 01-01-2029 7 Coupon Rate 0.080 0.050 8 Redemption Value 100 100 Coupons per Year 2 1 10 11 Market Data 12 Initial Yield 0.075 13 Yield Change 0.010 14 15 Required: Using any necessary data above, calculate the price, the Macaulay Duration and the Modified Duration for each bond. Then, predict the price change given a change in the prevailing yield. Then, assume the market yield changed, as described below. In the second table, calculate the 16 approximate price change and new price according to duration (the first-order approximation). Nm 009 B D E F McAllister Avionics 9-Year Bond 17 18 (Use cells A5 to C13 from the given information to complete this question.) 19 20 Juan Rojo, Incorporated 10-Year Bond 21 Initial Price 22 Macaulay Duration 23 Modified Duration 24 25 First-Order Approximation, Price Change Juan Rojo, Incorporated 10-Year Bond 26 Price Change 27 New Price 28 29 McAllister Avionics 9-Year Bond D E F A11 fx Market Data B 2 Consider the following newly issued bonds: 3 4 Inputs Juan Rojo, Incorporated 10-Year Bond McAllister Avionics 9-Year Bond 5 Settlement Date 01-01-2020 01-01-2020 6 Maturity Date 01-01-2030 01-01-2029 7 Coupon Rate 0.080 0.050 8 Redemption Value 100 100 Coupons per Year 2 1 10 11 Market Data 12 Initial Yield 0.075 13 Yield Change 0.010 14 15 Required: Using any necessary data above, calculate the price, the Macaulay Duration and the Modified Duration for each bond. Then, predict the price change given a change in the prevailing yield. Then, assume the market yield changed, as described below. In the second table, calculate the 16 approximate price change and new price according to duration (the first-order approximation). Nm 009 B D E F McAllister Avionics 9-Year Bond 17 18 (Use cells A5 to C13 from the given information to complete this question.) 19 20 Juan Rojo, Incorporated 10-Year Bond 21 Initial Price 22 Macaulay Duration 23 Modified Duration 24 25 First-Order Approximation, Price Change Juan Rojo, Incorporated 10-Year Bond 26 Price Change 27 New Price 28 29 McAllister Avionics 9-Year Bond