Question

The following tables shows 5 year returns of different funds

Mutual Fund	Annual Return (%)
	Year 1	Year 2	Year 3	Year 4	Year 5
Stocks	12.01	11.22	13.47	45.42	-21.93
Bonds	17.64	4.25	7.51	-1.33	7.36
Aggressive Growth	32.41	18.71	30.09	41.46	-23.26
Aggressive Value	32.36	20.61	12.93	7.06	-5.37
Moderate Growth	33.44	19.40	6.77	58.68	-9.02
Moderate Value	24.56	25.32	-6.70	5.43	17.31

Let’s assume the minimum return every year across all the funds is M. This means sum of money invested in stock, bonds, aggressive growth, aggressive value, moderate growth and moderate value in any year will return at least M%.

Keep in mind that the total percentage of money allocated across all the funds in any year will be 100%. This means stocks + bonds + …… moderate value = 100% or 1

The objective here is to maximize return in any year. E.g. in year 1 to maximize return, one can invest all the money in moderate growth, but this will not work because in year 2, moderate value returns higher. Therefore, the objective is to come up with an allocation across all the funds such that the return is maximum in the 5 year period.

Answer 1

The optimal allocation of funds in each of the five years is determined using the spreadsheet model as below:

Formulas:

B11 =SUMPRODUCT(B3:B8,G3:G8)   copy to B11:F11

G9 =SUM(G3:G8)   copy to G9:K9

G11 =SUM(B11:F11)