HomeTechniques and TipsCorrelation in @RISKHow @RISK Adjusts an Invalid Correlation Matrix

5.5. How @RISK Adjusts an Invalid Correlation Matrix

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

How does @RISK decide whether my correlation matrix is valid?  If the matrix is invalid, how does @RISK adjust it to create a valid matrix?

A correlation matrix is valid if it is self-consistent, meaning that the specified coefficients are mutually compatible. Please see How @RISK Tests a Correlation Matrix for Validity.

When you click Start Simulation, @RISK checks all correlation matrices for validity. If a matrix is invalid, @RISK looks for an adjustment weight matrix (see below), If the adjustment weight matrix exists, @RISK uses it to adjust the invalid correlation matrix, and the simulation proceeds. But if there's no adjustment weight matrix, @RISK displays this message:

The correlation matrix at ... is not self-consistent. @RISK can generate the closest self-consistent matrix.  OK generates a corrected matrix and continues, Cancel stops the simulation.

If you want to adjust the matrix on your own or create an adjustment weight matrix, click Cancel. This is usually a good idea, because in the absence of an adjustment weight matrix @RISK may make quite large changes in your correlation coefficients.

Adjustment weight matrix (@RISK 5.5 and later)

If you get the warning message about an invalid matrix, you can adjust the matrix yourself, or create an adjustment weight matrix to guide @RISK in adjusting the correlations. The adjustment matrix is a triangular matrix the same size as the correlation matrix; a square matrix is also acceptable as long as it is symmetric. The name of the adjustment weights matrix must match the range name for the correlation matrix, with the suffix _Weights.  For example, if your correlation matrix is named Matrix1, the associated adjustment weight matrix must be named Matrix1_Weights. You can name a matrix by highlighting its cells and then typing its name in the name box to the left of Excel's formula bar. Or, click Formulas » Define Name (click Insert » Name » Define in Excel 2003 and earlier).

In your adjustment weight matrix, enter a value 0 to 100 in each cell below the diagonal. The larger the value, the greater weight @RISK will place on keeping the original correlation coefficient. Therefore, larger values in the adjustment weight matrix cause @RISK to apply less adjustment to the corresponding correlation coefficients during this optimization, and smaller values let @RISK vary the corresponding correlation coefficients more. A value of 100 means that the corresponding coefficient must not be changed.

Technical details: The original correlation matrix is not self-consistent, meaning that it has one or more negative eigenvalues. We want to find a consistent matrix that is as close as possible to the inconsistent one, taking the adjustment weight matrix into account. This is a non-linear optimization problem, to minimize the weighted sum of squared differences between the inconsistent matrix and a candidate consistent matrix. We use the standard limited-memory BFGS algorithm to perform this optimization.

Weights are in the range 0 to 100, with 100 meaning "this value should not be changed" and 0 meaning "don't pay want attention to this value". Between those special values, other weights are treated in an exponential fashion, so that 50 versus 25 or 10 versus 5 doesn't mean "twice as important". The exact details of the exponential treatment are proprietary.

When @RISK adjusts an invalid matrix during simulation, it doesn't store the adjusted matrix anywhere permanent. @RISK does cache the adjusted matrix in your temporary folder, in a file called CORRMAT.MTX. It will reuse that file in future simulations if you haven't changed your original matrix.

Correcting a matrix outside of a simulation: You can perform the adjustment up front, rather than leaving @RISK to do it in every simulation. If you have a large correlation matrix, this can make a difference in the speed of your simulation. Use the RiskCorrectCorrmat( ) array function to place the corrected matrix in your worksheet, and have all your correlated inputs refer to the corrected matrix, not the original. A small example is attached; another example is available in @RISK 6.x/7.x via Help » Example Spreadsheets » Correlation section » Adjusting a Correlation Matrix.

When the RiskCorrectCorrmat( ) function performs an adjustment, it runs an optimization for the closest valid matrix to your original matrix, taking the weights into account. "Closest" means the valid matrix with the smallest sum of squared differences between its cell values and the cell values of your original invalid matrix. If your matrix is large, it may take considerable time to perform this optimization. You'll see messages on Excel's status line, referring to the step number (number of candidate valid matrices tried) and the residual (sum of squared differences). @RISK keeps at the optimization till the residuals stop decreasing sufficiently; unfortunately there's no way to know how many steps will be necessary, so @RISK can't give you a progress indicator in the form of percent complete.

No adjustment weight matrix

If you're running @RISK 4.x or 5.0, or if you're running a later version but you didn't specify an adjustment weight matrix, @RISK follows these steps to modify an invalid correlation matrix:

  1. Find the smallest eigenvalue (Eo)

  2. To shift the eigenvalues so that the smallest eigenvalue equals zero, subtract the product of Eo and the identity matrix (I) from the correlation matrix (C).

    C' = C – EoI

    The eigenvectors of the matrix are not altered by this shift.

  3. Divide the new matrix by 1 – Eo so that the diagonal terms equal 1.

    C'' = (1/(1−Eo)) C'

The matrix that @RISK calculates by this method is positive semi-definite, and therefore valid, but in no way is it special or optimal. It's one of many possible valid matrices, and some of the coefficients in it may be quite different from your original coefficients.

@RISK stores the new matrix in file CORRMAT.MTX in your temporary folder. You can use this as a guide to modify your matrix so that @RISK won't need to adjust it every time you run a simulation. See How @RISK Tests a Correlation Matrix for Validity to ensure that your edited matrix is self-consistent.

Additional keywords:  Corrmat property function, CorrectCorrmat function

Last edited: 2017-09-22


This page was: Helpful | Not Helpful