Maximization of interest

At the start of the year, a company wants to invest excess cash in one-month, three-month and six-month Certificates of Deposit (CD's). (Purchase price and yields for the different CD's appear in the table below). The company is somewhat conservative, however, and wants to make sure that it has a safety margin of cash-on-hand each month; i.e., cash-on-hand left over from the previous month / available at the outset, plus principal and interest from CDs that have become due, minus investments made at the start of the month, must be no less than the month's safety margin.

The size of the monthly safety margins are as follows:

Jan $175,000
Feb $90,000
Mar $80,000
Apr $180,000
May $150,000
Jun $85,000

How should the company maximize total earned interest?
Given that:

· Three-month CDs can only be bought at the start of January and April; six-month CDs can only be bought in January.

· Initial cash available is $400,000.
Yield Term Price Purchase at start of:
1-mo CDs: 1.0% 1 mo. $2,000 January thru June
3-mo CDs: 4.0% 3 $3,000 January and April
6-mo CDs: 9.0% 6 $5,000 January

What are the decision variables and the objective function?

Define cash-flow constraints for each month.

a. Solve using Lindo or Excel. (IMHO, this problem lends itself to a spreadsheet formulation, and the latter is definitely the way to go) (25 points)

b. Modify your formulation to take into account the added requirement that one may not purchase fractional amounts of CD's

Submit two separate spreadsheets / Lindo formulations.

Some suggestions:

a. variable definitions

You might want to define:

· variables* for each possible investment, each month

· variables for the total invested each month

· a variable for total interest earned (useful for the objective function)

· for each month, a variable that represents the amount of cash on hand during the month, after making the investments at the start of the month, but before realizing the income from investments that come due at the end of the month. (This must exceed the required safety margin.)

· a variable for each month's required safety margin

· for each month, a variable that represents the added income that's available to us at the start of each month from investments that have come due at the end of the previous month (= principle + interest)

· for each month, a variable that represents the cash on hand at the start of the month

* for 'variables' in Lindo, substitute 'cell' or column of cell entries' in Excel

b. constraint definitions

You'll need to define a series of constraints (and / or inter-cell relationships in Excel) that relate:

· The cash on hand available at the start of a given month to the amount available during the previous month

· The cash on hand during the month with the cash on hand at the start of the month

· The cash on hand during the month and the month's safety margin

c. Since the problem involves deciding how much money to invest for one-month, three-month, and six-month periods, you CANNOT consider it as involving six separate monthly decisions.

I need help with this, two (attachments) 1) Excel Spreadsheet and 2) Word that has problem easy to read format.

Please supply step by step process to obtain solution.