Statistical Analysis
Lognormal Cumulative Probability, Mean-Median Skew & Quantiles (LOGNORMDIST & LOGNORM.INV)
Model strictly positive right-skewed claim severities and response latencies by mapping log-space mean mu and volatility sigma into lognormal medians, expected means, cumulative probabilities, and VaR quantiles.
Why lognormal expected value exceeds the median by EXP(sigma^2 / 2)
A positive random variable X follows Lognormal(mu, sigma) when LN(X) is normally distributed with mean mu and standard deviation sigma. Because exponentiation stretches upper-tail realizations asymmetrically, the median sits at EXP(mu) while the arithmetic expected value rises to EXP(mu + 0.5 * sigma^2).
Any positive threshold x > 0 maps directly to a standard normal score z = (LN(x) - mu) / sigma for cumulative probability evaluation, and any target quantile p inverts via x_p = EXP(mu + z_p * sigma).
| Property | Closed-Form Expression | At mu = 4, sigma = 0.5 | Interpretation |
|---|---|---|---|
| Median (50th pct) | EXP(mu) | 54.60 | Half of claims fall below 54.60 |
| Expected Mean | EXP(mu + 0.5 * sigma^2) | 61.87 | Right tail lifts mean +13.3% above median |
| Log-Z at x = 148.41 | (LN(x) - mu) / sigma | ~2.00 | Upper 97.72% cumulative threshold |
| 97.5% Quantile | EXP(mu + 1.96 * sigma) | 145.47 | Reserve cap for 97.5% coverage |
Lognormal Median & Expected Mean Skew
Tracking both EXP(mu) and EXP(mu + 0.5 * sigma^2) prevents under-budgeting when log-volatility sigma increases.
=ROUND(EXP(B2 + 0.5 * B3 ^ 2), 2)Lognormal Quantile & Cumulative Probability
Map x into z_val = (LN(x) - mu) / sigma for cumulative probability, or exponentiate mu + z_p * sigma for the inverse quantile.
=ROUND(EXP(B2 + B5 * B3), 2)Try the example
Use the original values or change them to test your understanding.