Calculating Beta: Portfolio Math For The Average Investor
When you are examining a particular equity, an often-used measurement of risk is what we in the finance world call beta. But what risks are you taking by using one of the betas provided by the plethora of online information sources for equities? (For more information on what beta is and how it pertains to risk see these articles: Beta: Know the Risk and Beta: Gauging Price Fluctuations.)
Beta can be calculated in a number of ways, as the variables for input depend on your investment time horizon, your view of what constitutes "the market," and many other factors which we will explore further in this article.
Betas provided for you by online services have unknown variable inputs, which in all likelihood are not adaptive to your unique portfolio. In this article, we will discuss how to calculate beta using Microsoft Excel in order to provide you with a risk measure that's personalized for your portfolio.
Provided Betas Vs. Your Own Calculated Betas
We will begin by looking at the time frame chosen for calculating beta. Provided betas are calculated with time frames unknown to their consumers. This poses a unique problem to end users, who use this measurement to gauge portfolio risk. Long-term investors will certainly want to gauge the risk over a longer than a position trader who turns over his portfolio every few months.
Another problem may be the index used to calculate beta. Most provided betas use the American standard of the S&P 500 Index. If your portfolio contains equities that extend beyond U.S. borders, like a company that is based and operated in China, the S&P 500 may not be the best measure of the market. By calculating your own beta you can adjust for these differences and create a more encompassing view of risk.
One distinct advantage of calculating the beta yourself is that you can gauge its reliability by calculating the coefficient of determination, or as it is better known, the r-squared. This is a powerful tool that can gauge how well your beta measures risk. The range of this statistic is from one to zero. A measurement of one shows perfect correlation and a zero shows there is no correlation. The closer the r-squared is to one, the more reliable your beta is.
Another unknown is what method is being using to calculate the beta. There are two ways of doing this: regression and the capital asset pricing model (CAPM). CAPM is used more commonly in academic finance; investment practitioners more often use the regression technique. This allows for a better explanation of returns pertaining to the market rather than a theoretical explanation of the overall return of an asset, which takes interest rates as well as market returns into account. (For more insight, see The Capital Asset Pricing Model: An Overview.)
Inevitably, along with the previously proclaimed advantages, there are also disadvantages to doing it yourself. The main disadvantage is the time involved. Calculating beta yourself takes longer than just obtaining it through a website, but this time can be significantly cut down by using programs such as Microsoft Excel or Open Office Calc.
The Preliminaries & Calculation
Once you have decided on a time frame that aligns itself with your investment time horizon and have chosen an appropriate index, you can then move on to gathering the data. There are many sources for historical data on the internet. Look for historical prices of each equity to find the appropriate date information matching your chosen time horizon. On some sites, you will have the option to download the information as a spreadsheet. Choose this option and save the spreadsheet. Do the same for our chosen index as well. (To learn more, read Where can I find historical stock/index quotes?)
Copy both of the closing price columns into a new spreadsheet. They should be in order from newest to oldest from top to bottom, respectively. To obtain the correct format for calculation we must convert these prices into return percentages. To do this we just take the price from today minus the price from yesterday and divide the answer by the price of yesterday. The result is the percentage change. Below is a picture showing this in Excel. Figure 1 is of the formulas and Figure 2 is of the result.
The calculation of beta through regression is simply the covariance of the two arrays divided by the variance of the array of the index. The formula is shown below.
One advantage we discussed earlier is the ability to gauge the reliability of your beta. This is done by calculating the r-squared. From here we input the two arrays containing the percentage changes. Below is this formula in Excel.
The Bottom Line
Although calculating your own betas can be time-consuming compared to using service-provided betas, they provide the investor a better look at risk through personalization. In addition, we can also gauge the reliability of this risk measurement by calculating its r-squared. These advantages are a valuable tool to an investment arsenal and should be used by any serious investor. (To learn about other popular risk measures, read our article Introduction to Value at Risk (VAR) and Five Stats That Showcase Risk.)
Beta can be calculated in a number of ways, as the variables for input depend on your investment time horizon, your view of what constitutes "the market," and many other factors which we will explore further in this article.
Betas provided for you by online services have unknown variable inputs, which in all likelihood are not adaptive to your unique portfolio. In this article, we will discuss how to calculate beta using Microsoft Excel in order to provide you with a risk measure that's personalized for your portfolio.
Provided Betas Vs. Your Own Calculated Betas
We will begin by looking at the time frame chosen for calculating beta. Provided betas are calculated with time frames unknown to their consumers. This poses a unique problem to end users, who use this measurement to gauge portfolio risk. Long-term investors will certainly want to gauge the risk over a longer than a position trader who turns over his portfolio every few months.
Another problem may be the index used to calculate beta. Most provided betas use the American standard of the S&P 500 Index. If your portfolio contains equities that extend beyond U.S. borders, like a company that is based and operated in China, the S&P 500 may not be the best measure of the market. By calculating your own beta you can adjust for these differences and create a more encompassing view of risk.
 |
|
| Watch: Understanding Beta |
Another unknown is what method is being using to calculate the beta. There are two ways of doing this: regression and the capital asset pricing model (CAPM). CAPM is used more commonly in academic finance; investment practitioners more often use the regression technique. This allows for a better explanation of returns pertaining to the market rather than a theoretical explanation of the overall return of an asset, which takes interest rates as well as market returns into account. (For more insight, see The Capital Asset Pricing Model: An Overview.)
Inevitably, along with the previously proclaimed advantages, there are also disadvantages to doing it yourself. The main disadvantage is the time involved. Calculating beta yourself takes longer than just obtaining it through a website, but this time can be significantly cut down by using programs such as Microsoft Excel or Open Office Calc.
Once you have decided on a time frame that aligns itself with your investment time horizon and have chosen an appropriate index, you can then move on to gathering the data. There are many sources for historical data on the internet. Look for historical prices of each equity to find the appropriate date information matching your chosen time horizon. On some sites, you will have the option to download the information as a spreadsheet. Choose this option and save the spreadsheet. Do the same for our chosen index as well. (To learn more, read Where can I find historical stock/index quotes?)
Copy both of the closing price columns into a new spreadsheet. They should be in order from newest to oldest from top to bottom, respectively. To obtain the correct format for calculation we must convert these prices into return percentages. To do this we just take the price from today minus the price from yesterday and divide the answer by the price of yesterday. The result is the percentage change. Below is a picture showing this in Excel. Figure 1 is of the formulas and Figure 2 is of the result.
 |
| Figure 1: Formulas |
 |
| Figure 2: Results |
The calculation of beta through regression is simply the covariance of the two arrays divided by the variance of the array of the index. The formula is shown below.
| Beta = COVAR (E2:E99,D2:D99)/VAR(D2:D99) |
One advantage we discussed earlier is the ability to gauge the reliability of your beta. This is done by calculating the r-squared. From here we input the two arrays containing the percentage changes. Below is this formula in Excel.
| R-Squared = RSQ(D2:D99,E2:E99) |
The Bottom Line
Although calculating your own betas can be time-consuming compared to using service-provided betas, they provide the investor a better look at risk through personalization. In addition, we can also gauge the reliability of this risk measurement by calculating its r-squared. These advantages are a valuable tool to an investment arsenal and should be used by any serious investor. (To learn about other popular risk measures, read our article Introduction to Value at Risk (VAR) and Five Stats That Showcase Risk.)
by
Daniel McNulty
Daniel McNulty graduated from Washburn University with a BBA in Finance and Economics with honors. He has long been exposed to the financial markets and deals primarily with portfolio strategy topics. McNulty says he admires the lives and works of Gaius Julius Caesar, Rene Descartes and Marcus Aurelius.
Free Annual Reports
http://sp.fastclick.net/ad/tr/10858-64082-15546-0?mpt=2bfb9513164ba3867008b0dac58deaac
