Lognormal Distribution in Excel – Detailed Explanation

The lognormal distribution is used in statistics to model a random variable whose logarithm is normally distributed. In Excel, this is easily calculated using the LOGNORM.DIST function. Below is a more detailed explanation, including the syntax, steps to use it, and a sample dataset for testing.

Syntax of LOGNORM.DIST Function

The LOGNORM.DIST function returns the lognormal distribution of a value x with a given mean and standard deviation for the natural logarithm of the value.

Formula:

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

Arguments:

1. x: The value for which you want to calculate the lognormal distribution. (Must be greater than zero)
2. mean: The arithmetic mean of the natural logarithm of x.
3. standard_dev: The standard deviation of the natural logarithm of x.
4. cumulative: A logical value that determines the form of the function:
   • TRUE: Returns the cumulative distribution function (CDF).
   • FALSE: Returns the probability density function (PDF).

What is CDF and PDF?

• CDF (Cumulative Distribution Function): It gives the probability that a random variable will take a value less than or equal to x.
• PDF (Probability Density Function): It gives the probability of the random variable being equal to a specific value.

How to Use LOGNORM.DIST in Excel:

Step-by-Step Example:

Let’s calculate the lognormal distribution using a sample dataset.

Step 1: Create the Data

Consider the following data of stock prices:

Step 2: Calculate the Natural Logarithm of Stock Prices (ln(x))

Use the LN() function in Excel to get the natural logarithm of each stock price.

• For x = 8, =LN(8) gives 2.08.
• For x = 4, =LN(4) gives 1.39.
• For x = 10, =LN(10) gives 2.30.

Step 3: Calculate the Mean and Standard Deviation of ln(x)

Now, calculate the mean and standard deviation of the ln(x) values:

• Mean (µ):
• =AVERAGE(B2:B4)  –> Mean = 1.92
• Standard Deviation (σ):
• =STDEV.S(B2:B4)  –> Standard Deviation = 0.53

Step 4: Use the LOGNORM.DIST Function

Now, calculate the lognormal distribution for each stock price using the formula LOGNORM.DIST(x, mean, standard_dev, cumulative).

1. For x = 8 (Cumulative Distribution):
2. =LOGNORM.DIST(8, 1.92, 0.53, TRUE)

Result: 0.8815 (Cumulative Distribution)

1. For x = 8 (Probability Density Function):
2. =LOGNORM.DIST(8, 1.92, 0.53, FALSE)

Result: 0.1246 (Probability Density Function)

Testing with a Sample Dataset:

To demonstrate the function, let’s use a sample dataset for testing:

Key Takeaways:

• LOGNORM.DIST is a useful function for financial analysis, especially when analyzing stock prices or option pricing (e.g., Black-Scholes model).
• The function is available in Excel 2010 and later versions.
• The cumulative distribution gives the probability of a value being less than or equal to x, while the probability density gives the likelihood of the variable being exactly x.
• The LOGNORM.DIST function helps in handling data that follows a skewed distribution, making it more accurate than using a normal distribution for such data.

Errors to Watch Out For:

• #VALUE!: If non-numeric values are used as arguments.
• #NUM!: If x is less than or equal to 0, or if the standard deviation is non-positive.
• Ensure that the arguments for x, mean, and standard_dev are numeric.

Conclusion:

The LOGNORM.DIST function simplifies the calculation of lognormal distributions in Excel, providing a quick and easy way to analyze data that follows a log-normal distribution. It is a vital tool in fields such as finance, medical data analysis, and real estate.