Excel guide · 6 min read
How to calculate retained earnings in Excel
=B2+C2-D2 for the closing balance, link each year's opening to the previous year's closing (=E2), pull net profit from your P&L with a lookup, and add a check column that compares the closing figure with the balance sheet.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 | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Year | Opening | Net profit | Dividends / drawings | Closing | Retained % | Balance sheet | Check | Year | Revenue | Expenses | |
| 2 | 2021 | 0 | -7500 | 0 | -7500 | n/a | -7500 | 0 | 2021 | 84000 | 91500 | |
| 3 | 2022 | -7500 | 13600 | 0 | 6100 | 1 | 6100 | 0 | 2022 | 132000 | 118400 | |
| 4 | 2023 | 6100 | 27300 | 10000 | 23400 | 0.6336996337 | 23400 | 0 | 2023 | 168500 | 141200 | |
| 5 | 2024 | 23400 | 31300 | 15000 | 39700 | 0.52076677316 | 39700 | 0 | 2024 | 190200 | 158900 | |
| 6 | 2025 | 39700 | 29500 | 15000 | 54200 | 0.49152542373 | 54200 | 0 | 2025 | 205800 | 176300 | |
| 7 | 2026 | 54200 | 32800 | 18000 | 69000 | 0.4512195122 | 69000 | 0 | 2026 | 221400 | 188600 |
Download this sheet as an Excel practice file (.xlsx)
The retained earnings formula
Retained earnings are the profits a business has made since it started, minus what it paid out to its owners. They sit in equity on the balance sheet, and each year they move by exactly that year's profit (or loss) less dividends or drawings.
So the calculation is a roll-forward: closing = opening + net profit - dividends. The sample in A:H covers a business started in 2021, one row per year. Opening in B2 is 0 because the business is new; if yours isn't, type the retained earnings from the last balance sheet before your first row.
Result: -7500
-7,500 at the end of 2021: a first-year loss and no dividends. A negative balance is often called an accumulated deficit.
Link each opening balance to last year's closing
Don't type opening balances. In B3 enter =E2 and fill down, so every year starts where the last one ended. If you correct a figure in 2023, every later year updates, and a typed opening can never drift from the closing it should equal.
In 2023 the opening balance is 6,100 (2022's closing after a 13,600 profit wiped out the 2021 loss), profit is 27,300 and dividends are 10,000, so the closing balance is 23,400.
Result: 23400
23,400 at the end of 2023: 6,100 + 27,300 - 10,000. In the table, B4 is =E3 and E4 is =B4+C4-D4.
Pull net profit from the P&L
Net profit should come from the profit and loss statement, not be typed a second time. The P&L summary in J:M has revenue, expenses and net profit by year; column C finds the row for its year with XLOOKUP. The 0 at the end returns 0 for a year with no P&L row yet, instead of #N/A.
If your P&L is on another sheet with years across the top, use XLOOKUP across the row instead, or point at the year's net profit cell directly.
Result: 32800
32,800 of net profit for 2026: revenue of 221,400 minus expenses of 188,600. In Excel 2019 or earlier, use =IFERROR(VLOOKUP(A7,$J$2:$M$7,4,FALSE),0).
Check against the balance sheet
Type the retained earnings figure from each year's balance sheet in column G and subtract it from your roll-forward in column H. Every row should be 0. A difference points to something posted straight to equity: drawings booked as an expense, a prior-year correction, or a profit figure taken from a draft P&L.
A second check covers the whole period at once: the starting balance plus all the profits minus all the payouts should equal the last closing balance.
Result: 69000
69,000: 0 + 127,000 of total profit - 58,000 of dividends, the same as the 2026 closing balance in E7.
How much of each year's profit was kept?
The retention ratio is (net profit - dividends) / net profit. Return "n/a" for a loss year, where the ratio means nothing.
Result: 0.4512195122 shows as 45.1% with Percent format
0.4512: the business kept 45.1% of 2026's profit and paid out the rest.
Working backwards: dividends or profit from two balance sheets
Often you only have two balance sheets and one other figure, and need the missing one. The roll-forward rearranges either way. Dividends paid = opening retained earnings + net profit - closing retained earnings. Net profit = closing - opening + dividends.
For 2026: 54,200 opening + 32,800 profit - 69,000 closing = 18,000 of dividends, which matches the 18,000 in D7. If the answer you get doesn't match what was actually paid, something else went through retained earnings that year, such as a prior-year correction, and it needs its own line.
Result: 18000
18,000: the dividends implied by 2026's opening and closing retained earnings and its net profit.
Payout ratio for every year in one formula
The payout ratio is the other side of the retention ratio: dividends / net profit. The two always add up to 100% in a profit year. In Excel 365, one formula spills a ratio for every row, with "n/a" for the 2021 loss.
The trend says more than any single year: 0% in 2022, then 36.6%, 47.9%, 50.8% and 54.9% in 2026. Profit has grown, but payouts have grown faster, so the business keeps a shrinking share of each year's profit. Over the whole period it paid out 58,000 of 127,000, or 45.7% (=SUM(D2:D7)/SUM(C2:C7)).
In older Excel, put =IF(C2<=0,"n/a",D2/C2) in F2 and fill down.
Result: spills 6 rows × 1 column
| n/a |
| 0 |
| 0.3663003663 |
| 0.47923322684 |
| 0.50847457627 |
| 0.5487804878 |
Spills n/a, 0, 0.366, 0.479, 0.508, 0.549: the share of each year's profit paid out, 2021 to 2026.
A one-year statement of retained earnings
If someone asks for a statement of retained earnings, they usually want the roll-forward for one year laid out down the page rather than across. Link every line to the table so it never needs retyping.
- A1: "Retained earnings, 1 January 2026" and B1:
=E6(54,200). - A2: "Add: net profit for the year" and B2:
=C7(32,800). - A3: "Less: dividends" and B3: =-D7 (-18,000), shown in brackets with the format #,##0;(#,##0).
- A4: "Retained earnings, 31 December 2026" and B4:
=SUM(B1:B3).
Result: 69000
69,000: the closing line of the statement, equal to E7 and to the balance sheet figure.
Using retained earnings in ratios
Retained earnings are part of total equity, which is the denominator in return on equity. As retained earnings build up, equity grows, so the same profit gives a lower ROE: worth knowing before you compare years.
The movement in retained earnings also ties into the cash flow statement: profit is the first line, and dividends or drawings are a financing outflow. How much to pay out, and whether a company can legally pay a dividend, are questions for your accountant.
Common mistakes
Typing each year's opening balance
Link it to the previous closing (=E2) so a correction flows through every later year.
Drawings or dividends booked as an expense
They're a distribution of profit, not a cost. If they're in the P&L, profit is understated and the roll-forward subtracts them twice.
Using the closing balance from a draft P&L
Use final net profit after any year-end adjustments, then let the check column against the balance sheet confirm it.
Putting a prior-year correction into this year's profit
A mistake found in an earlier year's accounts is usually corrected through opening retained earnings, not this year's P&L. Give it its own line in the roll-forward so this year's profit and payout ratio stay clean, and agree the treatment with your accountant.
FAQ
What is the formula for retained earnings?
Retained earnings = beginning retained earnings + net income - dividends. In Excel: =B2+C2-D2, with each year's beginning linked to the previous year's ending.
Are retained earnings the same as cash?
No. Retained earnings show profit kept in the business over the years, but that profit may now sit in stock, equipment, money owed by customers or loans already repaid. A business can have large retained earnings and little cash. Look at the cash flow statement for the cash side.
Can retained earnings be negative?
Yes. When losses and payouts are larger than profits since the start, the balance is negative, often called an accumulated deficit.
Where do I find retained earnings?
In the equity section of the balance sheet. The movement during the year is shown in a statement of retained earnings or of changes in equity.
Do sole traders have retained earnings?
Sole traders and partnerships usually show the owner's capital account instead: capital introduced plus profit minus drawings. The roll-forward is the same shape, with drawings in place of dividends.
Functions used
XLOOKUPModern lookup in any directionVLOOKUPVertical lookupIFERRORFallback when a formula errorsSUMAdds numbersIFPicks a result from a test
Related guides
- How to make a balance sheet in Excel
- How to make a profit and loss statement in Excel
- How to make a cash flow statement in Excel
- How to calculate return on equity in Excel
- How to make a running total (cumulative sum) in Excel
- How to calculate shipping cost by weight in Excel
Updated