Question

3. You just won a $100,000 lottery and plan to invest it among the following alternatives: Investment Global currency mutual fund (GF Energy mutual fund (EF Certificate of deposit (CD) Annual Return Rate 9% 7% 4%

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I
2		a) and b)
3		Total amount to invest	100000
4
5		Investment	Annual Return Rate
6		Global current mutual fund (GF)	0.09
7		Energy Mutual Fund (EF)	0.07
8		Certificate of deposit (CD)	0.04
9
10			Amount Invested	Weight in portfolio
11		Global current mutual fund (GF)	20000	=D11/$D$14
12		Energy Mutual Fund (EF)	10000.0459605295	=D12/$D$14
13		Certificate of deposit (CD)	20000	=D13/$D$14
14			=SUM(D11:D13)
15
16		Constraints:
17		Amount invested in GF	=D11	>=	20000
18		Amount investment in EF	=D12	<=	30000
19		Amount invested in CD	=D13	>=	=D11
20		Total amount invested in GF and EF as % of total investment	=(D11+D12)/D14	<=	0.6
21		Total amount invested in EF and CD as % of total investment	=(D12+D13)/D14	>=	0.3
22		Total amount invested	=D14	<=	=D3
23
24		Total Return	=SUMPRODUCT(D6:D8,E11:E13)	=getformula(D24)
25
26			Target Variable (Maximize total return)
27			Changing variable (Amount invested in each asset)
28			Constraints
29
30		Using solver, amount invested in each asset can be calculated to maximize the return given the constraints.
31
32		Thus amount to be invested in different assets are
33		Investment	Amount Invested
34		Global current mutual fund (GF)	=D11
35		Energy Mutual Fund (EF)	=D12
36		Certificate of deposit (CD)	=D13
37

Solver Settings