MCM_CI(_V)

Description

The Monte Carlo (MC) method of error propagation uses simulations to predict the effects of uncertainty on transformed random variables. These two functions apply the MC method to estimate a 95% confidence interval for the transformed values. There are two functions: MCM_CI is seedable and nonvolatile, and MCM_CI_V is volatile and uses Excel’s faster and higher-quality RNG.

Syntax

This function depends on MCM (or MCM_V) and uses the same syntax, which is given below. The documentation for the MCM function should be consulted for details about the input arguments, particularly for defining the callback lambda function that extracts and transforms the input variables.

• MCM_CI(vars, se_vars, fn, [N=50000], [DFs], [skew=0], [kurt], [seed_str])
• MCM_CI_V(vars, se_vars, fn, [N=50000], [DFs], [skew=0], [kurt])

One difference from MCM is that the default is 50,000 simulations. Distributions with long tails might require a larger number for acceptable precision.

Example

The example below continues the example illustrated in the MCM documentation; the transformation resulted in a significant skew in the population values. In this example we are:

• assuming the standard deviations have known degrees of freedom (and hence accounting for the uncertainty in those values)
• using the volatile function for speed and accuracy (but it means that the values you see when you open the workbook might be slightly different)
• using $10^4$ simulations

Download: MCM-CI.xlsx

Source Code

Helpers used: FSolve, FSolveBump, FSolveLadder, FSolveMonotonic

The two functions are essentially identical, one calling MCM (and passing along any seed string) and the other calling MCM_V.

MCM_CI = LAMBDA(
    vars, se_vars, fn,
    [N], [DFs], [skew], [kurt], [seed_str],
    LET(
        N, IF(ISOMITTED(N), 50000, N),
        sims, MCM(vars, se_vars, fn, N, DFs, skew, kurt, seed_str),
        IF(
            ISTEXT(INDEX(sims,1,1)),
            sims,
            VSTACK(PERCENTILE.INC(sims, 0.025), PERCENTILE.INC(sims, 0.975))
        )
    )
);

MCM_CI_V = LAMBDA(
    vars, se_vars, fn,
    [N], [DFs], [skew], [kurt],
    LET(
        N, IF(ISOMITTED(N), 50000, N),
        sims, MCM_V(vars, se_vars, fn, N, DFs, skew, kurt),
        IF(
            ISTEXT(INDEX(sims,1,1)),
            sims,
            VSTACK(PERCENTILE.INC(sims, 0.025), PERCENTILE.INC(sims, 0.975))
        )
    )
);