I'm using Excel 2016 to analyse the ASX200 at June 2012. Out of the ASX200 index, I've found 136 stocks with 5-yr of monthly stock close price data (01 July 2007 - 30 June 2012). I generated monthly returns, COVAR matrix and annualised COVAR matrix.
I don't understand why I have been unable to obtain the BL-generated implied weight from the BL-generated implied returns for the 136 stocks. The BL-generated implied weight should be equal to the adjusted market weight as the latter was used as input. The BL-generated implied weight included (+_) several hundred % which is invalid.
Application of the same process worked previously on 34-stock for another period.
BL-generated implied returns equation:
Where: a. COVARM2 is the annualised COVAR matrix b. D3:EI3 is the adjusted market weight adding to 100% c. D144 is the Benchmark Return d. D145 is the RF Rate
BL-generated implied Weight equation: = MMULT(MINVERSE(COVARM2),F149:F284-D145)/SUM(MMULT(MINVERSE(COVARM2),F149:F284-D145)) Where: a. F149:F284 is the Implied Returns
A potential explanation is the 5-year of monthly data covered from 01 July 2007 - 30 June 2012 an that period included significant market instability. The COVAR matrix could be incorrect as the market over the mentioned period was not an equilibrium state. Please let me know if you have come across this problem before, potential resolution, and/or if I've made an error. I can send you the spreadsheet if that makes it easier for you to review my query. Thank you for your time in advance.