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.