CAPM Calculation in Excel: A Step-by-Step Guide

Excel makes CAPM calculations easy.

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.

Step 1

Open Microsoft Excel.

Step 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.

Step 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.

Step 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.

Step 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.

Step 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.