The uncertainty of share market tells, “No matter! how much one spread his/her wings, it is impossible to avoid all risk.” But, as investors, we would all love to find out the rate of return on any risky asset. This rate of return helps many investors to see if the stocks they owned are overvalued or undervalued. Which is why we will discuss the Capital Asset Pricing Model (CAPM) which actually was introduced by Nobel laureate in Economics and author of “Portfolio Theory and Capital Market” William F. Sharpe who introduced this model with the idea of two certain types of risks in investing in stock market 1. systematic risks, and 2. specific risks. Interest rates and recessions are best to describe systematic risks and specific risks which not exactly relate to the market moves and can be diversified away. That explains the improved Capital Asset Pricing Model (CAPM) to compute the systematic risks of the individual investments.

**Capital Asset Pricing Model (CAPM)**

In Equation,

ra = rrf + Ba (rm-rrf)

where,

rrf = Risk-free rate

Ba = Beta of the asset or security

rm = Expected Market Return

The difference value of expected return and the risk-free rate is the equity risk premium.

**Beta of the Asset**

In Capital Asset Pricing Model, Equity Risk Premium is multiplied by Beta coefficient. This Beta coefficient indicates the measure of stock’s risk. If the share price moves with the market at the same rate then the Beta value will be considered 1. But, if its value is 2 then the stock would rise by 20 percent if the market rose by 10 percent and would fall with the same percentage.

Now, that you know about the formula to calculate the CAPM, you can use it to get the rate of return on risky asset. The best part is, to do the calculation, you don’t need some special software to do that. On the contrary, you can do the calculation of CAPM in your Microsoft Excel.

**How to Calculate CAPM in Microsoft Excel?**

To understand it clearly, let’s assume there are two companies Tata Motors and Mahindra Ltd. which both are automobile companies. You want to compare the expected returns on b/w these two public companies. Tata has a Beta value of 2.6, while Mahindra has of 1.3. If we assume the risk-free rate is 0.50 percent and expected market return is 12 percent for the year.

Now, we are going to put risk-free rate in cell A2 whereas Beta and Expected Market Return is in cell A3 and A4 respectively. Expected Asset Return can be placed in A5 cell. After that enter “0.50%” into cell B2, “=2.6” into cell B3 and “12%” into cell B4.

After placing all values in the different cells, we can calculate the expected return of the Tata Motors for the year by putting the CAPM formula in Cell B5, enter “=B2+B3*(B4-B2)”. Similarly, to do the calculation of Mahindra Ltd. by putting a Beta value, risk-free rate, and expected a market return in the specific cells in excel.

**Final Thoughts: –**

CAPM is a quite reliable and most often used to determine the rate of return on risky asset. After determining the rate we can easily compare it to the market price. CAPM may not look perfect but it is idle to determine what return we deserve for putting our hard earned money at risk.

## Leave a Reply

4 Comments on "How to Calculate CAPM to Check your ROI in Stock Market?"

The Best Explanation I’ve ever seen. Your post gives a clear view on how to calculate CAPM in excels. It is very easy to follow.

The Best Explanation I’ve ever seen. Your post gives a clear view on how to calculate CAPM in excels. It is very easy to follow.

Thank you for sharing this. I’ve been looking for a way to calculate capm to find out the rate of return. Now I can simply do it in the excel sheet.

I truly appreciate this blog, excellent explanation for beginners about the CAPM calculations.