Probability Distributions

Return on
Asset A
Probability
-0.3
0.05
0
0.2
0.1
0.5
0.2
0.2
0.5
0.05
1

State of Economy
Depression
Recession
Normal
Mild Boom
Major Boom

Cells containing probability

values are named cells.
Names are created by
typing in a name in the
name box

Experiment with different values for the

return and probabilities and see how the the
probability graph and shape characteristics

Note: Probabilities
must sum to 1

Chart is created with

chart wizard. Chart type
is column chart.

Distribution for R_A

0.6

Probability

0.5
0.4
0.3
0.2

0.1
0
-0.3

0.1

0.2

0.5

Return

mR ===
E[ R]

Distribution Shape Characteristics

Mean
0.100
Variance
0.020
Std. Deivation
0.141
Skewness
0.000
Kurtosis
6.500

smR 2 ==-=
var[ R ]

skew[ R] =
kurt[ R] =

(r -=mR )4 Pr( R

rS R

SD( R) 4

r)

Pr( R

r)

rS R

(r

rS R

(r -=m

rS R

)2 Pr( R

)3 Pr( R

SD( R) 3

r)

r)

Simple Returns
Return Distribution
Initial Wealth
m
s
$10,000
0.05
0.10

Quantiles
Value-at-Risk
q(0.01)
q(0.05) 1% VaR
5% VaR
-0.1826
-0.1145 -$1,826.35 -$1,144.85

Use NORMINV(prob,mu,sigma) to
compute quantile

Interpretation of 5% VaR: With 5% probability the maximum loss of

investment value over the next month $1,448.
Interpretation of 1% VaR: With 1% probability the maximum loss of
investment value over the next month $1,826.

Continuously Compounded Returns

Return Distribution
Initial Wealth
m
s
$10,000
0.05
0.1

Quantiles
Value-at-Risk
q(0.01)
q(0.05) 1% VaR
5% VaR
-0.1826
-0.1145 -$1,669.28 -$1,081.75

Assumptions:
1. Simple monthly return
2. Initial wealth W = $100
3. Investment horizon = 1

5% monthly Value
investment over 1 mont
computed as:

where q(0.05) = 5% qua

simple return R. 1% VaR

Assumptions:
1. Simple monthly return R ~ N(0.05, (0.10)2 )
2. Initial wealth W = $100,000
3. Investment horizon = 1 month

5% monthly Value-at-Risk (VaR) = maximum dollar loss of initial

investment over 1 month horizon with 5% probability. This is
computed as:
VaR = q(0.05)*W
where q(0.05) = 5% quantile of Normal distribution for monthly
simple return R. 1% VaR is computed in an analogous way

1
p( x) =-
e
2p
x

p(x)
-3.0
-2.8
-2.5
-2.3
-2.0
-1.8
-1.5
-1.3
-1.0
-0.8
-0.5
-0.3
0.0
0.3
0.5
0.8
1.0
1.3
1.5
1.8
2.0
2.3
2.5
2.8
3.0

0.00
0.01
0.02
0.03
0.05
0.09
0.13
0.18
0.24
0.30
0.35
0.39
0.40
0.39
0.35
0.30
0.24
0.18
0.13
0.09
0.05
0.03
0.02
0.01
0.00

Pr(X < x)
0.00
0.00
0.01
0.01
0.02
0.04
0.07
0.11
0.16
0.23
0.31
0.40
0.50
0.60
0.69
0.77
0.84
0.89
0.93
0.96
0.98
0.99
0.99
1.00
1.00

Note: the function NORMDIST with mu=0,

and CUMULATIVE=FALSE is used to com
p(x) for the standard normal.

Standard Normal pdf

p(x)

1
- x2
2

If X ~ N(0,1) then
Pr( -1 < X < 1) = 0.67
Pr(-2 < X < 2) = 0.95
Pr(-3 < X < 3) = 0.99

0.45
0.40
0.35
0.30
0.25
0.20
0.15
0.10
0.05
0.00
-4.0

-3.0

-2.0

-1.0

0.0

1.0

Finding Area Under the Normal Curve

Pr(X < 2)
Pr(-1 < X < 2)
Pr(X > 2)

0.9772
0.8186
0.0228

The function NORMSDIST is

used to compute the area under
the standard normal curve

function NORMDIST with mu=0, sd=1

MULATIVE=FALSE is used to compute
he standard normal.

Normal pdf

Standard Normal CDF

1.00

Pr(X < x)

0.80
0.60
0.40
0.20

1.0

2.0

3.0

4.0

0.00
-4.0

-3.0

-2.0

-1.0

0.0
x

Normal Curve

Finding Quantiles of the Normal Distribution

a
qa
0.01
-2.33
The function NORMSINV is used
0.025
-1.96
to compute quantiles of the
0.05
-1.64
standard normal distribution
0.1
-1.28
0.5
0.00
0.9
1.28
0.95
1.64
0.99
2.33

1.0

2.0

2.0

3.0

4.0

Joint distribution table

Pr(Y=y)

1.5

1 Pr(X=x)
0
0.125
0.125
0.375
0.25
0.375
0.125
0.125
0.5

1
0.5

0
1
2
3

Y
0
0.125
0.25
0.125
0
0.5

-0.5

00.125
0
-0.5

Computing Covariance and Correlation

Mean
Variance
SD

X
1.500
0.750
0.866

Y
0.500
0.250
0.500

Y
0
0
1
1
2
2
3
3

0
1
0
1
0
1
0
1

p(x,y)
0.125
0
0.25
0.125
0.125
0.25
0
0.125

X-E[X]
-1.5
-1.5
-0.5
-0.5
0.5
0.5
1.5
1.5

cov( X , Y ) =- (x mmX )( y
x
SX y SY

)p

Probability Scatterplot of X vs. Y

Note: Probability
scatterplot is created with a
bubble chart

1.5
1

0.125

0.25

0.125

0.5
00.125
0

0.5

0.25
1

1.5

0.125
2

2.5

3.5

-0.5
x

, Y ) =-

x
SX y SY

Y-E[Y] (X-E[X])(Y-E[Y]) (X-E[X])(Y-E[Y])*p(x,y)

-0.5
0.75
0.094
0.5
-0.75
0.000
-0.5
0.25
0.063
0.5
-0.25
-0.031
-0.5
-0.25
-0.031
0.5
0.25
0.063
-0.5
-0.75
0.000
0.5
0.75
0.094
0.250 : cov(X,Y)
0.577 : corr(X,Y)
(x mm )( y
) p (x , y )
X

corr ( X , Y ) =

cov( X , Y )
SD( X ) SD(Y )

Asset
A
B
Wealth

Asset A
Asset B

Mean
5.0%
10.0%

Variance
1.0%
4.0%

SD
Covariance Correlation
10.0%
0
0
20.0%

$10,000
Portfolio Information
Share Dollar amount
50%
$5,000
50%
$5,000

Portfolio distribution
Mean
Variance
SD
7.50%
1.25%
11.18%

E[ Rp ] =+x A mm
xB
A

2
var( Rp ) =++
x 2Asss
xB2
A

2
B

2x A xB

AB

Initial wealth W is invested in 2 a

RA = monthly cc return on asset
RB = monthly cc return on asset
Assume
1. RA ~ N(mu_A, sigma2_A)
2. RB ~ N(mu_B, sigma2_B)
3. Cov(RA,RB) = sigma_AB
Portfolio
Rp = x_A*RA + x_B*RB

nitial wealth W is invested in 2 assets: asset A and asset B

RA = monthly cc return on asset A
RB = monthly cc return on asset B

. RA ~ N(mu_A, sigma2_A)
. RB ~ N(mu_B, sigma2_B)
. Cov(RA,RB) = sigma_AB

Rp = x_A*RA + x_B*RB

Pr(Y ====
y)

Pr( X

Pr( X ====
x)

Pr( X

x, Y

y)

ySY

Each cell in the joint distribution table

gives Pr(X=x,Y=y)=p(x,y). All probabilities
in main table must sum to 1.

x ,Y

y)

xS X

Joint distribution of X and Y

0.4

0.25

0.35

0.2

0.3

0.15

0.25

p(x,y)

p(x)

0
1
2
3
Pr(Y=y)

Joint distribution table

Y
0
1
Pr(X=x)
0.125
0
0.125
0.25
0.125
0.375
0.125
0.25
0.375
0
0.125
0.125
0.5
0.5

0.1

0.2
0.15
0.1

0.05

0.05

0
0

0
2

Bivariate continuous distribution

Bivariate Normal distribution

-3
-2.5
-2
-1.5
-1
-0.5
0
0.5
1
1.5
2
2.5

-3
0.000194
0.000767
0.002362
0.005665
0.010584
0.0154
0.01745
0.0154
0.010584
0.005665
0.002362
0.000767

1
-+ ( x
2

p( x, y) = (2p ) -1 e
-2.5
0.000767
0.003032
0.00934
0.022406
0.04186
0.060906
0.069016
0.060906
0.04186
0.022406
0.00934
0.003032

-2
0.002362
0.00934
0.02877
0.069016
0.128939
0.187605
0.212584
0.187605
0.128939
0.069016
0.02877
0.00934

-1.5
0.005665
0.022406
0.069016
0.165561
0.309308
0.450041
0.509963
0.450041
0.309308
0.165561
0.069016
0.022406

-1
0.010584
0.04186
0.128939
0.309308
0.577864
0.840787
0.952736
0.840787
0.577864
0.309308
0.128939
0.04186

-0.5
0.0154
0.060906
0.187605
0.450041
0.840787
1.223337
1.386223
1.223337
0.840787
0.450041
0.187605
0.060906

3 0.000194 0.000767 0.002362 0.005665 0.010584

0.0154

Bivariate Normal Distribution

-3

-2

2
-3

-1

-0.5

(X

x, Y

y)

ll probabilities

Marginal Distribution for X

Marginal Distribution for Y

0.6

0.5

p(y)

0.4

0
1

0.3

0.2

0.1

0
0

1
y

1
-+ ( x2 y 2)
2

p( x, y) = (2p ) -1 e
y
0
0.01745
0.069016
0.212584
0.509963
0.952736
1.386223
1.570796
1.386223
0.952736
0.509963
0.212584
0.069016

0.5
0.0154
0.060906
0.187605
0.450041
0.840787
1.223337
1.386223
1.223337
0.840787
0.450041
0.187605
0.060906

1
0.010584
0.04186
0.128939
0.309308
0.577864
0.840787
0.952736
0.840787
0.577864
0.309308
0.128939
0.04186

1.5
0.005665
0.022406
0.069016
0.165561
0.309308
0.450041
0.509963
0.450041
0.309308
0.165561
0.069016
0.022406

2
0.0023616
0.0093403
0.02877014
0.06901597
0.12893881
0.18760487
0.21258417
0.18760487
0.12893881
0.06901597
0.02877014
0.0093403

2.5
0.0007667
0.00303235
0.0093403
0.02240621
0.0418603
0.06090638
0.06901597
0.06090638
0.0418603
0.02240621
0.0093403
0.00303235

3
0.00019385
0.0007667
0.0023616
0.00566518
0.01058394
0.01539955
0.01744997
0.01539955
0.01058394
0.00566518
0.0023616
0.0007667

0.01745

0.0154 0.010584 0.005665

ormal Distribution

1.6
1.4

1.2
1
0.8 p(x,y)
0.6
0.4
0.2
0

0.0023616

0.0007667 0.00019385

Conditional Distribution for X

0
1
2
3

Pr(X=x) Pr(X=x|Y=0) Pr(X=x|Y=1)

0.125
0.25
0
0.375
0.5
0.25
0.375
0.25
0.5
0.125
0
0.25
1
1
1

Pr( X ===
x|Y

y)

Pr( X ==x ,Y y )
Pr(Y = y)

Conditional Distribution for Y

0
1

Pr(Y=y) Pr(Y=y|X=0) Pr(Y=y|X=1) Pr(Y=y|X=2) Pr(Y=y|X=3)

0.5
1
0.667
0.333
0
0.5
0
0.333
0.667
1

Pr(Y ===
y| X

x)

Pr( X ==x ,Y y )
Pr( X = x )

X ==x ,Y y )
Pr(Y = y)

===
y| X

x)

Pr( X ==x ,Y y )
Pr( X = x )

r(t) ~ N(0.05, (0.50)^2)

mu
0.05
sigma
0.5

mean
m
0.05
mean-3*sd
-1.45
mean+3*sd
1.55

sd
s
0.5

1+R(t) ~ log-Normal
mu
1.191246
sigma
0.562705

x
-1.450
-1.350
-1.250
-1.150
-1.050
-0.950
-0.850
-0.750
-0.650
-0.550
-0.450
-0.350
-0.250
-0.150
-0.050
0.050
0.150
0.250
0.350
0.450
0.550
0.650
0.750
0.850
0.950
1.050
1.150
1.250
1.350
1.450
1.550

p(x)
Pr(X < x)
0.0089
0.0013
0.0158
0.0026
0.0272
0.0047
0.0448
0.0082
0.0709
0.0139
0.1080
0.0228
0.1579
0.0359
0.2218
0.0548
0.2995
0.0808
0.3884
0.1151
0.4839
0.1587
0.5794
0.2119
0.6664
0.2743
0.7365
0.3446
0.7821
0.4207
0.7979
0.5000
0.7821
0.5793
0.7365
0.6554
0.6664
0.7257
0.5794
0.7881
0.4839
0.8413
0.3884
0.8849
0.2995
0.9192
0.2218
0.9452
0.1579
0.9641
0.1080
0.9772
0.0709
0.9861
0.0448
0.9918
0.0272
0.9953
0.0158
0.9974
0.0089
0.9987

y=exp(x) Pr(X < x)

0.23457
0.0013
0.25924
0.0026
0.286505
0.0047
0.316637
0.0082
0.349938
0.0139
0.386741
0.0228
0.427415
0.0359
0.472367
0.0548
0.522046
0.0808
0.57695
0.1151
0.637628
0.1587
0.704688
0.2119
0.778801
0.2743
0.860708
0.3446
0.951229
0.4207
1.051271
0.5000
1.161834
0.5793
1.284025
0.6554
1.419068
0.7257
1.568312
0.7881
1.733253
0.8413
1.915541
0.8849
2.117
0.9192
2.339647
0.9452
2.58571
0.9641
2.857651
0.9772
3.158193
0.9861
3.490343
0.9918
3.857426
0.9953
4.263115
0.9974
4.71147
0.9987

Use LOGNORMDIST function

to compute CDF for log-normal
random variable

Plotting the Normal distribution

1

-- 2 ( x
1
p ( x) =- e 2s
2ps 2

mean
m

sd
s
1

x
2

mean-3*sd
-5
mean+3*sd
7

-5.0
-4.5
-4.0
-3.5
-3.0
-2.5
-2.0
-1.5
-1.0
-0.5
0.0
0.5
1.0
1.5
2.0
2.5
3.0
3.5
4.0
4.5
5.0
5.5
6.0
6.5
7.0

p(x)
Pr(X < x)
0.0022
0.0013
0.0045
0.0030
0.0088
0.0062
0.0159
0.0122
0.0270
0.0228
0.0431
0.0401
0.0648
0.0668
0.0913
0.1056
0.1210
0.1587
0.1506
0.2266
0.1760
0.3085
0.1933
0.4013
0.1995
0.5000
0.1933
0.5987
0.1760
0.6915
0.1506
0.7734
0.1210
0.8413
0.0913
0.8944
0.0648
0.9332
0.0431
0.9599
0.0270
0.9772
0.0159
0.9878
0.0088
0.9938
0.0045
0.9970
0.0022
0.9987

m )2

Finding Area Under the Normal Curve

X ~ N(1,4)
Pr(X < 2)
0.6915
Pr(-1 < X < 2)
0.5328
Pr(X > 2)
0.3085

Finding Quantiles of the Normal Distributio

X ~ N(1,4)
a
qa
0.01
-3.65
0.05
-2.29
0.1
-1.56
0.5
1.00
0.9
3.56
0.95
4.29
0.99
5.65

Why the normal distribution is not appropriate for simple returns

R ~ N(0.05,(.50)^2)
Pr(R < -1) 0.017864

Normal distribution for cc returns

-0.86466

nder the Normal Curve

les of the Normal Distribution

qa = m + sza
-3.65
-2.29
-1.56
1.00
3.56
4.29
5.65