How to Use LOGNORM.DIST and LOGNORM.INV Functions in Microsoft Excel for Lognormal Probability

In quantitative finance, engineering reliability analysis, and environmental science, many physical and financial metrics cannot take negative values and display significant positive skewness. Financial asset prices, stock returns, income distributions, internet traffic bursts, and mechanical component lifespans frequently conform to lognormal distributions rather than standard Gaussian normal distributions. To model these asymmetric distributions accurately, Microsoft Excel provides two complementary functions: LOGNORM.DIST to calculate exact and cumulative probabilities, and LOGNORM.INV to compute the inverse quantile values for risk thresholds. Mastering both functions is indispensable for financial risk modelling and data analysis.

Understanding the Lognormal Distribution

A random variable X is lognormally distributed if its natural logarithm, ln(X), follows a normal distribution. While a normal distribution is symmetric around its mean and extends infinitely in both positive and negative directions, a lognormal distribution is bounded strictly on positive values (x > 0) with a long right-tail skew.

The distribution is characterised by two underlying parameters:

• Mean (μ): The mean of the natural logarithm of the distribution (ln(x)), not the mean of the raw values themselves.
• Standard Deviation (σ): The standard deviation of the natural logarithm of the distribution, which must be strictly greater than 0.

1. The LOGNORM.DIST Function: Syntax and Arguments

The LOGNORM.DIST function evaluates the probability density or cumulative probability for a specified value:

=LOGNORM.DIST(x, mean, standard_dev, cumulative)

Its arguments are defined as follows:

• x (Required): The value at which to evaluate the function. Must be strictly positive (x > 0).
• mean (Required): The mean of ln(x).
• standard_dev (Required): The standard deviation of ln(x). Must be greater than 0.
• cumulative (Required): A logical value:
  • TRUE: Returns the Cumulative Distribution Function (CDF)—the probability that the random variable is less than or equal to x.
  • FALSE: Returns the Probability Density Function (PDF)—the relative likelihood at point x.

Practical Example: Stock Price Threshold Probability

Suppose a financial analyst models a stock price expected in one year. Historical analysis estimates the log-mean at 3.5 (mean = 3.5) and the log-standard deviation at 0.4 (standard_dev = 0.4). What is the probability that the stock price will end the year below £40?

=LOGNORM.DIST(40, 3.5, 0.4, TRUE)

Excel returns approximately 0.6806 (or a 68.06% probability that the price will remain below £40).

To determine the probability that the stock exceeds £50:

=1 - LOGNORM.DIST(50, 3.5, 0.4, TRUE)

2. The LOGNORM.INV Function: Syntax and Arguments

When conducting Value-at-Risk (VaR) calculations or setting mechanical safety margins, analysts work backwards from a target confidence percentile to find the corresponding value. The LOGNORM.INV function calculates this inverse quantile:

=LOGNORM.INV(probability, mean, standard_dev)

Its arguments include:

• probability (Required): A probability corresponding to the lognormal distribution. Must be between 0 and 1 exclusive (0 < probability < 1).
• mean (Required): The mean of ln(x).
• standard_dev (Required): The standard deviation of ln(x). Must be positive (> 0).

Practical Example: Component Failure Lifespan Threshold

A manufacturing plant tests industrial pump seals whose lifespans (in operating hours) follow a lognormal distribution with mean = 8.2 and standard_dev = 0.5. The engineering team wants to determine the operational warranty threshold below which only 5% of seals will fail (the 5th percentile):

=LOGNORM.INV(0.05, 8.2, 0.5)

Excel returns approximately 1600.28 hours, indicating that 95% of seals will survive beyond 1,600 operating hours.

Troubleshooting Common Errors

• #NUM! Error: Returned if x <= 0 in LOGNORM.DIST, if standard_dev <= 0, or if probability <= 0 or probability >= 1 in LOGNORM.INV. Verify that inputs strictly satisfy domain limits.
• #VALUE! Error: Occurs when non-numeric text values or corrupted cell references are supplied.
• Legacy Functions: Older workbooks may reference LOGNORMDIST or LOGINV without periods. While still supported for backwards compatibility, always replace them with the modern, standardised LOGNORM.DIST and LOGNORM.INV.

Integrating LOGNORM.DIST and LOGNORM.INV into your financial models and risk calculations provides robust mathematical scaffolding for analysing non-negative, positively skewed data in Microsoft Excel.