Cellwise Start free

Excel guide · 4 min read

How to calculate return on equity in Excel

Quick answerReturn on equity is net income divided by average shareholders' (or owners') equity: =SUM(C4:C15)/AVERAGE(F2:G2), formatted as a percentage. Use twelve months of net income and the average of the opening and closing equity for the same period, so the profit and the equity it's measured against line up.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFG
1MonthRevenueNet incomeBalance sheetStart (31 Oct 2025)End (31 Oct 2026)
22025-09860003300Total equity182000214000
32025-10830002900Total assets410000446000
42025-11880003100
52025-12840002800
62026-01970004200
72026-02710001500
82026-03790002600
92026-04900003900
102026-05960004400
112026-061010004800
122026-071040005200
132026-08930004100
142026-09890003700
152026-10950004300

Download this sheet as an Excel practice file (.xlsx)

What you need

The sheet above has a small company's monthly revenue and net income in A:C, and two balance sheet figures, total equity and total assets, at the start and end of the last twelve months in E:G. Net income comes from the profit and loss statement; equity and assets come from the balance sheet.

ROE answers one question: how much profit did the business make for each dollar the owners have in it? It's a ratio for comparing periods or similar companies, not a verdict on its own.

Step 1: Trailing twelve months of net income

ROE is an annual figure. If you have monthly profit, add up the most recent twelve months (often called TTM). The list starts in September 2025, so the last twelve months are rows 4 to 15. Summing the whole column would count 14 months and overstate ROE.

When you add a month every month, use a formula that always takes the last 12 rows. In Excel 365, TAKE with a negative count does exactly that. In older versions, move the range down one row each month, or build it with INDEX:INDEX pointing at the last filled row and the row 11 above it.

=SUM(TAKE(C2:C15,-12))

Result: 44600

$44,600 of net income from November 2025 to October 2026. The same as =SUM(C4:C15). The full column adds up to $50,800.

Step 2: Divide by average equity

Equity changes during the year as profit is kept in the business, owners put money in or take it out. Divide by the average of the opening and closing equity, not just the closing figure. With only the year-end equity, ROE here comes out at 20.8% instead of 22.5%, because the profit made during the year has already been added to the denominator.

If you have month-end balance sheets, an average of all 13 month-ends is more precise still.

=SUM(C4:C15)/AVERAGE(F2:G2)

Result: 0.22525252525 shows as 22.5% with Percent format

22.5%: $44,600 of profit on $198,000 of average equity.

ROE vs ROA

Return on assets uses the same profit over average total assets. It measures how well the business uses everything it controls, whether paid for by owners or by lenders. For a profitable business with debt, ROE is always higher than ROA, because equity is only part of the assets.

=SUM(C4:C15)/AVERAGE(F3:G3)

Result: 0.10420560748 shows as 10.4% with Percent format

10.4% ROA, against 22.5% ROE. The gap comes from borrowing: liabilities fund about 54% of the assets.

The DuPont breakdown: why ROE is what it is

ROE splits into three ratios that multiply back to it: net profit margin (net income / revenue) shows pricing and costs, asset turnover (revenue / average assets) shows how hard the assets work, and the equity multiplier (average assets / average equity) shows how much is funded by debt.

Here the margin is 4.1%, asset turnover is 2.54 and the equity multiplier is 2.16. Lay each out in its own cell so you can see which one moved between years. Two companies can have the same ROE for completely different reasons: one with thin margins and high leverage, another with fat margins and no debt.

=(SUM(C4:C15)/SUM(B4:B15))*(SUM(B4:B15)/AVERAGE(F3:G3))*(AVERAGE(F3:G3)/AVERAGE(F2:G2))

Result: 0.22525252525 shows as 22.5% with Percent format

22.5%, exactly the ROE from step 2. If it doesn't match, one of the periods or averages doesn't line up.

When ROE isn't meaningful

If equity is negative (losses or withdrawals bigger than what owners put in), the ratio flips sign: a loss divided by negative equity shows as a positive ROE. If equity is tiny, ROE can be hundreds of percent and tells you nothing. Guard the cell so it says so instead of showing a number.

=IF(AVERAGE(F2:G2)<=0,"Not meaningful",SUM(C4:C15)/AVERAGE(F2:G2))

Result: 0.22525252525 shows as 22.5% with Percent format

22.5% here, because equity is positive. For a company with negative equity the cell reads "Not meaningful". The debt-to-equity guide shows the same problem from the borrowing side.

Common mistakes

Using closing equity only

Average opening and closing equity for the same twelve months. Closing equity already includes the year's profit.

Mixing periods

A quarter's profit over year-end equity understates ROE four times over. Use twelve months of profit, or annualize consistently.

Summing the whole income column

When the list holds more than 12 months, take the last 12 with TAKE or a fixed range that you move each month.

Reading ROE on its own

A high ROE can come from heavy borrowing. Show ROA or the DuPont parts next to it.

FAQ

What is the formula for ROE in Excel?

=net income / average equity. With twelve months of profit in C4:C15 and opening and closing equity in F2:G2: =SUM(C4:C15)/AVERAGE(F2:G2), formatted as a percentage.

What's the difference between ROE and ROA?

ROE divides profit by equity (what owners have in the business). ROA divides by total assets (funded by owners and lenders). With debt, ROE is higher than ROA.

What does a negative ROE mean?

Usually a loss on positive equity. But a positive ROE can also come from a loss on negative equity, so check that equity is positive before reading the number.

How do I calculate trailing twelve month ROE?

Sum the latest twelve months of net income, for example =SUM(TAKE(C2:C15,-12)) in Excel 365, and divide by the average equity over the same twelve months.

Functions used

Related guides

Updated