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. Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Maturity/Par 100 100 100 100 100 100 Maturity 4/1/2024 4/1/2026 4/1/2036 4/1/2023 4/1/2026 4/1/2027 Coupon 5.0% 3.0% 5.0% 2.0% 4.0% 5.0% Payment Frequency 2 2 4 4 2 2 Settlement Date 4/1/22 4/1/22 4/1/22 4/1/22 4/1/22 4/1/22 Yield 2.00% 3.50% 4.00% 3.25% 4.25% 3.75% Duration Matching Optimization Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Face Value Settlement Date Maturity Date Coupon % Coupon $ Yield % Payment Frequency Duration Portfolio % Investment Time Horizon Portfolio Duration Portfolio Yield 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. Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Maturity/Par 100 100 100 100 100 100 Maturity 4/1/2024 4/1/2026 4/1/2036 4/1/2023 4/1/2026 4/1/2027 Coupon 5.0% 3.0% 5.0% 2.0% 4.0% 5.0% Payment Frequency 2 2 4 4 2 2 Settlement Date 4/1/22 4/1/22 4/1/22 4/1/22 4/1/22 4/1/22 Yield 2.00% 3.50% 4.00% 3.25% 4.25% 3.75% Duration Matching Optimization Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Face Value Settlement Date Maturity Date Coupon % Coupon $ Yield % Payment Frequency Duration Portfolio % Investment Time Horizon Portfolio Duration Portfolio Yield
