I recently purchased the portfolio optimization add-in. I was hoping someone could help me figure out why I see such a significant difference in the optimal weightings each time I run it, while not changing any parameters. To test this I have gone back to the sample portfolio with AAPL, INTC, etc. and have the same problem. Below are the conditions followed by two different results.
Modify Correlation Matrix Manually
Data is entered as
Prices
Returns
Risk free Rate:
0.36%
Maintain Return Level
Target Return
2.00%
Min Constraint:
10.00%
30.00%
0.00%
0.00%
10.00%
0.00%
0.00%
Max Constraint:
70.00%
90.00%
100.00%
80.00%
100.00%
100.00%
100.00%
Current Units:
1.00
1.00
1.00
1.00
1.00
1.00
1.00
Product Name:
MSFT
IBM
INTC
SUNW
AMZN
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-3.83%
-0.27
10.33%
0.73
IBM
44.52%
1.00
-13.75%
-0.31
30.77%
0.69
INTC
13.52%
1.00
7.19%
0.53
20.71%
1.53
SUNW
2.27%
1.00
6.09%
2.69
8.36%
3.69
AMZN
25.53%
1.00
4.29%
0.17
29.83%
1.17
100.00%
100.00%
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-4.15%
-0.29
10.01%
0.71
IBM
44.52%
1.00
-11.54%
-0.26
32.98%
0.74
INTC
13.52%
1.00
-6.56%
-0.49
6.96%
0.51
SUNW
2.27%
1.00
11.90%
5.24
14.17%
6.24
AMZN
25.53%
1.00
10.35%
0.41
35.89%
1.41
100.00%
100.00%
I hope that came through ok. I have also tried significantly more iterations, but have not had any luck.
So if I am understanding correctly, by expanding the constraints to go from 0% to 100% it should keep all the data sets and come to a relatively consistent result. I tried running the same data set with the changes to the constraints, unfortunately I found the results to be different and are as follows.
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-12.09%
-0.85
2.07%
0.15
IBM
44.52%
1.00
-43.30%
-0.97
1.22%
0.03
INTC
13.52%
1.00
15.68%
1.16
29.20%
2.16
SUNW
2.27%
1.00
19.45%
8.57
21.72%
9.57
AMZN
25.53%
1.00
20.27%
0.79
45.80%
1.79
100.00%
100.00%
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-13.70%
-0.97
0.46%
0.03
IBM
44.52%
1.00
-43.14%
-0.97
1.38%
0.03
INTC
13.52%
1.00
19.71%
1.46
33.23%
2.46
SUNW
2.27%
1.00
8.41%
3.71
10.68%
4.71
AMZN
25.53%
1.00
28.71%
1.12
54.25%
2.12
100.00%
100.00%
Each was done with 10000 iterations. Can you think of anything else I can try to get a consistant portfolio recommendation? Otherwise is there a particular way that I can interperate the data to make the most use of it?
When the optization process is run, it ignores weightings that fall outside of the minimum and maximum limits placed on the desired weightings. This could be the cause of the differing weightings resulting in each time you run the optimization process with the same input data.
Thank you for the quick reply. I increased the number of iterations to 100,000. Unfortunately the numbers still came out significantly different, they are as follows:
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-4.03%
-0.28
10.13%
0.72
IBM
44.52%
1.00
-13.97%
-0.31
30.55%
0.69
INTC
13.52%
1.00
-7.62%
-0.56
5.90%
0.44
SUNW
2.27%
1.00
5.99%
2.64
8.26%
3.64
AMZN
25.53%
1.00
19.62%
0.77
45.16%
1.77
100.00%
100.00%
Product
Current
Theoretical Change
Optimal
Weighting
Units
Weighting
Units
Weighting
Units
MSFT
14.16%
1.00
-3.78%
-0.27
10.38%
0.73
IBM
44.52%
1.00
-13.28%
-0.30
31.24%
0.70
INTC
13.52%
1.00
-2.36%
-0.17
11.16%
0.83
SUNW
2.27%
1.00
0.71%
0.31
2.98%
1.31
AMZN
25.53%
1.00
18.72%
0.73
44.25%
1.73
100.00%
100.00%
Am I still not doing enough, if so is there a recommended amount? Otherwise is there something else that I might be doing incorrectly?
The optimization process runs random weightings to find the optimal one. Try increasing the number of iterations even more to minimize the difference in weightings each time you run it.
