A to Z of Excel Functions: The LOGNORM.INV Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the LOGNORM.INV function.

The LOGNORM.INV function

A Lognormal distribution is a statistical distribution of logarithmic values from an underlying Normal distribution. A Lognormal distribution may be translated to a Normal distribution and vice versa using the associated logarithmic translations.

A Normal distribution is a symmetrical probability distribution of outcomes that forms a bell curve. In a Normal distribution, 68% of the results fall within one standard deviation and 95% fall within two standard deviations, viz.

While most people are familiar with a Normal distribution, they may not be as familiar with the related Lognormal distribution.  A Normal distribution may be converted to a Lognormal distribution using logarithms.  It should be noted that Lognormal distributions can only arise from a Normally distributed set of random variables.

There can be a few reasons for using Lognormal distributions in conjunction with Normal distributions.  Most commonly, Lognormal distributions are the result of taking the natural logarithm where the base is equal to e=2.718...  However, the Lognormal distribution may be scaled using a different base, if desired, which affects the shape of the Lognormal distribution.

The Lognormal distribution plots the logarithm of random variables from a Normal distribution curve.  In general, the logarithm is known as the exponent to which a base number must be raised in order to produce the random variable (x) that is found along a Normally distributed curve.  It should be noted that Lognormal distributions are positively skewed with long right tails due to low mean values and high variances in the random variables.

Clear as mud, yes?

In practice, Normal distributions may present a few problems that Lognormal distributions can solve.  In particular, Normal distributions allow for negative random variables whilst Lognormal distributions include all positive variables.

Probably (get it?) the most common application where Lognormal distributions are used in finance is in the analysis of stock prices.  A Lognormal distribution is more suitable for this purpose because asset prices cannot be negative.  An important point to note is that when the continuously compounded returns of a stock follow a Normal distribution, then the stock prices follow a Lognormal distribution.  The Lognormal distribution curve may therefore be used to help better identify the compound return that the stock can expect to achieve over a period of time.

The distribution is occasionally referred to as the Galton distribution or Galton's distribution, after Francis Galton.  In summary, a Lognormal process is “simply” the statistical realisation of the multiplicative product of many independent random variables, each of which is positive and it is therefore the maximum entropy probability distribution for a random variate x for which the mean and variance of LN(x) are specified.  And if you follow all of that, get out of here because no one likes a smartarse. 

The LOGNORM.INV function returns the inverse of the Lognormal cumulative distribution function of x, where LN(x) is Normally distributed with parameters mean and standard_deviation. 

If p = LOGNORM.DIST(x, …) then LOGNORM.INV(p, …) = x.

The LOGNORM.INV  function employs the following syntax to operate:

The LOGNORM.INV function has the following arguments:

• probability: this is required and represents a probability associated with the Lognormal distribution
• mean: this is also required and denotes the mean of the natural logarithm of x, LN(x)
• standard_deviation: again required, this is the standard deviation of LN(x).

It should be noted that:

• this function has replaces LOGINV.  Microsoft states that this new function may provide improved accuracy, as well as provide a function name that better reflects its usage
• although LOGINV is still available for backward compatibility, consider using LOGNORM.INV from now on, because the former function may not be available in future versions of Excel
• if any argument is nonnumeric, LOGNORM.INV returns the #VALUE! error value
• if probability ≤ 0 or probability ≥ 1, LOGNORM.INV returns the #NUM! error value
• if x ≤ 0 or if standard_deviation ≤ 0, LOGNORM.INV returns the #NUM! error value.

Please see my comprehensive example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.