Calculate 60 monthly returns for the two items using the adjusted closing values. Note: You need 61 months of info to get 60 monthly returns. You should know how to calculate a simple holding period return based on the closing prices! This is just the percentage change from the prior period.
Before you calculate the statistics below, do just a little bit of "naming." On the Formula ribbon, look for Define Name. Select all 60 of the returns that you calculate for each security and give that whole range a name. You could, for example call the range that has the 60 monthly returns that you calculated for the S&P 500 something like SP500. Then when you calculate returns, standard deviations and such, you could have formulas like =average(SP500).
Once you have your monthly returns, calculate monthly average returns and standard deviations for the two data series. This information should go in the table that is already set up on the Return Data page. (There is just one average return and one standard deviation for each series.) Calculate the correlation coefficient between the monthly returns of the securities. (There is only one correlation coefficient.) Use Excel functions such as =Average( ) to help you out here. Excel makes doing all of this pretty easy.
Annualize the monthly data to make it easier to compare. For annualized returns, just multiply the average monthly return by 12. For annualized standard deviations, multiply the monthly standard deviation by the square root of 12. You should end up with one annualized return and one standard deviation for your each of your two data series. This should also go in the table that is already set up.
Create a chart using the data that you generated on the Stock Returns page that shows a graphical derivation of the stock's beta.This chart will plot the monthly returns of your security versus the monthly returns of the market. The market's returns must be reflected on the x-axis and your stock's returns should be on the y-axis. (Hint: X-Y scatterplot.) Do NOT copy your return data to this sheet to do this. Excel Best Practices call for inputting your data only once.