HomeTroubleshooting@RISK for Excel: SimulationCorrelation Matrix from StatTools Not Accepted

# 4.10. Correlation Matrix from StatTools Not Accepted

Applies to: @RISK for Excel, all releases

I have historical data for several variables that should be correlated. I used StatTools to create a correlation matrix, then copied it into my @RISK model. When I tried to run a simulation, @RISK said the matrix is invalid. What is wrong?

This is due to your data, not an error in StatTools or @RISK. It's common to have some missing data points with historical data, and missing data may lead to a correlation matrix that is not self consistent. (A self-consistent matrix has no negative eigenvalues. For more, please see How @RISK Tests a Correlation Matrix for Validity.)

According to Peter Jäckel, Monte Carlo Methods in Finance (Wiley, 2002), page 60:

Ad hoc correlation matrices, those calculated from incomplete data and those taken from news services, sometimes don't comply with the requirement of symmetry and positive semi-definiteness. ...

In practice, the problem of an invalid correlation matrix, i.e. one that has negative eigenvalues, can also very easily arise in the context of risk analysis for equity portfolios. This is because there are frequently asynchronous gaps in the historical stock exchange time series. The chance that slight inconsistencies in the data from which historical correlation coefficients are calculated can lead to negative eigenvalues grows rapidly as the size of the correlation matrix increases. ... Since equity index or portfolio analysis typically involves many underlying assets, the risk of negative eigenvalues of the correlation matrix calculated from historical data is particularly large.

There are two ways around this:

• If you have an invalid matrix, you can use RiskCorrectCorrmat( ) to transform it to a valid matrix. You can leave @RISK completely free to make changes, or you can specify adjustment weights for @RISK to use in creating the corrected matrix. Please see How @RISK Adjusts an Invalid Correlation Matrix for details.

• Or, you can redefine your StatTools data set to include only rows where all variables have data.

But whether the generated correlation matrix is valid or is corrected with RiskCorrectCorrmat( ), you should also apply your own knowledge and judgment to the correlations. There may be factors in the problem you are modeling that make it desirable to specify correlations different from the historical ones.

,

Last edited: 2013-02-24