that's all the information of the question

You observe the following information in the government bond markets on 12/6/2021. Coupon rate (%) Price Maturity Date 12/06/2022 12/06/2023 FV $100.00 Coupon Frequency Annual 3.5 $97.62 4 $94.53 $100.00 Annual 12/06/2024 4.5 $90.94 $100.00 Annual 12/06/2025 5.5 $88.77 $100.00 Annual The bonds have just paid their 2021 coupons. Use Excel to complete the calculations in this question. A. (6 marks) Construct the pure yield curve. Is it an upward or downward sloping term structure? B. (5 marks) Your friend is proposing a risk-free investment opportunity that generates $1mil at the end of each of the next 4 years. The investment costs $3.3mil today. Will you take it? C. (5 marks) What is the Macaulay duration and convexity of the coupon bond maturing on 12/6/2025? D. (5 marks) Imagine that you have a loan out that must be repaid in 7 years with a present value of $1mil. You plan to fund this obligation by holding some of the coupon bond from Part C and some of a perpetuity with yield of 6%. In order to immunise your total portfolio against unexpected changes in interest rates, how much of the coupon bond and the perpetuity (in dollars) will you hold in your portfolio? E. (4 marks) Two years later, the yield curve remains unchanged. Is your portfolio still immunised? Explain. You don't need to do any calculations here. The Excel contains answers for Question 1. All parts A to E including the calculations and written answers for Question 1 should be shown in the Excel file. The Excel file should include all workings and calculations. Formulas for the calculations should have cell references where possible. You observe the following information in the government bond markets on 12/6/2021. Coupon rate (%) Price Maturity Date 12/06/2022 12/06/2023 FV $100.00 Coupon Frequency Annual 3.5 $97.62 4 $94.53 $100.00 Annual 12/06/2024 4.5 $90.94 $100.00 Annual 12/06/2025 5.5 $88.77 $100.00 Annual The bonds have just paid their 2021 coupons. Use Excel to complete the calculations in this question. A. (6 marks) Construct the pure yield curve. Is it an upward or downward sloping term structure? B. (5 marks) Your friend is proposing a risk-free investment opportunity that generates $1mil at the end of each of the next 4 years. The investment costs $3.3mil today. Will you take it? C. (5 marks) What is the Macaulay duration and convexity of the coupon bond maturing on 12/6/2025? D. (5 marks) Imagine that you have a loan out that must be repaid in 7 years with a present value of $1mil. You plan to fund this obligation by holding some of the coupon bond from Part C and some of a perpetuity with yield of 6%. In order to immunise your total portfolio against unexpected changes in interest rates, how much of the coupon bond and the perpetuity (in dollars) will you hold in your portfolio? E. (4 marks) Two years later, the yield curve remains unchanged. Is your portfolio still immunised? Explain. You don't need to do any calculations here. The Excel contains answers for Question 1. All parts A to E including the calculations and written answers for Question 1 should be shown in the Excel file. The Excel file should include all workings and calculations. Formulas for the calculations should have cell references where possible