CAPM Calculator Excel, Formula, Free Template

How to calculate CAPM (capital assets pricing model) in Excel sheet

CAPM Calculator Excel / Formula

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

Where;

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:

=(E4+E8*(E6-E4))
CAPM Formula Excel

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

CAPM 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 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

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 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.

Conclusion

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.

Add a Comment

Your email address will not be published. Required fields are marked *