Question

Researchers in Finance find that, in general, stock market performs the best in January, and the worst in fall, such as October. You would like to test whether this is accurate.

You will do so by calculating the historical monthly holding period returns of S&P 500 index from 1950 to 2019. Across these 80 years of monthly data, you will calculate the average returns for all 12 months (Jan, Feb, … Dec) and find out which month performed the best and which month performed the worst.

There are multiple ways to do this, some ways are smart, only take a couple of minutes, some ways are more time-consuming and tedious.

Answer 1

The historical monthly prices of S&P 500 index are downloaded from Yahoo Finance.

The Adj.Close column is used because this is the price adjusted for corporate actions such as dividends, stock splits etc.

Holding period return in each month = (Current month price - previous month price) / previous month price

Next, we calculate the average of each month's return using AVERAGEIF function in Excel.

Next, we calculate the average of each month's return using AVERAGEIF function in Excel.

The best performing month is October and the worst performing month is August

The best performing month is October and the worst performing month is August

The best performing month is October and the worst performing month is August

The best performing month is October and the worst performing month is Augus