Prior to discussing how to calculate beta in excel, lets define it. Beta is a measure of a stock’s volatility in relation to the overall market.
The beta for a stock describes how much the stock’s price moves in relation to the market. It’s generally used as both a measure of systematic risk and a performance measure. A higher beta indicates that the stock is riskier, and a lower beta indicates that the stock is less volatile than the market.
Beta, which has a value of 1, indicates that it exactly moves following the market value.
Formula to Calculate Beta in Excel.
- Download historical security prices for the asset whose beta you want to measure.
- Download historical security prices for the comparison benchmark.
- Then calculate the percent change period to period for both the asset and the benchmark.
- Find the variance of the benchmark using =VAR.
- Find the covariance of asset to the benchmark using =COVARIANCE.S
β = Covar(rs , rm)/Var rm
Example:
Suppose your data in an excel spreadsheet is as follows.
![Calculate Beta in Excel.](https://www.learntocalculate.com/wp-content/uploads/2021/04/beta-in-excel-1024x339.png)
We will then calculate the percentage change in assets and the benchmark price.
![Calculate Beta in Excel.](https://www.learntocalculate.com/wp-content/uploads/2021/04/beta-in-excel2-1024x297.png)
- Find the variance of the percentage change in assets using the variance function VAR.S.
- Then find the covariance of the percentage change in benchmark using the covariance function COVARIANCE.S.
![Calculate Beta in Excel.](https://www.learntocalculate.com/wp-content/uploads/2021/04/BETA-IN-EXCEL-3-1024x323.png)
Then divide the covariance by the variance to get the beta.
![Calculate Beta in Excel.](https://www.learntocalculate.com/wp-content/uploads/2021/04/beta-1-1024x361.png)
Therefore, the beta is 2.744197874.