Determine how to invest excess cash in 1-month, 3-month and 6-month CDs so as to | |||||||

maximize interest income while meeting company cash requirements (plus safety margin). | |||||||

Yield | Term | Price | Purchase CDs in months: | ||||

1-mo CDs: | 1.0% | 1 | $2,000 | 1, 2, 3, 4, 5 and 6 | Interest | ||

3-mo CDs: | 4.0% | 3 | $3,000 | 1 and 4 | Earned: | ||

6-mo CDs: | 9.0% | 6 | $5,000 | 1 | Total | $0.00 | |

Month: | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | End |

Init Cash: | $400,000 | $325,000 | $335,000 | $355,000 | $275,000 | $225,000 | $240,000 |

Matur CDs: | $0 | $0 | $0 | $0 | $0 | $0 | |

Interest: | $0 | $0 | $0 | $0 | $0 | $0 | |

1-mo CDs: | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |

3-mo CDs: | 0.00 | 0.00 | |||||

6-mo CDs: | 0.00 | ||||||

Cash Uses: | $75,000 | ($10,000) | ($20,000) | $80,000 | $50,000 | ($15,000) | $60,000 |

End Cash: | $325,000 | $335,000 | $355,000 | $275,000 | $225,000 | $240,000 | $180,000 |

Problem | |||||||

A company wants to invest excess cash in 1-month, 3-month and 6-month Certificates of Deposit (CDs). The company | |||||||

has expected uses of cash in the next 6 months, and it wants to make sure that the principal and interest from maturing | |||||||

CDs meet the requirements for cash plus a safety margin for each month. For simplicity we assume that 3-month CDs | |||||||

can only be bought at the start of months 1 and 4, and 6-month CDs can only be bought in month 1. Initial cash available | |||||||

is $400,000. How many and what kind of CDs should the company buy in order to maximize the earned interest, and | |||||||

meet the safety margin of $100,000 each month? | |||||||

Solution | |||||||

The characteristics of the 3 different CDs are given in cells A5 through F9. | |||||||

1) The variables are the number of CDs to buy in each month. The variable cells are given names One_month_CDs, | |||||||

Three_month_CD1 and Three_Month_CD2, and Six_month_CDs. There are 6+2+1 = 9 variables. | |||||||

2) The constraints are the limitations on the formulas in this model. First, there is the safety margin requirement for each | |||||||

month. This gives | |||||||

Monthly_cash >= 100000 | |||||||

Then there are the logical constraints on the number of CDs to be bought. It is not possible to buy half or other fractions, | |||||||

or negative amounts of CDs. We can rule out negative amounts with the Assume Non-Negative option. This gives | |||||||

One_month_CDs = integer | |||||||

Three_month_CD1 = integer | |||||||

Three_month_CD2 = integer | |||||||

Six_month_CDs = integer | |||||||

3) The objective is to maximize earned interest. This is calculated by multiplying the number of CDs bought of each kind | |||||||

by the interest earned for each CD. This is given the name Total_interest. | |||||||

Remarks | |||||||

This is a good example of how the solver can help you make intelligent decisions in investments. Before solving the | |||||||

model with the Solver try to find a solution by hand. What interest is earned? | |||||||

The time required by the solver to solve this model can be considerable. Integer problems are very difficult to solve. In a | |||||||

model like this it would be possible to change the variables to the amount of money to be invested. This would give a | |||||||

'normal' problem. We chose this form since often it is not possible to invest an arbitrary amount of money in a CD. | |||||||

In some situations it is not desirable to use integer constraints. When an expected solution of a model yields a value | |||||||

that is 2034.86, for example, it is safe to assume we can round this number to 2035. If the value is 0.34 however, it is | |||||||

not safe to assume we can round this number. In each model, you have to tradeoff precision vs solution time to make a | |||||||

decision whether or not to use integer variables. |