Estimate CAPM Beta in Excel

I'm going to show you how to estimate cap em using Excel we're going to use coca-cola as our example company to estimate cap em the first thing we need is stock returns data I think the easiest place to get that for our purposes is Yahoo Finance so we go over to Yahoo Finance and type in Ko which is Coca Cola's ticker symbol once we're on this page we need to go to historical prices I want to estimate cap M using five years of daily returns so my start date is going to be December 31st 2010 and my end date is going to be December 31st 2015 notice that the daily returns checkbox is highlighted here if we wanted to estimate cap M with monthly returns we would have to click monthly so now we click get prices and then we have to scroll to the bottom to get our spreadsheet of stock data so the first thing we need to do is to switch these from starting our chronological order to reverse chronological order so I'm going to freeze a top cell and then sort it from oldest to newest so now it starts at the beginning of our time period and works its way down so we've got the date the opening price the high-low of the day the closing price and then volume and the adjusted closing price adjust a closing price adjust for dividend payments this is the column we're going to use so the first thing we need to do is to calculate the returns of Coca Cola to do this we simply take today's return subtract off yesterday's return and divide by yesterday's return notice that this is a decimal representation of the return I actually want to use the percentage reputation of returns so I'm going to multiply this by 100 now we just have to scroll or to drag this down I'm going to use shortcuts but you can just as easily use your mouse to drag it down so now that we have the returns of Coca Cola we need to get a the returns for the stock market or the market as a whole I want you to use the fama French or the Kenneth French's data website to get the market returns so we Google Kenneth French data and you'll be brought to as data page go to US research returns and then under US research returns data we have three fama French three factor data sources for daily cap M we need the fama French three factors daily if we were estimating cap M with monthly returns we would use the fama French three factors this top CSV file so download the daily fama French three factors CSV file once you have it open you'll see here's the date here's the excess market return there's the two fama french factors and then the risk-free rate we need to copy and paste this data into the stock returns for coca-cola that file notice that these returns start at July 1st 1926 we only want the returns starting on January 1st or the beginning of January in 2011 so we need to scroll down until we get to January 3rd 2011 so I'm going to highlight everything drag it down copy it and then paste it into this file notice that I copied over the date I did this so I can ensure that the date in the form of French file there the the form of French file is equal to the date on our stock returns from Yahoo Finance so they both start on January 3rd and they both end on December 31st so we're good there now I'm just going to delete the file to get it out of the way so this is our excess market return this is often abbreviated RM RF these are the two fama french factors we don't need these to estimate cap them so I'm going to hide them and this is the risk free rate abbreviated RF now we need to calculate the excess the excess market return for the stock for coca-cola so I apologize I meant excess return for coca-cola not excess market return for coca-cola so we take the return of coca-cola and we subtract off the risk-free rate now we just need to drag this down once again I'm using shortcuts to speed this up now we have all the data that we need in order to calculate cap m using daily stock returns we need to use the data analysis toolpak in excel which is under data and then data analysis you might not have this installed if you don't have this installed by default you need to go over to file options add-ins and then if you don't have it installed data analysis toolpak is going to show up under the inactive application happens if this is the case go down to manage and click go and you'll see the analysis toolpak and now it will be installed now we need to run a regression of Coca Cola's excess return on the market's excess return so click on data analysis then regression the y range is going to be our excess market return our sorry our excess return for coca-cola so we're going to highlight the entire excess return column the input the X range is going to be our RM RF our excess market return once we have that we click OK and a new sheet is created this has the output from our linear regression the important number right here is this oops this 0.58 this is the beta estimate from Capcom this is the abnormal return or the excess return during this time period for coca-cola but what we care about for cap M for our purposes right now is beta so that's all you actually have to need to do to calculate beta using cap M

Channel: Jonathan Kalodimos, PhD

Views: 165,694

Rating: 4.8335066 out of 5

Keywords: CAPM, Finance, Excel, Investments, Alpha, Beta

Id: ucKK528ApCw

Length: 9min 3sec (543 seconds)

Published: Tue Feb 16 2016