Excel guide · 4 min read
How to calculate return on equity in Excel
=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.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Month | Revenue | Net income | Balance sheet | Start (31 Oct 2025) | End (31 Oct 2026) | |
| 2 | 2025-09 | 86000 | 3300 | Total equity | 182000 | 214000 | |
| 3 | 2025-10 | 83000 | 2900 | Total assets | 410000 | 446000 | |
| 4 | 2025-11 | 88000 | 3100 | ||||
| 5 | 2025-12 | 84000 | 2800 | ||||
| 6 | 2026-01 | 97000 | 4200 | ||||
| 7 | 2026-02 | 71000 | 1500 | ||||
| 8 | 2026-03 | 79000 | 2600 | ||||
| 9 | 2026-04 | 90000 | 3900 | ||||
| 10 | 2026-05 | 96000 | 4400 | ||||
| 11 | 2026-06 | 101000 | 4800 | ||||
| 12 | 2026-07 | 104000 | 5200 | ||||
| 13 | 2026-08 | 93000 | 4100 | ||||
| 14 | 2026-09 | 89000 | 3700 | ||||
| 15 | 2026-10 | 95000 | 4300 |
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.
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.
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.
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.
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.
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
- How to calculate the asset turnover ratio in Excel
- How to calculate retained earnings in Excel
- How to calculate the debt-to-equity ratio in Excel
- How to calculate ROI in Excel
- How to calculate return on assets (ROA) in Excel
- How to calculate a tracking signal in Excel
Updated