Distributions in ModelRisk

See also: Distributions introduction, Modeling with objects, Distribution functions and the U parameter, Vose Select Distribution window

In ModelRisk a large number of distributions is included, from which you can sample random values, calculate the joint probability for given x data values, calculate and use the statistical moments, etc. We generally refer to these as VoseDistributions.

Each distribution has a separate topic with an explanation of its use and mathematics (e.g. Normal distribution). Use the links on the right to learn about the mathematics and use of a particular distribution.

Every univariate distribution in ModelRisk comes as a set of functions added to Excel: VoseDistribution, VoseDistributionProb, VoseDistributionProb10 and VoseDistributionObject. These functions are explained below.

These functions are also available for custom distributions like VoseDeduct, VoseCombined, VoseAggregatePanjer, VoseAggregateFFT, VoseRiskEvent, etc.

For a reminder of the parameters of a function, Excel's function arguments dialog can be convenient. You call this dialog by clicking next to the formula bar.

For an explanation about functions for fitting distributions, see Distribution fitting functions.

VoseDistribution

The general syntax for sampling a random value from a distribution is as follows:

=VoseDistribution([parameters separated by commas], U)

where Distribution is replaced by the name of the distribution.

• [parameters separated by commas] - each distribution has its own specific parameters. For example, the PERT takes a min, mode and max parameter - in that order. You can always look this information up on that distribution's ModelAssist topic or through the Function Arguments window as explained above.

• U - If an (optional) U-parameter is provided, the inverse cumulative percentile for that U-value is returned. U has to be a value on [0,1]. More info about the U parameter and its use can be found in this topic: Distribution functions and the U parameter

For example, to simulate a random value from a Cauchy(1,2) distribution, use

=VoseCauchy(1,2)

ModelRisk uses the Mersenne twister to generate random numbers.

To return the 99th percentile from a Cauchy(1,2) distribution, use

= VoseCauchy(1,2,0.99)

Multivariate distributions have an output of multiple cells, so random values from these are generated trough an array function.

VoseDistributionObject

With every distribution corresponds an Object function. When a ModelRisk function requires the distribution rather than a sampled value from it as a parameter, this parameter should be provided as a Distribution Object.

Using distribution Objects has the great advantage that you can keep your distributional assumptions in once place in the model, making it easier to maintain and update (analogous to keep your constants in one place).

The general syntax for creating a distribution object is as follows:

=VoseDistributionObject([parameters separated by commas])

where Distribution is replaced by the name of the distribution.

• [parameters separated by commas] - each distribution has its own specific parameters. For example, the PERT takes a min, mode and max parameter - in that order. You can always look this information up on that distribution's ModelAssist topic or through the Function Arguments window as explained above.

For example, to calculate the first four statistical moments of a LogNormal(1,2) distribution, you would use the VoseMoments array function on a LogNormal distribution object:

{=VoseMoments(VoseLogNormalObject(1,2))}

It is generally good practice to place a distribution object in a separate cell to keep overview. To place a Cauchy(1,2) Distribution Object in a spreadsheet cell you would use:

=VoseCauchyObject(1,2)

The above formula will be displayed as VoseCauchy(1,2).

For a more thorough explanation about objects see Modeling with objects.

VoseDistributionProb

These functions allow you to calculate the joint probability density/mass, joint cumulative probability and inverse cumulative of a given value or set of values.

General form:

VoseDistributionProb({x}, {parameters}, cumulative, truncation)

where Distribution is replaced by the name of the distribution.

• {x} - a set of one or more values or cell references, on which the probability is to be performed

• {parameters} - the parameters of the distribution

• Cumulative - an optional Boolean parameter. Set to FALSE (default) to return the joint probability density for continuous distributions or the joint probability mass for discrete distributions. Set TRUE to return the joint cumulative probability.

• Truncation - optional parameter that takes the form of either VoseXbounds(min,max) or VosePbounds(min,max), to truncate at specified x-values respectively p-values. Use VoseShift to shift the distribution along the X axis.

The probability calculation functions are explained more thoroughly here.

VoseDistributionProb10

The joint probability of probability density for a large set of values can quickly approach values too small for Excel to handle. Therefore ModelRisk has a set of functions that return Log base 10 of the probability calculations described above.

General form:

VoseDistributionProb10({x}, {parameters}, cumulative, truncation)

where Distribution is replaced by the name of the distribution.

• {x} - a set of one or more values or cell references, on which the probability calculation is to be performed

• {parameters} - the parameters of the distribution

• Cumulative - an optional Boolean parameter. Set to FALSE (default) to return the joint probability density for continuous distributions or the joint probability mass for discrete distributions. Set TRUE to return the joint cumulative probability.

• Truncation - optional parameter that takes the form of either VoseXbounds(min,max) or VosePbounds(min,max), to truncate at specified x-values respectively p-values.

The probability calculation functions are explained more thoroughly here.

Truncating  and shifting distributions

You can truncate or shift each of the distributions in ModelRisk by using the VoseXBounds, VosePBounds and/or VoseShift functions as parameter.  For example:

=VoseGammaObject(3,40,,VoseXBounds(,120))

produces a Gamma(3,40) distribution object constrained to lie below 120.

=VoseGammaObject(3,40,,VoseShift(30)

will generate random values from a Gamma(3,40) distribution shifted 30 units to the right along the X axis.

You can use both the shift and bound functions at the same time, separated by commas:

=VoseGamma(3,40,,VoseXBounds(70,120),VoseShift(30))

will generate random values of a Gamma(3,40) distribution constrained between 70 and 120 and then shifted 30 units to the right.

When using these functions for truncating and/or shifting a distribution, remember to leave an "open space" for the U parameter!

Apart from the method described above, one could also build logic into the model that rejects nonsensical values. For example, using the IF function: A2:=IF(A1<0,ERR(),0) only allows values into cell A2 from cell A1 that are >=0 and produces an error in cell A2 otherwise.

If you are faced with the problem of needing to constrain the tail of a distribution, however, to avoid unwanted values, it is worth questioning whether you are using the appropriate distribution in the first place.

For modifying a distribution specifically to model deductibles and payout limits of a claim severity distribution, see the Deduct calculation window.