Quantitative Finance
Sample & Population Covariance Formula (COVARIANCE.S / .P) & CAPM Beta
Calculate Population Covariance Cov_P = CORREL(X, Y) * STDEV.P(X) * STDEV.P(Y), Bessel-corrected Sample Covariance Cov_S = CORREL(X, Y) * STDEV.S(X) * STDEV.S(Y), and CAPM Market Beta = Cov(R_i, R_m) / Var(R_m).
Avoid mixing N vs N - 1 denominators in portfolio covariance matrices!
By definition of Pearson correlation r = Cov(X, Y) / (sigma_X * sigma_Y), multiplying CORREL(X, Y) * STDEV.P(X) * STDEV.P(Y) yields exact Population Covariance (dividing by N, matching COVARIANCE.P / COVAR), while CORREL(X, Y) * STDEV.S(X) * STDEV.S(Y) yields unbiased Sample Covariance (dividing by N - 1, matching COVARIANCE.S)!
Crucially, when computing CAPM Market Beta = Cov(R_asset, R_market) / Var(R_market), the N or (N - 1) denominator cancels out provided you use the SAME convention in both numerator and denominator—matching SLOPE(R_asset, R_market) identically!
| Statistic (Market [-4, 2, 6, 8] vs Stock [-6, 3, 9, 10]) | Denominator | Exact Value |
|---|---|---|
| Population Covariance (Cov_P) | N = 4 (116 / 4) | 29.0000 |
| Sample Covariance (Cov_S) | N - 1 = 3 (116 / 3) | 38.6667 |
| CAPM Market Beta (Cov / Var_Mkt = SLOPE) | Cancels (29 / 21) | 1.3810 |
Population Covariance (COVARIANCE.P Identity)
Computes (1/N) * Sum((X_t - X_bar)(Y_t - Y_bar)) across B2:B5 and C2:C5.
=IF(COUNT(B2:B5) < 2, "Need >= 2 Pairs", ROUND(CORREL(B2:B5, C2:C5) * STDEV.P(B2:B5) * STDEV.P(C2:C5), 4))Sample Covariance (COVARIANCE.S Identity) & Beta Check
Computes unbiased (1/(N-1)) sample covariance across B2:B5 and C2:C5.
=IF(COUNT(B2:B5) < 2, "Need >= 2 Pairs", ROUND(CORREL(B2:B5, C2:C5) * STDEV.S(B2:B5) * STDEV.S(C2:C5), 4))Try the example
Use the original values or change them to test your understanding.