# Correlation Between 2 Portfolios

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!

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.

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.