0
$\begingroup$

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:

=(MMULT(COVARM2,TRANSPOSE(D3:EI3))*(D144-D145)/MMULT(D3:EI3,MMULT(COVARM2,TRANSPOSE(D3:EI3))))+D145

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.

Improve this question
$\endgroup$
1
  • $\begingroup$ Hi Scorpion, Your 2007-2012 sample period does include the once-a-century "disequilbrium" that was the 2008 GFC. That WILL distort any model than tries to capture that event within any sample, no questions asked. However, my understanding of the Black-Litterman model is that it is supposed to add matrix'ed adjustments to reflect investor biases in/over any period; rather than change the period itself! Are the weights with no BL adjustments equally skew-wiff or volatile? Put simply, the 2008-9 crash is your problem here.... $\endgroup$
    – demully
    Commented Aug 24, 2020 at 1:25

2 Answers 2

Reset to default
1
$\begingroup$

This is a good resource for calculation of BL in Python, personally I find that Excel isn't great for this sort of thing.

https://medium.com/analytics-vidhya/black-litterman-model-for-asset-allocation-for-top-20-indian-companies-by-market-capitalization-c9fcbd362d72

Also when calculating inverses of Cov Matrices, you may occasionally run into the issue where your Matrix is not invertible as it is not positive semi-definite, in this case the Moore-Penrose pseudo-inverse is a good alternative.

Improve this answer
$\endgroup$
0
$\begingroup$

Thank you for your prompt response.

A 139x139 COVAR matrix requires at least 139 rows of returns. Three years of weekly returns data has resolved this issue.

Totally agree with you that the wild swings in the period 2007-2012 adversely impacted the annualised Stdev. I am aware that it's common practice to use 5-yr of monthly returns data or 2-yr of weekly returns data for BL analysis. As the wild swings adversely affect the Stdev, I wonder if professionals use tools to adjust/standardise the observed Stdev or use as is and apply the CML and Efficient Frontier as a snapshot in time until the next rebalance/check-in point.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.