Calculating the Variance –Covariance matrix

1. Calculating the Variance –Covariance matrix MGT 4850 Spring 2009 University of Lethbridge

2. Efficient Portfolios • Efficient frontier • Black (1972) – convex combination of any two efficient portfolios, e.g. if we have two efficient portfolios we can find the whole efficient frontier. • Minimize portfolio variance, subject to defined return and sum of weights equal 1.

3. Transpose and Multiplication • Weights - column vector Γ (row vector ΓT) • Returns - column vector E (row vector ET) • Portfolio return ET Γ • 25 stocks portfolio varianceΓTSΓ ΓT(1x25)*S(25x25)* Γ(25x1) • To calculate portfolio variance we need the variance/covariance matrix S.

4. variance/covariance matrix • Using Excess Returns • Return data for variance-covariance 295 • Excess return matrix A and its transpose AT for the calculation of S matrix • AT A/(M-1) → S (p. 292).

5. Excess Returns (fixing the row cell A$23)

6. AT A/(M-1) → S (p. 292).

7. Population vs. Sample

8. VBA (optional) Function VarCovar(rng As Range) As Variant Dim i As Integer Dim j As Integer Dim numCols As Integer numCols = rng.Columns.Count Dim matrix() As Double ReDim matrix(numCols - 1, numCols - 1) For i = 1 To numCols For j = 1 To numCols matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j)) Next j Next i VarCovar = matrix End Function

9. Array function 298 (Shift+CTRL+Enter)

10. variance/covariance matrix 299 • Offset Function → returns a reference to a range that is a given number of rows and columns for a given reference

11. Minvarprt=1.S-1/1. S-1.1T

12. Eff P=S-1[E(r)-c]/Sum{S-1[E(r)-c]}

13. Single Index Model