What Is CAPM Formula in Excel? Using CAPM to Analyze Risk Reward

The capital asset pricing model (CAPM) is a component of the efficient market hypothesis and modern portfolio theory. CAPM measures the amount of an asset's expected return which is the first step in building out an efficient frontier. CAPM itself uses a foundational equation to calculate the expected return of an asset (usually a stock) with the incorporation of several factors.

The formula for calculating the expected return of an asset using the capital asset pricing model is as follows:

As shown from the above equation, CAPM involves the risk-free rate, an asset’s beta, and the expected return of the market. It can be important to ensure that these values are all taken from the same time period. Here we use a 10-year time period.

To calculate an asset's expected return, start with a risk-free rate (the yield on the 10-year Treasury) then add an adjusted premium. The adjusted premium added to the risk-free rate is the difference in the expected market return times the beta of the asset. This formula can be calculated in Microsoft Excel as shown below. 

Understanding the CAPM

CAPM only provides an expected return on the asset in focus. This expected return can be an important value for an investor when considering an investment. Generally, the expected return matches the period of time used to find the expected market return. For example, the market may be expected to return 8% over a period of ten years. Thus, the expected return of the stock is also over a ten-year period.

The CAPM is only an estimate and has several caveats. Mainly, the factors used in the CAPM calculation are not static. The risk-free rate, beta, and market risk premium are all non-static factors that change nearly every day but more substantially will change in different market periods and environments or at least on an annual basis.

The CAPM can be an important statistic to follow, but generally, it is not always best used on its own. That’s why it forms the basis for the efficient market hypothesis and the building of an efficient frontier curve.

Efficient Frontier Curves

An efficient frontier curve involves the integration of multiple assets and all of their expected returns. The efficient frontier uses CAPM to help create an efficient portfolio that tells an investor the optimal percentage of investment in each integrated asset that will create the best theoretical return for a defined level of risk.

In this application, CAPM becomes important for its expected return calculation but that expected return is not always fully realized because a 100% investment in a single asset is not always the most prudent decision given other market investment alternatives as well.

Calculating CAPM in Excel

Now let’s assume you want to find the CAPM of a stock you are interested in investing in. Let’s assume the stock is Tesla (TSLA). First, you want to set up your Excel spreadsheet.

By setting it up in the following format, you leave yourself the opportunity to build it out for creating an efficient frontier curve as well as for simply analyzing and comparing the expected return of multiple assets or for adding other comparison metrics.

As you can see, the calculation is built with assumptions at the top that can be adjusted easily when changes may be made. This creates easy updates to the spreadsheet when assumptions change.

We assume a risk-free rate of 1% on the 10-year Treasury and a market return of 8% on the S&P 500 over 10 years. The S&P 500 is typically the best market return to use since most beta calculations are based on the S&P 500.

Telsa, for Example

We find that Tesla has a beta of 0.48. The table also includes standard deviation which is the next data component needed when building out the efficient frontier.

To find the expected return of Tesla we use the CAPM equation modified for Excel syntax as follows:

• =$C$3+(C9*($C$4-$C$3))

This translates to risk-free plus (beta times the market premium). Using the $ sign helps keep the assumptions static so that you can easily copy the formula to the right for multiple assets.

In this case, we get an expected return of 4.36% for Tesla. With this spreadsheet, we can now build out to the right for multiple assets. Say we want to compare Tesla to General Motors (GM). We can simply copy the formula in C10 to the right in D10. Then all we need to do is add in the beta for GM in cell D9. We find a beta of 1.30 which gives us an expected return of 10.10%.

Investment Analysis

As shown by the comparison of these two stocks, there is a pretty big difference between 4.36% and 10.10%. This mostly comes from the higher beta for General Motors vs. Tesla. Broadly, this means that an investor is more highly compensated through return for taking on more risk than the market. Thus, the expected return values are generally best viewed alongside beta as a measure of risk.

An efficient frontier takes multiple stock investing to the next level by seeking to plot the allocation of multiple stocks in a portfolio. There can also be other metrics like the Sharpe Ratio which can be more easily used to help an investor gauge the risk-reward of one stock vs. another.