Calculating the Variance –Covariance matrix

1. Calculating the Variance –Covariance matrix MGT 4850 Spring 2008 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 p. 151 • Excess return matrix R and its transpose RT for the calculation of S matrix • RT R/10 → S (p. 153-154).

5. VBA (skip for now) 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

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

7. Single Index Model