A random variable X is log‑normally distributed if its natural logarithm Y = ln(X) is normally distributed. Equivalently:
– ln(X) ~ N(μ, σ^2)
– X > 0 always (no negative values)
This property makes the log‑normal useful for modeling strictly positive quantities that grow multiplicatively (for example, prices, sizes, and biological measures).
Probability formulas
– Probability density function (PDF):
f(x) = [1 / (x σ √(2π))] exp{ − [ln(x) − μ]^2 / (2 σ^2) }, for x > 0
– Cumulative distribution function (CDF):
F(x) = Φ((ln x − μ)/σ), where Φ is the standard normal CDF
Key summary statistics
– Mean: E[X] = exp(μ + σ^2/2)
– Median: Med[X] = exp(μ)
– Mode: Mode[X] = exp(μ − σ^2)
– Variance: Var[X] = [exp(σ^2) − 1] exp(2μ + σ^2)
– Skewness: positive, with a long right tail increasing with σ
Differences and connections between Normal and Log‑Normal
– Normal: variable Z can be negative or positive; symmetric bell shape. If Z ~ N(μ, σ^2), then probabilities are symmetric about μ.
– Log‑Normal: variable X is strictly positive and skewed right. If ln(X) is normal, X is log‑normal.
– Connection: Multiplicative processes become additive under logs. If returns (log returns) are additive and normally distributed, prices (product of growth factors) are log‑normal.
– Practical implication in finance: log returns (continuously compounded returns) are often modeled as normal; prices then follow a log‑normal distribution.
Why log‑normal matters in finance
– Asset prices: Models that assume normally distributed log returns (e.g., geometric Brownian motion) imply log‑normal price distributions. This underpins Black–Scholes option pricing.
– Compound returns: The log‑normal captures multiplicative compounding and asymmetric upside potential with bounded downside at zero.
– Risk metrics: Expected future price, percentiles, and tail probabilities can be computed from μ and σ of ln(price).
– Limitations: Real financial returns can exhibit fat tails and extreme events that deviate from log‑normality; empirical tests are needed before assuming it.
How to recognize a log‑normal distribution (practical checks)
1. Visual inspection
• Plot histogram of raw X: look for right skew.
• Plot histogram of ln(X): if ln(X) appears symmetric and bell‑shaped, log‑normal may be appropriate.
2. Q–Q plot
• Make a Q–Q plot of ln(X) against a normal distribution. Points roughly on a straight line suggest ln(X) ≈ normal.
3. Statistical tests
• Test normality on ln(X) with Shapiro–Wilk, Anderson–Darling, or Kolmogorov–Smirnov tests.
4. Consider theory
• Does the generating process multiply factors (e.g., growth rates)? If so, log‑normal is plausible.
Estimating log‑normal parameters from data (step‑by‑step)
Given observed positive data X1…Xn:
1. Transform: Yi = ln(Xi)
2. Estimate μ (mu) = mean(Yi) = (1/n) Σ ln(Xi)
3. Estimate σ^2 (sigma squared) = sample variance of Yi (use population or sample version depending on context)
4. Use μ and σ to compute mean, median, variance, percentiles of X via the formulas above
Excel practical steps and functions
Excel provides built‑in log‑normal functions. Key functions:
– LOGNORM.DIST(x, mean, standard_dev, cumulative)
• Returns CDF if cumulative = TRUE, or PDF if FALSE
• Note: mean and standard_dev are the mean and SD of ln(x) (not of x)
– LOGNORM.INV(probability, mean, standard_dev)
• Inverse CDF: gives x corresponding to a cumulative probability
– LN(x) to compute natural log; EXP(x) to reverse
– NORM.INV(probability, mean, standard_dev) to get quantiles of a normal
– NORM.S.INV(probability) for standard normal quantiles
Example workflows in Excel
A. Estimating parameters from price data
1. Suppose you have prices in column A (A2:A101).
2. In B2 put =LN(A2) and copy down to B101.
3. Compute μ: =AVERAGE(B2:B101)
4. Compute σ: =STDEV.S(B2:B101) (or STDEV.P if you prefer the population SD)
5. Compute expected price (mean of X): =EXP(μ + σ^2 / 2)
6. Compute median price: =EXP(μ)
7. Compute probability price ≤ x0: =LOGNORM.DIST(x0, μ, σ, TRUE)
B. Simulating log‑normal prices
1. Simulate a log return: in C2 put =NORM.INV(RAND(), μ_daily, σ_daily)
2. Convert to growth factor: =EXP(C2)
3. Simulate price path: start with S0 in D1, then D2 = D1 * EXP(C2), copy forward.
C. Computing percentiles or confidence bounds
– 95th percentile of X: =LOGNORM.INV(0.95, μ, σ)
– 5th percentile of X: =LOGNORM.INV(0.05, μ, σ)
Worked numeric example (illustrative)
– Suppose ln(price) has μ = 3 and σ = 0.2.
• Median price = exp(3) ≈ 20.085
• Mean price = exp(3 + 0.02) = exp(3.02) ≈ 20.49
• 95th percentile ≈ LOGNORM.INV(0.95, 3, 0.2) (compute in Excel)
Practical use cases in financial analysis
– Option pricing (Black–Scholes relies on log‑normal terminal asset prices)
– Valuation and scenario analysis for assets that cannot be negative
– Economic and financial stress testing for prices/volumes that compound multiplicatively
– Modeling sizes, incomes, or volatilities where multiplicative effects dominate
Cautions and limitations
– Empirical returns often show heavier tails and volatility clustering (heteroskedasticity) not captured by a simple log‑normal model.
– Log‑normal disallows zero or negative values; but some assets can effectively go to zero (bankruptcy) — treated carefully.
– Parameter estimates are sensitive to the sample period and data quality; always test goodness of fit.
The bottom line
Log‑normal distributions arise when multiplicative growth or normality of log‑values is plausible. In finance, modeling log returns as normal implies log‑normal prices and is central to option pricing and many simulation exercises. Use transformation, visual checks, and formal tests to validate the assumption; Excel has built‑in functions (LOGNORM.DIST, LOGNORM.INV, LN, EXP, NORM.INV) that let you estimate, calculate probabilities, and simulate log‑normal variables easily.
Sources
– Investopedia. “Log‑Normal Distribution.”
– Microsoft Support. “LOGNORM.DIST function.” / (see LOGNORM.DIST documentation)
Continuing the article — more sections, worked examples, practical steps, and a concluding summary.
Key formulas and properties
– If Y ~ N(mu, sigma^2) then X = exp(Y) has a log-normal distribution. The probability density function (pdf) for X > 0 is
f(x) = (1 / (x sigma sqrt(2π))) * exp(−(ln x − mu)^2 / (2 sigma^2)).
– Moments:
• Mean: E[X] = exp(mu + 0.5 sigma^2)
• Median: median(X) = exp(mu)
• Mode: mode(X) = exp(mu − sigma^2)
• Variance: Var(X) = (exp(sigma^2) − 1) * exp(2 mu + sigma^2)
– Skewness and excess kurtosis are positive (long right tails); skewness = (exp(sigma^2) + 2) * sqrt(exp(sigma^2) − 1).
These formulas follow from the normal distribution of ln(X) and are widely used in finance and statistics (see Investopedia).
Practical step-by-step: fit a log-normal to data (prices)
1. Collect only positive observations (log-normal supports x > 0). If your data contain zeros or negatives, log-normal modeling requires adjustments (e.g., shift parameters or different models).
2. Transform: compute Yi = ln(Xi) for each observation Xi.
3. Estimate parameters of the normal distribution of the logs:
• mu_hat = mean(Yi) = AVERAGE(LN(range)) in Excel
• sigma_hat = standard deviation(Yi) = STDEV.S(LN(range)) in Excel
4. Interpret:
• median price ≈ exp(mu_hat)
• mean price ≈ exp(mu_hat + 0.5 * sigma_hat^2)
5. Check goodness of fit:
• Q–Q plot of Yi vs theoretical normal
• Histogram of Yi vs fitted normal curve
• Formal tests (e.g., Shapiro–Wilk on Yi, or Kolmogorov–Smirnov on X vs fitted lognormal)
6. If necessary, consider alternative heavy-tailed models or a mixture of lognormals.
Excel: common tasks and formulas
– Evaluate the CDF or PDF:
• LOGNORM.DIST(x, mean, standard_dev, cumulative)
• x = value at which to evaluate
• mean = mean of ln(x)
• standard_dev = standard deviation of ln(x) (must be > 0)
• cumulative = TRUE for CDF, FALSE for PDF
• Source: Microsoft documentation.
– Inverse (quantile): LOGNORM.INV(probability, mean, standard_dev) (or use LOGNORM.INV in versions that support it).
– Generate log-normal random draws:
• Use EXP(NORM.INV(RAND(), mu, sigma)) where mu and sigma are parameters of ln(X). Or in newer Excel versions: LOGNORM.INV(RAND(), mu, sigma).
– Estimate parameters from price range A2:A101:
• mu_hat = AVERAGE(LN(A2:A101))
• sigma_hat = STDEV.S(LN(A2:A101))
– Example: compute probability price > threshold K:
• P(X > K) = 1 − LOGNORM.DIST(K, mu_hat, sigma_hat, TRUE)
Worked finance example (stock price)
Assume current price S0 = 100. Suppose historical continuous (log) returns suggest ln(S_T/S0) has mean µc = 8% per year and volatility σ = 20% per year. For a one-year horizon T = 1
1. Under geometric Brownian motion (GBM) conventions, ln(S_T) ~ N( ln(S0) + (µc − 0.5 σ^2)T, σ^2 T ).
• mu_ln = ln(100) + (0.08 − 0.5*0.2^2)*1 = 4.60517 + (0.08 − 0.02) = 4.66517
• sd_ln = σ sqrt(T) = 0.2
2. Expected price:
• E[S_T] = exp(mu_ln + 0.5 * sd_ln^2) = exp(4.66517 + 0.02) ≈ 108.33
• Equivalently, E[S_T] = S0 * exp(µc * T) = 100 * exp(0.08) ≈ 108.33
3. Probability S_T > 120:
• z = (ln(120) − mu_ln) / sd_ln = (4.78749 − 4.66517) / 0.2 ≈ 0.6116
• P(S_T ≤ 120) ≈ Φ(0.6116) ≈ 0.7296 → P(S_T > 120) ≈ 0.2704
• Excel: =1 − LOGNORM.DIST(120, mu_ln, sd_ln, TRUE)
4. Note on risk-neutral pricing: replace µc with risk-free rate r (under Q measure) when valuing derivatives; this is the Black–Scholes assumption that terminal stock prices are lognormally distributed under risk-neutral drift (Black & Scholes, 1973).
Monte Carlo simulation (practical steps)
1. Choose N simulations (e.g., 10,000).
2. For each simulation i:
• Draw Z_i ~ N(0,1)
• Compute S_T^(i) = S0 * exp((µ − 0.5 σ^2) T + σ sqrt(T) * Z_i) (µ = drift; use r for risk-neutral)
3. Collect S_T samples and compute statistics (mean, quantiles), or feed into payoff functions (e.g., max(S_T − K, 0) for option payoff).
4. In Excel formula terms, for one simulation cell:
• =S0 * EXP((mu − 0.5*sigma^2)*T + sigma*SQRT(T)*NORM.S.INV(RAND()))
5. In Python (numpy) the equivalent:
• import numpy as np
• Z = np.random.normal(size=N)
• ST = S0 * np.exp((mu − 0.5*sigma**2)*T + sigma*np.sqrt(T)*Z)
Common pitfalls and cautions
– Zero or negative data: log transform fails at zero/negative values. Consider a shifted lognormal or alternative distributions.
– Parameter instability: mu and sigma estimated from a short sample may be noisy. Volatility especially can change across regimes.
– Heavy tails: empirical asset returns often show fatter tails and clustering of volatility (GARCH effects) not captured by a plain log-normal model on prices or normal model on log returns.
– Misinterpreting mu: be careful whether µ refers to mean of ln(X) or mean of ln(S_T/S0). Different conventions can cause errors when computing expected values.
– Using ordinary means: the arithmetic mean of prices is not the same as the mean implied by log-normal parameters — transform correctly.
– Confidence intervals for E[X]: the mean of a lognormal has a skewed sampling distribution; confidence intervals for E[X] are best obtained by bootstrapping or via the delta method; confidence intervals for the median are simply obtained by transforming the CI for mu (median = exp(mu)).
Example: estimating a 95% CI for the median price
– After fitting mu_hat and sigma_hat from Yi = ln(Xi), a 95% CI for mu is mu_hat ± t_{n−1,0.975} * SE_mu where SE_mu = sigma_hat / sqrt(n).
– Transform the interval endpoints back with exp(·) to obtain the CI for median(X).
When log-normal is the right model in finance
– Individual stock prices over short-to-moderate horizons often modeled as log-normal under GBM assumptions.
– Black–Scholes option pricing and many analytic derivative formulas rely on log-normal terminal prices under risk-neutral drift (Black & Scholes, 1973).
– Modeling positive-valued quantities (e.g., time-to-failure, certain incomes, insurance claim sizes) where multiplicative processes are plausible.
– Modeling multiplicative growth processes (compound returns), where log returns are additive and often approximately normal.
When to prefer alternatives
– If returns show strong skewness/kurtosis beyond what the log-normal can explain, consider Student’s t for returns, jump-diffusion models, or mixtures of lognormals.
– For volatility clustering, use stochastic volatility or GARCH models that let σ vary over time.
– For zero/negative values, use shifted lognormal, gamma, or other distributions suited to the support of your data.
Quick reference: formulas and Excel snippets
– Fit parameters from price in A2:A101:
• mu_hat: =AVERAGE(LN(A2:A101))
• sigma_hat: =STDEV.S(LN(A2:A101))
– CDF at x: =LOGNORM.DIST(x, mu_hat, sigma_hat, TRUE)
– PDF at x: =LOGNORM.DIST(x, mu_hat, sigma_hat, FALSE)
– Simulate one log-normal draw: =EXP(NORM.INV(RAND(), mu_hat, sigma_hat)) or =LOGNORM.INV(RAND(), mu_hat, sigma_hat)
– Probability X > K: =1 − LOGNORM.DIST(K, mu_hat, sigma_hat, TRUE)
Concluding summary
The log-normal distribution is a natural model when the log of a positive random variable is approximately normal. In finance it underpins much of classical pricing theory (e.g., Black–Scholes) and is a convenient model for stock prices because it enforces positivity and encodes multiplicative growth. Practical use requires correct parameter estimation on log-transformed data, careful interpretation of means vs medians, and awareness of empirical departures from the model (heavy tails, volatility clustering). Excel provides built-in functions (LOGNORM.DIST, LOGNORM.INV) to evaluate, simulate, and fit log-normal behavior, and Monte Carlo approaches allow flexible scenario analysis when closed-form results are inadequate.
References and further reading
– “Log-Normal Distribution,” Investopedia. (general explanation and financial use cases)
– Microsoft, LOGNORM.DIST function documentation.
– Black, F. & Scholes, M. (1973). “The Pricing of Options and Corporate Liabilities.” Journal of Political Economy.