This tool calculates beta across any time frame for any stock against any benchmark. It uses historical stock quotes downloaded from Yahoo Finance.
Other Excel spreadsheets on investexcel.net demonstrate how to calculate beta, and how Yahoo Finance calculates this measure of portfolio risk.
But this spreadsheet goes one step beyond this, and gives you a value of beta for your specific requirements
Beta measures historical systematic risk against a specific benchmark, and the values given on Yahoo Finance and Google Finance aren’t quite what you need. For example, Yahoo gives beta for the trailing 3 years against the S&P500 – but you need beta for the five years between 1995 to 2000 against the FTSE 100.
If so, this spreadsheet is perfect for you. Just enter
- a stock ticker whose beta you want
- a benchmark ticker
- and two dates
After you click the prominently-placed button, the tool grabs the historical market data from Yahoo Finance and calculates beta.
In the following screengrab, we’ve calculated the beta of Exxon Mobil (ticker: XOM) against the S&P500 for the three years trailing 31st March 2015.
You could, if wanted, change the time period or swap out the benchmark for NASDAQ 100 (ticker: ^NDX).
The value of beta given by this tool (specifically, the beta of the close prices) matches that quoted by Yahoo Finance.