Anomaly found in S&P 500 data provided by Yahoo!

There is monthly S&P 500 total return data from December 1990 to the present at

.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

"raylopez99" wrote

Seems to me it's simply that ^GSPC is an index, not an actual fund. It has no cap gain and dividend distributions that would result in an "adjusted close" the way funds do. Also, what each index-based fund does as far as dividends and capital gain distributions each year will vary a bit, depending on management.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

It looks like the return cited by your textbook includes dividends but not expenses. As for the others:

^GSPC doesn't include dividends or expenses VFINX (unadjusted) includes expenses but not dividends VFINX (adjusted) includes expenses and dividends

Also, bear in mind that an S&P 500 index fund like VFINX does not track the S&P 500 EXACTLY. There are a couple reasons for this. For example, VFINX is not 100% invested in stocks. According to Yahoo Finance, VFINX hold .02% cash and .03% bonds. So there's a minor deviation right there. As an aside, proponents of indexing claim this is a major strength of index funds. In an index fund, the vast majority of your money is actually invested. In managed mutual fund, this is not necessarily true. AGTHX, for example, currently holds

12.6% cash.

There's also the issue of the number of compounding periods used. It looks like you're using one. Personally, I prefer to use continuous compounding, just in case I need to do some calculus. Using continuous compounding, the rate of return for VFINX (adjusted) over your 10 year period was 16.34%. That's a difference of 1.41%.

--Bill

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Are you using those adjusted closes correctly?

They are a *one-period-only* correction. They may be used to determine a return for a single period - ie. the only thing you can use an adjusted close for is in comparison to the open for that same period. Then, for longer periods, you need to create chained returns (ie. (1 + ret1)(1 + ret2)(1 + ret3) )

You cannot do (adj.close2 - open1) for a total return which encompases periods 1 and 2.

If you used them correctly, you should get numbers which look a lot more like the VFINX, though I haven't double checked them myself.

How did you do the geometric returns, though - if you took (adj_close_at_end_of_yr_10 - open_at_begin_yr_1)^(1/10)

ie. tenth root of difference, you should *not* get the right numbers. The only open against which a adjusted close may be compared is the open for the matching reporting period, if my understanding of Yahoo's adjusted close reporting is right.

Thank you Bill and Elle, it appears that you've solved this problem. I was using N years, compounded every year, and the explanation about VFINX (adjusted) rings true. Since VFINX (adjusted) is the only free data I can find for a representative sampling of the US market, I'll stick to that, unless somebody has a better proxy. I am simply trying to judge how close to "the market" my stock porfolio has returned over the past 10 yrs (so far, pretty close).

BTW, what is interesting about "adjusted" data is that all historical data seems to change every time you download the series, but, as explained in the other thread I reference, that's an unavoidable fact of life, and I've automated this process and calculations in my spreadsheet.

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Does your portfolio match "the market"? Unless your portfolio has a beta of 1 or IS an S&P500 fund/index, you're missing the boat.

If your portfolio is loaded up with bonds, international funds, etc... you will need a composite benchmark to accurately accomplish your task.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

The size of the discrepancy suggests that ^GSPC doesn't include reinvested dividends.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Yes, I think I'm doing adjusted close correctly, insofar as every time I download the data I run the PV/FV/N/i analysis again (for the new period). Time consuming but unavoidable since the data for "adjusted close" going back to the beginning (early 1980s) changes every time a new month is added (much to my annoyance).

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Quite right, but I have not been able to figure out how to do an easy Treynor/Sharpe analysis using just simple tools available in Excel (though I'm sure you can do it with some programming finesse I'm not prepared to invest in). I simply assume my portfolio is "safer" than the market (since I own bonds, gold, which depress the beta I imagine) then use the "market" (SP500 or equivalent) as a bogey to track. Good enough for my non-professional purposes, though if there's an easy way to compute risk adjusted returns (other than the online tools which eTrade and others provide, using red/yellow/green for risk, which seem imprecise) I'd like to know about it.

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

If you're looking to keep it simple, why not use a hybrid of the S&P

500 and, say, the Lehman aggregate Bond index or some other bond index. Just weight the two indices to make a new benchmark that has similar risk to your portfolio.

If your portfolio is 20% bonds and 80% equities, then over a given period you would want to outperform ((the Lehman * 0.20) + (the S&P * .

80)). It's crude, but it will be slightly more accurate AND it's merely an extension of the work you've already done. Mostly a compy and paste situation for excel.

Bottom line is this: If you've got a portfolio with less risky investments (i.e. bonds), then conventional thinking suggests that ON AVERAGE and OVER THE LONG TERM you are going to consistently underperform the S&P 500 (unless your equities are more risky than the S&P). Keep that in mind. You don't want to become disgruntled with a perfectly good portfolio, because it is being held to overly aggressive expectations.

Good luck.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Yes. Indexes almost never include reinvested dividends, which obviously make a big difference over time.

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

Good idea. The problem is, I just noticed that AGG, the Lehman iShares Lehman Aggregate Bond (AGG), only goes back to 2003 (at least on Yahoo's "historical data" finance site).

Does anybody know where I can get Adjusted Close data for this or any other bond fund, free, so I can construct this hybrid index?

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

The AGG fund's inception date is 9/22/03.

Any earlier than that and your only real choice is to get actual Leh Agg index total returns.

I used to have access to them (and tracked component data for the Leh agg and other bond indices) directly from Lehman, but I no longer have access to it. I suspect that if you e-mailed Lehman directly, someone may be happy to send you a time series of daily total returns on the Agg. It's quite trivial on their systems.

True, but the problem is the "Adjusted Close" of an index has to be recomputed every time (see this thread)--it's not a fixed number that doesn't change month to month. So, unless I can find free historical data for Leh agg or some other bond indices (going back about 15 or 20 years in my case), I would have to email Lehman every month or so that I calculate my rate of return on my spreadsheet--I doubt Lehman wants to support me on that.

Anybody know where free bond historical price data exists, with an Adjusted Close?

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

That's why you ask for daily total returns, not daily index price levels or "adjusted close".

It's actually a bit more complicated than even that - they break things down into a "returns universe" and a "statistics universe" - because of the way that the index is constructed.

But you really don't want to go down the path of actually reconstructing daily returns on the Agg. Just ask Lehman for those daily (or other periodic) returns pre-calculated.

You can probably get those time series off a Bloomberg, too, but I don't know of any free and open source for those numbers.

Lehman does publish them daily:

But unless you're an actual client of theirs (to whom they usually offer lots of great data for "free"), they charge quite a bit for larger scale access to their data. Click on the "subscription services" link on that page above for details.

But Lehman only provides 1, 5, 10, 15 and 20 year time periods, which is fine for a "rolling average", but my problem is that i have a specific date from 15+ years ago that I must calculate the IRR (internal rate of return) to present for.

Yes, I agree. Nothing is free, and I was pleasantly surprised Yahoo had historical data for the S&P500 index.

Thanks for your help anyway. For now I'll stick to just comparing my portfolio to the S&P500, which, since it has bonds, it will lag, but that's OK.

RL

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.

I can't explain the differences, but I have been keeping track of month-end S&P 500 *Total Return* (includes reinvested dividends) data for years. It was originally available, along with historic returns, on the Barra web site. Since that closed down, I have been downloading the month-end information (available free for a few months after the close) from the S&P web site.

The numbers I calculate, using Excel, track your "textbook" figures:

ending 12/31/1997 10 yr: 18.041% 8 yr: 16.623% 6 yr: 18.052%

I suspect the small difference at 8 yr may be due to either a rounding error or possibly a typo in my data.

But I believe that the Yahoo data does not include dividends or expenses, since it is an Index; whereas VFINX probably includes dividends and expenses, and the textbook (like my data) includes dividends but no expenses.

--ron

-------------------------------------- Misc.invest.financial-plan is a moderated newsgroup where Moderators strive to keep the conversations on-topic for financial planning. Other posting guidelines include a request for brevity and another for trimming posts to which we respond. For all of the other tips and suggestions, see "FROM THE MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the Newsgroup.