Please solve for data group 3. Thank you in advance!

Please solve for data group 3. Thank you in advance!

Don't know for sure if this is relevant to the question:

Question 4: Bond Portfolio Management Pension funds pay lifetime annuities to recipients. If a firm remains in business indefi- nitely, the pension obligation will resemble a perpetuity. Suppose, therefore, that you are managing a pension fund with obligations to make perpetual payments. Your task is to immunize the value of your portfolio, using two bonds. The yield curve is flat at 9% and data on the coupons and maturities of the two bonds as well as the size of the obligation are given in Table 3. In this Table, bond maturities are expressed in years and the obligation is expressed in millions of EUR, paid yearly to beneficiaries. Table 3: Data to solve Question 4 for each data group Data group 1 Data group 2 Data group 3 Data group 4 Bond 1 Coupon 7% 1% 2% 2% Maturity 10 15 20 25 Bond 2 3% 5% 7% 8% Coupon Maturity 25 20 15 10 . Obligation 2 2.5 3 3.5 Report: The duration of your obligations, Bond 1, and Bond 2. The present value of your obligations (in million EUR). The weights and market values (in million EUR) of the two bonds that fully immunize your portfolio. Note: The solution to this problem involves solving a linear equation with one unknown. Do not use Excel's solver to find the solution, because the solver does not do well with solving linear problems. The market value of the portfolio cash flows in the subsequent 15 years, stemming jointly from the two bonds coupon maturity yield 1 1.375 15-May-13 2 1.375 15-Nov-13 3 3.625 15-May-14 4 4.25 15-Nov-14 5 1 15-May-15 6 0.375 15-Nov-15 7 4.125 15-May-16 8 4.5 15-Nov-16 9 1.875 15-Jun-13 10 1.125 15-Dec-13 11 1.125 15-Jun-14 12 0.75 15-Dec-14 13 0.75 15-Jun-15 14 2.625 15-Dec-15 15 1.875 15-Jun-16 16 2.125 15-Dec-16 17 1.5 15-Jul-13 18 1.375 15-Jan-14 19 1 15-Jul-14 20 1.75 15-Jan-15 21 0.625 15-Jul-15 2.25 15-Jan-16 23 1.75 15-Jul-16 24 2 15-Jan-17 25 4.375 15-Aug-13 26 1.375 15-Feb-14 27 0.75 15-Aug-14 28 1.25 15-Feb-15 29 0.5 15-Aug-15 30 11.25 15-Feb-16 31 10.625 15-Aug-16 9.25 15-Feb-17 33 1.75 15-Apr-13 0.5 15-Oct-13 1.25 15-Apr-14 36 0.5 15-Oct-14 37 2.5 15-Apr-15 38 1.25 15-Oct-15 2.625 15-Apr-16 40 3.125 15-Oct-16 0.037 0.135 0.213 0.273 0.347 0.403 0.493 0.627 0.05 0.158 0.232 0.287 0.356 0.44 0.55 0.656 0.068 0.157 0.237 0.292 0.366 0.445 0.566 0.691 0.066 0.174 0.251 0.311 0.374 0.447 0.524 0.63 0.193 0.27 0.333 0.392 0.507 0.638 0.755 0.903 22 32 34 35 39 Answers Question 4: Duration Present Value Obligations Bond 1 Bond 2 Portfolio Immunization: Weight Market Value Bond 1 Bond 2 Market Value Cash Flows: Bond 1 + Bond 2 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 0.37 0.29 DATE 01/31/2005 02/28/2005 03/31/2005 04/30/2005 05/31/2005 06/30/2005 07/31/2005 08/31/2005 09/30/2005 10/31/2005 11/30/2005 12/31/2005 01/31/2006 02/28/2006 03/31/2006 04/30/2006 05/31/2006 06/30/2006 07/31/2006 08/31/2006 09/30/2006 10/31/2006 11/30/2006 12/31/2006 01/31/2007 02/28/2007 03/31/2007 04/30/2007 05/31/2007 06/30/2007 07/31/2007 08/31/2007 09/30/2007 10/31/2007 11/30/2007 12/31/2007 01/31/2008 02/29/2008 03/31/2008 04/30/2008 05/31/2008 06/30/2008 07/31/2008 08/31/2008 09/30/2008 10/31/2008 11/30/2008 12/31/2008 01/31/2009 02/28/2009 03/31/2009 04/30/2009 05/31/2009 06/30/2009 07/31/2009 Mkt_RF SMB HML MOM RF -2.61 -1.27 1.58 3.27 2.2 -0.16 1.69 4 -1.68 -1.44 1.36 1.04 -2.86 -3.89 -0.24 -0.94 3.37 2.47 -0.78 0.2 0.95 2.82 2.37 2.81 4.03 2.59 -0.87 0.8 -0.74 -0.54 1.53 3.6 0.9 0.07 1.44 3.84 -2.5 -1.9 -0.7 -2.76 3.85 1.19 -0.73 0.74 0.15 0.24 0.18 1.72 3.5 5.64 0.46 4.86 -0.52 -0.44 0.57 -2.79 1.41 2.76 0.29 1.87 1.16 -0.11 2.56 1.34 -3.38 -2.71 2.28 -2.85 -0.53 -0.81 -0.73 -2.96 2.68 -1.34 2.13 0.68 -0.91 -3.21 1.32 -2.41 0.74 -1.82 3.3 2.11 -0.74 -0.06 1.74 -0.07 0.33 -0.57 0.66 -0.77 2.5 0.52 1.26 -0.16 -0.59 0.68 -1.84 1.78 0.17 -0.9 0.92 0.04 0.36 2.11 3.86 -1.38 -1.15 0.38 3.66 -0.11 0.28 -0.21 -1.72 0.62 -0.48 0.64 -3.42 -2.15 -2.93 2.65 0.63 -1.4 -0.99 -0.15 3.77 -1.31 -2.28 5.16 2.37 1.29 -3.67 5.38 -5.42 -3.68 -1.04 -0.6 -0.57 0.65 -0.77 5.22 -6.37 -1.55 4.73 -8.36 -2.27 0.61 -0.61 6.94 -1.28 -0.43 0.38 0.74 4.72 -2.04 -0.13 1.44 2.39 3.07 -2.39 3.24 -7.9 1.16 -3.11 11.3 -1.59 2.12 1.73 -7.47 1 1.74 1.22 -5.11 -9.99 -1.5 3.85 -2.82 -18.23 -3.77 -0.62 5.78 -7.75 -4.33 -2.94 5.66 1.4 4.58 -1.61 -3.48 -7.37 0.22 -7.47 -2.14 -9.85 -0.16 -5.16 2.72 8.73 -0.11 0.99 -8.18 10.46 5.33 4.02 -24.83 6.63 -1.17 -0.22 -11.06 -0.21 2.07 -3.03 4.59 8.1 1.28 5.11 -4.46 0.16 0.16 0.21 0.21 0.24 0.23 0.24 0.3 0.29 0.27 0.31 0.32 0.35 0.34 0.37 0.36 0.43 0.4 0.4 0.42 0.41 0.41 0.42 0.4 0.44 0.38 0.43 0.44 0.41 0.4 0.4 0.42 0.32 0.32 0.34 0.27 0.21 0.13 0.17 0.18 0.18 0.17 0.15 0.13 0.15 0.08 0.03 0 0 0 0.01 0.02 0.01 0 0.01 0.01 0.01 0.01 0 0 0.01 0 0 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0 0 0 0 0.01 0 08/31/2009 09/30/2009 10/31/2009 11/30/2009 12/31/2009 01/31/2010 02/28/2010 03/31/2010 04/30/2010 05/31/2010 06/30/2010 07/31/2010 08/31/2010 09/30/2010 10/31/2010 11/30/2010 12/31/2010 01/31/2011 02/28/2011 03/31/2011 04/30/2011 05/31/2011 06/30/2011 07/31/2011 08/31/2011 09/30/2011 10/31/2011 11/30/2011 12/31/2011 01/31/2012 02/29/2012 03/31/2012 04/30/2012 05/31/2012 06/30/2012 07/31/2012 08/31/2012 09/30/2012 10/31/2012 11/30/2012 12/31/2012 01/31/2013 02/28/2013 03/31/2013 04/30/2013 05/31/2013 06/30/2013 07/31/2013 08/31/2013 09/30/2013 10/31/2013 11/30/2013 12/31/2013 01/31/2014 02/28/2014 03/31/2014 04/30/2014 5.25 1.71 -2.52 0 0 0.52 0.68 2.23 1.5 1.86 -2.33 -3.65 1.02 -2.49 -1.63 -1.37 0.13 3.22 1.32 0.28 -1.75 -2.11 -1.5 -0.12 -1.64 -1.82 -0.2 -0.08 -0.12 3.29 -3.22 0.45 0.81 -0.49 0.48 0.46 0 3.15 4.49 -2.75 5.76 3.02 -3.58 3.68 6.32 1.98 -7.84 -5.42 6.93 -4.46 9.43 3.9 0.83 6.79 1.98 4.03 0.4 2.78 -1.5 -1.82 -2.25 -5.87 -8.48 11.49 -0.51 0.42 5.19 4.31 2.33 -0.6 -6.7 3.73 1.05 2.69 2.81 -1.61 0.65 1.27 5.31 0.97 3.7 1.29 2.49 -1.54 5.68 -2.56 3.78 4.15 2.78 2.63 -3.44 4.69 0.63 03 -6.63 -4.06 0.72 1.73 2.46 -4.21 3.29 4.02 1.97 -0.72 -2.5 1.53 1.04 1.22 1.39 2.37 -2.62 -0.42 2.07 2.14 0.06 -0.59 1.32 1.32 -1.09 -3.16 -0.49 2.88 1.15 -6.08 1.25 0.85 2.87 6.1 -0.65 1.84 0 -0.67 2.81 -3.09 -2.33 5.4 0.79 0.93 1.36 3.69 0.85 -0.81 0.17 -1.48 3.55 0.78 3.86 1.16 -2.17 2.12 2.13 0.09 -1.03 -0.78 -0.82 -2.94 -4.17 3.33 -0.95 -0.85 2.46 -1.54 -1.33 -0.88 -0.62 0.44 -2.21 0.93 1.03 -0.7 0.13 1.79 0.1 -0.9 0.67 -2.89 1.44 0.76 1.76 0.5 2.18 -1.73 0.78 -0.52 1.12 0.4 -1.58 -3.5 0 0 0 0 0 0.01 0 0 0.01 0.01 0.01 0.01 0.01 0.47 -2.2 -1.8 0 0 0 0 0 0.44 0.72 3.29 -0.84 3.09 1.71 0.09 -0.37 0.28 1.26 0.77 0.31 -2.04 -1.26 0.51 -0.43 -0.05 -1.48 -0.56 4.7 1.64 0 0.45 0.96 -2.15 -0.72 2.18 2.15 1.53 0.14 0.96 1.1 -0.92 3.39 0.28 1.9 0.26 1.3 1.12 -3.06 -3.8 0 0 0 0 0 0 0 0 oooon 0 0 05/31/2014 06/30/2014 07/31/2014 08/31/2014 09/30/2014 10/31/2014 11/30/2014 12/31/2014 1.99 2.8 -2.04 4.06 -2.44 2.03 2.23 -0.2 -1.91 3.17 -3.97 0.34 -3.55 2.47 -2.13 1.78 -0.53 0.38 -0.07 -1.06 -2.75 -3.47 -3.04 0.76 1.1 0.74 -0.19 0.96 0.18 -0.96 1.19 0.34 0 0 0 0 0 0 0 Question 4: Bond Portfolio Management Pension funds pay lifetime annuities to recipients. If a firm remains in business indefi- nitely, the pension obligation will resemble a perpetuity. Suppose, therefore, that you are managing a pension fund with obligations to make perpetual payments. Your task is to immunize the value of your portfolio, using two bonds. The yield curve is flat at 9% and data on the coupons and maturities of the two bonds as well as the size of the obligation are given in Table 3. In this Table, bond maturities are expressed in years and the obligation is expressed in millions of EUR, paid yearly to beneficiaries. Table 3: Data to solve Question 4 for each data group Data group 1 Data group 2 Data group 3 Data group 4 Bond 1 Coupon 7% 1% 2% 2% Maturity 10 15 20 25 Bond 2 3% 5% 7% 8% Coupon Maturity 25 20 15 10 . Obligation 2 2.5 3 3.5 Report: The duration of your obligations, Bond 1, and Bond 2. The present value of your obligations (in million EUR). The weights and market values (in million EUR) of the two bonds that fully immunize your portfolio. Note: The solution to this problem involves solving a linear equation with one unknown. Do not use Excel's solver to find the solution, because the solver does not do well with solving linear problems. The market value of the portfolio cash flows in the subsequent 15 years, stemming jointly from the two bonds coupon maturity yield 1 1.375 15-May-13 2 1.375 15-Nov-13 3 3.625 15-May-14 4 4.25 15-Nov-14 5 1 15-May-15 6 0.375 15-Nov-15 7 4.125 15-May-16 8 4.5 15-Nov-16 9 1.875 15-Jun-13 10 1.125 15-Dec-13 11 1.125 15-Jun-14 12 0.75 15-Dec-14 13 0.75 15-Jun-15 14 2.625 15-Dec-15 15 1.875 15-Jun-16 16 2.125 15-Dec-16 17 1.5 15-Jul-13 18 1.375 15-Jan-14 19 1 15-Jul-14 20 1.75 15-Jan-15 21 0.625 15-Jul-15 2.25 15-Jan-16 23 1.75 15-Jul-16 24 2 15-Jan-17 25 4.375 15-Aug-13 26 1.375 15-Feb-14 27 0.75 15-Aug-14 28 1.25 15-Feb-15 29 0.5 15-Aug-15 30 11.25 15-Feb-16 31 10.625 15-Aug-16 9.25 15-Feb-17 33 1.75 15-Apr-13 0.5 15-Oct-13 1.25 15-Apr-14 36 0.5 15-Oct-14 37 2.5 15-Apr-15 38 1.25 15-Oct-15 2.625 15-Apr-16 40 3.125 15-Oct-16 0.037 0.135 0.213 0.273 0.347 0.403 0.493 0.627 0.05 0.158 0.232 0.287 0.356 0.44 0.55 0.656 0.068 0.157 0.237 0.292 0.366 0.445 0.566 0.691 0.066 0.174 0.251 0.311 0.374 0.447 0.524 0.63 0.193 0.27 0.333 0.392 0.507 0.638 0.755 0.903 22 32 34 35 39 Answers Question 4: Duration Present Value Obligations Bond 1 Bond 2 Portfolio Immunization: Weight Market Value Bond 1 Bond 2 Market Value Cash Flows: Bond 1 + Bond 2 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 0.37 0.29 DATE 01/31/2005 02/28/2005 03/31/2005 04/30/2005 05/31/2005 06/30/2005 07/31/2005 08/31/2005 09/30/2005 10/31/2005 11/30/2005 12/31/2005 01/31/2006 02/28/2006 03/31/2006 04/30/2006 05/31/2006 06/30/2006 07/31/2006 08/31/2006 09/30/2006 10/31/2006 11/30/2006 12/31/2006 01/31/2007 02/28/2007 03/31/2007 04/30/2007 05/31/2007 06/30/2007 07/31/2007 08/31/2007 09/30/2007 10/31/2007 11/30/2007 12/31/2007 01/31/2008 02/29/2008 03/31/2008 04/30/2008 05/31/2008 06/30/2008 07/31/2008 08/31/2008 09/30/2008 10/31/2008 11/30/2008 12/31/2008 01/31/2009 02/28/2009 03/31/2009 04/30/2009 05/31/2009 06/30/2009 07/31/2009 Mkt_RF SMB HML MOM RF -2.61 -1.27 1.58 3.27 2.2 -0.16 1.69 4 -1.68 -1.44 1.36 1.04 -2.86 -3.89 -0.24 -0.94 3.37 2.47 -0.78 0.2 0.95 2.82 2.37 2.81 4.03 2.59 -0.87 0.8 -0.74 -0.54 1.53 3.6 0.9 0.07 1.44 3.84 -2.5 -1.9 -0.7 -2.76 3.85 1.19 -0.73 0.74 0.15 0.24 0.18 1.72 3.5 5.64 0.46 4.86 -0.52 -0.44 0.57 -2.79 1.41 2.76 0.29 1.87 1.16 -0.11 2.56 1.34 -3.38 -2.71 2.28 -2.85 -0.53 -0.81 -0.73 -2.96 2.68 -1.34 2.13 0.68 -0.91 -3.21 1.32 -2.41 0.74 -1.82 3.3 2.11 -0.74 -0.06 1.74 -0.07 0.33 -0.57 0.66 -0.77 2.5 0.52 1.26 -0.16 -0.59 0.68 -1.84 1.78 0.17 -0.9 0.92 0.04 0.36 2.11 3.86 -1.38 -1.15 0.38 3.66 -0.11 0.28 -0.21 -1.72 0.62 -0.48 0.64 -3.42 -2.15 -2.93 2.65 0.63 -1.4 -0.99 -0.15 3.77 -1.31 -2.28 5.16 2.37 1.29 -3.67 5.38 -5.42 -3.68 -1.04 -0.6 -0.57 0.65 -0.77 5.22 -6.37 -1.55 4.73 -8.36 -2.27 0.61 -0.61 6.94 -1.28 -0.43 0.38 0.74 4.72 -2.04 -0.13 1.44 2.39 3.07 -2.39 3.24 -7.9 1.16 -3.11 11.3 -1.59 2.12 1.73 -7.47 1 1.74 1.22 -5.11 -9.99 -1.5 3.85 -2.82 -18.23 -3.77 -0.62 5.78 -7.75 -4.33 -2.94 5.66 1.4 4.58 -1.61 -3.48 -7.37 0.22 -7.47 -2.14 -9.85 -0.16 -5.16 2.72 8.73 -0.11 0.99 -8.18 10.46 5.33 4.02 -24.83 6.63 -1.17 -0.22 -11.06 -0.21 2.07 -3.03 4.59 8.1 1.28 5.11 -4.46 0.16 0.16 0.21 0.21 0.24 0.23 0.24 0.3 0.29 0.27 0.31 0.32 0.35 0.34 0.37 0.36 0.43 0.4 0.4 0.42 0.41 0.41 0.42 0.4 0.44 0.38 0.43 0.44 0.41 0.4 0.4 0.42 0.32 0.32 0.34 0.27 0.21 0.13 0.17 0.18 0.18 0.17 0.15 0.13 0.15 0.08 0.03 0 0 0 0.01 0.02 0.01 0 0.01 0.01 0.01 0.01 0 0 0.01 0 0 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0 0 0 0 0.01 0 08/31/2009 09/30/2009 10/31/2009 11/30/2009 12/31/2009 01/31/2010 02/28/2010 03/31/2010 04/30/2010 05/31/2010 06/30/2010 07/31/2010 08/31/2010 09/30/2010 10/31/2010 11/30/2010 12/31/2010 01/31/2011 02/28/2011 03/31/2011 04/30/2011 05/31/2011 06/30/2011 07/31/2011 08/31/2011 09/30/2011 10/31/2011 11/30/2011 12/31/2011 01/31/2012 02/29/2012 03/31/2012 04/30/2012 05/31/2012 06/30/2012 07/31/2012 08/31/2012 09/30/2012 10/31/2012 11/30/2012 12/31/2012 01/31/2013 02/28/2013 03/31/2013 04/30/2013 05/31/2013 06/30/2013 07/31/2013 08/31/2013 09/30/2013 10/31/2013 11/30/2013 12/31/2013 01/31/2014 02/28/2014 03/31/2014 04/30/2014 5.25 1.71 -2.52 0 0 0.52 0.68 2.23 1.5 1.86 -2.33 -3.65 1.02 -2.49 -1.63 -1.37 0.13 3.22 1.32 0.28 -1.75 -2.11 -1.5 -0.12 -1.64 -1.82 -0.2 -0.08 -0.12 3.29 -3.22 0.45 0.81 -0.49 0.48 0.46 0 3.15 4.49 -2.75 5.76 3.02 -3.58 3.68 6.32 1.98 -7.84 -5.42 6.93 -4.46 9.43 3.9 0.83 6.79 1.98 4.03 0.4 2.78 -1.5 -1.82 -2.25 -5.87 -8.48 11.49 -0.51 0.42 5.19 4.31 2.33 -0.6 -6.7 3.73 1.05 2.69 2.81 -1.61 0.65 1.27 5.31 0.97 3.7 1.29 2.49 -1.54 5.68 -2.56 3.78 4.15 2.78 2.63 -3.44 4.69 0.63 03 -6.63 -4.06 0.72 1.73 2.46 -4.21 3.29 4.02 1.97 -0.72 -2.5 1.53 1.04 1.22 1.39 2.37 -2.62 -0.42 2.07 2.14 0.06 -0.59 1.32 1.32 -1.09 -3.16 -0.49 2.88 1.15 -6.08 1.25 0.85 2.87 6.1 -0.65 1.84 0 -0.67 2.81 -3.09 -2.33 5.4 0.79 0.93 1.36 3.69 0.85 -0.81 0.17 -1.48 3.55 0.78 3.86 1.16 -2.17 2.12 2.13 0.09 -1.03 -0.78 -0.82 -2.94 -4.17 3.33 -0.95 -0.85 2.46 -1.54 -1.33 -0.88 -0.62 0.44 -2.21 0.93 1.03 -0.7 0.13 1.79 0.1 -0.9 0.67 -2.89 1.44 0.76 1.76 0.5 2.18 -1.73 0.78 -0.52 1.12 0.4 -1.58 -3.5 0 0 0 0 0 0.01 0 0 0.01 0.01 0.01 0.01 0.01 0.47 -2.2 -1.8 0 0 0 0 0 0.44 0.72 3.29 -0.84 3.09 1.71 0.09 -0.37 0.28 1.26 0.77 0.31 -2.04 -1.26 0.51 -0.43 -0.05 -1.48 -0.56 4.7 1.64 0 0.45 0.96 -2.15 -0.72 2.18 2.15 1.53 0.14 0.96 1.1 -0.92 3.39 0.28 1.9 0.26 1.3 1.12 -3.06 -3.8 0 0 0 0 0 0 0 0 oooon 0 0 05/31/2014 06/30/2014 07/31/2014 08/31/2014 09/30/2014 10/31/2014 11/30/2014 12/31/2014 1.99 2.8 -2.04 4.06 -2.44 2.03 2.23 -0.2 -1.91 3.17 -3.97 0.34 -3.55 2.47 -2.13 1.78 -0.53 0.38 -0.07 -1.06 -2.75 -3.47 -3.04 0.76 1.1 0.74 -0.19 0.96 0.18 -0.96 1.19 0.34 0 0 0 0 0 0 0