Calculating from mutual share prices the correlation coeffiient of returns

[xxxBogus-email-address]

[Plz excuse my ignorance.]

I'm trying to use Excel 97 to find the correlation coefficent
between the weekly returns of 2 mutual funds. However, I only have
their share prices (in columns) downloaded from Yahoo Finance.. So, I
assume that first I must convert the weekly data into weekly returns,
then use the CORREL() function to get the coefficient between the two
data sets.

Is there an Excel function that will calculate a column of returns
from a column of share prices? If not, did I do this correctly?

=====

DATA FROM YAHOO FINANCE

Neuberger Berman Genesis Tr (NBGEX) Weekly 9/1/094 to 9/18/04
Col A
Date
13-Sep-04
7-Sep-04
30-Aug-04
23-Aug-04
16-Aug-04
*** [snip]

Col G
Adj. Close*
39.86
39.65
39.28
38.73
38.44
*** [snip]
---

Templeton Foreign A (TEMFX) Weekly 9/1/094 to 9/18/04
Col K
Date
13-Sep-04
7-Sep-04
30-Aug-04
23-Aug-04
16-Aug-04
*** [snip]

Col Q
Adj. Close*
11
10.95
10.82
10.78
10.69
*** [snip]

=====

EXCEL CALCULATIONS

Neuberger Berman Genesis Tr (NBGEX) Weekly 9/1/094 to 9/18/04
Col H
Return
=(G3-G4)/G4
=(G4-G5)/G5
=(G5-G6)/G6
=(G6-G7)/G7
=(G7-G8)/G8
*** [snip]

---

Templeton Foreign A (TEMFX) Weekly 9/1/094 to 9/18/04
Col R
Return
=(Q3-Q4)/Q4
=(Q4-Q5)/Q5
=(Q5-Q6)/Q6
=(Q6-Q7)/Q7
=(Q7-Q8)/Q8
*** [snip]

Correlation Coefficient
=CORREL(H3:H431,R3:R431)
which returns 0.622

(which, if accurate, indicates that the performance of these two funds
is highly correlated and do not further diversification goals)

Thx much.

 

[Harlan Grove]

DATA FROM YAHOO FINANCE

Neuberger Berman Genesis Tr (NBGEX) Weekly 9/1/094 to 9/18/04
Col A
Date
13-Sep-04
7-Sep-04
30-Aug-04
23-Aug-04
16-Aug-04
*** [snip]

Col G
Adj. Close*
39.86
39.65
39.28
38.73
38.44
*** [snip] ....
Correlation Coefficient
=CORREL(H3:H431,R3:R431)
which returns 0.622

(which, if accurate, indicates that the performance of these two funds
is highly correlated and do not further diversification goals)

Do you really believe a time period of less than one month is sufficient to
truly measure the correlation of returns in two mutual funds?!

Perhaps I could interest you in a bridge or some beachfront property in
Kansas to diversify your portfolio?

 

[xxxBogus-email-address]

DATA FROM YAHOO FINANCE

Neuberger Berman Genesis Tr (NBGEX) Weekly 9/1/094 to 9/18/04
Col A
Date
13-Sep-04
7-Sep-04
30-Aug-04
23-Aug-04
16-Aug-04
*** [snip]

Col G
Adj. Close*
39.86
39.65
39.28
38.73
38.44
*** [snip] ...
Correlation Coefficient
=CORREL(H3:H431,R3:R431)
which returns 0.622

(which, if accurate, indicates that the performance of these two funds
is highly correlated and do not further diversification goals)

=====

Do you really believe a time period of less than one month is sufficient to
truly measure the correlation of returns in two mutual funds?!

Perhaps I could interest you in a bridge or some beachfront property in
Kansas to diversify your portfolio?

=====

I asked this Q in the misc.invest.mutual-funds newsgroup (at

http://groups.google.com/[email protected]&rnum=2).
I saw several places in Bernstein's The Intelligent Asset Allocator
that he uses monthly, but Yao et al, in Managing Your Portfolio, uses
daily at pp. 38-39 and in multiple tables at 237-55.

Out of ignorance, I compromised w/ weekly. What is the most reliable
measure? Are there recognized authorities that analyze the pros and
cons of various periods?

Are the Excel mechanics in my original post here OK? More efficient
way to do the calculations?

Thx.

 

[Harlan Grove]

I saw several places in Bernstein's The Intelligent Asset Allocator
that he uses monthly, but Yao et al, in Managing Your Portfolio, uses
daily at pp. 38-39 and in multiple tables at 237-55.

....

Only 5 data points for the two series of returns gives an almost meaningless
correlation statistic. For example, enter 10 in A1 and B2, then select A2:A5
and type the formula

=A1*(0.7+0.8*RAND())

and press [Ctrl]+[Enter] to enter this into the 4 cells at once. Then select
B2:B5 and type the formula

=B1*(0.95+0.2*RAND())

and press [Ctrl]+[Enter]. The first series, A1:A5, will *on* *average*
produce a 10% period-to-period return while the second will produce only a
5% gain, but the former is 4 times as volatile as the latter. Both produce
expected period-to-period gains, so they should be positively correlated.
However, they hardly represent similar portfolios.

Enter 1 to 1000 in A9:A1008 and enter the formula =CORREL(A1:A5,B1:B5) in
B8, select A8:B1008 and run Data > Table, and enter A8 as the Column input
cell. Then enter =COUNTIF(B9:B1008,">.6") in C8 and
=COUNTIF(B9:B1008,"<-.25") in D8. You'll see that C8 shows strong positive
correlation for 40-45% of the 1000 simulated series, and significant
negative correlation for 20-25% of the 1000 simulated series.

While these results do show that there's more likely to be positive
correlation than negative correlation from actual observations of such
positively correlated series, there's a nonnegligible chance that you could
get false negative correlations. The reverse is also true: if one series
produces expected gains and the other expected losses, you could get a
nonnegligible number of false positive correlations.

My point is that 5 data points is just too few to rely upon. Daily returns
for an entire month (approx 21 trading days in the average month) would
produce much more reliable statistics.

That said, most mutual funds other than sector funds are already
substantially diversified. You're unlikely to realize much additional
diversification from holding shares in, say, two growth funds rather than
one growth fund or two equity income funds rather than one equity income
fund. You would realize significant additional diversification from
investing in one growth fund and one equity income fund rather than one or
two of either type. If you want major diversification with low fees and low
transaction volume (so, generally, lower taxable distributions and more
build-up of net asset value) as an added bonus, invest in index funds. If
you really want higher returns than index funds provide, that comes from
*LESS* not more diversification.

As for your Excel formulas, you're doing about as well as you can.

 

[xxxBogus-email-address]

I feel bad that you spent so much time on the 5 data points issue. I
used the convention of 3 asterisks to indicate omitted data from the
post. N is actually much larger. I apologize for the
misunderstanding.

I actually have about 430 data points by downloading the weekly data
from yahoo re these two funds

Re general propositions on diversification among stock funds, the
sources I have read so far seem to indicate that I'm more likely to
get more diversification from domestic value, growth, blend or index
stock funds by going with special sector (eg real estate/REIT) or
foreign funds. I also recognize that the general domestic economy (and
perceptions of it) is likely to produce pretty high correlations among
most domestic stock funds. But I wanted to get a better fix on the
correlation coefficients for the specific stock finds offered by my
deferrred comp (457b) providers. If I can find a stock fund among
them, say, with a .4 correlation, versus a .7 correlation, I think I
have made some diversification progress. (Although that is not to say
that the other stats for the fund will be as favorable)

Thx again for your thoughts on this.

 

[Harlan Grove]

. . . If I can find a stock fund among
them, say, with a .4 correlation, versus a .7 correlation, I think I
have made some diversification progress. (Although that is not to say
that the other stats for the fund will be as favorable)

....

So true. Burying half your money in the back yard and investing in an index
fund is highly likely to produce low correlations too. It's very difficult
to find 2 funds with low correlations of returns and both with desirable
long term total returns.

You're also correct that coupling a broad fund with a sector fund would
increase diversification, but I don't see that as contrary to what I wrote
before. Seeking to diversify by investing in multiple funds within the same
general fund type is problematic. It's easier to diversify by choosing one
each from different fund types.

Beyond that, if your investments would be tax-defferred, you could gain even
greater diversification by investing in bond and convertible security funds.