An investor wants to put together a portfolio, drawing from a set of 5 candidate bonds. | ||||||||

What is the best combination of bonds to get the optimum yield with a given investment time horizon ? | ||||||||

Bond 1 | Bond 2 | Bond 3 | Bond 4 | Bond 5 | Bond 6 | Total | ||

Portfolio % | 20.00% | 20.00% | 20.00% | 20.00% | 10.00% | 10.00% | 100.00% | |

Duration (Years) | 2.8 | 3.1 | 3.7 | 3.5 | 3.8 | 4 | ||

Yield to Maturity | 8.00% | 6.00% | 10.00% | 9.00% | 8.00% | 5.00% | ||

Investment Time Horizon | 3.8 | |||||||

Portfolio Duration | 3.4 | |||||||

Portfolio Yield | 7.90% | |||||||

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, he wants to make sure that the average duration of the bonds equals his investment time | ||||||||

horizon. How should the investor choose his portfolio to optimize the combined yield of the bonds, | ||||||||

while making sure that the duration of the portfolio equals the investment time horizon? The duration | ||||||||

and the yield to maturity are known for each bond. | ||||||||

Solution | ||||||||

1) The variables are the percentages or fractions of our available funds to invest in each bond. In | ||||||||

worksheet BOND1 these are given the name Portfolio_fractions. | ||||||||

2) The constraints are very simple. First we have the logical constraints: | ||||||||

Portfolio_fractions >= 0 via the Assume Non-Negative option | ||||||||

Portfolio_total = 1 | ||||||||

Then there is the constraint to make sure that the portfolio duration equals the investment time horizon: | ||||||||

Portfolio_duration = Time_horizon | ||||||||

3) The objective is to maximize the portfolio yield. This is given the name Portfolio_yield | ||||||||

Remarks | ||||||||

In this model we assume that the duration of the bond is known. In worksheet BOND2 we will see how | ||||||||

to use EXCEL's build-in functions to calculate the duration of each bond. | ||||||||