Please fill in the given excel file for the following bond problems

1. Given the information about Target?s bond, Symbol: TGT.IJ and CUSIP: 87612EAU0, on FINRA's website: http://www.finra.org/, what is the yield on this bond based on the last trade date 11/02/2016?

2. Suppose that a life insurance company has issued a guaranteed investment contract such that a liability of $1 million must be paid five years from now. We assume that the amount is fixed and is not adjusted for inflation over the time. The life insurance company must invest enough money today to meet this future obligation. Let us also assume that the investment universe available to the life insurance company consists of two bonds, B1 and B2, with face values of $100, maturities of 12 years and 5 years, and coupon rates of 6% and 5%, respectively. Further, the coupons are paid semiannually and the discount rate is 5.21%.

2.1 What is the duration of each bond? 2.2 How much should the life insurance company invest in the two bonds so that the overall portfolio (bonds and liability) is immunized against changes in interest rates?

3. Consider an asset manager who is managing funds to satisfy the liabilities for a four-year annuity for policyholders and assume that the policy calls for semi-annual payments over the next four years based on an assumed expected life of four additional years. The life insurance company?s asset manager is considering investing in five different high-investment-grade bonds (B1 to B5). The relevant information is provided in the following table:

Information about the available bonds. Bond Types B1 B2 B3 B4 B5 Par Values $ 100.00 $100.00 $100.00 $100.00 $100.00 Maturity (in Yrs) 2.5 3 3.5 4 4 Coupon Rate 5% 10% 6% 8% 7% Frequency 2 2 2 2 2 Current Price $102.36 $110.83 $96.94 $114.65 $96.63 Periods Cash Obligations 1 $ 100,000.00 2 $ 200,000.00 3 $ 100,000.00 4 $ 200,000.00 5 $ 800,000.00 6 $ 1,200,000.00 7 $ 400,000.00 8 $ 1,000,000.00

If the asset manager would like to ensure that the coupon payments from the bonds cover the annuity?s obligations and minimize the cost of acquiring the bonds today while still meeting all expected future obligations, how much money would the manager invest in each type of bond?

Trade History TARGET CORP + Add to Watchlist Symbol TGT.IJ CUSIP 87612EAU0 Coupon Rate 7% Maturity Date 1/15/2038 Last Trade Price $145.76 Last Trade Date 11/2/2016 Next Call Date Callable Yes Last Trade Yield US Treasury Yield Payment Frequency 2 First Coupon Date 7/15/2008 Semi-Annual Price/Yield Chart Price Chart Yield Chart - 31/07/2003 04/04/2012 Zoom: 1D 5D 1M 3M YTD 1Y 3Y 5Y 10Y Max Price Price $ 60 80 100 120 140 2008 2009 2010 2011 2012 2013 2014 2015 2016 Credit and Rating Elements Moody's Rating Standard & Poor's Rating Fitch Rating TRACE Grade Default A2 (05/28/2013) A (03/28/2014) A- (09/26/2016) Investment Grade Bankruptcy N Insurance Mortgage Insurer Pre-Refunded/Escrowed Additional Description Senior Unsecured Note Put & Redemption Provisions Call Date Call Price Call Frequency Continuously Put Date Put Price Put Frequency Classification Elements Bond Type US Corporate Debentures Debt Type Industry Group Industrial Industry Sub Group Retail Sub-Product Asset CORP Sub-Product Asset Type Corporate Bond State Use of Proceeds Security Code Special Characteristics Medium Term Note N Issue Elements *dollar amount in thousands Offering Date 1/14/2008 Dated Date 1/17/2008 Original Offering* $2,250,000.00 Amount Outstanding* $659,818.00 Series Issue Description Project Name Day Count Form Depository/Registration Security Level 30/360 Book Entry DTC, Clearstream, Euroclear Senior Collateral Pledge Capital Purpose Bond Elements *dollar amount in thousands Original Maturity Size* Amount Outstanding Size* Yield at Offering Price at Offering Coupon Type Escrow Type 2,250,000.00 659,818.00 7.05% $99.32 Fixed Trade History TARGET CORP + Add to Watchlist Symbol TGT.IJ CUSIP 87612EAU0 Coupon Rate 7% Maturity Date 1/15/2038 Last Trade Price $145.76 Last Trade Date 11/2/2016 Next Call Date Callable Yes Last Trade Yield US Treasury Yield Payment Frequency 2 First Coupon Date 7/15/2008 Semi-Annual Price/Yield Chart Price Chart Yield Chart - Zoom: 1D 5D 1M 3M YTD 1Y 3Y 5Y 10Y Max Price Price $ 60 80 100 120 140 2008 2009 2010 2011 2012 2013 2014 2015 2016 Credit and Rating Elements Moody's Rating Standard & Poor's Rating Fitch Rating TRACE Grade Default Date 11/2/2016 1/15/2017 7/15/2017 1/15/2018 7/15/2018 1/15/2019 7/15/2019 1/15/2020 7/15/2020 1/15/2021 7/15/2021 1/15/2022 7/15/2022 1/15/2023 7/15/2023 1/15/2024 7/15/2024 1/15/2025 7/15/2025 1/15/2026 7/15/2026 1/15/2027 7/15/2027 1/15/2028 7/15/2028 1/15/2029 7/15/2029 1/15/2030 7/15/2030 1/15/2031 7/15/2031 1/15/2032 7/15/2032 1/15/2033 7/15/2033 1/15/2034 7/15/2034 1/15/2035 A2 (05/28/2013) 7/15/2035 A (03/28/2014) 1/15/2036 A- (09/26/2016) 7/15/2036 1/15/2037 7/15/2037 1/15/2038 Investment Grade Bankruptcy N Insurance Mortgage Insurer Pre-Refunded/Escrowed Additional Description Senior Unsecured Note Put & Redemption Provisions Call Date Call Price Call Frequency Continuously Put Date Put Price Put Frequency Classification Elements Bond Type US Corporate Debentures Debt Type Industry Group Industrial Industry Sub Group Retail Sub-Product Asset CORP Sub-Product Asset Type Corporate Bond State Use of Proceeds Security Code Special Characteristics Medium Term Note N Issue Elements *dollar amount in thousands Offering Date 1/14/2008 Dated Date 1/17/2008 Original Offering* Amount Outstanding* $2,250,000.00 $659,818.00 Series Issue Description Project Name Day Count Form Depository/Registration Security Level 30/360 Book Entry DTC, Clearstream, Euroclear Senior Collateral Pledge Capital Purpose Bond Elements *dollar amount in thousands Original Maturity Size* Amount Outstanding Size* Yield at Offering Price at Offering Coupon Type Escrow Type 2,250,000.00 659,818.00 7.05% $99.32 Fixed Cash Flows Yield Computations Needed 1,000,000 Liability 5 Years $100 Face Value Bond 1 12 Years Maturity 6% Coupon Rate 5.21% Discount Rate 2 Semi Annual Bond 2 5 Years Maturity 5% Coupon Rate 5.21% Discount Rate 2 Semi Annual Period Dates Cfs PV of CF t*PV of CFS T^2+T PV of CFxT^2+T Computations Needed 4-year annuity for policyholders and assume that the policy calls for semi-annual payments over the next four years based on an assumed expected life of four additional years. Periods Tax Obligation 100,000 200,000 100,000 200,000 800,000 1,200,000 400,000 1,000,000 Bond 1 100 Par Value 2.5 Maturity 5% Coupon Rate 2 Frequency 102.36 Current Price Bond 2 100 Par Value 3 Maturity 10% Coupon Rate 2 Frequency 110.83 Current Price Bond 3 100 Par Value 3.5 Maturity 6% Coupon Rate 2 Frequency $96.94 Current Price Bond 4 100 Par Value 4 Maturity 8% Coupon Rate 2 Frequency $114.65 Current Price Bond 5 100 Par Value 4 Maturity 7% Coupon Rate 2 Frequency $96.63 Current Price Settlement Date 10/31/2016 Maturity Date 2/1/2021 Coupon Rate 5.38% Payment Frequency 2 2/1/2014 First Coupon Date 8/1/2016 LastCoupon Date $108.77 Settlement Price Par Value $100 To compute Accured Int t T c Accured Interest 91 180 $2.69 $1.36 Dates Cfs 10/31/2016 ($110.13) 2/1/2017 $2.69 8/1/2017 $2.69 2/1/2018 $2.69 8/1/2018 $2.69 2/1/2019 $2.69 8/1/2019 $2.69 2/1/2020 $2.69 8/1/2020 $2.69 2/1/2021 $102.69 Yield (XIRR) 3.168% Settlement Date 10/31/2016 Maturity Date 2/1/2021 Coupon Rate 5.38% Payment Frequency 2 2/1/2014 First Coupon Date 8/1/2016 LastCoupon Date $108.77 Settlement Price Par Value $100 To compute Accured Int t T c Accured Interest Rate 91 180 $2.69 $1.36 3.168% Dates Cfs Price (NPV) 10/31/2016 $0.00 $110.13 2/1/2017 $2.69 8/1/2017 $2.69 2/1/2018 $2.69 8/1/2018 $2.69 2/1/2019 $2.69 8/1/2019 $2.69 2/1/2020 $2.69 8/1/2020 $2.69 2/1/2021 $102.69 Settlement Date 10/28/2016 Maturity Date 3/1/2039 Coupon Rate 8.95% Payment Frequency 2 3/1/2009 First Coupon Date 9/1/2016 LastCoupon Date $160.04 Settlement Price Par Value $100 To compute Accured Int t T c Accured Interest 57 180 $4.48 $1.42 Dates Cfs 10/28/2016 ($161.46) 3/1/2017 $4.48 9/1/2017 $4.48 3/1/2018 $4.48 9/1/2018 $4.48 3/1/2019 $4.48 9/1/2019 $4.48 3/1/2020 $4.48 9/1/2020 $4.48 3/1/2021 $4.48 9/1/2021 $4.48 3/1/2022 $4.48 9/1/2022 $4.48 3/1/2023 $4.48 9/1/2023 $4.48 3/1/2024 $4.48 9/1/2024 $4.48 3/1/2025 $4.48 9/1/2025 $4.48 3/1/2026 $4.48 9/1/2026 $4.48 3/1/2027 $4.48 9/1/2027 $4.48 3/1/2028 $4.48 9/1/2028 $4.48 3/1/2029 $4.48 9/1/2029 $4.48 3/1/2030 $4.48 9/1/2030 $4.48 3/1/2031 $4.48 9/1/2031 $4.48 3/1/2032 $4.48 9/1/2032 $4.48 3/1/2033 $4.48 9/1/2033 $4.48 3/1/2034 $4.48 9/1/2034 $4.48 3/1/2035 $4.48 9/1/2035 $4.48 3/1/2036 $4.48 9/1/2036 $4.48 3/1/2037 $4.48 9/1/2037 $4.48 3/1/2038 $4.48 9/1/2038 $4.48 3/1/2039 $104.48 Yield (XIRR) 4.665% Settlement Date 10/28/2016 Maturity Date 3/1/2039 Coupon Rate 8.95% Payment Frequency 2 3/1/2009 First Coupon Date 9/1/2016 LastCoupon Date $160.04 Settlement Price Par Value $100 To compute Accured Int t T c Accured Interest 57 180 $4.48 $1.42 Period 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Dates Cfs 10/28/2016 ($161.46) 3/1/2017 $4.48 Yield (XIRR) 9/1/2017 $4.48 3/1/2018 $4.48 9/1/2018 $4.48 3/1/2019 $4.48 9/1/2019 $4.48 3/1/2020 $4.48 9/1/2020 $4.48 3/1/2021 $4.48 9/1/2021 $4.48 3/1/2022 $4.48 9/1/2022 $4.48 3/1/2023 $4.48 9/1/2023 $4.48 3/1/2024 $4.48 9/1/2024 $4.48 3/1/2025 $4.48 9/1/2025 $4.48 3/1/2026 $4.48 9/1/2026 $4.48 3/1/2027 $4.48 9/1/2027 $4.48 3/1/2028 $4.48 9/1/2028 $4.48 3/1/2029 $4.48 9/1/2029 $4.48 3/1/2030 $4.48 9/1/2030 $4.48 3/1/2031 $4.48 9/1/2031 $4.48 3/1/2032 $4.48 9/1/2032 $4.48 3/1/2033 $4.48 9/1/2033 $4.48 3/1/2034 $4.48 9/1/2034 $4.48 3/1/2035 $4.48 9/1/2035 $4.48 3/1/2036 $4.48 9/1/2036 $4.48 3/1/2037 $4.48 9/1/2037 $4.48 3/1/2038 $4.48 9/1/2038 $4.48 3/1/2039 $104.48 0.046649 PV of CF t*PV of CFS 4.373002 4.373001917 4.273329 8.546657325 4.175927 12.52778174 4.080746 16.32298356 3.987734 19.93866994 3.896842 23.38105257 3.808022 26.65615322 3.721226 29.76980919 3.636409 32.7276786 3.553525 35.5352455 3.47253 38.19782489 3.393381 40.72056754 3.316036 43.10846479 3.240454 45.36635304 3.166595 47.4989183 3.094419 49.51070052 3.023888 51.40609778 2.954965 53.18937041 2.887613 54.86464502 2.821796 56.43591834 2.757479 57.90706101 2.694628 59.28182127 2.63321 60.56382849 2.573192 61.75659669 2.514541 62.86352786 2.457228 63.88791529 2.40122 64.83294674 2.34649 65.70170751 2.293006 66.49718351 2.240742 67.22226414 2.189669 67.87974516 2.13976 68.47233146 2.090989 69.00263975 2.043329 69.47320115 1.996756 69.88646377 1.951244 70.24479516 1.90677 70.55048469 1.863309 70.80574592 1.820839 71.01271887 1.779337 71.17347216 1.738781 71.29000524 1.699149 71.36425039 1.66042 71.39807482 1.622575 71.39328254 37.0178 1665.801144 159.3109 3970.341102 885.7118119 T^2+T PV of CFxT^2+T 2 8.7460038336 6 25.6399719744 12 50.1111269679 20 81.6149177964 30 119.6320196522 42 163.6673679884 56 213.2492257518 72 267.9282827359 90 327.2767860243 110 390.8877005255 132 458.3738986319 156 529.3673780644 182 603.5185069921 210 680.4952955469 240 759.9826928764 272 841.6819089089 306 925.3097600249 342 1010.598037858 380 1097.292900475 420 1185.154285189 462 1273.955342326 506 1363.481889222 552 1453.531883818 600 1543.914917186 650 1634.451724373 702 1724.973712955 756 1815.322508713 812 1905.349517862 870 1994.915505292 930 2083.890188271 992 2172.151845111 1056 2259.586938286 1122 2346.089751519 1190 2431.562040376 1260 2515.912695899 1332 2599.057420852 1406 2680.918418145 1482 2761.424091023 1560 2840.508754629 1640 2918.112358544 1722 2994.180219932 1806 3068.662766928 1892 3141.515291927 1980 3212.697714409 2070 76626.85263242 32430 141103.5481978 Yield Maturity Rate Par Value Coupon Payment 9% 30 6% 1000 60 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -1000 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 Yield Maturity Rate Par Value Coupon Payment 9% 30 6% 1000 60 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -1000 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 422410.8