How to Calculate CAPM in Excel

The Capital Access Pricing Model, or CAPM, allows investors to assess the risk of a stock to decide if the expected gain is worth the risk of the investment. This formula takes into account the volatility, or Beta value, of a potential investment, and compares it with the overall market return and an alternative "safe bet" investment. The resulting CAPM gives you the expected rate of return, which the potential investment must exceed to be worth the risk.

Instructions

    • 1

      Open Microsoft Excel.

    • 2

      Enter the alternative "risk free" investment in cell A1. This could be a savings account, government bond or other guaranteed investment. As an example, If you had a risk-free savings account that yielded 3 percent annual interest, you would enter ".03" in cell A1.

    • 3

      Enter the stock's beta value in cell A2. This beta value gives you an idea of the volatility of the stock. The overall stock market has a beta value of one, so the individual stock's beta value determines the volatility compared to the overall market. As an example, a beta value of one half is half as risky as the overall market, but a beta value of two is twice as risky. The beta values are listed in numerous financial websites, or can be obtained through your investment broker. In the example, If your stock had a beta value of two, then you would enter "2.0" in cell A2.

    • 4

      Enter the expected market return for a broad indicator, such as the S&P 500, in cell A3. In the example, the S&P 500 has yielded investors an average of 8.1 percent for over 17 years, so you would enter ".081" in cell A3.

    • 5

      Solve for the asset return using the CAPM formula: Risk-free rate + (beta(market return-risk-free rate). Enter this into your spreadsheet in cell A4 as "=A1+(A2(A3-A1))" to calculate the expected return for your investment. In the example, this results in a CAPM of 0.132, or 13.2 percent.

    • 6

      Compare the CAPM with the stock's expected rate of return. If your investment broker tells you the stock is expected to gain 15 percent annually, then it is worth the risk, because 15 percent is larger than the 13.2 percent threshold. However, if the expected return was just 9 percent, it would not be worth the risk, because the rate of return is considerably less than the threshold CAPM value.

Related Searches

References

Resources

  • Photo Credit Stockbyte/Stockbyte/Getty Images

You May Also Like

  • How to Calculate CAPM

    How to Calculate CAPM in Excel. The Capital Access Pricing Model, or CAPM, ... Capital asset pricing model (CAPM) and arbitrage pricing...

  • How to Calculate Beta in Excel

    How to Calculate Beta in Excel. Beta measures the volatility of a company security against a benchmark, such as the S&P 500....

  • How to Calculate CAPM in Beta Stocks

    How to Make a CAPM in Excel; How to Use CAPM to Calculate a Correlation Coefficient; Print this article; Instructions. 1. ......

  • How to Calculate the Cost of Equity Using CAPM

    ... (CAPM). The formula for calculating the cost of equity using CAPM is the risk-free rate plus ... The Capital Asset Pricing...

  • How to Calculate CAPM

    The capital asset pricing model depicts the, the concept of investment risk of a security with the expected return of that security....

  • Models of CAPM Calculation

    The Capital Asset Pricing Model (CAPM) ... How to Calculate CAPM in Excel; Practical Use of a CAPM; CAPM Explained; The Best...

  • How to Calculate CAPM Alpha

    The Capital Asset Pricing Model (CAPM) is a method for pricing risky assets such as publicly traded stocks. ... How to Calculate...

  • CAPM Vs. DDM

    Capital asset pricing model (CAPM) and arbitrage pricing theory ... How to Make a CAPM in Excel; CAPM Market Risks; Factors of...

  • How to Plot Relative Risk in Microsoft Excel

    How to Plot Relative Risk in Microsoft Excel. To plot relative risk, you need two sets of figures from your relative risk...

  • The Significance of CAPM

    Finance professionals want to know the rate of return they will earn on stock prior to investing their money. Many formulas exist...

  • How to Calculate the CAPM of a Market Premium

    The Capital Asset Pricing Model, or CAPM, is a tool that is used to estimate the return of a capital asset given...

  • How to Calculate Beta Risk

    The best way to calculate beta is with Excel or a similar spreadsheet application using the ... Beta Financial Risk. ... large...

  • CAPM Explained

    Capital Asset Pricing Model ... (CAPM) is a formula which tries to relate the risk/return trade-off with market returns. ... How to...

  • Practical Use of a CAPM

    Capital asset pricing model (CAPM) and arbitrage pricing theory ... How to Calculate CAPM in Excel; CAPM Explained; What Is the Formula...

Related Ads

Check It Out

Make-at-Home Vs. Takeout: Pumpkin Pie