NORMINV Function in Excel: Returns the inverse of the normal cumulative distribution considering two inputs - the specified mean and standard deviation.
What is the NORMINV function?
The NORM.INV function assumes a normal distribution and returns any P-value on this normal distribution using a given mean and standard deviation - also called uncertainty.
How to apply the NORMINV function in Excel?
The NORM.INV function uses three inputs, of which all three are required.
- The first argument, probability, is a probability corresponding to the normal distribution. Using this input, Excel is essentially being told what P-value on the normal distribution shall be returned. Attention! Excel will return the inverse of the normal cumulative distribution! So if we would like to determine the P90 value, we would have to use the P10 value as input for the probability since P90 is the inverse of P10.
- The second argument, mean, requires the arithmetic mean of the distribution. This is the P50 energy production value of the power plant.
- The third argument, standard_dev, requires the standard deviation of the distribution. This is the energy production uncertainty, typically higher for wind farms than for solar parks.
How to use the NORMINV function to determine P75, P90, and any other P-value of energy production?
Using the NORM.INV function correctly in an energy production-related context is not super intuitive - so let's see how it's done in a best practice financial model for renewable energy investments.
In the above example, we consider a wind farm with 2,400 full load hours per year. So our mean energy production on a P50 basis is 2,400 MWh/MW - this is the input "mean" for the second argument of the formula.
We also know that the standard deviation, here referred to as uncertainty, is 12%. The mean and uncertainty are all we need to determine any other P-value.
For the first argument of the formula"probability", Excel needs a percentage input, more precisely, any value between 0 and 1. In the above model, the P-value input cells have been formatted so that a "P" appears in front of the value, but actually, the cells only contain the numbers 50, 75, and 90.
For the P75 value, the first argument essentially reads "(1-(75/100))". Since Excel returns the inverse cumulative distribution, we need to subtract 75% from 100% to arrive at the P75 value as output.
For the second argument, "mean", a simple reference to the cell containing 2,400 MWh/MW is sufficient.
The last argument, "standard_dev", has to be converted to the same unit as the mean. Given that the mean has been provided as an absolute input in MWh/MW, we need to multiply this mean with the 12% uncertainty in energy production.
After carefully setting up the function, Excel returns 2,206 and 2,031 MWh/MW for the P75 and P90 values, respectively.
How to properly apply the NORMINV function in a financial model for renewable energy investments?
Do you want to learn how to model the NORM.INV function within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
Article sources
