Question

q1) Write all the steps used in finding the variance-covariance matrix directly from returns. Which steps can be skipped in Excel? Write the steps in plain English

q2) What are the diagonal entries of a variance-covariance matrix? If you have two assets with correlation of 0.65 and variances of 0.03 and 0.04, what would be the entries in the variance-covariance matrix (write in terms of (1,1), (1,2), (2,1), (2,2) where the first value refers to row number and the second value refers to column number)?

q3) What is the difference between minimum variance frontier and efficient frontier?

Answer 1

q1) Consider two stock first and two with returns in column A and Colmn B

Data - Data Analysis - Covariance

Add Input Range - Grouped by column - Select labels and enter output range as show:

Variance Covariance Matrix:

Alternate Method:

Using Query : COVAR(OFFSET($A$2:$A$30,,ROWS($1:1)-1),OFFSET($A$2:$A$30,,COLUMNS($A:A)-1))

and than draging it horizontaly and vertically. This is will all the values of variance and covariance matrix:

q2) ; Correlation = 0.65, Variance first = 0.03, Variance second = 0.04

Covariance = Correlation (first, second) * Standard Deviation of first * Standard Deviation of second

Standard Deviation = Square Root (Variance)

Covariance(First,secoond) = 0.65 * square Root (0.03) * square root (0.04)

= 0.65 * 0 .1732 * .2 = 0.02252

(1,1) = Variance = 0.03, (1,2) = Covariance = 0.02252, (2,1) = Covariance = 0.02253, (2,2) = Variannce = 0.04

Diagnal entries of matrix is variances of the factors.

q3) Minimum variance frontier tells us minimum variance that can be achieved for a given level of expected return. The portion of the minimum variance frontier beginning with the global minimum-variance portfolio and continuing above it is called Efficient Frontier.

The portfolio on the minimum-variance frontier with the smallest variance of return