Question

I have a portfolio of 56 stocks with varying weights. My particular stock holds a weight of 1/56.

Please walk me through the steps of how to calculate the:

1) 5 year beta of my stock with the portfolio

2) 5 year beta of my stock with the S&P 500

3) 5 year beta of the portfolio with the S&P 500

Answer 1

The formula to calculate beta is:

In order to calculate beta in excel, the following steps can be followed:

• You would need daily historical returns of both the benchmark, whether it is the portfolio or S&P 500, and the individual stock for the past 5 years. They can be arranged in 2 columns side by side.
• Next step is to calculate covariance between daily returns of the individual stock and the benchmark and the variance of daily returns of the benchmark. Dividing the covariance by variance will give you the beta of individual stock with respect to the benchmark.

As an example, computation of beta of Tesla with respect of S&P 500 would look like this: