Calculating Beta in Excel: Portfolio Mathematics for Ordinary Investors

In order to measure the risk of a particular stock, many investors turn to Beta. Although many financial websites provide them, what risk do you take if you use one of the beta versions provided by external sources? The Beta provided by the online service has unknown variable inputs, which may not be suitable for your unique investment portfolio. Beta can be calculated in many ways, because the input variables depend on the time frame of your investment, your perception of the composition of the “market”, and several other factors. This means that the customized version is the best.

Learn how to use Microsoft Excel to calculate your own Beta to provide a personalized risk measurement for your personal investment portfolio.

Key points

  • Beta is a measure of the relative risk of a particular stock to the broader stock market.
  • Beta studied the correlation of price changes between stocks and the S&P 500 index.
  • Beta can be calculated using Excel so that you can determine the risk of the stock yourself.

Provide Beta version comparison. Calculated Beta

First look at the time range selected for calculating Beta. The provided beta is calculated in a time frame unknown to the consumer. This presents a unique problem for end users, who need this measurement to measure portfolio risk. Compared with position traders who change their portfolios every few months, long-term investors definitely want to measure risk over a longer period of time.

Another problem may be the exponent used to calculate beta. Most of the betas provided use the U.S. standard of the S&P 500 index. If your portfolio contains stocks that extend beyond the borders of the United States, such as a company operating and operating in China, the S&P 500 index may not be the best indicator of the market. By calculating your own Beta, you can adjust for these differences and create a more comprehensive risk view.

One obvious advantage of calculating Beta yourself is the ability to measure the reliability of Beta by calculating the coefficient of determination or the well-known r-squared. This is a powerful tool that can determine how much risk your Beta version measures. The range of this statistic is zero to one. The closer the r-squared is to 1, the more reliable your beta is.

Another unknown factor in pre-made betas is the method used to calculate them. There are two calculation methods: regression and capital asset pricing model (CAPM). CAPM is more commonly used in academic finance; investment practitioners use regression techniques more. This can better explain market-related returns, rather than a theoretical explanation of the overall return of assets, which takes into account interest rates and market returns.

Inevitably, there are disadvantages to doing it yourself. The main issue is the time involved. Calculating the beta yourself takes longer than calculating through the website, but using programs such as Microsoft Excel or Open Office Calc can significantly reduce the time.

Preliminary steps and calculation Beta

Once you have decided on a time frame that matches your investment time frame and selected the appropriate index, you can continue to collect data. Look up the historical price of each stock to find the appropriate date information that matches the time range you selected. On some websites, you can choose to download the information as a spreadsheet. Select this option and save the spreadsheet. Do the same for the index of your choice.

Copy the two closing price columns into a new spreadsheet. They should be in order from newest to oldest. In order to obtain the correct calculation format, we must convert these prices into index and stock price returns. To do this, simply subtract yesterday’s price from today’s price, and then divide the answer by yesterday’s price. The result is a percentage change. Below is an example showing this in Excel.

Figure 1: Results

Calculating beta by regression is simply the covariance of the two arrays divided by the variance of the indexed array. The formula is shown below.

Beta = COVAR(E2:E99,D2:D99)/VAR(D2:D99)

One of the advantages we discussed earlier is the ability to measure the reliability of your Beta version. This is done by calculating r squared. From here we enter two arrays containing percentage changes. Below is this formula in Excel.

R-square = RSQ(D2:D99,E2:E99)

Bottom line

Although calculating your own Beta may be more time-consuming compared to using the Beta provided by the service, they do provide better risk observations through personalization. In addition, we can also measure the reliability of this risk measure by calculating its r-squared. These advantages are valuable tools for investment tools and should be used by any serious investor.


Share your love