How to calculate CAPM (capital assets pricing model) in Excel sheet
So, in order to calculate CAPM, you need to use the formula in excel;
=(Risk-free rate of interest/return (Rf) + Beta (β) x (Expected return of market E(Rm) – Risk-free rate of interest/return (Rf))
You can see in the excel sheet example
Where;
E4 = Risk-free rate of interest/return (Rf)
E6 = Beta (β)
E8 = Expected return of market E(Rm)
In this case, the formula is used to calculate CAPM Excel as follows:
=(E4+E8*(E6-E4))
Definition of CAPM
CAPM or capital assets pricing model recognizes the relationship between expected return and risk to evaluate financial security. Thus, the capital assets pricing model determined the expected return on financial security investment. if the expected return is higher than the required rate; financial security is accepted otherwise rejected.
This article will identify and use the CAPM Calculator Excel as an example and template in simple steps.
Capital Asset Pricing Model (CAPM) Formula/Equation
Where
ER = Expected return on a financial security investment
Rrf = Risk-free rate of return
β = Investment beta
Rm = Expected return on a market
(Rm – Rrf) = Market risk premium
Firstly, you need to put desired data into the columns such as Risk-free rate of interest/return (Rf); Beta (β); Expected return of market E(Rm) to calculate the CAPM.
Enter the desired values into the respective fields as shown in the above example.
Download the template file of the CAPM Excel Calculator
CAPM assumptions
There are some basic assumptions of CAPM:
- The security financial market is highly efficient, sophisticated, and competitive, where informed sellers and buyers function.
- Markets are generally considered risk-averse.
- A higher expected return would be required by the investor in terms of risk premium to take additional risk.
- It is assumed that there is no cost of trading, regulations, or taxes.
What is the difference between the cost of capital, WACC, and CAPM cost of equity
The cost of equity capital is calculated with the CAPM formula. The WACC equation highlights the value of the cost of equity plus the cost of debt securities. Therefore, CAPM calculates the cost of equity capital which is essential to measure the value of WACC.
CAPM has a very important role to play in selecting the portfolio investment, which will be used to calculate the cost of equity, while the CAPM is used in the WACC equation for equity investment appraisal purposes.
Conclusion
The cost of equity is an opportunity cost in that investors bear the additional risk to attain the desired level of expected return.
Hence, the fund manager must ensure the equity stock return is higher than the cost of equity or at least equates to the cost of equity in order to satisfy the investors.
