Optimization Problem

I am trying to learn some optimization and am going through some questions and can't figure this one out at all.

Here it is:

Your fraternity, has decided to set up a legacy scholarship fund for future members using money left over from the frat house beer fridge receipts. Since the scholarship is for future frat members, no one at the frat today can be eligible so you have to wait 5 years for everyone to graduate from their co-op degree programs before the monies can be awarded. The award will be valued at $12,000 for the first year and increase $2,000 per year for three more years after which the fund will be exhausted (you’re leaving only a temporary legacy). You have the following investment options:

There are 4 investment vehicles:

1) G.I.C which is available for purchase every year and matures at the end of each year. Its return at Maturity is 6%.

2) Energy Trust which is available for purchase in year 1, 2, 5, and 7 and matures at the end of 2 years. Its return at maturity is 14%.

3) Long term G.I.C. which is available for purchase in year 1 & 4 and matures at the end of 3 years. Its return at maturity is 18%.

4) Growth Fund which is available in year one and matures at the end of 7 years. Return at maturity is 65%.

You want to come up with an investment plan to meet the funding objective while minimizing the initial investment outlay of cash (i.e. everything left over from the beer fridge receipts after making these investments will be used for a party to celebrate your fraternity’s philanthropy!)
1) Determine the algebraic formulation of this problem. What type of problem is it? Why?
2) Solve the problem using Excel, that is, determine the investment plan.

 

1) Clearly it's a constrained optimization problem. There are inequality constraints (such as, the amount invested in any particular vehicle for any particular year must be >= zero) and equality constraints (such as, the sum of the weights must equal one). The different payout schedules in different years supply a couple of inequality constraints as well.

Why not say there are 32 unknowns, namely the weights for the four investment vehicles in each of the eight years. Some of them are known to be zero beforehand (as when a certain vehicle is unavailable for a certain year), and others have known sums (yearly weights sum to 1).

 

Manual solution: first find the compound annual growth rates of the four investment vehicles. They are 6.00%, 6.771%, 5.672%, and 7.416% respectively. Clearly vehicle 3 is substandard and should never be purchased.

The payouts occur at the end of year 5 ("EY5"), the end of year 6 ("EY6"), the end of year 7 ("EY7"), and the end of year 8 ("EY8"). Payments are
EY5 = 12K
EY6 = 14K
EY7 = 16K
EY8 = 18K

Investment vehicle 4 should be used wherever possible since it has the highest compound annual growth rate. Thus we use vehicle 4 to pay the EY7 payment, and vehicle 4 + vehicle 1 to pay the EY8 payment.

EY7 = 16K = StartAmountP7 * 1.65 thus StartAmountP7 = $9697
EY8 = 18K = StartAmountP8 * 1.65 * 1.06 thus StartAmountP8 = $10292

Payment EY6 occurs after six years, thus we can use investment vehicle 2, rolled over 3 times

EY6 = 14K = StartAmountP6 * (1.14)^3 thus StartAmountP6 = $9450

Payment EY5 occurs after five years so we can use investment vehicle 2 twice, and investment vehicle 1 once

EY5 = 12K = StartAmountP5 * (1.14)^2 * 1.06 so StartAmountP5 = $8711

So the total amount needed at the beginning of year 1 is $9697 + $10292 + $9452 + $8711 = $38152.

 

Thanks a lot for the help.

Now I think I have to come up with a way to do it in excel using a bunch of inequalities and constraints.

thanks again