So I was doing some research on the S&P 500 and came across some interesting data. Basically, I wanted to know what the stock market does on average. I particularly wanted to know the percentage of how many days the stock market is up vs down.
Getting my data was actually simpler than you might think. I went straight to Yahoo Finance and entered S&P 500 as a search for a ticker. From there I selected the “Historical Data” tab and then selected a time period of 20 years: from January 1, 1996 – January 1, 2016. [Update: I’ve included the up days vs down days for January 1, 2016 through December 31, 2021.]
The percentage of stock market days up from ‘96 – 2016 was 53.29%. The percentage of stock market days down was 46.71%. Updating that further, the percentage of stock market days up (from 2016 – 2021) was 54.86%, while days down was 45.14%.
How should investors use this information?
How to Calculate Up/Down Days Yourself in Excel
Unfortunately, that data didn’t have the % return for each day, but it wasn’t much trouble to find that out. I exported the 20 years of data into an Excel Spreadsheet. With this option, I was able to make quick judgments and conclusions with a little manipulating.
In the Excel Spreadsheet, I created a quick formula in a new column. To make this easier to type and understand, assume x = adjusted close and y = adjusted close the previous day. So my formula was =(x – y) / y. This gave the return % for the day. I simply dragged this formula down the entire column so I had return % data for 20 years.
This is great, but who has time to count each number and tally up the positive and negative dates? I certainly didn’t, so I found this out in a quick and clever shortcut.
I made a new formula in the column next to return %. Assume Hx refers to the cell(s) in the return % column. It was =IF(Hx > 0, 1, 0). Basically this means that if the return % is positive, put a 1 in the cell of the new column. If the return % is negative, put a 0.
Dragging this down the column (which you can do by clicking the bottom right hand corner of the cell and holding it down and moving the mouse down), I had 1s and 0s for every day of the 20 years. Now all I had to do was select every cell in that column, and look at the “Sum” at the bottom of excel. This gave the number 2683.
Next I needed the total number of days. Remember that the stock market is closed on the weekends and holidays, so it wasn’t as easy as 365 * 20. So I made a new column with the first cell having a value of “1”, and dragged it all the way down. The “Sum” of that was 5035.
See how simple this is? We know that 2683 of 5035 days resulted in the S&P 500 going up. This means the S&P was down on 2,352 of those days.
This was how I came up with the percentages for up and down days, 53.29% and 46.71%.
Calculating Returns from Total Up/Down Days
Of course, with the original dataset I had to take it a step further. We have all this useful data, why not? In a technique similar to above, I calculated the total return % to be 2200.69% on the up days and -2043.19% on the down days.
This resulted in a 157.5% net total, which averages out to 7.88% per year. Which only confirms the widely accepted belief that the stock market tends to return about 7% over very long time periods.
I was also curious about the average gain/loss. It was interesting to find that the S&P lost an average of -0.87% on the down days but only gained an average of +0.82% on the up days.
We can conclude from this that the stock market falls harder than it gains, but it falls less frequently. This should be unsurprising to the seasoned investor.
Finally, I dissected the data further to get actual probabilities of various return % integrals. Totally unnecessary but most likely interesting to the numbers nerds out there.
Let me just vomit the findings here:
- >10% day: .04%
- >9% day: .04%
- >8% day .04%
- >7% day: .06%
- >6% day: .14%
- >5% day: .28%
- >4% day: .66%
- >3% day: 1.4%
- >2% day: 4.0%
- >1% day: 15.2%
- 0%<x<1%: 30.2%
As for the negative days…
- <-1% day: 14.4%
- <-2% day: 4.4%
- <-3% day: 1.4%
- <-4% day: .62%
- <-5% day: .32%
- <-6% day: .20%
- <-7% day: .08%
- <-8% day: .06%
- <-9% day: .02%
- <-10% day: 0%
I’m surprised that there weren’t any days with a loss greater than 10. But I know for a fact that there have been days like that in the last 50 years. Don’t underestimate the always looming possibility of a black swan.
Of course the numbers above are an optimistic case. We’re comparing the end result during a raging bull market. The return numbers will naturally be favorable.
But let’s say that we weren’t so lucky in our time period. Let’s say we ended the 20 year time period at the absolute worst time, right in the middle of a terrible bear market in early 2009.
The net return % from January 1, 1989 – January 1, 2009 is 150.63%. This works out to an average 7.5% yearly return, even with a terrible cherry picked time period!
This confirms the power of long term investing in a period of 20+ years.
And for the nerds out there.. I didn’t forget you. The percentage of stock market days up in this 20 year time period is also 53%, 53.1% to be exact. Our odds are a little better than a coin flip, but it’s that small discrepancy that makes us winners in the long run.
This is why it’s so important to remember the fundamental principles of investing: