What is the minimum cost portfolio, consisting of up to 6 bonds, that provides enough
cash flow to cover liabilities in each period?  
Interest Rate 7%        
Characteristics of bonds        
  Bond 1 Bond 2 Bond 3 Bond 4 Bond 5
Face Value $1,000 $1,000 $1,000 $1,000 $1,000
Coupon Payment $100 $125 $150 $200 $75
Years to Maturity 3 5 6 4 6
Price $1,078.73 $1,225.51 $1,381.32 $1,440.34 $1,023.83
           
  Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Cost
Number Purchased 10 10 10 10 10 $61,497
Cash Flow Bond 1 Bond 2 Bond 3 Bond 4 Bond 5   Total   Liability
Year 1 $1,000 $1,250 $1,500 $2,000 $750 $6,500 $32,000
Year 2 $1,000 $1,250 $1,500 $2,000 $750 $6,500 $25,000
Year 3 $1,000 $1,250 $1,500 $2,000 $750 $6,500 $22,000
Year 4 $1,250 $1,500 $2,000 $750 $5,500 $28,000
Year 5 $1,250 $1,500 $750 $3,500 $25,000
Year 6     $1,500   $750   $2,250   $20,000
Problem                  
In models BOND1 and BOND2 we saw a way for an investor to protect against interest rate fluctuations. Here, we'll look
at another method. An investor wants to put together a portfolio consisting of up to 6 different bonds. He has certain cash-
flow requirements in the future that the coupons of the bonds should cover. (For example, a pension fund must meet
requirements for future pension payments.) These payments are independent of interest rate changes. How should the
investor choose his portfolio to minimize the cost of the bonds, while making sure that the payments cover his future cash-
flow requirements?                  
                   
Solution                  
1) The variables are the number of each bond to include in the portfolio. In worksheet BOND3 these are given the name
Purchased_bonds.                  
2) The constraints are very simple. First we have the logical constraints:          
  Purchased_bonds >= 0 via the Assume Non-Negative option        
  Purchased_bonds = integer (We can not buy fractions of a bond)        
Then there is the constraint to make sure that the cash-flow requirements are met:        
  Cash_flow >= Liabilities              
3) The objective is to minimize the portfolio cost. This is given the name Total_cost.        
                   
Remarks                  
In this model we assume that money coming in from maturing bonds can not be used to cover the cash-flow requirements.
Also, we do not account for excess money in one period that may be transferred to the next period. In model BOND4 we
will account for this.