HomeTechniques and TipsCorrelation in @RISKHow @RISK Tests a Correlation Matrix for Validity

5.4. How @RISK Tests a Correlation Matrix for Validity

Applies to:  @RISK 4.x–7.x

How does @RISK decide whether my correlation matrix is valid?

The basic principle is that if two inputs are each strongly correlated to a third, they must be at least weakly correlated to each other. For example, it would be inconsistent to correlate A and B at 0.9, A and C at 0.8, but B and C at 0.0.  A valid matrix is one where the correlation coefficients are mutually consistent.

When only three inputs are involved, it's pretty easy to check for valid combinations. If the coefficient of A and B is m, and the coefficient of A and C is n, then the coefficient of B and C must be in the range of

m n  ±  sqrt(  (1-m²) (1-n²) )

Source: Two Random Variables, Each Correlated to a Third at Math Forum.

For example, if A and B correlate at 0.9, and A and C correlate at 0.8, then B and C must correlate in the range of

0.9 * 0.8  ± sqrt( (1-0.9²) (1-0.8²) ) = 0.72 ± 0.26153 = 0.458 to 0.982

Here's how @RISK generalizes this principle for a correlation matrix of any size:

If a correlation matrix is created using a full data set, it will be positive semi-definite if there is a linear relationship between any of the variables and positive definite if there is no linear relationship.

The easiest way to determine if a matrix is positive definite is to calculate its eigenvalues, and that is what @RISK does at the start of a simulation. A positive definite matrix will have all positive eigenvalues and a positive semi-definite matrix will have eigenvalues greater than or equal to zero and at least one eigenvalue equal to zero.

For @RISK, a "valid" matrix is any matrix that is positive definite or positive semi-definite, and an "invalid" matrix is any matrix that has at least one negative eigenvalue.  For details on how @RISK adjusts an invalid correlation matrix, please see How @RISK Adjusts an Invalid Correlation matrix.

How can I determine ahead of time if my matrix is invalid?

With @RISK 5.x–7.x: In the @RISK Model window, click the Correlations tab and use the Check Matrix Consistency command to have @RISK check whether the matrix is self consistent.

With @RISK 4.5 and earlier: An "invalid" matrix has one or more negative eigenvalues.  Excel itself doesn't have a worksheet function to calculate eigenvalues, but there are many software applications and Excel add-ins with that capability. One freeware alternative is MATRIX at http://digilander.libero.it/foxes/ (accessed 2013-03-14).  (We mention this as one example, without endorsement and without prejudice to any other software for computing eigenvalues.)

See also:  How @RISK Adjusts an Invalid Correlation Matrix

Last edited: 2015-06-23

This page was: Helpful | Not Helpful