If I have a list of 20 different hedge fund monthly return streams of varying lengths, and I wanted to create a formula that will calculate the correlation between each return stream and say the monthly return of the S&P 500 Index for each given time period, how would I do that? For example, if Fund 1 has a track record of 30 months, I want to find the correlation to the S&P 500 for those 30 months. Fund 2 has a track record of 50 months, so I want to find the correlation to the S&P 500 for 50 months, etc. Thanks for any help.
For excel 2007, under data tab, there is data analysis pack. It has build-in correlation function.
assuming you’re using Excel - just use the correl() function and make sure the fields match for the relevant periods for each fund - ie use the relevant 30 periods for fund 1, 50 periods for fund 2. for the return fields for the funds and index, use the continuously compounded returns ie natural log of nominal returns, ie LN(price0 / price-1) for each period. Alternatively, can use the diff in log of prices for each period, but i use the log of the nominal period return. Same result. then do significance test to see if correlation coefficient is significant - ie significantly different from nil. (H0: r = 0) 2-tailed t-test where test statistic = r * root (n-2) / root (1 - r squared). eg if n=30, r must be at least 0.36 to be significant at 95% confidence level, or at least 0.47 to be significant at 99% conf. etc (ok - I’m bored waiting for results…)
Let me clarify–I want to create ONE formula that I can use for every fund without having to modify it each time. So I don’t want to have a correl(a1:a30, b1:b30) type formula and have to customize it for each return stream.
if you have say 20 colums for 20 different hedge fund returns, you can use the same formula for correlation coefficient as long as the values in the unused hedge fund return cells are blank (or non-numeric) for the periods where you have no data. The correl function will ingore blanks and non-numeric data in the hedge fund cell even if there is a value in the index return cell (which there would be). So in the hedge fund return cells, make it an =IF formula to leave the cell blank if there is no hedge fund value for that month. (Personally I don’t use blanks, I always put something in there so I can see there is a formula not a blank cell. I generally use “.” instead of blanks) then, to do the signicance test on the correlation coefficient, show the p-value by using: =TDIST(test stat, df, 2). where df = n-1. So a p-value <0.05 means signifant to 95%, etc. cheers
Sweet, thanks for the tip. I make skipE99 look good for not knowing something that simple.
just so you know, the correl function in excel is biased. so do your correl and then *n/(n-1)