Lognormal Distribution In Excel

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Lognormal Distribution In Excel?

Lognormal distribution in statistics calculate and determine the distribution of a variable whose logarithm is normally distributed. The original formula is a very complex formula to calculate. But in Excel, we have a built-in function to calculate the lognormal distribution, the Lognorm.Dist function.

Lognormal distribution returns a continuous statistical distribution of a random variable, a normally distributed logarithm.

For example, consider the below data.

Using Lognormal distribution in Excel, we can find whether the data is TRUE or FALSE as shown in the below image.

Key Takeaways

  • The lognormal distribution is a statistical term used to calculate the distribution of a variable whose logarithm is normally distributed. Excel’s Lognorm.Dist function simplifies the complex formula for calculating it.
  • For the lognormal distribution Excel function, non-numeric arguments will return #VALUE! error, and x=0 or standard deviation <=0 will return #NUM! error. To calculate LOGNORM.DIST, use LOGNORM.DIST(x,mean,standard_dev) = NORM.S.DIST((ln(x)-mean)/standard_dev).
  • The function, compatible with 2010 and earlier versions, returns the cumulative lognormal distribution of x, where ln(x) is usually distributed with mean and standard_dev.

LOGNORM.DIST() Excel Formula

The syntax of LOGNORM.DIST(x, mean,standard_dev, cumulative) in Excel, returns the lognormal distribution of x, with given parameters for the mean and standard deviation of the natural logarithm, Ln(x). The above function requires the following parameters or arguments:-

LOGNORM.DIST Formula 1
  • x:- the required value of ‘x’ whose lognormal distribution is to be returned.
  • mean:- mean of Ln(x)
  • standard_dev:- standard deviation of Ln(x)
  • cumulative:- If the cumulative is TRUE, then the function returns cumulative distribution. Else FALSE gives the probability density.

How To Calculate Lognormal Distribution In Excel?

The cumulative distribution function (CDF) is a probability variable that takes a value less than equal to x. At the same time, the probability density function (PDF) of a continuous random variable explains the relative likelihood of the random variable x to take on a given value.

Also, LOGNORM.DIST is generally useful in analyzing stock prices as normal distributionNormal DistributionNormal Distribution is a bell-shaped frequency distribution curve which helps describe all the possible values a random variable can take within a given range with most of the distribution area is in the middle and few are in the tails, at the extremes. This distribution has two key parameters: the mean (µ) and the standard deviation (σ) which plays a key role in assets return calculation and in risk management strategy.read more cannot be applied to calculate the price of the stocks. We can also use the function to calculate option pricingOption PricingOption pricing refers to the process of determining the theoretical value of an options contract. read more for the Black Scholes model.

Lognormal Distribution in Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Lognormal Distribution In Excel (wallstreetmojo.com)

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Now, let us go through some examples for the lognormal distribution used in Excel.

Example #1

You can download this Lognormal Distribution Excel Template here – Lognormal Distribution Excel Template

Consider below the stock price of the listed companies for arriving at the mean and standard deviation excelStandard Deviation ExcelThe standard deviation shows the variability of the data values from the mean (average). In Excel, the STDEV and STDEV.S calculate sample standard deviation while STDEVP and STDEV.P calculate population standard deviation. STDEV is available in Excel 2007 and the previous versions. However, STDEV.P and STDEV.S are only available in Excel 2010 and subsequent versions. read more parameters.

Lognormal Distribution Step 1

Follow the below steps:

  1. To start with, we must calculate the natural logarithm values for the respective stock prices.


    Lognormal Distribution Step 2

    As seen in the above data, the = LN(Number) returns the natural logarithm value of the given number.

  2. Next, compute the squared values of the natural logarithm numbers as shown below.


    Lognormal Distribution Step 3

  3. Now, we would also require the sum of the natural logarithm of the stock price and the sum of squared natural logarithm values to calculate the standard deviation.


    Lognormal Distribution Step 4

  4. Then, calculate the mean for the natural logarithm for the stock price.

    Mean, µ= (5.97 + 5.99 + 6.21 + 6.54)/4
    Or µ= 6.18


    Lognormal Distribution Step 5

  5. Remember, the calculation for standard deviation can be done manually and using a direct Excel formula.


    Below is the table for the stock price, the stock price’s mean, and standard deviation values.

    Lognormal Distribution Step 6

The standard deviation is calculated using  =STDEV.S(Range of natural logarithm column ln(Stock Price)).

However, we can use the above parameters for mean and standard deviation to calculate the Excel lognormal distribution of any given value ‘X’ or stock price as shown below.

Step 1:- Consider the below table to understand LOGNORM.DIST function

LOGNORM.DIST Function Step 1

Note that the above table shows the parameter values required to calculate the Excel lognormal distribution for x, 10.

Step 2:- Next, we will insert the values in the formula function and select the arguments B2, B3, and B4. Now, we can choose the cumulative parameter option TRUE.

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

LOGNORM.DIST Function Step 2

Next, as shown in the above screenshot, we will first enter the TRUE option to get the cumulative distribution function.

LOGNORM.DIST Function Step 3

Now, we arrive at the value shown in cell C19 for the cumulative distribution function (CDF).

Step 3:- Now, let us calculate the lognormal distribution in Excel for the probability density function (PDF) by selecting the same argument B2,B3, and B4, and FALSE in the cumulative parameter.

LOGNORM.DIST Function Step 4

Next, as seen in the above image, we arrive at the result in cell C20 for probability density function (PDF).

Step 4:- As seen in the above function, the LOGNORM.DIST is compatible with the 2010 excel version and later. However, we can also use LOGNORMDIST, which uses the same parameters as the latest versions. Therefore, considering the same parameter values, we will populate the function for LOGNORMDIST, as shown below.

LOGNORM.DIST Function Step 5

Note that the value is the same as the LOGNORM.DIST for the TRUE parameter in the cumulative argument.

Likewise, we can use Lognormal distribution in Excel.

Example #2

Step 1:- Consider the below table to understand LOGNORM.DIST function

LOGNORM.DIST Excel - Example 2

Note that the above table shows the parameter values required to calculate the Excel lognormal distribution for x, 10.

Step 2:- Next, we will insert the values in the formula function and select the arguments B2, B3, and B4. Now, we can choose the cumulative parameter option TRUE.

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

Lognorm.dist Excel - Example 2 - Step 1

Next, as shown in the above screenshot, we will first enter the TRUE option to get the cumulative distribution function.

Now, we arrive at the value shown in cell C19 for the cumulative distribution function (CDF).

Lognorm.dist Excel - Example 2 - Step 1 - output

Step 3:- As seen in the above function, the LOGNORM.DIST is compatible with the 2010 excel version and later. However, we can also use LOGNORMDIST, which uses the same parameters as the latest versions. Therefore, considering the same parameter values, we will populate the function for LOGNORMDIST, as shown below.

Lognorm.dist Excel - Example 2 - Step 3

Now, we can see the value resulted in the same figure as the LOGNORM.DIST for the TRUE parameter in the cumulative argument.

Lognorm.dist Excel - Example 2 - Step 3 - result

Likewise, we can use Lognormal distribution in Excel.

Example #3

Step 1:- Consider the below table to understand LOGNORM.DIST function

Example 3 - Step 1

Note that the above table shows the parameter values required to calculate the Excel lognormal distribution for x, 10.

Step 2:- Next, we will insert the values in the formula function and select the arguments B2, B3, and B4. Now, we can choose the cumulative parameter option TRUE.

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

Example 3 - Step 2

Next, as shown in the above screenshot, we will first enter the TRUE option to get the cumulative distribution function.

Now, we arrive at the value shown in cell C19 for the cumulative distribution function (CDF).

Example 3 - Step 2 - value

Step 3:- As seen in the above function, the LOGNORM.DIST is compatible with the 2010 excel version and later. However, we can also use LOGNORMDIST, which uses the same parameters as the latest versions. Therefore, considering the same parameter values, we will use the LOGNORMDIST function, as shown below.

Example 3 - Step 3

Now, we can see the value is the same as the LOGNORM.DIST for the TRUE parameter in the cumulative argument.

Example 3 - Step 3 - Output

Likewise, we can use Lognormal distribution in Excel.

Important Things To Note

  • If any parameter or argument is non-numeric, then the lognormal distribution Excel function will return #VALUE! Error message.
  • If arguments x is equal to 0 or if the standard deviation is less than and equal to 0, then the function would return #NUM! Error message.
  • Equivalent expression to calculate LOGNORM.DIST is LOGNORM.DIST(x,mean,standard_dev) = NORM.S.DIST((ln(x)-mean)/standard_dev)
  • This function is compatible with version 2010.

Frequently Asked Questions

1. What are the limitations of Log normal Distribution?

Log Normal distributions, characterized by their two parameters (Arithmetic Mean and standard deviation), are often under-predicted due to their limited flexibility.

2. What is the shape of the Lognormal Distribution?

Log-normal distributions are positively skewed with long right tails due to low mean values and high variances in random variables.

3. Is the Lognormal Distribution bounded by zero?

The Lognormal Distribution, bounded below 0 and skewed to the right, is commonly used to model the probability distribution of asset prices due to its skewed right tail.

This article is a guide to Lognormal Distribution in Excel. We learn how to calculate and use lognormal distribution parameters in Excel, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. stephen says

    This article explained away a lot of my confusion. I am bookmarking it.

Leave a Reply

Your email address will not be published. Required fields are marked *