How to calculate beta

The beta is fairly simple to calculate. The data required are the prices of the security being values, and an index of the market it is being valued against.

It is usual to measure market returns using broad index such as the FTSE 350 (or all-share) or the S & P 500. It is easy to calculate daily returns by dividing each days index level by that of the previous day.

To do calculate daily returns in a spreadsheet one can simply past daily end of day index levels into a column of the spreadsheet, and then enter a formula to divide each number by the one immediately above it in the next column, something like this:


Once you have entered the first formula you can copy and paste it into the remaining rows.

You then do the same with prices in the next two adjacent columns. Assuming you have used the first two columns for the index, this would be columns 3 and 4.

You then need to either calculate the slope of the linear regression of securities prices against market prices. This is equal to the covariance of the returns on the security with returns on the market divided by the population variance of the returns on the market. You should use the population variance as you have a complete set of data for the period over which you are calculating the beta.

A spread sheet offers two ways of doing this. You can use the COVAR function to calculate the covariance, then use VARP to calculate the variance of the market and then divide the covariance by the variance. Alternatively, you can use the SLOPE function to calculate the slope directly. Note that the returns on the security must come first in the formula.

The slope is the beta.

The above examples use a spreadsheet which is the most commonly available and familiar tool that can be used. It is not always the best: if you are calculating lots of betas, or otherwise dealing with large datasets you may prefer statistical software (especially considering its more reliable at producing the right answer), or simply use a service that provides beta numbers to you.

Copyright Graeme Pietersz © 2005-2019