LOGNORM.INV Function

Calculate the inverse of the lognormal cumulative lognormal distribution function at a given value of x

Inverse Lognormal Distribution Excel

The LOGNORM.INV Function is categorized under Statistical functions. It will calculate the inverse lognormal distribution in Excel at a given value of x.  We can use the function to analyze data that’s been logarithmically transformed.

LOGNORMAL distribution is often used in financial analysis to make investment decisions. It is often used in analyzing stock prices as normal distribution cannot be used to model stock prices. The normal distribution includes a negative side and stock prices cannot fall below zero.

The LOGNORM.INV function is useful in financial analysis when we are given the probability and we wish to find the value of x. For example, we can use the function to know the probability of a stock price rising and want to find the stock price that is x.

Also, the function is useful in pricing options. The Black-Scholes model uses the lognormal distribution as its basis to determine option prices.

 

Formula

=LOGNORM.INV(probability,mean,standard_dev)

 

The LOGNORM.INV function uses the following argument:

  1. Probability (required argument) – It is the probability associated with the lognormal distribution.
  2. Mean (required argument) – It is the mean of In(x).
  3. Standard_dev (required argument) – It is the standard deviation of In(x).

 

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

To learn more, launch our free Excel crash course now!

 

How to use Inverse Lognormal Distribution in Excel?

To understand the uses of the LOGNORM.INV function, let us consider an example:

 

Example

Suppose we are given the following data:

 

Inverse Lognormal Distribution Excel - Formula

 

Using the probability, mean and standard deviation given above, the formula for calculating the inverse of lognormal cumulative distribution is shown below:

 

LOGNORM.INV - Example 1

 

We get the result below:

 

LOGNORM.INV - Example 1a

 

Notes about Inverse Lognormal Distribution Excel

  1. #NUM! error – Occurs when:
    • The argument x given is less than or equal to zero; or
    • The argument standard_dev is less than or equal to zero.
  2. #VALUE! error – Occurs when any of the given arguments is non-numeric.
  3. The LOGNORM.INV function was introduced in Excel 2010 and hence is unavailable in earlier versions. For older versions, we can use the LOGINV function.

 

Free Excel Course

Work your way toward becoming an expert financial analyst by checking our Free Excel Crash Course. Learn how to use Excel functions and create sophisticated financial analysis and financial modeling.

 

free Excel keyboard shortcuts course

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!