How to Use Excel to Calculate Beta Coefficient

1. Create a new spreadsheet in Microsoft Excel. Type the historical data for the stock in question and the benchmark in two separate columns.
2. Find the percent change of the data for the benchmark and the stock using the following formula:=((Cell2-Cell1)/Cell1)*100Cell 1 indicates the previous period data point, and cell 2 indicates a current period data point. Drag the formula with the mouse down the two columns.For example, if the stock's daily stock price was $100 per share in June 2010 and is $125 in June 2011, the $125 is cell 2 and the $100 is cell 1. Subtract 100 from 125 to get 25. Divide 25 by 100, which equals .25. Multiple this by cell 1 or 100. The percent change for this particular stock is 25 percent.Complete this formula for the SP 500 as well, which is the benchmark. When finished, you will have a percent of change for your stock and the SP 500.
3. Figure out the beta coefficient by using the 'SLOPE' function in Excel. The slope function is '=SLOPE(range of % change of equity, range of % change of index).' For example, if there were daily changes to Apple's stock price in cells A1:A260, and the daily changes to the SP 500 were in column B1:260, the function would be '=SLOPE(A1:A260,B1:B260).'