How Do I Calculate the Cost of Equity Using Excel?

When assessing the relative effectiveness of different financing plans, businesses use the capital asset pricing model, or CAPM, for determining the cost of equity financing. Equity financing is the amount of capital generated through the sale of stock. The cost of equity financing is the rate of return on the investment required to maintain current shareholders and attract new ones. Though this concept can seem intimidating, once the necessary information is assembled, calculating the CAPM cost of equity (COE) is simple using Microsoft Excel.

Calculating COE With Excel

To calculate COE, first determine the market rate of return, the risk-free rate of return and the beta of the stock in question. The market rate of return is simply the return generated by the market in which the company's stock is traded, such as the Nasdaq or S&P 500. The risk-free rate is the expected rate of return as if the funds were invested in a zero-risk security.

While nothing is completely risk-free, the rate of return for U.S. Treasury bills (T-bills) is generally used as the risk-free rate because of the low volatility of this type of investment and the fact that the return is backed by the government. The stock's beta is a reflection of its volatility relative to the wider market. A beta of 1 indicates the stock moves in sync with the broader market, while a beta above 1 indicates greater volatility than the market. Conversely, a beta lower than 1 indicates the stock's valuation is more stable.

 E ( R i ) = R f + β i × [ E ( R m ) R f ] where: E ( R i ) = Expected return on asset i R f = Risk-free rate of return β i = Beta of asset i E ( R m ) = Expected market return \begin{aligned} &E(R_i) = R_f + \beta_i \times [ E ( R_m ) R_f ] \\ &\textbf{where:} \\ &E(R_i) = \text{Expected return on asset i} \\ &R_f = \text{Risk-free rate of return} \\ &\beta_i = \text{Beta of asset i} \\ &E(R_m) = \text{Expected market return} \\ \end{aligned} E(Ri)=Rf+βi×[E(Rm)Rf]where:E(Ri)=Expected return on asset iRf=Risk-free rate of returnβi=Beta of asset iE(Rm)=Expected market return

After gathering the necessary information, enter the risk-free rate, beta and market rate of return into three adjacent cells in Excel, for example, A1 through A3. In cell A4, enter the formula = A1+A2(A3-A1) to render the cost of equity using the CAPM method.

Article Sources

Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
  1. TreasuryDirect. "Treasury Bills In Depth." Accessed July 17, 2020.