The Capital Asset Pricing Model (CAPM) is a financial model that helps to determine the expected return on an investment based on its risk. Here’s how you can calculate CAPM in Microsoft Excel.

Definition of CAPM

CAPM or Capital Asset Pricing Model helps to calculate the cost of equity for an investment. Using the CAPM formula we can find the expected return for an asset. It can further be used for financial ratio like – Sharpe Ratio and others.

The CAPM states that the expected return of an asset is equal to the risk-free rate of return plus a risk premium, which is proportional to the asset’s beta. The beta coefficient measures the volatility of an asset’s returns relative to the overall market. An asset with a beta greater than 1 is considered more risky than the market, while an asset with a beta less than 1 is considered less risky.

The CAPM is widely used by investment professionals and financial analysts as a tool for calculating the required rate of return on an investment, as well as for asset pricing and portfolio optimization. However, it has also been criticized for its oversimplifications and assumptions, and it is important to consider its limitations when using it for investment decision-making.

CAPM Formula with its components

E_r = R_f + [B_i x (R_m – R_rf)]

• E_r = expected return of investment
• R_f = risk free rate
• B_i – beta of investment
• R_m = Expected return on the market
• R_m – R_rf – market risk premium

Step 1: Input the values which act as input for the CAPM Model. The details of the input will be available online as mentioned in the sources.

Start by inputting data into your worksheet for the risk-free rate (the return on a low-risk investment, such as a Treasury bond), the expected return on the market, and the beta (a measure of risk) for the stock you want to analyze.

The next step is to calculate the average return of the overall market, also known as the market return. You can use data from a market index, such as the S&P 500, to calculate the market return. To do this, you’ll need to gather data on the closing prices of the market index over a period of time, such as the past year. You can then calculate the average return using the following formula in Excel:

=AVERAGE((End of Period Price – Start of Period Price) / Start of Period Price)

For example, if you have data on the closing prices of the S&P 500 for the past year, you would use this formula to calculate the average return:

=AVERAGE((B2:B253 – B1:B252) / B1:B252)

Step 2. Calculate Expected Return using the CAPM Formula.

Expected Return: Risk Free Rate + Beta *(Expected Market Return – Risk Free rate)

Expected return = 8,7%

Plot the results. To visualize the relationship between beta and expected return, you can plot the data in a scatter chart. Select the two columns of data (one for beta, one for expected return), go to the “Insert” tab, select “Scatter” (or “Scatter with only Markers”), and choose the first option.

It’s important to keep in mind that the CAPM is just a model, and it may not always accurately predict the actual return on an investment. There are other factors that can impact investment returns, such as market conditions, company-specific risks, and macroeconomic events.

CAPM with Country Risk premium(CRP) – For Foreign Investments

Country Risk premium is the addition return that an investor can expect if he is investing in a foreign company.

When to use CRP?

• If the investment is done in foreign land, overseas investment
• If there is higher political and geographical risk associated with this risk
• CRP for developing / turbulent countries is higher than developed countries. The higher the risk of a country the higher the value of CRP.

Formula:

Expected Return: Risk Free Rate + Beta *(Expected Market Return – Risk Free rate) + Country Risk Premium

Expected return = 24,7%

Feel free to use the CAPM calculator for your own needs.

Related posts:

How to calculate ROI in Excel How to calculate ROA? How to calculate COGS?