Cost of Equity and CAPM in Excel

The cost of equity is the return shareholders require for holding a company's stock. The capital asset pricing model, CAPM, estimates it from three inputs: the risk-free rate, the stock's beta, and the equity risk premium. It is the discount rate for equity cash flows and a building block of WACC, so the inputs deserve care and a clean Excel layout.

The CAPM formula and its inputs

CAPM is Re = Rf + beta*(Rm-Rf). Rf is the risk-free rate, usually a long-dated government bond yield. beta measures how much the stock moves with the market. The term (Rm-Rf) is the equity risk premium, the extra return the market is expected to deliver over the risk-free rate.

Beta scales the premium: a beta of 1 earns the full market premium, a beta above 1 earns more for higher systematic risk, and a beta below 1 earns less. Multiplying beta by the premium and adding the risk-free rate gives the required return on the stock.

Worked example

The risk-free rate is 4 percent, the stock's beta is 1.2, and the equity risk premium is 5 percent. Apply CAPM directly.

1. Equity risk premium contribution: =1.2*0.05 returns 0.06, or 6 percent.
2. Cost of equity: =0.04+1.2*0.05 returns 0.10, or 10 percent.
3. Raise beta to 1.5: =0.04+1.5*0.05 returns 0.115, or 11.5 percent.
4. Lower beta to 0.8: =0.04+0.8*0.05 returns 0.08, or 8 percent.

=0.04+1.2*0.05 returns 0.10.

Laying it out in a model

CAPM has only three inputs, so give each its own cell and compute the cost of equity in one output cell that feeds the rest of the model.

• Keep Rf, beta, and the equity risk premium in three labeled input cells.
• Source the risk-free rate from a current long-dated government yield.
• Reference a published or computed equity risk premium rather than typing one.
• Feed the single cost of equity cell into both the DDM and the WACC block.

Pitfalls

The most common error is hardcoding an input inside the formula, such as =0.04+1.2*0.05, which buries the risk-free rate, beta, and premium where no reviewer can flex them. Pull each into its own cell so the model is auditable.

Confusing the equity risk premium (Rm-Rf) with the full market return Rm double counts the risk-free rate and inflates the cost of equity. Using a short-term bill yield for Rf when valuing a long-lived business mismatches the horizon; match the risk-free maturity to the cash-flow horizon, typically a 10-year or longer yield.