Today, an institutional investor who manages a government employee pension fund, is considering investing part of the funds he manages in a bond portfolio. This portfolio will consist of four types of bonds (Bond A, Bond B, Bond C and Bond D). As of today, we have the following information for each type of bond:

Bond A:

• Nominal value: $100
• Semi-annual coupons of constant amount
• Time to maturity: 3 years
• Yield to maturity: 7.5% nominal annual
• Price as of today: $97

Bond B:

• Nominal value: $100
• Coupon rate for the calculation of the first coupon: 11% effective annual
• Annual coupons increasing at a constant rate of 2% effective annual
• Time to maturity: 4 years
• This bond has a repurchase option (Callable Bond) at the end of the third year from today. In case of repurchase, the coupon corresponding to the period plus a Call Price of $107 will be paid, implying a Yield to Call of 9% effective annual

Bond C:

• Nominal value: $100
• Semi-annual payments of constant amount, which are paid only during the first year from today (that is, two coupons will be paid during the first year and later no coupons will be paid)
• Time to maturity: 2.5 years
• Yield to maturity: 7.0% nominal annual
• Price as of today: $103

Bond D:

• Nominal value: $100
• Coupon rate for the calculation of the first coupon: 8% effective annual.
• Annual coupons increasing at variable rate. The coupons will grow at an annual effective rate of 1% in the second year and, subsequently, the coupons will grow at an annual effective rate of 5%
• Time to maturity: 4 years
• Price as of today: $98

The portfolio will consist of 80 Bonds A, 100 Bonds B, 120 Bonds C and 75 Bonds D.

1. Calculate the nominal annual coupon rate of a Bond A.
2. Calculate the price today of a Bond B if the bond will be repurchased at the end of the third year from today.
3. Using the price obtained in the previous question, calculate the effective annual Yield to Maturity of a Bond B.
4. Calculate the semi-annual coupon to be paid in the first year of a Bond C.
5. Calculate the effective annual Yield to Maturity of a Bond D if it is purchased today.
6. Calculate the effective semi-annual Yield to Maturity of the portfolio assuming that all instruments will be purchased today and will be kept until maturity (for Bond B, assume that the bond will not be repurchased, that is, the investor will receive all cash flows until maturity, however, consider the repurchase option for determining the price at t = 0).
7. Approximate the percentage change in the value of the portfolio through its duration if the nominal annual Yield to Maturity decreases by 200 basis points. (HINT: Estimate the duration using semi-annual cash flows).

I will appreciate if you can show me how to solve it in excel