Vertex42 The Excel Nexus

A simple optimal portfolio problem is the cash matching problem. To describe this problem, suppose that we face a known sequence of future monetary obligations (If we manage a pension fund, these obligations might represent required annuity payments ) We wish to invest now so that these obligations can be met as they occur; and accordingly, we plan to purchase bonds of various maturities and use the coupon payments and redemption values to meet the obligations The simplest approach is to design a portfolio that will, without future alteration, provide the necessary cash as required

To formulate this problem mathematically, we first establish a basic time period length, with cash flows occurring at the end of these periods For example, we might use 6-month periods Our obligation is then a stream y — (yi, yi, . ., y/(), starting one period from now. (We use boldface letters to denote an entire stream.) Likewise each bond has an associated cash flow stream of receipts, starting one period from now. If there are m bonds, we denote the stream associated with one unit of bond j by cj = (c i j, cij,. . . , cnj) The price of bond j is denoted by pj. We denote by Ay the amount of bond j to be held in the portfolio. The cash matching problem is to find the Ay's of minimum total cost that guarantee that the obligations can be met Specifically, nt minimize pj.xj j=\

in subject to ^ Cjj Xj > y,' for / — 1, 2,. . ., n j= i

The objective function to be minimized is the total cost of the portfolio, which is equal to the sum of the prices of the bonds times the amounts purchased The main set of constraints are the cash matching constraints. For a given i the corresponding constraint states that the total amount of cash generated in period i from all m bonds must be at least equal to the obligation in period / . The final constraint rules out the possibility of selling bonds short

This problem can be clearly visualized in terms of an array of numbers in a spreadsheet, as in the following example.

Example 5.3 (A 6-year match) We wish to match cash obligations over a 6-year period. We select 10 bonds for this purpose (and for simplicity all accounting is done on a yearly basis) The cash flow structure of each bond is shown in the corresponding column in Table 5 3 Below this column is the bond's current price For example, the first column represents a 10% bond that matures in 6 years. This bond is selling at 109. The second to last column shows the yearly cash requirements (or obligations) for cash to be generated by the portfolio. We formulate the standard cash matching problem as a linear programming problem and solve for the optimal portfolio. (The solution can be found easily by use of a standard linear programming package such as those available on some spreadsheet programs.) The solution is given in the bottom row of Table 5 3, The actual cash generated by the portfolio is shown in the right-hand column, This column is computed by multiplying each bond column j by its solution value Ay and then summing these results. The minimum total cost of the portfolio is also indicated in the table.

Note that in two of the years extra cash, beyond what is required, is generated This is because there are high requirements in some years, and so a large number of bonds must be purchased that mature at those dates However, these bonds generate coupon payments in earlier years and only a portion of these payments is needed to

TABLE 5.3

Cash Matching Example

TABLE 5.3

Cash Matching Example

Yr |
Bonds |
Req'd |
Actual | |||||||||

1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 | |||

I |
¡0 |
7 |
8 |
6 |
7 |
5 |
10 |
8 |
7 |
100 |
100 |
171 74 |

2 |
10 |
7 |
8 |
6 |
7 |
5 |
10 |
8 |
107 |
200 |
200 00 | |

.3 |
10 |
7 |
8 |
6 |
7 |
5 |
110 |
108 |
800 |
800 00 | ||

4 |
10 |
7 |
8 |
6 |
7 |
105 |
100 |
119.34 | ||||

5 |
10 |
7 |
8 |
106 |
107 |
800 |
800 00 | |||||

6 |
110 |
107 |
108 |
1,200 |
1,200 00 | |||||||

X |
109 |
94 8 |
99 5 |
93 i |
97 2 |
92 9 |
no |
104 |
102 |
95 2 |
2,381 14 Cost | |

0 |
11.2 |
0 |
681 |
0 |
0 |
0 |
6 3 |
0 28 |
0 |

A spreadsheet layout clearly shows the problem and its solution in this example, the cash flow streams of 10 different bonds are shown year by year, as 10 columns in the array The current price of each bond is listed below the stream, and the amount to be included in a portfolio is listed below the price Cash flows required to be generated by the portfolio are shown in the penultimate column, and those act!tally generated are shown in the last column

A spreadsheet layout clearly shows the problem and its solution in this example, the cash flow streams of 10 different bonds are shown year by year, as 10 columns in the array The current price of each bond is listed below the stream, and the amount to be included in a portfolio is listed below the price Cash flows required to be generated by the portfolio are shown in the penultimate column, and those act!tally generated are shown in the last column meet obligations in those early years. A smoother set of cash requirements would not lead to such surpluses

There is a fundamental flaw in the cash matching problem as formulated here, as evidenced by the surpluses generated in our example. The surpluses amount to extra cash, which is essentially thrown away since it is not used to meet obligations and is not reinvested. In reality, such surpluses would be immediately reinvested in instruments that were available at that time,, Such reinvestment can be accommodated by a slight modification of the problem formulation, but some assumptions about the nature of future investment opportunities must be introduced The simplest is to assume that extra cash can be carried forward at zero interest; that it can, so to speak, be put under the mattress to be recovered when needed, This flexibility is introduced by adjoining artificial "bonds" having cash flow streams of the form (0, , 0, — 1, 1,0, ,0) Such a bond is "purchased" in the year with the —1 (since it absorbs cash) and is "redeemed" the next year An even better formulation would allow surplus cash to be invested in actual bonds, but to incorporate this feature an assumption about future interest rates (or, equivalently, about future bond prices) must be made. One logical approach is to assume that prices follow expectations dynamics based on the current spot rate curve. Then if r' is the estimate of what the 1-year interest rate will be a year from now, which under expectations dynamics is the current forward rate /1.2, a bond of the form (0, — I. 1 + 1 \ 0, . . . , 0) would be introduced The addition of such future bonds allows surpluses to be reinvested, and this addition will lead to a different solution than the simple cash matching solution given earlier.

Other modifications to the basic cash matching problem are possible, For example, if the sums involved are not large, then account might be made of the integer nature oí the required solution; that is, the .,v¿ variables might be restricted to be integers Other modifications combine immunization with cash matching

Was this article helpful?

If you're like a lot of people watching the recession unfold, you have likely started to look at your finances under a microscope. Perhaps you have started saving the annual savings rate by people has started to recover a bit.

## Post a comment