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