0
$\begingroup$

I have a set of assets, n. I'm trying to find the correlation between 2 portfolios, say x and y, where x is nested in, or, a sub-set of y. That is, x is a portfolio based on a sub-set of n, while y is a portfolio based on the entire set of assets, n. The weights in each portfolio sum to 1, and I'm using Excel. Thanks for your time!

$\endgroup$

2 Answers 2

1
$\begingroup$

You will need the covariance matrix to calculate this.

Say you have a collection of $n$ assets. The value of asset $i$ is represented by the random variable $X_i$ and the corresponding portfolio weight is are $w_i$, and $v_i$ for the two portfolios.

The correlation between the two portfolios is: $$ \frac{\sigma(w^TX,v^TX)}{\sqrt{(w^T\Sigma w)(v^T\Sigma v) }} = \frac{w^T\Sigma(X)v}{\sqrt{(w^T\Sigma w)(v^T\Sigma v) }}$$

Where $\Sigma$ is the covariance matrix.

You can arrive to this conclusion by using variance's bilinear property.

And without using vector notation:

$$ \rho = \frac{\Sigma ^n _i \Sigma ^n _k w_iv_k \sigma(X_i,X_k)}{\sqrt{Var( P_1 )Var(P_2) }} $$

Where $Var( P_1 )$ the variance of portfolio 1.

Also take a look at this related question at math stack exchange.

$\endgroup$
0
$\begingroup$

If you're in Excel, get the returns of both portfolios into 2 columns, matched up by time. The "correl()" function will get you the correlation coefficient.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.