I need help setting up my Duration Matching Optimization Table for solver to calculate portfolio yield. Thank you.

ExcelHW#2 - AutoSave Of Hav Svo File Home Insert Page Layout Formulas Data Review View Help Cut Calibri 11 A You ab Wra BluA = == Mer LG Copy $ Format Painter Clipboard Paste Font Alignment E32 A H I 2 Maturity/Par 3 Maturity 4 Coupon 5 Payment Freq. 6 Settlement Date 7 Yield B C D E F G Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 1 100 100 100 100 100 100 12/2/2022 12/1/2024 12/1/2030 12/1/2021 12/1/2025 12/1/2026 5.00% 3.00% 5.00% 2.00% 4.00% 5.00% 2 4 4 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 2.00% / 3.50% 4.00% 3.25% 4.25% 3.75% 2 Duration Matching Optimization Bond1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 14 Face Value 100.00 100.00 100.00 100.00 100.00 100.00 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 16 Settlement Date 17 18 Coupon % 19 20 Coupon $ / 5.00% 3.00% 5.00% 2.00% 4.00% 5.00% 51 5 2.00% 3.50% 4.00% 3.25% 4.25% 3.75% 22 Yield % 23 24 Payment Frequency 25 26 Duration 2.81 4.64 8.59 1.99 5.32 5.94 28 Portfolio % 10% 10% 20% 20% 20% 20% 100% 30 31 32 Investment Time Horizon 33 34 Portfolio Duration 4.88 35 36 Portfolio Yield 3.46% Bond Immunization Model An investor wants to put together a portfolio, consisting of portions of 6 different bonds. What is the best combination of bonds to get the optimum yield with a given investment time horizon? The period from settlement to maturity varies for each bond. Problem An investor wants to put together a portfolio consisting of up to 6 different bonds. To minimize risk of loss of principal value due to interest rate fluctuations and to assure enough cash flow at a certain point in the future, she wants to make sure the average duration (Note: use DURATION formula to calculate duration of each bond.) of the bonds equals her investment time horizon. How should the investor choose her portfolio to optimize the combined yield of the bonds, while making sure the duration of the portfolio equals the investment time horizon? The bonds in question have various maturity dates as well and coupon payment frequencies. The annual payments, the yield and the face value of the bonds are all known. Solution The objective is to maximize the portfolio yield under an investment time horizon of 4 - 6 years (4,5,6). a. Target Cell: Maximize the target cell of the portfolio yield b. Changing Cells: These are the individual percentage holdings of each individual Bond. c. Constraints: Portfolio Duration must equal the investment time horizon. Each bond holding cannot be less than 0 Each bond holding cannot be more than 33% Sum of the percentage holding of bonds must be equal to 100% d. Scatter Chart: Add a chart illustrating the yield over the investment time horizon of 4 through 6 years. Appropriately label and format the chart and data. ExcelHW#2 - AutoSave Of Hav Svo File Home Insert Page Layout Formulas Data Review View Help Cut Calibri 11 A You ab Wra BluA = == Mer LG Copy $ Format Painter Clipboard Paste Font Alignment E32 A H I 2 Maturity/Par 3 Maturity 4 Coupon 5 Payment Freq. 6 Settlement Date 7 Yield B C D E F G Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 1 100 100 100 100 100 100 12/2/2022 12/1/2024 12/1/2030 12/1/2021 12/1/2025 12/1/2026 5.00% 3.00% 5.00% 2.00% 4.00% 5.00% 2 4 4 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 2.00% / 3.50% 4.00% 3.25% 4.25% 3.75% 2 Duration Matching Optimization Bond1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 14 Face Value 100.00 100.00 100.00 100.00 100.00 100.00 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 11/17/2019 16 Settlement Date 17 18 Coupon % 19 20 Coupon $ / 5.00% 3.00% 5.00% 2.00% 4.00% 5.00% 51 5 2.00% 3.50% 4.00% 3.25% 4.25% 3.75% 22 Yield % 23 24 Payment Frequency 25 26 Duration 2.81 4.64 8.59 1.99 5.32 5.94 28 Portfolio % 10% 10% 20% 20% 20% 20% 100% 30 31 32 Investment Time Horizon 33 34 Portfolio Duration 4.88 35 36 Portfolio Yield 3.46% Bond Immunization Model An investor wants to put together a portfolio, consisting of portions of 6 different bonds. What is the best combination of bonds to get the optimum yield with a given investment time horizon? The period from settlement to maturity varies for each bond. Problem An investor wants to put together a portfolio consisting of up to 6 different bonds. To minimize risk of loss of principal value due to interest rate fluctuations and to assure enough cash flow at a certain point in the future, she wants to make sure the average duration (Note: use DURATION formula to calculate duration of each bond.) of the bonds equals her investment time horizon. How should the investor choose her portfolio to optimize the combined yield of the bonds, while making sure the duration of the portfolio equals the investment time horizon? The bonds in question have various maturity dates as well and coupon payment frequencies. The annual payments, the yield and the face value of the bonds are all known. Solution The objective is to maximize the portfolio yield under an investment time horizon of 4 - 6 years (4,5,6). a. Target Cell: Maximize the target cell of the portfolio yield b. Changing Cells: These are the individual percentage holdings of each individual Bond. c. Constraints: Portfolio Duration must equal the investment time horizon. Each bond holding cannot be less than 0 Each bond holding cannot be more than 33% Sum of the percentage holding of bonds must be equal to 100% d. Scatter Chart: Add a chart illustrating the yield over the investment time horizon of 4 through 6 years. Appropriately label and format the chart and data