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