Retained Earnings Formula: A Worked Example + the Statement Built in Excel

Guide · Updated July 2026 · ~13 min read

Here is the formula, with numbers, before anything else:

Ending retained earnings = Beginning retained earnings + Net income − Dividends declared

A company opens 2025 with retained earnings of 68,400. It makes a net profit after tax of 41,750 during the year and declares dividends of 12,000. Closing retained earnings:

68,400 + 41,750 − 12,000 = 98,150

That is the whole calculation. Whether you searched for the retained earnings equation, the formula for calculating retained earnings, or — the UK phrasing — the retained profit formula, they are the same three terms in the same order. The rest of this post is what a glossary page never gives you: where each input comes from, the statement of retained earnings built cell by cell in Excel, how the closing figure ties into the balance sheet, and the two traps — a negative balance, and the assumption that retained earnings is money you can spend.

The formula, term by term

Three points that a definition-only page skips:

Use profit after tax. The number that rolls into retained earnings is the final line of the income statement, after interest and after tax. Rolling in operating profit or pre-tax profit is one of the two commonest reasons a balance sheet stops balancing — if you are building the P&L in the same workbook, the profit and loss statement in Excel walkthrough gets you to the right bottom line.

Dividends are not an expense. They never appear on the P&L, which is exactly why people cannot find them there. A dividend is a distribution of profit, not a cost of earning it, so it hits retained earnings directly. Look in the board minutes, the dividend vouchers, or the ledger account for dividends declared.

Declared, not paid. Deduct dividends declared in the period, even if the cash goes out after the year end. One nuance for UK accounts, as of 2026: a final dividend proposed after the balance sheet date is generally not a liability of the year just ended — it is only disclosed — so it enters next year's statement, when it is approved. An interim dividend is normally recognised when paid. Presentation rules differ by framework and get amended, so confirm the current treatment against the standard you report under.

One variant worth naming: a stock dividend (a bonus or scrip issue in UK terms) also reduces retained earnings, but the amount moves to share capital — total equity unchanged, no cash out.

A three-year roll-forward, including a loss year

The formula chains: each year's closing balance is the next year's opening balance. Here is the same company across three years, with a loss year included because that is where sign errors happen:

The 2023 column is the loss case: 52,300 − 11,400 − 0 = 40,900. A loss simply enters the formula as a negative — there is no separate "loss formula". Notice also that no dividend was declared in the loss year; that is typical, and in the UK it can be mandatory (more on that below).

Building the statement of retained earnings in Excel, cell by cell

The statement of retained earnings is this roll-forward presented formally for one period. Open a blank sheet — call it Retained Earnings — and type the following:

B10 returns 98,150. Now four finishing steps that turn a calculation into a statement:

1. Link the profit line instead of typing it. If the P&L is in the same workbook, replace B8 with a reference to its bottom line — for example ='P&L'!B38. A typed number goes stale the moment the P&L changes; a link cannot.
2. Show negatives in brackets. Select B5:B10, press Ctrl+1, choose Custom on the Number tab, and enter #,##0;(#,##0). Dividends now display as (12,000), which is how every published statement shows them.
3. Rule the total. Select B10, then Home > Font group > Borders dropdown > Top and Double Bottom Border. The double underline is the accountant's signal that a column ends here.
4. Widen column A (double-click the boundary between the A and B column headers) and bold rows 1, 2 and 10.

When the adjustment row earns its keep

B6 sits at zero in most years, but it is not decoration. Suppose you discover in 2025 that closing inventory in the 2024 accounts was overstated by 3,200 — so 2024's profit, and therefore opening retained earnings, is overstated by the same amount. The correction goes in B6 as -3200 (net of any tax effect, in practice), B7 restates the opening balance to 65,200, and the closing balance becomes 94,950. Corrections of prior-period errors go through this row precisely so that the current year's P&L is not polluted by last year's mistake.

Add the payout ratio while you are here

Two cells give you the ratios analysts compute from this statement. In B12, =-B9/B8 returns the payout ratio — 12,000 ÷ 41,750 ≈ 28.7% of profit distributed. In B13, =1+B9/B8 returns the retention ratio, ≈ 71.3% kept back. Format both as percentages with Ctrl+Shift+%. If you go on to project retained earnings forward, the retention ratio is the driver a financial model in Excel uses to roll equity into future years.

The multi-year version

For the three-year schedule, put years across columns on a sheet called RE roll: opening balance in row 4, profit in row 5, dividends in row 6 (entered as positives this time, with labels making the deduction explicit), closing in row 7 as =B4+B5-B6. Then the chaining is one formula: in C4 enter =B7, fill right, and every year's opening balance pulls from the prior year's closing automatically. If a year's opening balance is ever typed over, the chain breaks silently — which is why the checks section below exists.

Rearranging the formula when a figure is missing

Because the equation has four terms, knowing any three gives you the fourth. The rearrangement people actually need most is dividends, since many sets of published accounts show the opening and closing balances and the profit, but never state the dividend as a line:

Dividends declared = Beginning RE + Net income − Ending RE

From our numbers: 68,400 + 41,750 − 98,150 = 12,000. In Excel, with opening in B5, profit in B8 and closing in B10: =B5+B8-B10.

The other two rearrangements, for completeness:

• Net income = Ending RE − Beginning RE + Dividends declared — useful for sanity-checking a P&L against two balance sheets.
• Beginning RE = Ending RE − Net income + Dividends declared — useful when reconstructing a prior year.

If the net income you back out this way does not match the P&L, something moved through equity that was not profit or an ordinary dividend — a prior-period adjustment, a share buyback, or a revaluation — and the accounts' statement of changes in equity is where to look.

Where the figure lands on the balance sheet

Retained earnings is a component of equity. The equity block of the balance sheet for our example:

With total liabilities of 68,450, total assets must equal 201,600 — assets = liabilities + equity, and retained earnings is usually the largest moving part of the right-hand side. Two things follow from that:

The balance sheet figure is cumulative; the statement shows one year's movement. The 98,150 on the 2025 balance sheet is every profit the company has ever made, less every loss and every dividend, since incorporation. The statement of retained earnings explains only the 68,400 → 98,150 step.

A broken roll-forward is the classic reason a balance sheet is out. If assets and liabilities each tie to their schedules but the sheet is out of balance, check the retained earnings line first: opening balance not rolled from last year's closing, pre-tax profit used instead of post-tax, or a dividend deducted twice (once here, once wrongly as an expense). Never plug the difference into retained earnings to force a balance — the error is still there, now hidden inside equity.

Wire the check into the workbook rather than doing it by eye. On the balance sheet, with retained earnings in B22:

=IF(B22='Retained Earnings'!B10,"OK","OUT BY "&TEXT(ABS(B22-'Retained Earnings'!B10),"#,##0"))

The free balance sheet template in Excel has this philosophy built in — its check row prints "OK - balanced" or "OUT BY" the difference, and the download at /templates/hisab-balance-sheet-template.xlsx gives you the equity block ready-labelled so the closing figure from your statement has somewhere to land.

Trap 1: negative retained earnings

Run the formula through enough loss years and the balance goes negative: opening 15,000, loss of 40,000, and the balance sheet now shows retained earnings of (25,000) — formatted in brackets, and often relabelled accumulated deficit in US-style accounts. Three things to understand about it:

It is not automatically a solvency problem. A company with 500,000 of share premium and (25,000) of retained earnings has positive equity of 475,000 plus share capital. Early-stage companies routinely run accumulated deficits for years while funded by share issues. The figure to check is total equity, not one row of it.

It usually blocks dividends. UK company law, as it stands in 2026, permits distributions only out of profits available for the purpose — broadly, accumulated realised profits less accumulated realised losses. A company with negative retained earnings therefore generally cannot pay a dividend, however much cash sits in the bank, and declaring one anyway risks creating an unlawful distribution that directors can be required to repay. The detail is jurisdiction-specific and periodically amended: this is background, not legal advice, so check the current position with your accountant before any declaration.

Losses are not the only cause. Distributing more than the current year's profit in a good run of years, or (under US GAAP) large share buybacks charged against retained earnings, can drive the balance negative in a profitable company. The roll-forward tells you which story you are looking at — which is a good reason to keep the multi-year schedule, not just the single-year statement.

Trap 2: retained earnings is not cash

The single most persistent misreading of the balance sheet is treating retained earnings as a pot of money. Our example company closed 2025 with retained earnings of 98,150 — and cash of 6,300. Both are true simultaneously:

Retained earnings sits on the claims side of the balance sheet: it records that profits were kept in the business, not where they are now. The profits bought machines, sit in unpaid invoices, and are stacked on the warehouse floor. So when a director asks "we've made 41,750 this year — can we take 30,000 out?", the answer needs two tests, not one: are there distributable profits (the retained earnings test), and is there cash to pay it without starving the business (a cash flow test the formula cannot answer). Passing one test and failing the other is common in both directions.

How HISAB 360 helps

Building one statement by hand teaches you the mechanics. Producing it every month, tied to a moving trial balance, is where errors creep in. HISAB 360 is a paid Excel add-in with an AI assistant docked in a panel inside Excel; it generates financial statements — P&L, balance sheet, and the equity movement between them — directly from a trial balance you paste in or from live accounting data. It connects to six systems (QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting), pulls the general ledger and closing balances into a sheet, and computes retained earnings as part of the build rather than leaving you to roll it forward manually — opening balance, current-period profit and dividend movements placed where they belong, with the balance check intact. Because the statements are real formulas on a real sheet, not a static export, you can trace the retained earnings cell back to its inputs and adjust the presentation afterwards.

Honest limits: it is Windows desktop Excel only (Microsoft 365 or 2016+), not Mac and not Excel on the web, and it is paid — there is a 15-day free trial with 50 AI credits and no card required. And no tool removes the judgement calls above: whether a dividend is lawful and affordable stays a human decision.

The five checks before you file

A retained earnings figure is only as good as its ties. Before the statement goes anywhere:

Checks 1 and 4 are the pair that catch a broken chain; run them every period, not just at year end. If you close monthly, they belong on the close list itself — the month-end close checklist in Excel is built for exactly that kind of recurring tie-out.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.