LOGNORM.INV function

The LOGNORM.INV function in Excel calculates the inverse of the log-normal distribution. Specifically, it returns the value $x$ for which the cumulative probability (or percentile) equals a given probability $p$, based on the log-normal distribution. Essentially, it helps to find the data value corresponding to a specific cumulative probability or percentile for a log-normal distribution.

This function is useful when you have a log-normal distribution and want to find the value of the random variable that corresponds to a specific cumulative probability.

Syntax:

LOGNORM.INV(probability, mean, standard_dev)

Arguments:

• probability: Required. The cumulative probability for which you want to find the corresponding value $x$. This value must be between 0 and 1, where 0 represents the minimum possible value and 1 represents the maximum possible value. For example, a probability of 0.95 means you’re looking for the value corresponding to the 95th percentile.
• mean: Required. The mean (average) of the natural logarithm of the distribution. This is the mean of the underlying normal distribution.
• standard_dev: Required. The standard deviation of the natural logarithm of the distribution. This is the standard deviation of the underlying normal distribution.

Output:

The function returns the value $x$ such that the probability that a random variable from the log-normal distribution is less than or equal to $x$ is equal to the specified probability.

How It Works:

The LOGNORM.INV function uses the inverse of the cumulative distribution function (CDF) for the log-normal distribution. The relationship is based on the fact that if $Y$ follows a normal distribution, then $X = e^Y$ follows a log-normal distribution. The function calculates the value $x$ for a given cumulative probability based on the log-normal model:

$$x = e^{(\mu + \sigma \cdot Z)}$$

Where:

• $\mu$ is the mean of the natural logarithm of the distribution.
• $\sigma$ is the standard deviation of the natural logarithm of the distribution.
• $Z$ is the z-score corresponding to the given cumulative probability, which is derived from the inverse normal distribution.

Example:

1. Example 1: Finding the Value Corresponding to a Cumulative Probability Suppose you have a log-normal distribution with a mean of 0.5 and a standard deviation of 0.2. You want to find the value of $x$ corresponding to the 95th percentile (cumulative probability = 0.95).

   Use the formula:

   =LOGNORM.INV(0.95, 0.5, 0.2)
   

   This will return the value of $x$ such that the cumulative probability for the log-normal distribution is 0.95. This is the value that represents the 95th percentile of the distribution.

2. Example 2: Finding the Value for a Lower Probability If you want to find the value corresponding to the 5th percentile (cumulative probability = 0.05), use:

   =LOGNORM.INV(0.05, 0.5, 0.2)
   

   This will return the value corresponding to the lower 5% of the distribution.

Key Points:

• The probability argument specifies the cumulative probability (or percentile) for which you want to find the corresponding value $x$ in the log-normal distribution.
• The mean and standard deviation refer to the normal distribution of the logarithms of the values in the data, not the log-normal distribution itself.
• The LOGNORM.INV function is useful for finding quantiles of a log-normal distribution, making it applicable in fields like finance, economics, and scientific data analysis.

Use Cases:

• Finance: In finance, the LOGNORM.INV function is used to determine the price or return threshold corresponding to a specific cumulative probability (e.g., the 95th percentile of stock prices or financial returns).
• Risk Management: It helps to model the worst-case scenarios or extreme values in risk analysis (e.g., determining the value above which 5% of the values might fall).
• Environmental Science: The function is used to estimate environmental factors that follow a log-normal distribution, such as pollutant concentrations at a certain cumulative probability.
• Health Sciences: In biology and medicine, the LOGNORM.INV function can be used to model measurements (like the size of organisms or substances) that follow a log-normal distribution.

Example Interpretation:

• If LOGNORM.INV(0.95, 0.5, 0.2) returns a value of 2.5: It means that there is a 95% probability that the random variable $X$ from the log-normal distribution will be less than or equal to 2.5.
• If LOGNORM.INV(0.05, 0.5, 0.2) returns a value of 0.5: It means that the value below which only 5% of the values fall in the log-normal distribution is 0.5.

Notes:

• The function is particularly useful in applications where values are positively skewed, and data cannot take negative values.
• The LOGNORM.INV function is an inverse function of the cumulative distribution function (CDF), so it is the opposite of LOGNORM.DIST.
• As with other statistical functions, ensure that the mean and standard deviation are based on the natural logarithms of the data when using LOGNORM.INV.