Applies to: @RISK 5.x and Up

 

RiskCompound Function

Syntax:

RiskCompound(dist#1 or value or cellRef, dist#2 or cellRef, deductible, limit)

Purpose:

RiskCompound models risks that may occur a variable number of times, with the impact of each occurrence governed by a distribution.

Returns the sum of a random number of independent values drawn from a specified distribution.

How it works:

1. Frequency distribution (dist#1): Determines the number of samples to draw from dist#2.

2. Severity distribution (dist#2): Values to be summed.

3. Optional deductible: Subtracted from each dist#2 sample.

4. Optional limit: If (dist#2 sample − deductible) exceeds the limit, the sample is capped at the limit.

Evaluation:

• In each simulation iteration, RiskCompound first samples dist#1 (or a specified value/cell).

• This determines how many samples to draw from dist#2.

• The sum of these samples, after applying deductible and limit, is returned as the function value.

Example 1:

• • Frequency (dist#1): Probability of occurrence (e.g., RiskBinomial, RiskPoisson)
    • Number of claims
  • Severity (dist#2): Impact or severity (e.g., RiskNormal, RiskTriang)
    • Claim amounts (severity)

  • RiskCompound returns the total claims for the iteration.

Example 2:

• • Risk occurs with 40% probability, severity follows RiskNormal(100, 10):
    =RiskCompound(RiskBinomial(1, 0.4), RiskNormal(100, 10))

  • Risk occurs with a variable frequency, using a Poisson distribution for frequency:
    =RiskCompound(RiskPoisson(1.4), RiskNormal(100, 10))

Handling Frequency and Severity with Cell References:

• Embedded distributions (inside RiskCompound) can speed up simulations, especially with large frequencies.

• Cell references for frequency or severity work but can impact performance if severity is re-evaluated multiple times (e.g., for each frequency occurrence).

Continuous Frequency Distributions:
If a continuous distribution is used for frequency, @RISK truncates the value to an integer. For example, a value of 3.7 in a frequency distribution results in 3 occurrences.

Deductible and Limit:

• Per-occurrence: Deductible and limit are applied to each severity sample.

• Example: If the frequency is 6, severity samples are drawn 6 times, with the deductible and limit applied to each.

• Formula: MIN(limit, MAX(sample - deductible, 0))

Notes:

• The deductible and limit are applied individually to each occurrence.

• The RiskCompound function sums all occurrences' values for the total in each iteration.

 

Correlation of RiskCompound

RiskCompound() itself can’t be directly correlated, as @RISK can’t know in advance how many severity samples it will draw. Instead, you can:

• Correlate the frequency distributions or use the same frequency distribution for multiple RiskCompound() functions, ensuring simultaneous occurrences.

• “Unpack” the severity distribution into multiple copies summed with Excel’s SUM(), replacing RiskCompound with explicit frequency and severity distributions.

See the attached workbooks Correlating RiskCompound.xlsx and Unpacking RiskCompound.xlsx for examples.

Attachments:
DTS-2552 explanation.xlsb