Palisade Knowledge Base

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:

Warning
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.

How do I set up and use an adjustment weight matrix?

This feature is available in @RISK 5.5 and newer.

You can 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.

In your adjustment weight matrix, enter a weight 0 to 100 in each cell below the diagonal. A weight of 100 means that the corresponding coefficient must not be changed, and a weight of 0 means that you don't care how much @RISK changes the corresponding coefficient. Between 0 and 100, larger weights place greater importance on the original coefficients. In other words, larger weights cause @RISK to apply less adjustment to the corresponding correlation coefficients, and smaller weights let @RISK adjust the corresponding correlation coefficients more.

The adjustment can be done during a simulation, or in a one-time procedure before a simulation. Both possibilities are explained below.

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

As mentioned above, weights are in the range 0 to 100. Between those special weights, other weights are treated in an exponential fashion. The exact details are proprietary, but 50 versus 25 or 10 versus 5 means "more important", not "twice as important".

Correcting a matrix during simulation:

The name of your adjustment weights matrix must match the range name of 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. If a correlation matrix is inconsistent, @RISK looks for an adjustment weights matrix with the right name, and if it finds one it will adjust the inconsistent matrix without displaying any message. 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. (In Excel 2003 and older, click Insert » Name » Define.)

Please see the attached example, KB75_AdjustDuringEverySimulation.xlsx.

When @RISK adjusts an invalid matrix during simulation, it doesn't store the adjusted matrix in your workbook or 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 make all your correlated inputs refer to the corrected matrix, not the original. With this approach, you can assign any name, or no name, to the adjustment weight matrix.

Please see the attached example, KB75_RiskCorrectCorrmat.xlsx.

When the RiskCorrectCorrmat( ) function performs an adjustment on a large matrix, it may take considerable time. 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.

What if I don't use an 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:  CorrectCorrmat function

Last edited: 2017-10-20

Downloads

This page was: Helpful | Not Helpful