Calculating stock beta using Excel

hello and welcome in this screencast we will take a look at how to compute the better for given stock the better is a measure of the volatility of the stock relative to the stock market as a whole so let's get started and download some data from Yahoo Finance I'm going to download Amazon stock information so if you go to historical prices and select weekly from let's say 2002 March 16 2002 or let's say July 14th 2002 to July 14 2012 you get prices and go all the way down download spreadsheet now we can delete all information other than the adjusted close and next we can download information from the S&P 500 again historical priors july 14 2002 and weekly get prices download the spreadsheet okay now I'm just going to upload this into my working spreadsheet so here's my data I've copied the information from the two downloaded spreadsheets into my working spreadsheet these are the Amazon closing prices now we need to calculate the returns the return is just the current stock price divided by the previous period stock price minus one expressed as a percentage we do the same thing for this future and I can copy this all the way to the bottom and this one too let's we just need to delete the last observation because it does not have a previous start price to compute returns from okay so having done this it's now straightforward to compute the beta the beta is nothing but the covariance of the returns from Amazon to the returns from SN P divided by the variance of the film's from s LP and that's about one point one zero six seven now another way to compute the beta is to use a slope function the slope function gives the slope of the line that would be formed if you were to plot Amazon returns with SNP returns so here Amazon returns are the known Weis and S&P returns are the known exes and if you do a slope you will get the same number I just need to increase the number of digits here to show that they're really the identical number so these are two ways in which you can compute the beta for a stock compared to the beta for a stock market index the things to remember are you want to use the adjusted clothes and you want to make sure you choose the same time interval for both of the stocks if you're using weekly for your S&P 500 or your stock market index then use weekly for your stock index as well so hopefully that should help with this I hope you enjoyed this video thanks for watching

Channel: Codible

Views: 464,357

Rating: 4.869565 out of 5

Keywords: beta, finance, stock beta, excel 2010, microsoft excel

Id: zlClflcSrM8

Length: 4min 29sec (269 seconds)

Published: Sat Jul 14 2012