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.