How to Calculate CAPM in Excel

Updated March 23, 2017

The Capital Access Pricing Model, or CAPM, allows investors to access 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.

Open Microsoft Excel.

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 per cent annual interest, you would enter "0.03" in cell A1.

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.

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 per cent for over 17 years, so you would enter "8.1" in cell A3.

Enter "=B1+(B2*(B3-B1))" in cell A4, which will return the CAPM for your investment. In the example, this results in a CAPM of 0.132, or 13.2 per cent.

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

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author