Yield of Bond 1- bond 4 range from 1.00% to 15.00 %.

Find the data presented in Group Exercise 4 sheet in the Excel file. There are 4 bonds with different coupon rate and maturity but with the same YTM (Columns A-E and Rows 3-12). You have 1 Bond 2 today and thinking about hedging your position (sometimes called immunization). Excel does not provide any easy function to calculate duration and convexity. Functions bondprice, duration and secondDur are custom-made VBA functions. You may check it by pressing ALT + F11 when using the Excel file. (Using the function is not critical for this exercise. (Todays YTM is 6%)

A. By using the discounted cash flows are weights, calculate the Macaulay durations of the for bonds in (Bond 1 - Bond 4). Also, calculate the modified duration from this.

B. You have Bond2 and wish to immunize your portfolio to be delta-hedged. Use Bond 1 and Bond 3 to build two immunized portfolios (one with Bond 1 and 2 the other with Bond 2 and 3).

C. In the above exercise (B) calculate what is (should be) the cost of the immunization in each of the two cases?

D. Now, you not only wish to hedge against delta but also with to hedge against convexity (called Gamma hedging). Use Bond 1 and Bond 3 along with Bond 2 to form delta and gamma hedged portfolio.

E. Now, use Bond 4 to delta hedge Bond 2 instead of 1 or 3.

F. Create delta and gamma hedged portfolio with Bond 1, 2 and 4 and do the same activities in C. Compare the performance and hedging cost of your immunized portfolios from B-F. Explain your results in detail

PLEASE HELP ME ANSWER THIS QUESTION IN EXCEL IN DETAILS. THANK YOU VERY MUCH!!

Bond Coupon, Yield and Maturity Yield to maturity 6% Bond 4 Bond 3 3/1/20 Bond 1 3/1/20 6.70% 3/1/30 Weight Duration Convexity 7.67 76.40 10.00 136.50 Sum of weights Target Duration Target Convexity Issue date Coupon rate Maturity date Maturity Face value Bond 2 3/1/20 6.988% 3/1/35 15 1,000 3/1/50 0.00% 3/1/50 30 1,000 10 30 1,000 1,000 Inverse matrix 4.283556 -3.2835564 0.42835526 Weight A Weight B Sum of weights Bond price $1,051.52 $1,095.96 $986.24 $174.11 Duration Duration Convexity 7.67 7.67 76.40 10.00 10.00 136.50 14.64 14.64 332.74 30.00 30.00 930.00 Bond Coupon, Yield and Maturity Yield to maturity 6% Bond 4 Bond 3 3/1/20 Bond 1 3/1/20 6.70% 3/1/30 Weight Duration Convexity 7.67 76.40 10.00 136.50 Sum of weights Target Duration Target Convexity Issue date Coupon rate Maturity date Maturity Face value Bond 2 3/1/20 6.988% 3/1/35 15 1,000 3/1/50 0.00% 3/1/50 30 1,000 10 30 1,000 1,000 Inverse matrix 4.283556 -3.2835564 0.42835526 Weight A Weight B Sum of weights Bond price $1,051.52 $1,095.96 $986.24 $174.11 Duration Duration Convexity 7.67 7.67 76.40 10.00 10.00 136.50 14.64 14.64 332.74 30.00 30.00 930.00