How to Calculate Cost of Equity in Excel or Google Sheets

To calculate cost of equity in Excel or Google Sheets, put the risk-free rate in B1, beta in B2 and the equity risk premium in B3, then enter =B1+B2*B3 in B4. With 5%, 1.2 and 5%, the result is 11%. The same sheet can also hold the dividend growth model, a beta regression with =SLOPE(), and a full WACC.

Every formula below works the same in Excel and Google Sheets. The numbers match the cost of equity calculator, so you can check your sheet against it.

Setting up the sheet

Put labels in column A and values or formulas in column B. Format any rate cell as a percentage (Ctrl+Shift+5 in Excel, or Format > Number > Percent in Sheets) so 0.05 shows as 5%.

The full layout used in this guide:

Row	A (label)	B (value or formula)	Result
1	Risk-free rate	5%	5.00%
2	Beta	1.2	1.20
3	Equity risk premium	5%	5.00%
4	Cost of equity (CAPM)	=B1+B2*B3	11.00%
6	Share price	50	$50.00
7	Current annual dividend (D0)	2	$2.00
8	Dividend growth rate	4%	4.00%
9	Next year’s dividend (D1)	=B7*(1+B8)	$2.08
10	Cost of equity (DDM)	=B9/B6+B8	8.16%
12	Size premium	3%	3.00%
13	Industry premium	0%	0.00%
14	Company-specific premium	2%	2.00%
15	Cost of equity (build-up)	=B1+B3+B12+B13+B14	15.00%
17	Unlevered beta	0.9	0.90
18	Target debt-to-equity	0.5	0.50
19	Tax rate	21%	21.00%
20	Relevered beta	=B17*(1+(1-B19)*B18)	1.2555
21	Cost of equity (relevered)	=B1+B20*B3	11.28%
23	Market value of equity	600	600
24	Market value of debt	400	400
25	Pre-tax cost of debt	6%	6.00%
26	WACC	see below	8.50%

Rows 5, 11, 16 and 22 are left blank as spacers.

CAPM

B4: =B1+B2*B3

That’s the whole formula: risk-free rate plus beta times the equity risk premium. If you’d rather work from an expected market return than a premium, put the market return in B3 and use:

B4: =B1+B2*(B3-B1)

Both give 11% when the market return is 10%. The CAPM guide explains where each input comes from. For the risk-free rate, the US Treasury’s daily par yield curve lists yields by maturity.

Calculating beta with SLOPE

If you’d rather measure beta than look it up, you need price history for the stock and for a market index. Set up a second tab called Returns:

Column	Contents
A	Date
B	Stock closing price
C	Index closing price
D	Stock return
E	Index return

Put the oldest prices in row 2 and work down. Returns start in row 3, since the first period has nothing to compare against:

D3: =B3/B2-1
E3: =C3/C2-1

Fill both down to the last row. With 61 monthly prices in rows 2 to 62, you get 60 returns in rows 3 to 62. Then calculate beta:

=SLOPE(Returns!D3:D62, Returns!E3:E62)

Order matters. The stock returns go first (the y values) and the index returns second (the x values). Swap them and you get a different, wrong number.

You can confirm the result with covariance over variance, which is the textbook definition of beta:

=COVARIANCE.S(Returns!D3:D62, Returns!E3:E62)/VAR.S(Returns!E3:E62)

It should match the SLOPE result exactly. To see how well the index explains the stock’s moves, use =RSQ(Returns!D3:D62, Returns!E3:E62). A low R-squared means the beta estimate is noisy, and an industry average may be a safer choice.

To pull prices automatically, Google Sheets has GOOGLEFINANCE, which returns daily or weekly closing prices:

=GOOGLEFINANCE("TICKER", "close", DATE(2021,1,1), DATE(2025,12,31), "WEEKLY")

In Microsoft 365, STOCKHISTORY does the same thing and supports monthly data with an interval of 2:

=STOCKHISTORY("TICKER", DATE(2021,1,1), DATE(2025,12,31), 2)

Replace TICKER with the stock’s symbol, then run it again with an index or index fund symbol for column C. A regression beta is a levered beta. The guide on levered vs unlevered beta explains when you’d adjust it.

Dividend growth model

B9:  =B7*(1+B8)
B10: =B9/B6+B8

B9 turns the current dividend into next year’s dividend. B10 divides by the share price and adds growth. With a $50 price, $2.00 dividend and 4% growth, B9 is $2.08 and B10 is 8.16%.

If you want it in one cell:

=B7*(1+B8)/B6+B8

The common mistake is dividing the current dividend by the price without growing it first. That gives 8.00%. The dividend growth model guide covers when this model breaks down.

Build-up method

B15: =B1+B3+B12+B13+B14

This reuses the risk-free rate and equity risk premium from the top of the sheet and adds the size, industry and company-specific premiums. With the example inputs, it returns 15%. The private company guide explains how to choose each premium.

Relevered beta and CAPM

B20: =B17*(1+(1-B19)*B18)
B21: =B1+B20*B3

B20 is the Hamada equation. An unlevered beta of 0.9 at a 0.5 debt-to-equity ratio and a 21% tax rate relevers to 1.2555. B21 feeds that into CAPM for a cost of equity of about 11.28%. For industry unlevered betas to put in B17, Damodaran’s betas by industry page is a common source.

WACC

B26: =B23/(B23+B24)*B4+B24/(B23+B24)*B25*(1-B19)

With $600 of equity, $400 of debt, the 11% CAPM cost of equity in B4, a 6% pre-tax cost of debt and a 21% tax rate, this returns 8.496%, or 8.50% at two decimal places. The WACC guide walks through each step.

Make sure the cost of equity you reference matches the capital structure in B23 and B24. If you relevered beta at a different debt level, point the formula at that result instead of B4.

A quick sensitivity table

Cost of equity estimates are only as good as their inputs, so it helps to see a range. In D1:D6, list betas from 0.8 to 1.8 in steps of 0.2. In E1, enter:

E1: =$B$1+D1*$B$3

Fill it down to E6. The dollar signs lock the risk-free rate and premium while beta changes row by row. With a 5% risk-free rate and 5% premium, a beta of 0.8 gives 9.0% and a beta of 1.8 gives 14.0%.

Checking your work

Before you rely on the sheet, run through these:

1. Every rate is stored as a decimal (0.05), not a whole number (5).
2. The DDM uses next year’s dividend, not the current one.
3. SLOPE has the stock returns first.
4. The beta’s capital structure matches the WACC weights.
5. The results match the calculator for the same inputs.

The methodology page lists the exact formulas the calculator uses, so you can line them up cell by cell. For definitions of any input, see the glossary entries on the risk-free rate, equity risk premium and beta.