Statistical Functions in Excel

Sample Statistics versus Population Statistics

Population Statistics– Observations include all possible outcome– Degree of freedom = N

Sample Statistics– Observations are a representation– Degree of freedom = N-1

Excel Statistical Functions

Population Statistics– Standard deviation, variance, and covariance– STDEVP(x), VARP(x), COVAR(x,y)

Sample Statistics– Sample size: COUNT(x)– Standard deviation and variance– STDEV(x), VAR(x)– There is no Excel function for sample covariance

• Correct COVAR(x,y) for degree of freedom• Sample covariance = COVAR() * N / (N-1)

– Where N is sample size

Arithmetic Average– AVERAGE()

Correlation coefficient– CORREL(x,y)

Geometric Mean

Geometric mean

Geometric average return• x1 = (1 + r1)

1

1 2 11Geometric Mean= ...

NN N

n N Nnx x x x x

1Geometric Average Return= (1 ) 1

NN

nnr

GEOMEAN Function

Using Excel to compute geometric average return\– A2:A100 contains monthly return, r.

GEOMEAN() returns the geometric mean Three strategies:1. Convert returns to 1+r -> B2:B100Y is 1+r.

Geometric average return = GEOMEAN(B2:B100)

2. Use the monthly return, r, and enter as an array. Geometric average return = {GEOMEAN(1+ A2:A100)-1}.

3. PRODUCT() is also a useful function. Geometric average return = (PRODUCT(B2:B100)^(1/N))-1

Homework 4 – Due July 10

Benninga: 7.3– Download data file from course website

– Compute population statistics, not sample statistics

– Use Data Table to create the portfolios with varying percentages in the SP500

SHW3Holden: Figure 9.1SHW4