What Is LOGNORM.INV Function in Google Sheets?
The LOGNORM.INV in Google Sheets gives the inverse of the log-normal cumulative distribution. It means that it finds the value corresponding to a given probability in a log-normal distribution with a specified standard deviation and mean. For those who do not know, a log-normal distribution is a type of distribution where the logarithm of the random variable follows a normal distribution. Such distributions are skewed to the right, and they take only positive values. Their growth is skewed.
As an example, we can say that stock prices mostly follow a log-normal distribution as they can’t fall below zero and have compounding returns. In a simple example, we wish to find the stock price of a company where there is a 90% chance that the price will be less than or equal to the current price after a year. We apply the following function where the mean is 3.5 and the standard deviation is 0.4 from past data.
=LOGNORM.INV(0.90, 3.5, 0.4)
It returns 55.29, which means there’s a 90% chance the stock will be $55.29 or less.
Key Takeaways
- LOGNORM.INV is a function in Google Sheets used to calculate the value at which a specified probability occurs in a log-normal distribution.
- Its syntax is as follows: =LOGNORM.INV(probability, mean, standard_dev), where mean and standard_dev refer to mean and SD of the natural log of the dataset
- LOGNORM.INV is useful for modeling real-world examples where the data is skewed and positive, for instance, stock prices and insurance claims.
- It is commonly used to find the maximum value a variable may reach within a certain confidence level, like 95%.
- In short, LOGNORM.INV tells the value corresponding to a given probability.
Syntax
Now that you have a brief idea of the function, let us look at the LOGNORM.INV formula in Google Sheets. It is as follows:
=LOGNORM.INV(probability, mean, standard_dev)
- probability: A number between 0 and 1 representing the probability.
- mean: The mean of the distribution.
- standard_dev: The standard deviation of the distribution.
How To Use LOGNORM.INV Function in Google Sheets?
LOGNORM.INV in Google Sheets can be used in a wide number of practical applications, like predicting a future stock price. To use the function, there are two ways to enter the LOGNORM.INV function in Google Sheets.
Entering the LOGNORM.INV Function Manually
To understand the function, in this example, we are analyzing the distribution of income in a certain region. We wish to find an income level that corresponds to a particular percentile. Here, we assume that the mean of the natural logarithm of the income is 10, and the standard deviation is 0.5.
Step 1: Enter the details as shown below. Select an empty cell to display the result. Here, we will find the income level corresponding to the 95th percentile.
Step 2: Enter the LOGNORM.INV formula by entering an equal’s sign, the function name, followed by a parenthesis.
=LOGNORM.INV(
Step 3: Inside the parentheses, specify the probability. Here, we specify the cell reference holding the probability, B1. Next, enter the cell references of the mean and standard deviation. Close the parentheses. Press Enter to apply the formula and display the result.
=LOGNORM.INV(B1,B2,B3)
When you apply the LOGNORM.INV formula, it will return the income level that corresponds to the 95th percentile. This result shows the inverse of the standard normal cumulative distribution that is associated with the given probability.
Using the Google Menu Bar
- Choose the cell where you want to enter the LOGNORM.INV formula.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Statistical” ➝ “LOGNORM.INV”
- Enter the required arguments. Close the bracket and press the “Enter” key.
Examples
LOGNORM.INV is useful for analysts to work with log-normally distributed data. It helps determine thresholds based on probabilities, which helps you take important decisions. You can also understand the distribution of data in skewed datasets.
Example #1 – Determine the value of an investment with a given probability
In this example, we must find the investment value with a 93% probability of being exceeded. It corresponds to the 7th percentile. For this, we have analysed historical data.
Step 1: The historical data is as shown below. We find the mean using the following formula:
=AVERAGE(A2:A7)
Step 2: We apply the following formula to find the standard deviation.
=STDEV.S(A2:A7). Enter the probability of 93% or 0.93 as well.
Step 3: Use the following formula:
=LOGNORM.INV(B10,B8,B9)
The result of this formula will be the investment value with a 7% probability of being less than or equal to it, or a 93% probability of being exceeded.
Example #2 – Predict future stock prices by understanding the distribution of past returns
As all of us know, stock prices fluctuate; they don’t go up in a straight line. However, the returns follow a normal distribution, and prices mostly follow a log-normal distribution. Here, we can find the mean and standard deviation of the log returns and estimate the stock prices at a particular confidence level, say 90%.
Say we possess the past daily returns for a stock and find the mean to be 0.8.
The standard deviation of the logarithm is 0.2.
The current price of the stock is $150. Let us find the maximum price we can expect with 90% confidence in 1 year?
Step 1: Enter all the details as shown below.
Step 2: Enter the following formula in Google Sheets.
=LOGNORM.INV(B2,B3,B4)
We can also enter the values directly as:
=LOGNORM.INV(0.90, 0.8, 0.2)
Step 3: Press Enter.
Step 4: Now, multiply that result by the current stock price as:
=150 * LOGNORM.INV(0.95, 0.08, 0.2)
Let’s say this gives you: $225.79
So, you have a 90% chance that the stock price will be $225.79 or less after a year with historical return patterns.
Example #3 – Assess the likelihood of insurance claims based on historical data
An insurance analyst has past historical data on insurance claims over the past few years. He must find the maximum claim amount to expect with 95% probability based on past data. Let us look at how to implement the same using LOGNORM.INV in Google Sheets.
Step 1: Let us enter the claim amount of the past in Column A and find its natural log using:
=LN(A2) and dragging it down.
Step 2: Let us calculate the mean (AVERAGE) and standard deviation of the logs using the following formulas.
Mean: =AVERAGE(B2:B9)
Standard deviation: =STDEV.P(B2:B9)
Step 3: Use LOGNORM.INV to estimate the claim at 95%
=LOGNORM.INV(0.95, 7.3, 1.1)
The result we get is. Hence, we have a 95% chance that an insurance claim will be $1,360 or less, based on historical data.
Important Things to Note
The #NUM! error occurs when the probability is less than or equal to zero or greater than one or the argument standard_dev is less than or equal to zero.
The #VALUE! error occurs when any of the given arguments is non-numeric in the formula for LOGNORM.INV in Google Sheets.
If the outcome looks ambiguous, check your mean and standard deviation and ensure that the data belongs to a log-normal distribution.
Frequently Asked Questions (FAQs)
It is always a matter of importance to investors on when to use which method. We use the lognormal or LOGNORM.DIST is extremely helpful when analyzing stock prices. If the growth factor used is assumed to be normally distributed, using the lognormal distribution is relevant. Normal distribution cannot be used to model stock prices as the stock prices cannot fall below zero.
We can use other Google Sheets functions to find these values. You can first use the LN function to find the natural log.
1. Take the natural log of the data with the following formula =LN(cell reference)
2. Then use =AVERAGE(range) to find the mean of the given values.
3. Next, let us use the following function to find the standard deviation =STDEV.P() or =STDEV.S().
4. Now apply these values to the LOGNORM.INV function.
The LOGNORM.DIST function gives the probability that a value is less than or equal to X. For instance, you can find the chance that an insurance claim is less than or equal to 10K.
The LOGNORM.INV gives the value of X for a given probability that shows what is the claim amount at 95% risk?
Basically, use LOGNORM.DIST when you know the value and want to find the probability and LOGNORM.INV when you know the probability and want to find the value.
Download Template
This article must help understand LOGNORM.INV Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is LOGNORM.INV Function in Google Sheets. We learn its syntax & use it to find the value corresponding to a given probability. You can learn more from the following articles. –
Leave a Reply