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).

Target Cell:

Maximize the target cell of the portfolio yield

Changing Cells:

These are the individual percentage holdings of each individual Bond.

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%

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.