What is Lognormal Distribution in Excel

Lognormal distribution returns a continuous statistical distribution of a random variable, a normally distributed logarithm. The following is are the types of lognormal functions used in Excel:-

LOGNORM.DIST Formula

The distribution function syntax is defined as LOGNORM.DIST(x, mean,standard_dev, cumulative) in Excel, which 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:-

  • 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.

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.

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

Calculating Lognormal Distribution Excel Parameters

Let us go through some examples for the lognormal distribution used in Excel.

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.

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

  • Now, we must calculate the natural logarithm values for the respective stock prices. As seen in the above data, the = LN(Number) returns the natural logarithm value of the given number. Next, compute the squared values of the natural logarithm numbers. The same is shown below. 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. Next, calculate the mean for the natural logarithm for the stock price.Mean, µ= (5.97 + 5.99 + 6.21 + 6.54)/4Or µ= 6.18 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.

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

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

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. The explanation for the same is shown below.

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

The above table shows the parameter values required to calculate the excel lognormal distribution for x, 10.

Step 2:- Now, we will insert the values in the formula function to arrive at the result by selecting the arguments B2, B3, B4, and the cumulative parameter will have options TRUE and FALSE to be chosen.

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

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

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,B4, and FALSE in the cumulative parameter.

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.

As can be seen, the value resulted in the same figure as the LOGNORM.DIST for the TRUE parameter in the cumulative argument.

Things to Remember about Lognormal Distribution in Excel

  • 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. Later, in versions 2007 and earlier, LOGNORMDIST(x,mean,standard_dev) must be used, which returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters/arguments mean and standard_dev.

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: –

  • Formula of Standard Normal DistributionExcel Normal Distribution GraphRANK in ExcelCORREL Function in Excel