Question

If possible please solve in Excel.

An individual has $200,000 to invest. They are trying to maximize their return, but have given their investor advisor instruction on how to invest the money. The investment advisor has given them four different alternatives to invest in, they are listed in the table below along with their rates of return that is estimated. Investment-Type Hydro one - Stock Bank of Canada- Treasury Bills Bell Canada-Bonds Alberta Oil Sands Co. - Stock Rate of Return 12% 1.25% 3.35% 1.75% The individual has the following parameters. a) That no more than 50% can be invested in bonds. b) That at least 25% is invested in treasury bills. c) That at least 15% be invested in Alberta Oil Sands Co. d) That all of the $200,000 is to be invested. Find the optimal investment mix and what is the project return in the next year?

Answer 1

Let,

x1 = Amount invested in Hydro One- Stock
x2 = Amount invested in Bank of Canada - Treasury Bills
x3 = Amount invested in Bell Canada-Bonds
x4 = Amount invested in Alberta Oil Sands Co. - Stock

Objective is to maximize return so objective function = Max 0.12x1+0.0125x2+0.0335x3+0.0175x4

Subject to,

1. x1+x2+x3+x4 = 200000 (Investment amount)

2. x3 <= 50%*200000 (No more than 50% in Bonds)
or,x3 <= 100000

3. x2 >= 25%*200000 (at least 25% in treasury bills)
or, x2 >= 50000

4. x4 >= 15%*200000 (At least 15% in Alberta Oil Sands Co)
or, x4 >= 30000

x1,x2,x3,x4 >= 0 (Non-negativity constraint)

Solving in solver we get,

x1 = Amount invested in Hydro One- Stock = 120000

x2 = Amount invested in Bank of Canada - Treasury Bills = 50000

x3 = Amount invested in Bell Canada-Bonds = 0

x4 = Amount invested in Alberta Oil Sands Co. - Stock = 30000

Maximized annual return = 15550

Solver screenshot

B C D E F G H - nm + x1x2x3 x4 120000 50000 0 30000 Objective function 15550 1 1 1 5 Investment amount 6 No more than 50% in Bonds 7 at least 25% in treasury bills 8 At least 15% in Alberta Oil Sands Co 200000 = 0 <= 50000 >= 30000 >= 200000 100000 50000 30000

Solver formula

B C D E G H x1 120000 X2 X3 500000 X4 30000 Objective function =0.12*B3+0.0125*C3+0.0335*D3+0.0175 *E3 1 1 5 Investment amount 6 No more than 50% in Bonds 7 at least 25% in treasury bills 8 At least 15% in Alberta Oil Sands Co 9 =SUMPRODUCT(B5:E5, $B$3:$E$3) = 200000 =SUMPRODUCT(B6:E6,$B$3:$E$3) <= 100000 =SUMPRODUCT(B7:E7, $B$3:$E$3) >= 50000 =SUMPRODUCT(B8:E8, $B$3:$E$3) >= 30000