My Math Forum  

Go Back   My Math Forum > Science Forums > Economics

Economics Economics Forum - Financial Mathematics, Econometrics, Operations Research, Mathematical Finance, Computational Finance


Reply
 
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 e-mail, I will gladly attach the spreadsheet and let you take a look. *Thanks in advance*, this has been bugging me for days now!!!!
DynamiCAM is offline  
 
January 26th, 2012, 09:50 AM   #2
Math Team
 
Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 3,539
Thanks: 108

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 145522
2000 01 04 1399.42
2000 01 05 1402.11
2000 01 06 1403.45
......
2011 12 30 125760
2012 01 03 1277.06
2012 01 04 1277.30
2012 01 05 128106
2012 01 06 127781
If you want to see THEM ALL, go here: www.economagic.com/em-cgi/data.exe/sp/day-sp500c

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!
Denis is offline  
January 26th, 2012, 10:04 AM   #3
Global Moderator
 
CRGreathouse's Avatar
 
Joined: Nov 2006
From: UTC -5

Posts: 13,709
Thanks: 320

Math Focus: Number theory, computational mathematics, combinatorics, FOM, symbolic logic
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.
CRGreathouse is offline  
January 26th, 2012, 12:34 PM   #4
 
Joined: Jan 2012

Posts: 4
Thanks: 0

Re: Problem with evaluating S&P Return using Excel

Quote:
Originally Posted by CRGreathouse
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.
I was thinking Geometric as well. However, as you said, this would cause my "return" to be even lower. I know for a fact that I've heard in the last decade the S&P has returned around 0.4%-1%, and this number is w/o dividend reinvestment.

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!!
DynamiCAM is offline  
January 26th, 2012, 12:37 PM   #5
 
Joined: Jan 2012

Posts: 4
Thanks: 0

Re: Problem with evaluating S&P Return using Excel

Quote:
Originally Posted by DynamiCAM
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!!
My file is 15kb too big to attatch. But anyone who would like to see my spreadsheet I will email it. If I can get this correct this will be a valuable tool for anyone to use to determine any securities risk with it's historical price information. I will gladly share it when completed.
DynamiCAM is offline  
January 26th, 2012, 01:25 PM   #6
Math Team
 
Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 3,539
Thanks: 108

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 (ending-beginning) / periods: (1314.65 - 1469.25) / 3036 = -.050922....

Simple example:
100
98....-2
95....-3
89....-6
80....-9

-2-3-6-9 = -20 ; -20 / 4 = -5

80 - 100 = -20; -20 / 4 = -5
Denis is offline  
January 26th, 2012, 04:03 PM   #7
Global Moderator
 
CRGreathouse's Avatar
 
Joined: Nov 2006
From: UTC -5

Posts: 13,709
Thanks: 320

Math Focus: Number theory, computational mathematics, combinatorics, FOM, symbolic logic
Re: Problem with evaluating S&P Return using Excel

Quote:
Originally Posted by Denis
Well, I see geometric mean as worthless...when working with numerous periods like 3036 in this case.
You'll get poor results, then. The geometric mean is the only sensible way to deal with increases of this type.

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?
CRGreathouse is offline  
January 26th, 2012, 05:17 PM   #8
Math Team
 
Joined: Oct 2011
From: Ottawa Ontario, Canada

Posts: 3,539
Thanks: 108

Re: Problem with evaluating S&P Return using Excel

Quote:
Originally Posted by CRGreathouse
> You'll get poor results, then. The geometric mean is the only sensible way to deal with increases of this type.

Agree, CR; but the OP "seems" to be inquiring about the effectiveness of averaging daily changes.

> 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?

Agree, sure not indicative of much; but the annual income remains $30 per $1000, of course.

> 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?

Well, ok, but no need to switch banks for that; you can just switch account numbers in the same bank
Denis is offline  
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:
Originally Posted by CRGreathouse
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.
Only 2 big crashes. (Following periods that were getting hot.)
Erimess is offline  
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:
Originally Posted by DynamiCAM
I was thinking Geometric as well. However, as you said, this would cause my "return" to be even lower. I know for a fact that I've heard in the last decade the S&P has returned around 0.4%-1%, and this number is w/o dividend reinvestment.
But your time period isn't "the last decade." Change time periods even slightly, depending on what's been going on, can have drastic changes in the returns. The market was pretty hot the last of the 90's and peaked around beginning of 2000. It was up to 1400 something, and then went down under 900 in the post-911 crash. Comparing the last decade is starting in the middle of that big crash when the thing was lower, and yeah, we're back down to about that point with little return. But if you compare today to 1999, you're hitting when it was near the peak and there's going to be a loss.

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?
Erimess is offline  
Reply

  My Math Forum > Science Forums > Economics

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





Copyright © 2014 My Math Forum. All rights reserved.