Probability Distributions with Excel

Last Update: September 15, 2020

Probability distributions consist of all possible values that a discrete or continuous random variable can have and their associated probability of being observed. Classical or a priori probability distribution is theoretical while empirical or a posteriori probability distribution is experimental.

This topic is part of Business Statistics with Excel course. Feel free to take a look at Course Curriculum.

This tutorial has an educational and informational purpose and doesn’t constitute any type of forecasting, business, trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.

An example of probability distributions is normal probability distribution which consists of all possible values that a continuous random variable can have and their associated probability of being observed. Its probability distribution function is bell-shaped, symmetric from its mean and mesokurtic. Standard normal probability distribution consists of normal probability distribution with mean zero and unit variance. Any normal probability distribution can be converted into a standard normal probability distribution through continuous random variable standardization.

1. Formula notation.

$pdf\left&space;(&space;x|\mu,\sigma^{2}&space;\right&space;)=\frac{1}{\sqrt{2\pi\sigma^{2}}}e^{-\frac{\left&space;(&space;x-\mu&space;\right&space;)^{2}}{2\sigma^{2}}}$

$\mu=\mu,\;&space;\mu=0$

$\sigma^2=\sigma^2,\;\sigma^2=1$

$s=0$

$ek=0$

$z=\frac{x-\mu}{\sigma}$

Where $pdf\left&space;(&space;x|\mu,\sigma^{2}&space;\right&space;)$ = normal probability density function, $x$ = continuous random variable, $\mu=\mu$ = normal probability distribution mean, $\mu=0$ = standard normal probability distribution mean, $\sigma^2=\sigma^2$ = normal probability distribution variance, $\sigma^2=1$ = standard normal probability distribution variance, $s=0$ = normal and standard normal probability distributions skewness, $ek=0$ = normal and standard normal probability distributions excess kurtosis, $z$ = continuous random variable standardization, $\mu$ = continuous random variable mean, $\sigma$ = continuous random variable standard deviation.

2. Excel example.

2.1. Probability distributions data, daily returns and standardized daily returns calculation.

• Data: S&P 500® index replicating ETF (ticker symbol: SPY) daily adjusted close prices (2007-2015).
• Data daily arithmetic returns and standardized daily arithmetic returns calculation.
• Initial daily arithmetic return assumption not fixed and only included for educational purposes.

2.2. Probability distributions data, daily returns and standardized daily returns calculation formulas.

2.3. Standardized daily returns descriptive statistics calculation.

2.4. Standardized daily returns descriptive statistics calculation formulas.

2.5. Standardized daily returns density histogram and standard normal probability distribution curve overlay calculation.

• Standardized daily returns absolute frequency calculation done using Microsoft Excel Analysis Toolpak® Add-In Histogram Analysis Tool.

2.6. Standardized daily returns density histogram and standard normal probability distribution curve overlay calculation formulas.

2.7. Standardized daily returns density histogram and standard normal probability distribution curve overlay chart.

+