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$
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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.