I want to calculate by-myself 5 year return & STD for SPY ETF.

What I did:

  1. Downloaded to Excel from yahoo finance historical data for the ETF (daily Adj. Close) from 1/1/start_year to 1/1/start_year+5.
  2. (A) Calculated for each year in Excel STDEV.P based on daily closed prices. (B) Calculated for each year return by end_year_price/start_year_price.
  3. (A) Simple Arithmetic Average for the STD based on yearly STD's I have from above. (B) Geomean for the return based on yearly returns I have from above (compound average).

Problem: The numbers far away from the figures we can find in Morningstar, yahoo finance etc. (Assume I work correct from the perspective of using numbers, percentages, +/-1, *100 etc...)

Question: This is a Math exercise not a real world finance one. Does from pure mathematical point of view I have mistakes? Which means that the deviations from the figures published in yahoo finance can be explained with taxes, dividends etc. real economic world. OR: I have mathematical mistakes and this is not the way how to compute this figures (in such case - I would like to know how what are the mistakes).


  • 1
    $\begingroup$ Returns are ( end_year_price -start_year_price)/start_year_price $\endgroup$ – sparkle Dec 5 '15 at 9:12
  • $\begingroup$ @sparkle Thanks, I mentioned assume that from the perspective of working with percentages etc. I aligned the numbers. The bug is not here $\endgroup$ – michael Dec 5 '15 at 9:21

I believe a few things need to be said here. First, returns are usually calculated (END_VALUE-BEGIN_VALUE)/BEGIN_VALE. There are other ways, but this is what is usually used, and much arguments can be had on what "value" actual is. Second, data frequency should be aligned so daily standard deviation should be aligned to daily expected returns. Third, the periods for the measures should be aligned so that a 5 year return is matched by a 5 year standard deviation.

Now that should get you on the right track. However, since in these three decisions alone much discretion has to be applied it is expected that your calculations do not match what you see on a web site, unless you are able to infer their choice.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.