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.