In this article, I will show you how to calculate and interpret the capital asset pricing model (CAPM). The CAPM is often used to calculate the cost of equity (r_e), which is common practice when calculating the Weighted Average Cost of Capital (WACC) for company projects or for stock market investors attempting to estimate the discount rate of a company for valuation purposes. The CAPM is also used in portfolio management/theory to decide whether a stock should be included in a diversified portfolio or not.

Investors should seek to thoroughly understand the CAPM given its place in modern financial theory and its application with calculating the cost of equity of a stock investment based on its risk-reward relationship with the market. Despite the importance of the CAPM and its theoretical representation of the behavior of stocks and the market, the model is limited in its real-world applications (as later discussed). However, the CAPM is still important to understand as the model and the finance theory behind the model are included in more recent and sophisticated multi-factor models that better estimate expected returns of stock market investments.

Capital Asset Pricing Model (CAPM) Explained

The capital asset pricing model (CAPM) is an investment theory and model of equity valuation that was proposed by William Sharpe (1964), John Litner (1965), Jack Treynor (1961, 1962), and Jan Mossin (1966), and builds on the "model of portfolio choice" created by Harry Markowitz (1959). The CAPM was proposed by its founders to better explain the relationship between the expected return of a stock market investment and market risk.

The CAPM formula is below:

R_e = R_f + β*(R_m - R_f)

where:

• R_e = cost of equity
• R_f = risk-free rate
• β = beta
• R_m = expected market return

According to the CAPM, the expected return that you can generate from a stock investment depends on the risk-free rate (R_f) and the market risk premium (R_m - R_f), which is scaled by a factor called "beta" (β). This concept is made clear in the CAPM chart illustration below:

As the CAPM chart above shows, the expected return E(R_i) is on the vertical y-axis and the beta (β) on the horizontal x-axis. According to the CAPM model, SML stands for "security market line" and describes the relationship between beta and the fair value expected return for any stock. This chart can also be used to determine whether a stock is mispriced by the market, as later demonstrated with Home Depot (HD), the world's largest home improvement specialty retailer.

There are only three inputs that go into the CAPM calculation. Below are the steps on how to find the CAPM for Home Depot:

Step #1: Risk-Free Rate

To find the CAPM (aka cost of equity), begin by finding the risk-free rate (R_f), which is the theoretical rate of return received on a zero-risk investment.

The 10-year Treasury Note from the U.S. government is often used as a proxy for the risk-free rate. This rate is used because the U.S. government has never defaulted on its debt obligations, largely due to the drastic consequences it would have on the economy and serving the American people. Therefore, the probability of the U.S. government defaulting and not being able to pay an annual return for the next 10 years is next to none.

The 10-year U.S. Treasury rate can be found on FINRA. You can also reference the live chart below to see the up-to-date 10-year U.S. Treasury rate:

As you can see, as of writing, the 10-year U.S. Treasury rate is 3.32%.

Step #2: Beta

Next, find beta (β), which is a measure of systematic risk (aka undiversifiable or market risk) of a security or portfolio compared to the overall market. Systematic risk refers to market risk that portfolio diversification cannot reduce (e.g., recessions, interest rates, inflation, etc.), while unsystematic risk refers to company or industry specific risk (e.g., financial obligation, management, or location) that can be reduced through portfolio diversification. Beta, as a measure of systematic risk, quantifies the volatility of stock movements when the market (i.e., the S&P 500 Index) moves up or down.

For most publicly traded companies, you can use a site like Yahoo Finance to find their beta. As of writing, Home Depot's beta (5-yr monthly) is 0.94.

Beta can also be found using the formula below:

β = Covariance (R_e, R_m) / Variance (R_m)

where:

• β = beta
• R_e = return on individual stock
• R_m = return on overall market
• Covariance = stock's return relative to the overall market
• Variance = how the market moves relative to its mean

Based on this formula, the greater the standard deviation of the stock, the higher the beta will be, given that more stock volatility relative to the portfolio means a higher beta.

In the Excel spreadsheet linked below, I demonstrate how I derived a beta of 1.01 for Home Depot, utilizing 5-year daily adjusted close data from Yahoo Finance for both Home Depot (HD) and the S&P 500 Index. This figure differs from the one provided by Yahoo Finance, as it's calculated using daily pricing data, whereas Yahoo Finance uses monthly data:

Note that the trendline's formula slope in the chart above (data from the spreadsheet above) is equal to the calculated beta. Therefore, you can also use the slope equation in Excel to calculate beta, as the spreadsheet linked above shows.

Now that you have your beta number or calculation completed, it's useful to be able to understand what it really means. In short, beta can be interpreted in four different ways:

• Beta > 1.0: Stock offers higher volatility and higher expected returns than the market.
• Beta = 1.0: Stock has the exact same volatility and expected returns of the market.
• Beta < 1.0: Stock offers lower volatility and lower expected returns than the market.
• Beta < 0: Negative beta means the asset correlates negatively to the given market (e.g., with gold stocks/ETFs).

Note that when you calculate CAPM for different publicly traded companies, the only difference is their individual beta relative to the market. Given the calculations are being done on the same day, all other variable inputs to calculate the CAPM (R_m and R_f) will be the same.

Step #3: Expected Return of the Market

Finally, we can find the expected return of the market (R_m). For this, you can follow one of the three methods:

• Use analyst estimates of long-term annual market returns.
• Use the historical average annual market returns (e.g., over a long-term period such as 10+ years).
• Use the historical compound annual growth rate (CAGR) market returns (e.g., over a long-term period such as 10+ years).

I prefer using the historical CAGR method for the expected market returns, given the ambiguity of simple arithmetic mean calculations and analyst forecasts.

From 1971 to 2021 (over the last 50 years), the CAGR of the market has been 11.24%. Therefore, we'll use this number in our CAPM calculation as the expected return of the market (R_m). You can also just be conservative and use 10% as the expected return of the market, which is roughly the average annualized historical return.

Step #4: Calculate and Interpret the CAPM

Now that we have all the inputs required to calculate the CAPM, we can complete the calculation for Home Depot. This is shown below:

HD R_e = 3.32% + 1.01*(11.24% - 3.32%) --> 11.32%

As you can see, Home Depot's CAPM is 11.32%. Again, according to the CAPM, this is the expected return a stock market investor would currently receive for investing in Home Depot stock.

We can analyze Home Depot's stock by comparing the theoretical return calculated by the CAPM to its actual historical average annual return. Currently, the 10-year historical average annualized return of Home Depot stands at 22.28%, a figure roughly twice as high as our CAPM calculation of 11.32%.

This disparity suggests, from a CAPM perspective, that Home Depot's stock might have been undervalued historically. The reason is that the actual return has significantly exceeded the expected return estimated by CAPM, which is based on Home Depot's beta of 1.01. In the context of the Security Market Line (SML), this would place Home Depot's stock above the line, indicating past performance that surpassed the expected return for its given level of market risk.

However, it's crucial to note that while this analysis points to historical performance, it does not necessarily imply that Home Depot's stock is currently undervalued. The CAPM is a forward-looking measure, and factors like changing market conditions and investor expectations can alter a stock's valuation over time. Therefore, while the historical data is useful, it should be interpreted with an understanding of these dynamics when assessing current valuation.

How to Calculate and Interpret the CAPM

This is an optional but highly-recommended section that covers the setup and steps you'll need to take to calculate CAPM on Excel. Previously, you saw how to go about calculating beta in Excel (with the spreadsheet linked in the beta section of this article). However, now we'll go step-by-step on calculating this beta number and also all the other inputs to be able to calculate and better interpret the CAPM on Excel.

You can follow along by downloading this completed CAPM exercise in the Excel spreadsheet linked below for Home Depot:

Step #1: Setup, Download, and Organize the Data

The first step in calculating the CAPM on Excel is to setup, download, and organize the data to perform our calculation and analysis. These steps are provided below:

1. To perform any type of regression analysis, you must install the "Data Analysis" Excel ToolPak add-in program. Follow these steps to install the add-in on Excel: Open Excel --> File --> Options --> Add-ins --> Go --> Analysis ToolPak --> OK. Restart your Excel if the "Data Analysis" button/text doesn't initially show up under the "Data" tab on the Excel ribbon.
2. Navigate to Yahoo Finance or some other data aggregator website for historical stock market financial data.
3. Download the monthly, weekly, or daily adjusted close price data for the market (S&P 500) and your selected company. I typically use a 5-year period.
4. Combine the adjusted close price data over the 5-year period from the S&P 500 and your selected company into one spreadsheet tab, then sort it by ascending order (meaning oldest dates come first). At this point, you should have a date column and two additional columns for the closing prices for the S&P and your selected company.
5. Calculate the monthly, weekly, or daily return percentage for both the S&P 500 and your selected company in two separate columns. This formula is simply: [(new date's adjusted close price / previous date's adjusted close price) - 1]. Drag/copy this percentage return calculation to the bottom of your data set.
6. Navigate to the "Daily Treasury Par Yield Curve Rates" page on the U.S. Department of Treasury website, where rates on the risk-free rate are regularly updated.
7. Select the appropriate time period (e.g., 5-years) and download the CSV file. Copy and match the "10 Yr" column rates and dates to the current spreadsheet tab where all other data is currently located. This is easiest to accomplish if you're already using daily adjusted close price data.

Step #2: Calculate the Risk-Free Rate

After completing step #7 in the list above, the risk-free rate is rather simple to calculate:

1. Use the =AVERAGE formula in Excel on the column for the 10-year daily treasury rates over the appropriate period.
2. You can also just use today's rate on the U.S. 10-Year Treasury Note and skip this step altogether.

Step #3: Calculate Beta

As previously shown, you can calculate beta by using the standard beta formula (from covariance and variance) or by using the slope formula in Excel. It's also an output of any regression analysis.

1. Use the =COVARIANCE.P Excel formula on the percentage return calculation columns you just made for your selected company and the S&P 500.
2. Use the =VAR.P Excel formula on the percentage return calculation column for the S&P 500.
3. Divide the covariance calculation by the variance calculation to get beta.
4. You can also find beta by using the =SLOPE formula on the percentage return calculation columns for your selected company and the S&P 500.

Step #4: Calculate Expected Return of the Market

The expected return of the market is simply an average calculation of the percentage returns of the market over the appropriate period. However, this percentage (whether it be on a monthly, weekly, or daily closing price data set) must be converted into an annual/yearly percentage in order to be used in the CAPM calculation, as described below:

1. The expected return of the market is just the average of the percentage return calculation column for the S&P 500, using the =AVERAGE Excel formula.
2. If you have daily closing price data, use this formula to convert the result to an annual percentage: r_yearly = (1 + r_daily)²⁵² - 1. Note that you should use 252 instead of 365 because there 252 open trading days in the year.
3. If you have weekly closing price data, use this formula to convert the result to an annual percentage: r_yearly = (1 + r_weekly)⁵² - 1
4. If you have monthly closing price data, use this formula to convert the result to an annual percentage: r_yearly = (1 + r_month)¹² - 1

Step #5: Calculate the CAPM

For this step, you'd just calculate the CAPM normally using the same formula. It's provided below again for your convenience:

R_e = R_f + β*(R_m - R_f)

For our Home Depot example, given the difference in time periods and the slight differences in beta, the risk-free rate, and the expected market return, the CAPM for Home Depot is now 13.27%.

Step #6: Calculate the Excess Returns

To better interpret the CAPM, you can choose to calculate the excess returns of your selected company and the S&P 500. This is to find "alpha," the abnormal rate of return on a security or portfolio that exceeds what the CAPM predicted or expected in its valuation model:

1. Create a new column on Excel and subtract the risk-free rate column from the percentage return calculation column for your selected company.
2. Create a new column on Excel and subtract the risk-free rate column from the percentage return calculation column for the S&P 500.
3. Use the =INTERCEPT Excel formula and select both of these new columns to find alpha. To interpret this alpha, we can create a regression analysis, as discussed below.

Step #7: Create a Regression Analysis

Performing a regression analysis will help you to understand whether a certain variable is statistically significant or useful in its current application. This is not something that can be accomplished through the formulas we've reviewed in the previous steps:

1. Assuming you have the "Data Analysis" Excel ToolPak installed, navigate to Data --> Data Analysis --> Regression --> OK.
2. "Input Y Range" is the dependent variable. This will be the excess returns column for your selected company.
3. "Input X Range" is the independent variable. This will be the excess returns column for the S&P 500 Index.
4. Select "Labels" if your column selections has labels in them and choose an output location for your regression. Click on the "OK" button.

After this regression is completed, a summary output similar to the one shown below should result (without the formatting):

As you can see, the CAPM beta of this regression is 1.0131 and the intercept number of 0.0393% is the alpha. If the p-value for alpha or beta is greater than 5%, then the value you've computed for alpha or beta is not statistically significant (not useful). However, if the p-value is less than 5%, then the regression output is statistically significant (useful) for alpha and/or beta.

With our Home Depot example, our alpha is clearly not significant whereas our beta is significant. Ultimately, this means that by purchasing Home Depot stock at its current price and level of beta (which is practically the same as the market), there's not any significant excess return an investor should expect that the CAPM model does not explain.

CAPM Downsides and Assumptions

Many investors calculate the CAPM for their cost of equity because it considers the volatility of the stock (beta) and the interest rate environment from the risk-free rate (the U.S. 10-year Treasury rate). The CAPM formula also adds a market risk premium over the risk-free rate, as equity shareholders bear more risk than debt holders, and therefore require a higher rate of return than debt holders.

To elaborate, with the CAPM, the market risk premium (MRP or R_m - R_f) has a direct impact on the expected return of a stock (R_e), which is dependent on the value of beta (β). Clearly, the CAPM has one risk-factor, beta. As beta increases, your expected return goes up, and vice versa. Therefore, according to the CAPM model, the only factor that is affecting your return is market risk, not firm-specific risk because portfolio's can be diversified.

This is where the CAPM falls short, as its only risk factor is beta, which is also just a measure of market volatility related risk, not total risk. As previously stated, the CAPM also comes with a number of assumptions, some more realistic than others. You can see these assumptions in the table below:

Clearly, the assumptions the CAPM comes with are another downside to the CAPM and its utility for investors.

In summary, the CAPM is a good starting point for understanding the cost of equity of a potential investment opportunity from a retail investment perspective, but because of the assumptions the model carries and because beta is the only risk factor, it lacks the ability to accurately capture and assess the risk-reward relationship of stock investments.

The Bottom Line

The capital asset pricing model (CAPM) is an investment theory and equity valuation model that was proposed to explain the relationship between the expected return on stock market investments and market risk. The model only has three inputs to calculate the cost of equity for an equity investment: the risk-free rate, the expected market return, and beta.

Given the U.S. government's solvency, the U.S 10-year Treasury Note is typically used as a proxy for the risk-free rate. The expected market return is just the average annualized returns of the S&P 500, which is about 10%. As a form of systematic risk, beta measures the volatility of stock movements relative to the market when it moves up or down. The market's beta is 1.0, and any CAPM beta greater than (less than) 1.0 means the equity investment offers higher (lower) volatility and higher (lower) expected returns than the market.

The CAPM leaves a lot to be desired in real-world application for investors, given its questionable assumptions and reliance on beta as a form of risk -- let alone its only form of risk. To better evaluate and explain returns in the stock market, more sophisticated investors should turn to multi-factor models such as the Arbitrage Pricing Theory (APT) model or the Fama and French Multifactor Models. Ultimately, these models add additional risk factors that help to better estimate expected returns of stock market investment opportunities, which can then be utilized in building strategic long-term portfolios.