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

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.

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