# Calculating the corr. coefficient between returns of portfolios in EXCEL

I want to determine the correlation between the returns of a portfolio with returns of the market (suitable index). To find how the portfolio performs in bear resp. bull markets, I want to calculate the correlation coeffients over different periods when the market is in decline or in rise (is this the correct antonym for decline/recess?).

So in my first attempt I just took the returns (where 20% is 0.20) from the market and the portfolio for 4-months period in between the longer period of rise, and then calculated the correlation coefficient. But what I found was that the they had strong negative correlation (~-0.4), although both the market and the index had strictly positive returns during the whole period. Doesn't this seem a bit misleading? Shouldn't they be positively correlated instead?

So instead I created the timeseries of how the values of the portfolio resp. market changed over time (normalized), then calculated the correlation coefficients over the same period and found that the correlation coefficient was ~0.9. This seems more reasonable.

My question is what is the correct way to determine the corr. coeffient in this case? Is it even acceptable to do this. Is it more suitable to look at the rolling correlation instead and try to see how it changes over time.

Note: Excel was used to calculate the corr. coeffient.

edit:

So the the market is just the index normalized so that its value is 1 at time one. In other words all time points are divided by the first value of the time series.

The portfolio is just calculated by adding the returns of the strategy for every period. The value of this time series starts at one aswell. At time t the value of the portfolio is $1+r_{1}+r_2+r_3 + ... + r_t$ etc. (Notice I do not want to calculate it by continous compounding since it is not reasonable for the strategy)

To answer the question: Can the correlation of returns be negative, if both had positive returns?

Yes. The correlation coefficient can be understood as a normalized covariance, right?

So the covariance is a measure of how both values move in relation to their expected value (here - the mean).
So it depends on this if the covariance is positive or negative.
To put it differently with $X$ as market return and $Y$ as portfolio return.
$$V(X,Y) = E[ (X-E[X]) (Y-E[Y] ]$$

In your case you probably used this estimator $$\hat{V}(X,Y)=\frac{1}{N-1}\sum_{i=1}^{N}\left( x_{i}-\bar{x} \right) \left( y_{i}-\bar{y} \right)$$ Where $\bar{x}$ and $\bar{y}$ are the respective arithmetic means.

Now obviously if both have positive returns, yet one portfolio deviates away below from its mean while the other deviates away above its mean, you get a negative number in that sum.
So even though both might be positive in absolute values, it depends on where they are moving.

Finally, if your covariance is negative, well what is your correlation coeefficient?

As for your second question: Please detail exactly how you calculated the time series and normalization because I can't tell you if what you did in either case was correct. Best post the formulas to be sure.

• Sorry, I should have been more clear. I under that the correlation could indeed be negative as you say. What I meant was that it is a bit misleading since if you would have a negative correlation with the market you would assume that your strategy experience losses when the market is doing well and vice versa? I will edit my question and add the things you mentioned. – Good Guy Mike Apr 23 '13 at 14:19
• In this example the distinction needs to be made what losses mean. If the correlation is negative and if the market moves up, it is reasonable to expect losses - in a relative sense. You'd do better with the market PF and your returns would diminish, but that they become negative. Maybe it would be easier to view this as some sort of movement. Remember that the covariance is a measure of linear spread or divergence from a point (the mean), not a measure of what the absolute values will be! – IMA Apr 23 '13 at 14:39
• "If the correlation is negative and if the market moves up, it is reasonable to expect losses - in a relative sense", of course, good point! Didn't think of that. "Maybe it would be easier to view this as some sort of movement" what do you mean by this? – Good Guy Mike Apr 23 '13 at 18:55