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

CAPM: An Overview

Many investors use the capital asset pricing model (CAPM) as a way to estimate the potential return of a stock or other asset within the context of its intrinsic risk. Used primarily to analyze relatively risky investments, CAPM gives the investor a way to evaluate the stock’s potential reward given its potential risk and compare it to other alternative investments.

A Microsoft Excel spreadsheet can be used to calculate CAPM.

Key Takeaways

• CAPM can be used to help you build a portfolio of stocks that have the potential for the reward you seek given the level of risk you can accept.
• CAPM is most often used to evaluate riskier stocks.
• CAPM can be used with other metrics like the Sharpe Ratio when analyzing the risk-reward of multiple assets.

CAPM Formula

CAPM is one component of the efficient market hypothesis, which states that the current prices of assets in a financial market always reflect all of the information available to investors. The logical inference is that investors are unlikely to consistently pick stocks that outperform the wider market.

Calculating CAPM is meant to establish the so-called “efficient frontier.” That is, it helps identify the highest potential return for a given level of risk or the lowest level of risk for a desired level of return.

The formula for CAPM calculates the expected return of an asset (usually a stock) with the incorporation of several factors.

The CAPM formula is as follows:

As shown from the above equation, CAPM involves the risk-free rate of an investment like a U.S. Treasury bond, an asset’s beta, and the expected return of the market.

It is 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 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 ten years. Thus, the expected return of the stock is also calculated over ten years.

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 all change nearly every day and will change more substantially in different market periods and environments or at least on an annual basis.

The CAPM is an important statistic, but 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 asset that will create the best theoretical return for a defined level of risk.

It’s worth noting that CAPM is not always fully realized because any investor striving for a balanced portfolio would not devote the entire portfolio to a single asset.

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 to create an efficient frontier curve and to analyze and compare the expected return of multiple assets or add other comparison metrics.

As you can see, the calculation is built with assumptions at the top that can be adjusted easily when changes are needed. This allows 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 benchmark to use since most beta calculations are based on the S&P 500.

Example of CAPM Calculation

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 this example, there is a big difference between Tesla’s 4.36% and General Motors’ 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.

What Is a Stock’s Beta?

A stock’s beta is a measurement of its risk in relation to the broader stock market. The beta of the S&P 500 is always 1.0. The beta of all other stocks changes almost daily.

The example above, for instance, calculates the CAPM of Tesla and General Motors for comparison. Tesla stock’s actual beta was about 2.51 as of March 23, 2025, and General Motors’ beta was 1.43. By the time you read this, both those numbers may have changed significantly.

How Do I Benefit from Calculating CAPM?

CAPM can help you compare the relative risk of a stock against its potential reward. If used consistently, it can help you construct an efficient portfolio that has the best chance of meeting the level of return you want to achieve.

What Can an Investor Do Using Microsoft Excel?

At its most basic, Microsoft Excel is a place to list your stocks and other investments and track their price movements from day to day. You can get that from any business or broker’s website. Excel has many more functions, however. It can calculate standard deviation, percentage of return, overall profit and loss, and more.

There are alternatives to Microsoft Excel, including Google Sheets and Apple Numbers, depending on your needs.

The Bottom Line

An efficient frontier takes multiple stock investing to the next level by seeking to plot the allocation of multiple stocks in a portfolio.

There are 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.