How to Make a CAPM in Excel

How to Make a CAPM in Excel thumbnail
Investing in the stock market is a risky endeavor, no matter how much math you do.

When evaluating stocks, the Capital Asset Pricing Model helps to determine if a particular stock is worth the financial risk. You can run the calculations for the CAPM in an Excel spreadsheet provided you know the variables that make up the CAPM equation. The CAPM equals the risk free rate plus the beta of a stock multiplied by the difference between the expected market return and the risk free rate. Or CAPM = RFR + B * (EMR - RFR).

Instructions

    • 1

      Launch Excel and create a new, blank spreadsheet. Type headings in the first row for the stock symbol, the stock's beta and the CAPM of the stock.

    • 2

      Type in cell A4 "Risk Free Rate" and in cell A6 "Expected Market Return." Cell A5 will hold the RFR value and cell A7 will hold the EMR value.

    • 3

      Type the following formula into cell B3 "=A5+B2(A7-A5)" and press "Enter." The formula won't return any results until you fill in the rest of the details.

    • 4

      Get the beta for the stock by researching it through your favorite stock quote tool. The beta refers to the relative change for that stock as compared to the Standard and Poor's 500. So a beta of one means that the stock is exactly as stable as the S&P 500. A beta below one means the stock is more stable and a beta above one means the stock is more volatile than the rest of the stock market.

    • 5

      Enter the risk free rate based on a guaranteed investment product. For example, you could enter the rate of return on a government savings bond or a guaranteed certificate of deposit.

    • 6

      Enter the expected market average based on what you think the stock market average will be over the term in which you plan on investing. You can compare to past times of equal length or average over larger spans of time. A typical EMR is around 10 percent.

    • 7

      Evaluate the stock based on how much you think it will earn compared to the CAPM. So if the CAPM is 10 percent and you think the stock might be able to earn 12 percent, then it may be worth the risk. However if the CAPM is 29 percent and you don't expect the stock to return more than 25 percent earnings, then the stock is probably not a good risk.

Tips & Warnings

  • Use different EMR and RFR to see the effect on the CAPM.

  • This article does not constitute investment advice.

Related Searches

References

Resources

  • Photo Credit Hemera Technologies/PhotoObjects.net/Getty Images

Related Ads

Featured