In statistics we have a term called a lognormal distribution which is calculated to find out the distribution of a variable whose logarithm is normally distributed, the original formula is a very complex formula to calculate it but in excel we have an inbuilt function to calculate the lognormal distribution which Lognorm.Dist function.
What is Lognormal Distribution in Excel
Lognormal distribution returns a continuous statistical distribution of a random variable, which is normally distributed logarithm. The following is are the types of lognormal functions used in excel:-
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.
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 a 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 distribution cannot be applied to calculate the price of the stocks. The function can also be used to calculate option pricing for the Black Scholes model.
Calculating Lognormal Distribution Excel Parameters
Let us go through some of the 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 excel parameters.
Step 1:- Now calculate the natural logarithm values for the respective stock prices.
As can be seen in the above data, the =LN(Number) returns the natural logarithm value of the given number.
Step 2:- Next, compute the squared values of the natural logarithm numbers; the same is shown in the below table.
Step 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.
Step 4:- Next, calculate the mean for natural logarithm for the stock price.
Mean, µ= (5.97 + 5.99 + 6.21 + 6.54)/4
Or µ= 6.18
Step 5:- The calculation for standard deviation can be done manually and using direct excel formula.
Below is the table for mean and standard deviation values for the stock price.
The standard deviation is calculated by using =STDEV.S(Range of natural logarithm column ln(Stock Price)).
However, the above parameters for Mean and Standard Deviation can be further used 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, which is 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 selected.
As shown in the above screenshot, we will first enter the TRUE option to get the cumulative distribution function.
Thereby we arrive at the value as shown in the cell C19 for cumulative distribution function (CDF).
Step 3:- Now, let us calculate the lognormal distribution in excel for 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 for the latest versions. 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 the function will return #VALUE! error message.
- If arguments x is less than and 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, and later, in version 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 has been a guide to Lognormal Distribution in Excel. Here we learn how to calculate and use Lognormal Distribution parameters in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –