Lognormal distribution in Excel: LOGNORM.INV, parameters and when to use it
In short: a quantity that is usually moderate but now and then turns out several times larger, such as the cost of a repair. It never goes below zero.
A lognormal distribution describes a quantity that cannot fall below zero and has a long upper tail, such as a cost, a loss or a duration; its logarithm is normally distributed. In Excel, =LOGNORM.INV(RAND(), LN(median), s) draws from it. A claim’s repair cost with a median of 2,000 (USD) and s = 0.8 has a mean of 2,754, above the median, and a P90 of 5,576, 2.8 times the median.
What a lognormal distribution is
Where a normal distribution adds up small, independent effects, a lognormal one multiplies them: a repair that turns out 20% more complex, then needs a part 30% dearer, then takes 10% longer. Its values are always positive, most of them gather near the low end, and a thin tail reaches far to the right: a few claims cost several times the typical one.
How to read the chart: each bar counts how many of the 10,000 simulated values fell in that stretch, and the line is the theoretical curve. The taller the bar, the more often values land there. The long, low stretch on the right is the rare, expensive claims.
Two numbers set it. The median is the typical value: half the outcomes lie below it. The spread s sets how far the tail reaches: around 0.25 the values stay fairly close to the median, around 1 some are several times larger. (Technically, s is the standard deviation of the logarithm of the values.) The mean, the peak and the percentiles all follow from these two numbers.
Formulas: mean, standard deviation, peak and percentiles
| Quantity | Formula and example |
|---|---|
| Mean | median × es²/2 = 2,000 × e0.32 = 2,754 |
| Standard deviation | mean × √(es² − 1) = 2,608 |
| Peak (mode) | median × e−s² = 1,055 |
| P10 and P90 | median × e∓1.2816 s = 717 and 5,576 |
The order peak < median < mean is typical of a right-skewed quantity. The mean is pulled up by the tail: only 34.5% of the simulated claims cost more than the average claim. A budget built on the median, or on the most likely cost, falls short of the average.
The lognormal distribution in Excel
LOGNORM.INV(probability, mean, standard_dev) is easy to misread: its mean and standard deviation are those of the logarithm of the values, not of the values. For the claim above they are LN(2,000) = 7.60 and 0.8. Entering the mean and standard deviation of the costs themselves is the most common mistake with this function, and it gives meaningless results. The workbook therefore keeps the median in a cell and passes LN(median); in one cell the draw is =LOGNORM.INV(RAND(),LN(D6),F6).
| Cell | Formula | Value |
|---|---|---|
| Random number u (I6) | =RAND() | |
| Draw (J6) | =LOGNORM.INV(I6, | |
| Draw in one cell | =LOGNORM.INV(RAND(), | |
| Mean (K6) | =D6*EXP(F6^2/2) | 2,754 |
| P10 (L6) | =LOGNORM.INV(0.1, | 717 |
| P90 (M6) | =LOGNORM.INV(0.9, | 5,576 |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 2,754 | 2,797 | 2,754 |
| Standard deviation | 2,608 | 2,659 | 2,603 |
| P10 | 717 | 734 | 717 |
| P50 (median) | 2,000 | 2,020 | 2,000 |
| P90 | 5,576 | 5,642 | 5,575 |
Excel draws with RAND(), fully at random; xellstorm spreads its draws evenly over the range (Latin Hypercube sampling), so its statistics land closer to the exact values.
If you know the mean and the standard deviation of the values instead, convert them first: s = √(ln(1 + (sd / mean)²)) and median = mean / √(1 + (sd / mean)²). A mean of 3,000 and a standard deviation of 1,500 give s = 0.4724 and a median of 2,683. In Excel: =SQRT(LN(1+(sd/mean)^2)) and =mean/SQRT(1+(sd/mean)^2). LOGNORM.DIST(x, LN(median), s, TRUE) gives the chance of a value below x, and the older LOGINV works like LOGNORM.INV.
When to use a lognormal distribution
- Costs, losses and claim sizes that cannot be negative and occasionally run to several times their typical value.
- Durations and lead times: a job rarely finishes much faster than usual but can take far longer, as in the delivery lead time example, where a lognormal is fitted to past deliveries.
- Prices and growth factors that change by percentages: a price that rises or falls by random percentages over many periods ends up lognormal.
Compared with the alternatives: a normal distribution is symmetric and can go negative; a PERT or triangular distribution has a hard maximum, which suits an expert’s range but not a quantity that can overrun by a multiple; a gamma distribution has the same kind of shape with a thinner tail. When you have data, fit both lognormal and gamma and compare.
Entering a lognormal distribution in xellstorm
- Make the cell an input and, on the Distributions step, choose Lognormal.
- Its fields follow scipy: s is the spread of the logarithm, scale the median and loc a shift, 0 unless the values start above zero. For the claim: s 0.8, scale 2000, loc 0. The Mean and P10 – P90 columns confirm what you typed.
- If you think in percentiles, select the input and use “From estimates” in the side panel: type a P10, P50 and P90 and choose Lognormal, which finds s and the median for you. With past values, “From data” fits a lognormal with loc 0 and compares it with other shapes.
“Import from workbook” converts =LOGNORM.INV(RAND(), mu, sigma) cells into lognormal inputs, and add-in functions such as RiskLognorm (mean and standard deviation of the values), RiskLognorm2 (of their logarithm), VoseLognormal and PsiLogNormal.
Questions
How do I generate lognormal random numbers in Excel?
Use =LOGNORM.INV(RAND(), LN(median), s), where the median is the typical value and s the standard deviation of its logarithm. For a median of 2,000 and s = 0.8, that is =LOGNORM.INV(RAND(), LN(2000), 0.8). Each recalculation draws a new value.
What are the mean and standard deviation in LOGNORM.INV?
They are the mean and standard deviation of ln(x), often written μ and σ, not of the values themselves. μ is the logarithm of the median: LN(2,000) = 7.60. To start from the mean and standard deviation of the values, convert them: σ = √(ln(1 + (sd / mean)²)) and μ = ln(mean) − σ² / 2.
Why is the mean of a lognormal distribution higher than its median?
Because its tail reaches far to the right while values cannot fall below zero: the occasional very large value lifts the average. The mean is the median × es²/2, so the gap grows with the spread: with s = 0.8, a median of 2,000 gives a mean of 2,754.
Lognormal or normal: which should I use?
Use a normal distribution for quantities that vary symmetrically around a target and stay far from zero. Use a lognormal for positive quantities whose upside is longer than their downside: costs, losses, durations, prices. When the standard deviation is small compared with the mean (below about a tenth of it), the two are almost identical.
Related
- Guide
Which probability distribution should I use? A guide for Monte Carlo models
Find the right probability distribution in two questions: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta and more, with what each is for and its Excel formula. - Guide
Gamma distribution in Excel: waiting times, totals and GAMMA.INV
The gamma distribution describes positive, right-skewed quantities such as the total time of several steps. Shape and scale, GAMMA.INV and GAMMA.DIST in Excel, and gamma vs lognormal. - Delivery lead times
What is the chance a delivery arrives on time?
Fit distributions to a small, synthetic delivery history, compare on-time probabilities and see why more simulation trials do not fix a small sample. Includes an Excel workbook.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.