question 8

question 9

Bond - B1 Year Cashflow PVF@4% Present Value Weight Year * Weight Convexity 1 $ 90.00 0.961538462 $ 86.54 2.00% 0.019984627$ 173.08 2 $4,590.00 0.924556213 $ 4,243.71 98.00% 1.960030746 $ 25,462.28 $ 4,330.25 1 1.980015373 25635.355031 Present Value of the bond = $ 4,330.25 Macaulay Duration = 1.980015373 Convexity of the bond = 5.473429633 Bond - B2 Year Cashflow PVF@4% Present Value Weight Year * Weight Convexity 1 $ 112.50 0.961538462 $ 108.17 2.54% 0.025422687$ 216.35 2 $ 112.50 0.924556213 $ 104.01 2.44% 0.048889782$ 624.08 3 $ 112.50 0.888996359 $ 100.01 2.35% 0.070514109$ 1,200.15 4 $4,612.50 0.854804191 $ 3,942.78 92.66% 3.706510875 $ 78,855.69 $ 4,254.98 1 3.851337454$ 80,896.25 Present Value of the bond = $ 4,254.98 Macaulay Duration = 3.851337454 Convexity of the bond = 17.57777879 Bond B3 Year] Cashflow PVF@4% Present Value Weight 1 $ 157.50 0.961538462 $ 151.44 3.44% 2 $ 157.50 0.924556213 $ 145.62 3.31% 3 $ 157.500.888996359 $ 140.02 3.18% 4 $ 157.50 0.854804191 $ 134.63 3.06% 5 $4,657.50 0.821927107 $ 3,828.13 87.01% $ 4,399.83 1 Present Value of the bond = $ 4,399.83 Macaulay Duration = 4.668785787 Convexity of the bond = 25.29874384 Year * Weight Convexity 0.034420005 $ 302.88 0.066192317 $ 873.71 0.095469688$ 1,680.20 0.122397036 S 2,692.63 4.350306741 $ 114,843.76 4.668785787$ 120,393.19 Bond - B4 Year Cashflow PVF@4% Present Value Weight 1 $ 180.00 0.961538462$ 173.08 3.85% 2 $ 180.00 0.924556213 $ 166.42 3.70% 3 $ 180.00 0.888996359 $ 160.02 3.56% 4 $ 180.00 0.854804191) $ 153.86 3.42% 5 $ 180.00 0.8219271071 $ 147.95 3.29% 6 $ 180.000.790314526 $ 142.26 3.16% 7 $4,680.00 0.759917813 $ 3,556.42 79.03% $ 4,500.00 1 Present Value of the bond = $ 4,500.00 Macaulay Duration = 6.242136857 Convexity of the bond = 44.36116175 Year * Weight Convexity 0.038461538 S 346.15 0.073964497 $ 998.52 0.106679563 $ 1,920.23 0.136768671 $ 3,077.30 0.164385421 $ 4,438.41 0.189675486 S 5,974.78 5.53220168 $ 199,159.26 6.242136857 $ 215,914.65 Year * Weight 0.04158286 0.079967038 0.115337074 0.147868043 Bond - B5 Year Cashflow PVF@4% Present Value Weight 1 $ 202.50 0.961538462 $ 194.71 4.16% 2 $ 202.50 0.924556213 $ 187.22 4.00% 3 $ 202.50 0.888996359 $ 180.02 3.84% 4 $ 202.50 0.854804191 $ 173.10 3.70% 5 $ 202.50 0.821927107 $ 166.44 3.55% 6 $ 202.50 0.790314526 $ 160.04 3.42% 7 $ 202.50 0.759917813 $ 153.88 3.29% 8 $ 202.50 0.730690205 $ 147.96 3.16% 9 $ 202.50 0.702586736 $ 142.27 3.04% 10 $4,702.50 0.675564169 $ 3,176.84 67.85% $ 4,682.50 1 Present Value of the bond = $ 4,682.50 Macaulay Duration = 8.308351966 Convexity of the bond = 79.05439438 Cova UwN 0.177726013 Convexity $ 389.42 $ 1,123.34 $ 2,160.26 $ 3,461.96 $ 4,993.21 $ 6,721.63 $ 8,617.47 $ 10,653.46 $ 12,804.64 $ 349,452.46 $ 400,377.84 0.205068477 0.230044766 0.252796446 0.273457694 6.784503558 8.308351966 Year Cashflow PVF@4% Present Value 1 $ 225.00 0.961538462 $ 216.35 2 $ 225.00 0.924556213 $ 208.03 3 $ 225.00 0.888996359 $ 200.02 4 $ 225.00 0.854804191 $ 192.33 5 $ 225.00 0.821927107 $ 184.93 6 $ 225.00 0.7903145261 $ 177.82 7 $ 225.00 0.759917813 $ 170.98 8 $ 225.00 0.730690205 $ 164.41 9 $ 225.00 0.702586736 $ 158.08 10 $ 225.00 0.675564169 $ 152.00 11 $ 225.00 0.649580932 $ 146.16 12 $ 225.00 0.62459705 $ 140.53 13 $ 225.00 0.600574086 $ 135.13 14 $ 225.00 0.577475083 $ 129.93 15 $ 225.000.555264503 $ 124.93 16 $ 225.00 0.533908176 $ 120.13 17 $ 225.00 0.513373246 $ 115.51 18 $ 225.00 0.493628121 $ 111.07 19 $ 225.00 0.474642424 $ 106.79 20 $4,725.00 0.456386946 $ 2,156.43 $ 5,111.56 Present Value of the bond = $ 5,111.56 Macaulay Duration = 13.54471812 Convexity of the bond = 225.8538087 Weight 4.23% 4.07% 3.91% 3.76% 3.62% 3.48% 3.34% 3.22% 3.09% 2.97% 2.86% 2.75% 2.64% 2.54% 2.44% 2.35% 2.26% 2.17% 2.09% 42.19% Year * Weight Convexity 0.042324839 $ 432.69 0.081393922 $ 1,248.15 0.117395079 $ 2,400.29 0.150506512$ 3,846.62 0.18089725 $ 5,548.01 0.208727596 $ 7,468.47 0.234149547 $ 9,574.96 0.257307195$ 11,837.18 0.278337109 $ 14,227.38 0.297368707 $ 16,720.21 0.314524594 $ 19,292.55 0.329920902 $ 21,923.36 0.343667607 24,593.51 0.355868824 $ 27,285.70 0.366623101 $ 29,984.28 0.376023693 $ 32,675.18 0.384158821$ 35,345.75 0.391111922 $ 37,984.68 0.396961887 $ 40,581.93 8.437449014 $ 905,699.89 13.54471812 $ 1,248,670.81 1 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. Bond - B1 Year Cashflow PVF@4% Present Value Weight Year * Weight Convexity 1 $ 90.00 0.961538462 $ 86.54 2.00% 0.019984627$ 173.08 2 $4,590.00 0.924556213 $ 4,243.71 98.00% 1.960030746 $ 25,462.28 $ 4,330.25 1 1.980015373 25635.355031 Present Value of the bond = $ 4,330.25 Macaulay Duration = 1.980015373 Convexity of the bond = 5.473429633 Bond - B2 Year Cashflow PVF@4% Present Value Weight Year * Weight Convexity 1 $ 112.50 0.961538462 $ 108.17 2.54% 0.025422687$ 216.35 2 $ 112.50 0.924556213 $ 104.01 2.44% 0.048889782$ 624.08 3 $ 112.50 0.888996359 $ 100.01 2.35% 0.070514109$ 1,200.15 4 $4,612.50 0.854804191 $ 3,942.78 92.66% 3.706510875 $ 78,855.69 $ 4,254.98 1 3.851337454$ 80,896.25 Present Value of the bond = $ 4,254.98 Macaulay Duration = 3.851337454 Convexity of the bond = 17.57777879 Bond B3 Year] Cashflow PVF@4% Present Value Weight 1 $ 157.50 0.961538462 $ 151.44 3.44% 2 $ 157.50 0.924556213 $ 145.62 3.31% 3 $ 157.500.888996359 $ 140.02 3.18% 4 $ 157.50 0.854804191 $ 134.63 3.06% 5 $4,657.50 0.821927107 $ 3,828.13 87.01% $ 4,399.83 1 Present Value of the bond = $ 4,399.83 Macaulay Duration = 4.668785787 Convexity of the bond = 25.29874384 Year * Weight Convexity 0.034420005 $ 302.88 0.066192317 $ 873.71 0.095469688$ 1,680.20 0.122397036 S 2,692.63 4.350306741 $ 114,843.76 4.668785787$ 120,393.19 Bond - B4 Year Cashflow PVF@4% Present Value Weight 1 $ 180.00 0.961538462$ 173.08 3.85% 2 $ 180.00 0.924556213 $ 166.42 3.70% 3 $ 180.00 0.888996359 $ 160.02 3.56% 4 $ 180.00 0.854804191) $ 153.86 3.42% 5 $ 180.00 0.8219271071 $ 147.95 3.29% 6 $ 180.000.790314526 $ 142.26 3.16% 7 $4,680.00 0.759917813 $ 3,556.42 79.03% $ 4,500.00 1 Present Value of the bond = $ 4,500.00 Macaulay Duration = 6.242136857 Convexity of the bond = 44.36116175 Year * Weight Convexity 0.038461538 S 346.15 0.073964497 $ 998.52 0.106679563 $ 1,920.23 0.136768671 $ 3,077.30 0.164385421 $ 4,438.41 0.189675486 S 5,974.78 5.53220168 $ 199,159.26 6.242136857 $ 215,914.65 Year * Weight 0.04158286 0.079967038 0.115337074 0.147868043 Bond - B5 Year Cashflow PVF@4% Present Value Weight 1 $ 202.50 0.961538462 $ 194.71 4.16% 2 $ 202.50 0.924556213 $ 187.22 4.00% 3 $ 202.50 0.888996359 $ 180.02 3.84% 4 $ 202.50 0.854804191 $ 173.10 3.70% 5 $ 202.50 0.821927107 $ 166.44 3.55% 6 $ 202.50 0.790314526 $ 160.04 3.42% 7 $ 202.50 0.759917813 $ 153.88 3.29% 8 $ 202.50 0.730690205 $ 147.96 3.16% 9 $ 202.50 0.702586736 $ 142.27 3.04% 10 $4,702.50 0.675564169 $ 3,176.84 67.85% $ 4,682.50 1 Present Value of the bond = $ 4,682.50 Macaulay Duration = 8.308351966 Convexity of the bond = 79.05439438 Cova UwN 0.177726013 Convexity $ 389.42 $ 1,123.34 $ 2,160.26 $ 3,461.96 $ 4,993.21 $ 6,721.63 $ 8,617.47 $ 10,653.46 $ 12,804.64 $ 349,452.46 $ 400,377.84 0.205068477 0.230044766 0.252796446 0.273457694 6.784503558 8.308351966 Year Cashflow PVF@4% Present Value 1 $ 225.00 0.961538462 $ 216.35 2 $ 225.00 0.924556213 $ 208.03 3 $ 225.00 0.888996359 $ 200.02 4 $ 225.00 0.854804191 $ 192.33 5 $ 225.00 0.821927107 $ 184.93 6 $ 225.00 0.7903145261 $ 177.82 7 $ 225.00 0.759917813 $ 170.98 8 $ 225.00 0.730690205 $ 164.41 9 $ 225.00 0.702586736 $ 158.08 10 $ 225.00 0.675564169 $ 152.00 11 $ 225.00 0.649580932 $ 146.16 12 $ 225.00 0.62459705 $ 140.53 13 $ 225.00 0.600574086 $ 135.13 14 $ 225.00 0.577475083 $ 129.93 15 $ 225.000.555264503 $ 124.93 16 $ 225.00 0.533908176 $ 120.13 17 $ 225.00 0.513373246 $ 115.51 18 $ 225.00 0.493628121 $ 111.07 19 $ 225.00 0.474642424 $ 106.79 20 $4,725.00 0.456386946 $ 2,156.43 $ 5,111.56 Present Value of the bond = $ 5,111.56 Macaulay Duration = 13.54471812 Convexity of the bond = 225.8538087 Weight 4.23% 4.07% 3.91% 3.76% 3.62% 3.48% 3.34% 3.22% 3.09% 2.97% 2.86% 2.75% 2.64% 2.54% 2.44% 2.35% 2.26% 2.17% 2.09% 42.19% Year * Weight Convexity 0.042324839 $ 432.69 0.081393922 $ 1,248.15 0.117395079 $ 2,400.29 0.150506512$ 3,846.62 0.18089725 $ 5,548.01 0.208727596 $ 7,468.47 0.234149547 $ 9,574.96 0.257307195$ 11,837.18 0.278337109 $ 14,227.38 0.297368707 $ 16,720.21 0.314524594 $ 19,292.55 0.329920902 $ 21,923.36 0.343667607 24,593.51 0.355868824 $ 27,285.70 0.366623101 $ 29,984.28 0.376023693 $ 32,675.18 0.384158821$ 35,345.75 0.391111922 $ 37,984.68 0.396961887 $ 40,581.93 8.437449014 $ 905,699.89 13.54471812 $ 1,248,670.81 1 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