What is the formula for calculating CAPM in Excel?

The Capital Asset Pricing Model (CAPM) is a component of the efficient market hypothesis and modern portfolio theory. CAPM measures the amount of return expected from an asset which is the first step in constructing an efficient frontier. CAPM itself uses a fundamental 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 on an asset using the fixed asset valuation model is as follows:

As the above equation shows, CAPM involves the risk-free rate, the beta of an asset, and the expected market return. It may be important to ensure that these values ​​are all from the same time period. Here we use a period of 10 years.

To calculate the expected return of an asset, start from a risk-free rate (the return on 10-year cash flow) and then add an adjusted premium. The adjusted premium added to the risk-free rate is the difference between the expected market return multiplied by the beta of the asset. This formula can be calculated in Microsoft Excel as shown below.

Understanding CAPM

The CAPM only provides an expected return on the targeted asset. This expected return can be an important value for an investor when considering an investment. Generally, the expected return is the time period used to find the expected market return. For example, the market can be expected to return 8% over a ten-year period. Thus, the expected return of the Stock is also over a period of 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 ratebeta and market risk premium are all non-static factors that change almost daily, but will change more dramatically over time and in market environments or at least on an annual basis.

CAPM can be an important stat to track, but generally it’s not always best to use it alone. This is why it forms the basis of the efficient market hypothesis and the construction of an efficient frontier curve.

Effective boundary curves

An efficient frontier curve involves integrating 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 embedded 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 this expected return is not always fully realized because a 100% investment in a single asset is not always the most prudent decision considering other alternatives. market investment.

CAPM calculation in Excel

Now suppose you want to find the CAPM of a stock you want to invest in. Suppose the action is You’re here. First, you want to set up your Excel spreadsheet.

By configuring it in the following format, you allow yourself the flexibility to construct it to create an effective frontier curve as well as to simply analyze and compare the expected return of multiple assets or to add other comparison metrics.

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

We assume a 1% risk-free rate on the 10-year Treasury and an 8% market return on the 10-year S&P 500. The S&P500 is generally 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 the standard deviation which is the next data component needed when constructing the efficient frontier.

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

This translates into a more risk-free (beta multiplied by the market premium). By using the $ sign helps keep assumptions static so 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 to the right for multiple assets. Let’s say we want to compare Tesla to General Engines. We can simply copy the formula in C10 on the right in D10. Then all we need to do is add the GM beta in cell D9. We find a beta of 1.30 which gives us an expected return of 10.10%.

Investment analysis

As the comparison of these two stocks shows, there is quite a big difference between 4.36% and 10.10%. It comes mainly from the highest beta for General Motors against Tesla. Basically, this means that an investor is better compensated through returns for taking on more risk than the market. Thus, expected return values ​​are generally best viewed with beta as a measure of risk.

A efficient frontier takes multi-stock investing to the next level by seeking to plot the allocation of multiple stocks in a portfolio. There may also be other measures like the Sharpe ratio that can be more easily used to help an investor assess the risk-reward ratio of one stock versus another.