First, you will need to have Microsoft Excel handy. This is the hardest step actually - the rest is plug and play.
Make one column of data showing the monthly average price for the stock you're interested in, for a number of years ending in the current year. How far you go back will depend on what the market did during that time (you don't want data selection bias) and what the stock did during that time (did the company change its core business from one sector to another, if so then you're most interested in the current business period). Then, to the right of this data, do simple division to get the percentage changes month to month (simple division of the current month divided by the previous month, minus 1). These monthly percentage changes will be part 1 of your source data for the Beta calculation. Let's call them Data Group A
Make a second column of data showing the monthly average S&P Index quotes for the same period. Or whatever index best applies to the country or business sector of the stock you're looking at. The index should be representative and not taken from a different country's stock market. Like you did for the stock price data above, to the right of all the S&P Index quotes you will add the percentage change of that month versus the previous month (simple division of the current month divided by the previous month, minus 1). These monthly percentage changes are the second data source for our stock Beta calculation. Let's call them Data Group B.
The Beta calculation is very simple. Beta, as many of us know, is simply the Covariance of the individual stock price returns and market returns, divided by the Variance of stock and the market returns. On Excel this is super easy, there are functions for Covariance and Variance built into the program.
So in this case, you add a cell on the spreadsheet called "Covariance of Stock & Market Returns" and in the cell to the right of that cell, type the text "=COVAR.P" which will then prompt you for the data you want to find the Covariance for. Highlight all Data Group A with your mouse, and then type "," after you've highlighted it... the comma then moves you to the second group of data modeled in the Covariance function. Then you highlight all Data Group B with your mouse, and finally type ")" which closes the function. Hit the "return" key and you'll have the Covariance of Data Group A and Data Group B. This is the numerator in your Beta calculation.
To get the denominator in your Beta calculation, simply go to another cell and call it "Variance of Stock Returns" and to the right of that cell, type the text "=VAR.P" which will then prompt you for the data. Highlight all of Data Group A with your mouse, then type ")" to close out the function. This gives you the Variance of the stock returns. Repeat the same step for Data Group B and call is "Variance of Market Returns." Multiply them together and this is the denominator in your Beta calculation.
Beta then, is simply dividing the numerator by the denominator. Boom you've got your Beta. If you have a non-listed asset you want to track Beta for, and you have access to good price/value data for the asset, then you can do the same thing for it, and replace the non-listed price/value data in the column to generate the Data Group A. But beware price smoothing and appraisals as a source for price/value data of non-listed assets, particularly less liquid heterogeneous assets (like real estate, for example). The data may not be representative or stale and at minimum would need to be unsmoothed in such instances.
No comments:
Post a Comment