1. Duration and Effective Duration of a 5-year coupon with coupon rate 6%, yield 5%, and a 0.1% change of yield.

please explain how you solve this in excel and show work so I know how to do the rest of the assignment. thank you

X1 Efficient Duration Q Search Sheet Home Insert Page Layout Formulas Data Review View 9+ Share Insert Calibri (Body) - 11 - A- A- Wrap Text General 49 , X Delete Paste BIU A Merge & Center $ % ) .00 .00 2.0 Conditional Format Cell Formatting as Table Styles T Format Sort & Filter J22 X fx B D E F H 1 R S T 1 1 1 1 PE 2 1 + V 1 + + + + 3 2T 2T (1+3) +)** (1 +) (1+3) (1+ 1 Coupon Discount Price Duration 2 5% 5% 1 4.376032 3 4 Maturity Cashflow DF PV PV Time 5 0.5 0.025 0.97561 0.02439 0.012195 6 1 1 0.025 0.951814 0.023795 0.023795 7 1.5 0.025 0.928599 0.023215 0.034822 8 2 0.025 0.905951 0.905951 0.022649 0.022649 0.045298 9 2.5 0.025 0.883854 0.022096 0.055241 10 3 0.025 0.862297 0.021557 0.064672 11 3.5 0.025 0.841265 0.021032 0.073611 12 4 0.025 0.820747 0.020519 0.082075 13 4.5 0.025 0.800728 0.020018 0.090082 14 5 1.025 0.781198 0.800728 0.800728 4.003642 15 16 17 18 1 1 2 1* 3 2 + ) P(1+3)(1+*)** (1+3)** (1+3)* D = 3+ + + 1+) (1+2) 19 20 21 22 23 24 25 26 27 28 29 30 za 28 Sheet2 + Ready + 110% X1 Efficient Duration Q Search Sheet Home Insert Page Layout Formulas Data Review View 9+ Share Insert Calibri (Body) - 11 - A- A- Wrap Text General 49 , X Delete Paste BIU A Merge & Center $ % ) .00 .00 2.0 Conditional Format Cell Formatting as Table Styles T Format Sort & Filter J22 X fx B D E F H 1 R S T 1 1 1 1 PE 2 1 + V 1 + + + + 3 2T 2T (1+3) +)** (1 +) (1+3) (1+ 1 Coupon Discount Price Duration 2 5% 5% 1 4.376032 3 4 Maturity Cashflow DF PV PV Time 5 0.5 0.025 0.97561 0.02439 0.012195 6 1 1 0.025 0.951814 0.023795 0.023795 7 1.5 0.025 0.928599 0.023215 0.034822 8 2 0.025 0.905951 0.905951 0.022649 0.022649 0.045298 9 2.5 0.025 0.883854 0.022096 0.055241 10 3 0.025 0.862297 0.021557 0.064672 11 3.5 0.025 0.841265 0.021032 0.073611 12 4 0.025 0.820747 0.020519 0.082075 13 4.5 0.025 0.800728 0.020018 0.090082 14 5 1.025 0.781198 0.800728 0.800728 4.003642 15 16 17 18 1 1 2 1* 3 2 + ) P(1+3)(1+*)** (1+3)** (1+3)* D = 3+ + + 1+) (1+2) 19 20 21 22 23 24 25 26 27 28 29 30 za 28 Sheet2 + Ready + 110%