To measure the risk of a particular equity, many investors turn to beta. Though plenty of financial sites provide them, what risks are you taking by using one of the betas provided by an outside source? Betas provided for you by online services have unknown variable inputs, which in all likelihood are not adaptive to your unique portfolio. Betas can be calculated in a number of ways, since the variables for input depend on your investment time horizon, your view of what constitutes "the market" and several other factors. This means a customized version is best.

Learn how to calculate your own beta using Microsoft Excel in order to provide a risk measure that's personalized for your individual portfolio.

Provided Betas Vs. Personally Calculated Betas
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 need this measurement to gauge portfolio risk. Long-term investors will certainly want to gauge the risk over a longer time period than a position trader who turns over his or her 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 the ability to gauge the beta's reliability by calculating the coefficient of determination, or as it is better known, the r-squared. This is a powerful tool that can determine how well your beta measures risk. The range of this statistic is zero to one. The closer the r-squared is to one, the more reliable your beta is.

Another unknown factor of pre-made betas is the method used to calculate them. There are two ways to calculate: 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.

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

Preliminary Steps & Calculating Beta
Once you've 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 data. 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 your chosen index as well.

Copy both of the closing price columns into a new spreadsheet. They should be in order from newest to oldest. To obtain the correct format for calculation we must convert these prices into return percentages for both the index and the stock price. To do this, 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 an example showing this in Excel.

Figure 1: 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 do offer 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.

Related Articles
  1. Trading Strategies

    Risk Management Techniques For Active Traders

    Use stop-loss and take-profit points to your advantage with these strategies.
  2. Mutual Funds & ETFs

    The Risks Of Real Estate Sector Funds

    Discover the risks and rewards of investing in real estate funds, as well as some of the best and worst performers.
  3. Forex Education

    Understanding Forex Risk Management

    There's risk in every trade you take, but as long as you can measure risk, you can manage it.
  4. Bonds & Fixed Income

    The Risks Of Mortgage-Backed Securities

    Find out how weighted average life guards against prepayment risk.
  5. Retirement

    The Evolution Of Enterprise Risk Management

    This growing sector can tell you a lot about the companies you are investing in.
  6. Options & Futures

    Common Risks That Can Ruin Your Retirement

    These unexpected bumps can sideline your post-work plans and prevent you from riding out your assets.
  7. Chart Advisor

    Now Could Be The Time To Buy IPOs

    There has been lots of hype around the IPO market lately. We'll take a look at whether now is the time to buy.
  8. Entrepreneurship

    Creating a Risk Management Plan for Your Small Business

    Learn how a complete risk management plan can minimize or eliminate your financial exposure through insurance and prevention solutions.
  9. Investing Basics

    5 Tips For Diversifying Your Portfolio

    A diversified portfolio will protect you in a tough market. Get some solid tips here!
  10. Entrepreneurship

    Identifying And Managing Business Risks

    There are a lot of risks associated with running a business, but there are an equal number of ways to prepare for and manage them.
  1. Are secured personal loans better than unsecured loans?

    Secured loans are better for the borrower than unsecured loans because the loan terms are more agreeable. Often, the interest ... Read Full Answer >>
  2. Which mutual funds made money in 2008?

    Out of the 2,800 mutual funds that Morningstar, Inc., the leading provider of independent investment research in North America, ... Read Full Answer >>
  3. Why are mutual funds subject to market risk?

    Like all securities, mutual funds are subject to market, or systematic, risk. This is because there is no way to predict ... Read Full Answer >>
  4. Why have mutual funds become so popular?

    Mutual funds have become an incredibly popular option for a wide variety of investors. This is primarily due to the automatic ... Read Full Answer >>
  5. Can your car insurance company check your driving record?

    While your auto insurance company cannot pull your full motor vehicle report, or MVR, it does pull a record summary that ... Read Full Answer >>
  6. Is my IRA/Roth IRA FDIC-Insured?

    The Federal Deposit Insurance Corporation, or FDIC, is a government-run agency that provides protection against losses if ... Read Full Answer >>

You May Also Like

Hot Definitions
  1. Turkey

    Slang for an investment that yields disappointing results or turns out worse than expected. Failed business deals, securities ...
  2. Barefoot Pilgrim

    A slang term for an unsophisticated investor who loses all of his or her wealth by trading equities in the stock market. ...
  3. Quick Ratio

    The quick ratio is an indicator of a company’s short-term liquidity. The quick ratio measures a company’s ability to meet ...
  4. Black Tuesday

    October 29, 1929, when the DJIA fell 12% - one of the largest one-day drops in stock market history. More than 16 million ...
  5. Black Monday

    October 19, 1987, when the Dow Jones Industrial Average (DJIA) lost almost 22% in a single day. That event marked the beginning ...
  6. Monetary Policy

    Monetary policy is the actions of a central bank, currency board or other regulatory committee that determine the size and ...
Trading Center