Home > Forum Home > Excel Portfolio Optimization Template > Inconsistency in Optimal Weight Results Share

Inconsistency in Optimal Weight Results

Excel Help for Inconsistency In Optimal Weight Results in Excel Portfolio Optimization Template


Forum TopicPost Reply Login

Inconsistency In Optimal Weight Results

Rate this:
(3/5 from 1 vote)
ConfusedHi,

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.

Thanks in advance.

Jeff

 jd
 Posted by on
 
Replies - Displaying 1 to 5 of 5Order Replies By: Most Recent | Chronological | Highest Rated
Surprised
Rate this:
(3/5 from 1 vote)
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.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
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?  

Thanks again. 

Jeff

 jd
 Posted by on
Fedup
Rate this:
(3/5 from 1 vote)
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.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
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?

Thanks.  Jeff

 jd
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
I think that the only way to minimize the difference is to increase the number of iterations in the CoVar sheet even further.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Inconsistency in Optimal Weight Results in the