Question
Need help with Q10 ii QUESTION 8: (18 marks) Using Excel to do this question Assume you have six different bonds: B1 A two-year bond
Need help with Q10 ii
QUESTION 8: (18 marks)
Using Excel to do this question Assume you have six different bonds:
B1 A two-year bond with a nominal rate of 2 % per annum
B2 A four-year bond with a nominal rate of 2.5 % per annum
B3 A five-year bond with a nominal rate of 3.5 % per annum
B4 A seven-year bond with a nominal rate of 4 % per annum
B5 A ten-year bond with a nominal rate of 4.5 % per annum
B6 - A twenty-year bond with a nominal rate of 5 % per annum All these bonds pay annual coupons and have face values of $4,500. Calculate their Present Values, Macauley Durations and Convexities using a YTM of 4% (YTM = 0.04).
QUESTION 9: (5 + 7 = 12 marks)
Using Excel to do this question
Suppose a fund manager is committed to making annual payments of $45,000 for the next 20 years (an annuity) and they use a discount rate of 0.04 or 4 % pa. 5
a. What is the Present Value of these payments? Calculate the Macauley Duration and Convexity.
b. To fund these payments the fund manager must invest in the six bonds described in QUESTION 8.
Assume she is trying to minimize transaction costs; use the figures in QUESTIONS 8 to write the equations that would need to be satisfied to immunize the annuity described in this question. Note that the fund manager is concerned that the application of these conditions could result in only one or two different types of bonds being held. As this is considered risky she introduces a diversification condition whereby she must hold a minimum of Five of each of B1, B2, B3, B4, B5 and B6. This condition will also need to be considered in your equations.
QUESTION 10: (15 marks) Using Excel to do this question
For the portfolio described in QUESTIONS 8 and 9, using the solver in excel, find the portfolio of bonds that the fund manager must invest in to immunize the portfolio. Find the immunized portfolio following the three major constraints in addition to the diversification constrain for two scenarios:
i. The fund manager must hold a minimum of Five of each of B1, B2, B3, B4, B5 and B6.
ii. The fund manager must hold a minimum of Fifteen of each of B1, B2, B3, B4, B5 and B6.
Explain how much of each bond the fund manager should hold for each scenario.
workings for Question 10 i
3 Face Value (FV) Coupon Rate YTM $ 4,500.00 2.00% $ 4,500.00 2.50% $ 4,500.00 3.50% $ 4,500.00 4.00% $ 4,500.00 4.50% $ 4,500.00 5.00% 4% 4% 4% 4. BOND 1 5 BOND 2 6 BOND 3 7 BOND 4 8 BOND 5 9 BOND 6 10 Bond Portfolio 11 12 13 Liability 14 PV McD Convexity $ 4,330.25 1.980015373 5.473429633 $ 4,254.98 3.851337454 17.57777879 $ 4,399.83 4.668785787 25.29874384 $ 4,500.00 6.242136857 44.36116175 $ 4,682.50 8.308351966 79.05439438 $ 5,111.56 13.54471812 225.8538087 $611,564.69 9.20912507 137.9267633 4% 4% 4% YTM Face Value (FV) = Coupon (CF) 0 $ 45,000.00 PV $ 611,564.69 McD Convexity 9.209124836 116.7401193 4% 15 16 17 (Holding mins bond each) Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Total 18 Number of bonds 39.20 5.00 5.00 5.00 5.00 68.99 128.19 19 Price $ 4,330.25 $ 4,254.98 $ 4,399.83 $ 4,500.00 $ 4,682.50 $ 5,111.56 20 Value $ 169,749.48 $ 21,274.91 $ 21,999.17 $ 22,500.00 $ 23,412.48 $352,628.65 $611,564.69 21 McD 1.980 3.851 4.669 6.242136857 8.308351966 13.54471812 9.20912507 22 Convexity 5.473 17.578 25.299 44.36116175 79.05439438 225.8538087 137.9267633 23 Weights 27.76% 3.48% 3.60% 3.68% 3.83% 57.66% 100.00% 24 25 26 Minimum nuber of bonds 128.19 27 PV (Bonds) $611,564.69 28 Portfolio McD 9.20912507 29 Portfolio Convexity 137.9267633 30 Liability Convesity 116.7401193
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started