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