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 | $175 | $75 | ||||

Years to Maturity | 3 | 5 | 6 | 4 | 6 | ||||

Price | $1,078.73 | $1,225.51 | $1,381.32 | $1,355.66 | $1,023.83 | ||||

Bond 1 | Bond 2 | Bond 3 | Bond 4 | Bond 5 | Cost | ||||

Number Purchased | 50 | 50 | 50 | 50 | 50 | $303,253 | |||

Cash Flow | Bond 1 | Bond 2 | Bond 3 | Bond 4 | Bond 5 | Total w/Int | Liability | ||

Year 1 | $5,000 | $6,250 | $7,500 | $8,750 | $3,750 | $31,250 | $32,000 | ||

Year 2 | $5,000 | $6,250 | $7,500 | $8,750 | $3,750 | $30,448 | $25,000 | ||

Year 3 | $5,000 | $6,250 | $7,500 | $8,750 | $3,750 | $37,079 | $22,000 | ||

Year 4 | $6,250 | $7,500 | $8,750 | $3,750 | $42,384 | $28,000 | |||

Year 5 | $6,250 | $7,500 | $3,750 | $32,891 | $25,000 | ||||

Year 6 | $7,500 | $3,750 | $19,694 | $20,000 | |||||

Problem | |||||||||

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. Excess payments in a period can be reinvested, to be available in the | |||||||||

next period, at a certain interest rate. 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 | |||||||||

The solution is similar to the one in BOND3. The difference is that the cash-flow takes into account the reinvestment of excess | |||||||||

funds in one period for the next period. Remember that the original idea behind exact matching was to minimize the influence | |||||||||

of interest rate changes. In this model, however, we are again more dependent on the interest rate, since a shift in the future | |||||||||

rate would affect the solution to the model. Thus, the market value of the portfolio may fluctuate to a greater extent than if we | |||||||||

ignored reinvestment opportunities. | |||||||||