In-Class Exercise: Are Stock Return Normally Distributed?
Log into a Bloomberg terminal with your account credentials. Go to the Bloomberg page for the S&P500 index (SPX).
Download monthly(frequency) index data into Excel from 12/29/2006 to 12/30/2016. In total you should have 12x10+1=121 index data points.
Those will give you 120 returns next. To save you time, I have downloaded the price data in the Excel file posted.
Calculate monthly holding period return (HPR)-one return each month, month end to month end. The index itself contains dividends (paid from the 500 stocks) but it does not pay them out. In other words, it is a tradable index but it is not a financial security itself. Therefore, calculation of HPR is simplified to only capital gain.
Draw histogram of monthly returns in Excel. You will need to first define the range and find out the bin size. This is what I suggest you do:
1) find the minimum and the maximum of your return data series. Use =min( ) and =max( ) functions. Now you have the range;
2) ask yourself how detailed/refined you like to visualize your data: e.g., every 1%, every 0.5%, or every 0.1% .
This is entirely up to you. However, you can end up with too many bins by choosing very small bin size. Try different bin sizes until you are visually satisfied with your histogram.
Last, answer the most important question in a textbox right below your histogram: by visually inspecting your histogram, do you think your return series fits a normal distribution or not?
Think about the bell shape, being symmetric, thin tails, etc.
If you do not see ‘Data Analysis' under the ‘Data' dropdown menu, you will need to load the Analysis ToolPak first.
Create histogram in Excel:
Create a histogram in Excel
Video tutorial
Excel Histogram - statistics(youtube)
What you should turn in? Just your Excel file. Upload your Excel to this assignment directly on Blackboard.