Determining the Number of Iterations for a Desired Confidence Interval in @RISK

Applies to: @RISK for Excel 5.x and Up

Question:
How many iterations do I need in my simulation so that the estimate of the mean is accurate within a specific confidence interval?

Monte Carlo Sampling

The number of iterations depends on the sampling method. For traditional Monte Carlo sampling, the iterations needed to estimate the mean within a desired margin of error can be calculated using:

[
n = \left( \frac{z_{\alpha/2} \cdot S}{E} \right)^2
]

Where:

• n = number of iterations needed

• S = estimated standard deviation of the output

• E = desired margin of error (half the confidence interval width)

• zα/2 = critical value from the standard normal distribution corresponding to the confidence level

Example (95% confidence, ±5 units margin):

1. Determine α: 95% confidence → α = 0.05 → α/2 = 0.025

2. Compute zα/2 in Excel: =NORMSINV(1-0.025) → z ≈ 1.96

3. Estimate standard deviation (e.g., with RiskStdDev over 100 iterations) → S ≈ 53.5

4. Calculate n:

[
n = \left( \frac{1.96 \times 53.5}{5} \right)^2 \approx 440
]

Thus, at least 440 iterations are needed to be 95% confident the mean is within ±5 units.

Latin Hypercube Sampling

• Produces sample means that are closer together for the same number of iterations.

• Requires fewer iterations to achieve the same confidence, but no simple formula exists.

• See: Latin Hypercube Versus Monte Carlo Sampling and Confidence Interval with Latin Hypercube Sampling in @RISK documentation.

Convergence Method

Instead of pre-calculating iterations, you can set convergence criteria:

1. In Simulation Settings → General, set Number of Iterations to Automatic.

2. In the Convergence tab, set the desired Convergence Tolerance (margin of error as a percentage of the statistic).

3. @RISK will run until the target confidence is reached.

Summary:

Monte Carlo: calculate n with standard formula.

• Latin Hypercube: fewer iterations suffice; use convergence criteria.

• Convergence approach often simpler than pre-computation.

 

Related Articles:

Latin Hypercube Versus Monte Carlo Sampling

Section "Confidence Interval with Latin Hypercube Sampling" in Confidence Intervals in @RISK.

Attachments:
ConfIntervalWidth2.xls