Home → Techniques and Tips → Correlation in @RISK → Same Correlation Coefficients for Several Groups of Inputs
Applies to: @RISK 4.x–7.x
I have multiple groups of inputs, and I want to use the same set of correlation coefficients for each group. But @RISK correlates all the inputs of all the groups together, which is not what I want. How do I tell @RISK that inputs A, B, C are correlated with each other, and D, E, F are correlated with each other with the same coefficients, but A, B, and C are not correlated with D, E, and F?
The short answer is to use the optional "instance" argument to RiskCorrmat( ), assigning a different instance to each group of correlated inputs. See attached example CorrelationGroups.xls. After a simulation, the worksheet CorrelationAudit_Report within that workbook shows sample correlations within a group and between groups.
You can set up the correlations by pointing and clicking (Model A below) or by formula editing (Model B below). These methods will work with any number of groups, and any number of inputs per group.
Solution details — point and click, Model A:
If the correlated groups aren't too large and there aren't too many of them, you can easily correlate separate groups of inputs through menu selections. For simplicity we'll show two groups of three inputs each. Within the attached example CorrelationGroups.xls, the two worksheets ModelA and @RISK Correlations were created by this method.
In @RISK 5.x–7.x:
You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.
Special case: If the groups of inputs are in a contiguous rectangular array, either as rows or as columns, you can short-cut the above process:
In @RISK 4.x:
You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.
Solution details — formula editing, Model B:
As an alternative to point-and-click, you can take advantage of Excel's ability to replicate formulas by dragging the fill handle. (Search Excel help for "fill handle" if this is unfamiliar to you.) This method scales well to larger groups of correlated inputs, or greater numbers of groups.
For this example we'll show ten groups of four inputs each, representing growth in the value of stocks and a bank account over ten years. Performances of stocks in a given year are positively correlated to each other but negatively correlated to interest rates. Within the attached example CorrelationGroups.xls, worksheet ModelB was created by this method.
Create your correlation matrix; row and column heads are optional but help to document the model. Highlight just the actual coefficients and define a name for them (menu selection Insert, Name, Define). In our example the correlation matrix including headings is C18:G23, and SecondCorr is the name of the 4×4 array of coefficients in D20:G23.
Set up your first group of correlated inputs as one row or one column. Create the distribution in the usual way, but add a RiskCorrmat function as an additional argument within the distribution function. The three arguments to RiskCorrmat are the name you assigned to the correlation matrix, the input number, and the instance. For reasons that will become clear in the next step, the instance argument should be a reference to the column header.
In our example, the first group is Year 1, in column E. Growth factors are the @RISK distributions in cells E9, E11, E13, and E15; look at the formulas for those cells and see how the RiskCorrmat function is used. The new values at year end are in cells E10, E12, E14, and E16. Notice that the growth factors are correlated, but the year-end values are not.
Highlight the cells of the first year, E8:E16, and drag the fill handle to create the additional groups through year 10 in column N. Notice how the instance argument changes in each group, but is the same for all the inputs within a group; this was the reason for the cell reference in step 2. Note also that the named correlation matrix does not change from one column to the next.
You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.
Additional keywords: Corrmat property function
Last edited: 2016-12-12