An investor wants to put together a portfolio, consisting out of a maximum of 6 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 is 4 years for each bond. | ||||||||

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

Portfolio % | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 120.00% | |

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

Coupon Payment | $150 | $100 | $120 | $175 | $0 | $125 | ||

Face Value | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | ||

Duration (Years) | 3.3563 | 3.52136 | 3.43042 | 3.27448 | 4 | 3.41532 | ||

Investment Time Horizon | 3.5 | |||||||

Portfolio Duration | 4.19957 | |||||||

Portfolio Yield | 9.80% | |||||||

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? All bonds | ||||||||

mature in 4 years and have one annual interest payment. The annual payments, the yield and the | ||||||||

face values of the bonds are all known | ||||||||

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

The solution is the same as in worksheet BOND1. The difference is that instead of the duration of | ||||||||

each bond being given, it is now calculated from the yield, annual coupon and face value. | ||||||||

The Excel function DURATION is only available if you have installed the Analysis ToolPak add-in. If | ||||||||

you see #NAME? on the worksheet, choose Tools Add-Ins..., click to put a check mark next to | ||||||||

Analysis ToolPak, then click OK. If Analysis ToolPak is missing from the list of available add-ins, you | ||||||||

may need to install it from the Excel or Office CD. | ||||||||