
Economics Economics Forum  Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance 
 LinkBack  Thread Tools  Display Modes 
January 26th, 2012, 09:01 AM  #1 
Joined: Jan 2012 Posts: 4 Thanks: 0  Problem with evaluating S&P Return using Excel Hi, first poster here, but glad to have found a large community willing to help. I am currently finishing up my FSU finance program, interning at Merrill Lynch, and continually work on my own small Roth IRA. I am in the process of creating an Excel template that I can use over and over to measure volatility and beta of securities. What I did was compile a daily closing value of the S&P 500 (information from Yahoo! Finance) and list that information in a spreadsheet. A1;A3036 are the dates from Jan 3, 2000 to Jan 24, 2012. In the column B1:B3036 are the daily closing valuations of the index. In column C, I measured the amount of daily change from one day to the next. My problem is when I average the daily changes, I get a negative return, in which I know the S&P has returned somewhat of a positive return since 2000. Is Yahoo! Finance's information incorrect, or am I calculating incorrectly? If anyone would like to speak through email, I will gladly attach the spreadsheet and let you take a look. *Thanks in advance*, this has been bugging me for days now!!!! 
January 26th, 2012, 09:50 AM  #2 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 3,089 Thanks: 36  Re: Problem with evaluating S&P Return using Excel
daily closing value of the S&P 500: 1999 12 31 1469.25 2000 01 03 1455·22 2000 01 04 1399.42 2000 01 05 1402.11 2000 01 06 1403.45 ...... 2011 12 30 1257·60 2012 01 03 1277.06 2012 01 04 1277.30 2012 01 05 1281·06 2012 01 06 1277·81 If you want to see THEM ALL, go here: www.economagic.com/emcgi/data.exe/sp/daysp500c So of course you'll get a negative result! You said: "I get a negative return, in which I know the S&P has returned somewhat of a positive return since 2000." Not according to the data! 
January 26th, 2012, 10:04 AM  #3 
Global Moderator Joined: Nov 2006 From: UTC 5 Posts: 12,866 Thanks: 95  Re: Problem with evaluating S&P Return using Excel
You need to take the geometric averages, not the arithmetic averages. So if you have a 50% loss followed by a 100% gain, followed by a 3% gain, you should average them as cuberoot((1  0.50)(1 + 1.00)(1 + 1.03)) = 1.009901... or about a 0.99% gain per day. (Arithmetic averages would make this seems like a 17.67% increase per day...) But this will make the average even lower. From 12/31/1999 to 01/06/2012 the value dropped about 13%, or 1.16% per year. Of course your choice of starting and ending dates can change things a lot, and I don't know if anything funny happened with the index in that time. 
January 26th, 2012, 12:34 PM  #4  
Joined: Jan 2012 Posts: 4 Thanks: 0  Re: Problem with evaluating S&P Return using Excel Quote:
I'm trying to figure out who is wrong: Yahoo! Finance data My calculations The supposed fact that the S&P has returned a "limited" but postivie return W/O DIVIDEND REINVESTMENT I have attached my template. GEOMEAN in the f(x) menu hasn't given me a numerical value it says error.. not sure why this is either. I need an excel/math whiz on this!!  
January 26th, 2012, 12:37 PM  #5  
Joined: Jan 2012 Posts: 4 Thanks: 0  Re: Problem with evaluating S&P Return using Excel Quote:
 
January 26th, 2012, 01:25 PM  #6 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 3,089 Thanks: 36  Re: Problem with evaluating S&P Return using Excel
opening Jan.3/00: 1469.25 closing Jan.24/12: 1314.65 Well, I see geometric mean as worthless...when working with numerous periods like 3036 in this case. 1469.25(1+i)^3036 = 1314.65 ; i = .00003662... The average of differences ,to me, is simply (endingbeginning) / periods: (1314.65  1469.25) / 3036 = .050922.... Simple example: 100 98....2 95....3 89....6 80....9 2369 = 20 ; 20 / 4 = 5 80  100 = 20; 20 / 4 = 5 
January 26th, 2012, 04:03 PM  #7  
Global Moderator Joined: Nov 2006 From: UTC 5 Posts: 12,866 Thanks: 95  Re: Problem with evaluating S&P Return using Excel Quote:
Consider a savings account (bond, etc.) yielding a 3% return each year over a span of 30 years. The arithmetic mean gives an average return of 4.75%. How is that sensible? Even better, maybe you swich banks after 15 years to another offering the same rate. Withthe arithmetic mean you get an average return of 3.72%. Why did that change when you're making the exact same amount of money?  
January 26th, 2012, 05:17 PM  #8  
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 3,089 Thanks: 36  Re: Problem with evaluating S&P Return using Excel Quote:
 
January 26th, 2012, 10:22 PM  #9  
Joined: Apr 2011 From: USA Posts: 782 Thanks: 1  Re: Problem with evaluating S&P Return using Excel Quote:
 
January 26th, 2012, 11:10 PM  #10  
Joined: Apr 2011 From: USA Posts: 782 Thanks: 1  Re: Problem with evaluating S&P Return using Excel Quote:
And yes, it would be without dividend reinvestment, because an index doesn't have dividends. But there are absolutely different ways you can define a return. I get stuff off Morningstar and while it might be interesting to know exactly how they calculate stuff, I've always been more concerned with things on a comparative basis, and they already have risk numbers on there. You're in finance and interning at Merrill Lynch and they've never taught you how to do this?  
January 28th, 2012, 09:53 PM  #11  
Joined: Jan 2012 Posts: 4 Thanks: 0  Re: Problem with evaluating S&P Return using Excel Quote:
 
January 28th, 2012, 10:05 PM  #12  
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 3,089 Thanks: 36  Re: Problem with evaluating S&P Return using Excel Quote:
 
January 29th, 2012, 11:46 PM  #13  
Joined: Apr 2011 From: USA Posts: 782 Thanks: 1  Re: Problem with evaluating S&P Return using Excel Quote:
And I was also not talking about premade tools created for people to just point and click blindly. Far from it, and you're assuming. I didn't ask if they pointed you to some tools online that you could use. I'm very much against tools as a method of "learning" how to do something, but rather the understanding behind it. You can ask anyone here exactly how much I hate memorizing processes and using prefab stuff. I was talking about actually learning how it's done, the math behind it, the concepts behind it, etc. I said "how it's done" not "what tools exist." If you're in finance, you should be taught this. "Average return" can be defined in different ways, and you can't figure out how it's done if you're not understanding the meaning behind it. That is what I'm talking about in terms of being "taught this stuff," not some silly tool. Yes, I mentioned Morningstar  I also said that's where I go  there's already tons of stuff out there for the nonfinance majors like me. I wasn't implying you should go there  if you're going to do finance, I would highly suggest you learn the stuff, not go to Morningstar. You weren't getting the returns you expected to get  I was tossing my two cents in about some reasoning behind it. Did you even pay any attention to what I was saying?  

Tags 
evaluating, excel, problem, return, sandp 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Return on Investment Questions?  Oranges'n'Lemons  Economics  2  February 21st, 2012 09:36 AM 
need help to find interest rate of return by excel  nastenka  Economics  0  December 24th, 2009 02:12 PM 
Return of the Factoring!!  JakeK  Algebra  5  September 3rd, 2009 08:18 AM 
Return Modulo as 1 Instead of 0  SidT  Number Theory  10  July 5th, 2009 10:00 AM 
Calculating Return  damo12  Economics  11  September 18th, 2008 09:36 AM 