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 as;
=(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
E4 = Risk-free rate of interest/return (Rf)
E6 = Beta (β)
E8 = Expected return of market E(Rm)
In this case, formula is used to calculate CAPM excel as follows:
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 accepted otherwise rejected.
This article will identify and use the CAPM Calculator Excel as example and template in simple steps.
Capital Asset Pricing Model (CAPM) Formula/Equation
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 desire 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 desire values into the respective fields as shown in the above example.
Download template file of CAPM Excel Calculator
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 as risk-averse.
- A higher expected return would be required by the investor in terms of risk premium to take an additional risk for.
- It is assumed that there is no cost of trading, regulations, or taxes.
What is difference between cost of capital, WACC and CAPM cost of equity
The cost of equity capital is calculated with CAPM formula. WACC equation highlights the value of the cost of equity plus the cost of debt securities. Therefore, CAPM calculates the cost of equity capital that 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 WACC equation for equity investment appraisal purposes.
The cost of equity is an opportunity cost that investors bear additional risk to attain 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.