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