The Normal and Lognormal Distributions

Abstract

In the last chapter, we examined the probability distribution of discrete random variables. In this chapter, we will look at the probability distribution of random variables.

Appendices

Appendix 7.1: Excel Code—Normal Distribution

Appendix 7.2: Excel Code—Uniform Distribution

Appendix 7.3: QQ Plot

A QQ plot is a probability plot of quantiles of two distributions against each other. It is a graphical method for comparing two probability distributions . If the two distributions are similar, the points on the QQ plot will approximately lie on the line Y = X.

Below is an example to show you how to use Excel to graph a QQ plot. Assume we have gotten the JNJ stock return data. Data period is 1967–2011. We would like to compare this data and normal distribution.

Here are the main steps to calculate the QQ plot:

Step 1: Select column B and choose Data → Sort A to Z.

After pressing the A to Z button and expanding the selection, we get the result like below:

Step 2: Standardize the return data.

The formula for standardized return in cell C4 is

$$ =\frac{\left(\mathrm{B}2-\mathrm{AVERAGE}\left(\$\mathrm{B}\$2:\$\mathrm{B}\$46\right)\right)}{\mathrm{STDEV}\left(\$\mathrm{B}\$2:\$\mathrm{B}\$46\right)} $$

Step 3: Construct the sample number column E.

Step 4: Use the number of Step 3 to calculate the cumulative distribution function (CDF).

The CDF formula in the cell F2 is

$$ =\frac{\mathrm{E}2}{\left(\mathrm{COUNT}\left(\$\mathrm{E}\$2:\$\mathrm{E}\$46\right)+1\right)} $$

Step 5: Use NORMSINV function to z of column F.

The formula of z in cell G2 is

$$ =\mathrm{NORMSINV}\left(\mathrm{F}2\right) $$

Step 6: Use columns G, z and z, to chart a benchmark line, and use column C and column G, standard return and z, to graph a comparative line.