Retained Earnings Won't Tie to QuickBooks or Xero: Build the Roll-Forward Proof in Excel

Guide · Updated July 2026 · ~18 min read

Your signed 2024 accounts say retained earnings of 68,400. The trial balance you exported from QuickBooks Online this morning says the opening balance is 71,600. Nobody in the practice posted a journal to retained earnings, and the software did not post one either — it rolls last year's profit into that account by calculation, not by a transaction you can open — and yet 3,200 has appeared in it.

That gap is the real retained-earnings problem. The formula itself takes ten seconds:

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

So a company that opens 2025 on 68,400, makes 41,750 after tax and declares 12,000 of dividends closes on 68,400 + 41,750 − 12,000 = 98,150. 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. And knowing them tells you nothing about why your ledger disagrees with the accounts you filed, which of the two figures to trust, or what to write on the workpaper when the partner asks.

This post builds the proof schedule instead: a roll-forward in Excel that starts at the closing balance in last year's signed accounts, adds the movements your accounting system actually recorded, and lands on the equity line of this year's balance sheet — with every difference named rather than plugged. It covers what QuickBooks Online, Xero and Zoho Books each do to retained earnings at year end without asking you, the four things that move the number after you have signed, and the check formula that prints OUT BY 3,200 instead of letting a broken chain travel quietly into next year.

The three balances that have to agree

A retained-earnings figure only means something when three separate balances agree: the closing balance in last year's signed accounts, the opening retained earnings on the trial balance you have just pulled out of the ledger, and the retained earnings line on the balance sheet you are about to issue. You control the first and the third. The second is maintained by software. The four terms below are the vocabulary you will use to describe any difference between them.

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.

Retained earnings is a system account: what QuickBooks, Xero and Zoho Books do at year end

The reason two competent people can produce two different retained-earnings figures for the same company is that nobody produced one of them. In a cloud ledger, retained earnings is maintained by the software. At the financial year end, the year's accumulated profit stops being reported as current-year earnings and starts being reported as retained earnings. There is no journal to open, nobody to ask, and in general no entry in the account's transaction list to point at. The balance is a result, not a posting.

QuickBooks Online. Retained Earnings exists in the chart of accounts from day one, and QuickBooks rolls the prior year's net income into it at the fiscal year end by calculation rather than by writing a transaction. Click the amount on the balance sheet and you are not taken to a register of entries; you get a profit-and-loss breakdown by year, which is the software showing its working rather than its records. The practical consequence for the proof below: you cannot audit this balance by reading a ledger account, because most of it was never posted to one. You audit it by rebuilding it.

Opening Balance Equity. QuickBooks Online also carries an Opening Balance Equity account, used when opening balances are entered at conversion or when an account is created with a balance already on it. It is a holding account. It is meant to be cleared to retained earnings or owners' equity once the conversion has been checked, and on a file that was migrated in a hurry it very often was not. Total equity still comes out right, so nothing looks wrong on the face of the balance sheet — but retained earnings alone is out by whatever is parked next door, which is exactly the sort of difference this page is about.

Xero. Xero reports two equity lines while the year is running: current year earnings and retained earnings. The first is folded into the second at the financial year end set on the organisation. Tie last year's signed accounts to retained earnings, tie this period's profit to current year earnings, and be careful which of the two you are comparing against — if your schedule includes the current year's profit and the row you read off the balance sheet does not, you will be out by exactly this year's profit and spend an hour looking for it.

Zoho Books. The same pattern again: a retained-earnings account the system maintains under Equity, with the year's earnings shown separately until the books roll over. Zoho Books also offers transaction locking, which is the control that stops the balance moving behind you — see the companion post on Zoho Books export limits for getting a complete trial balance out of it in the first place.

Two cautions. First, the year-end behaviour, the account names and the settings that govern them are vendor decisions and they get changed; confirm what your own file does before you rely on any of the above, because the cost of a stale assumption here is reconciling to the wrong row. Second, none of these mechanisms is a mistake. They are all reasonable. They simply mean the number in your ledger is derived, so a disagreement with your signed accounts is not settled by arguing with the software — it is settled by a schedule.

Four reasons the number moves after you have signed

When the opening retained earnings in this year's export does not equal the closing retained earnings in last year's accounts, it is nearly always one of four things. Work down the list in order; the first is much the commonest.

Cause 1 deserves the extra sentence. Every cloud ledger has some form of lock date or transaction-locking setting, and on most files nobody has set one, so a bookkeeper correcting a coding error in March can silently change a figure that went out in a signed set of accounts in January. Setting the lock date the day the accounts are signed is a two-minute job that removes this cause permanently; until it is done, re-running last year's P&L is part of your opening-balance check, not an optional extra.

The proof schedule, cell by cell

The statement of retained earnings is this roll-forward presented formally for one period. The proof schedule is the same statement with two more columns beside it: what the signed accounts say, what the ledger export says, and the difference between them. 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.

The reconciling block, and the cell that has to read zero

D5 is the whole problem stated in one cell: (3,200), the ledger's opening balance against the signed accounts. A schedule that shows the difference and stops is not a proof. Underneath the statement, list what the difference is made of, and make the list prove itself:

Leave row 15 empty for the next item you find; the SUM already covers it. B18 is the only cell on the schedule anyone else needs to read. While it says OUT BY anything, you have a difference you cannot explain, and the correct thing to do with it is keep looking — not round it, not net it off against something else, and not move it into the closing balance because the closing balance happens to agree.

Two rules make the block usable as a workpaper. Every item gets a reference a reviewer can follow: a journal number, a dividend voucher, a page of the prior-year accounts. And every item gets a direction — is the ledger wrong, or is your schedule wrong? Those lead to opposite actions, and a list of bare numbers records neither.

When the adjustment row earns its keep

Every reconciling item has to land somewhere, and a genuine prior-year error lands in B6. It 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.

This is a different 3,200 from the opening difference earlier on, and the distinction matters: that one was activity posted into a year already closed, this one is an error in the accounts as filed. The first is explained in the reconciling block and leaves the signed figures alone; the second restates them, in B6, with disclosure. Getting them the wrong way round is how a reconciling item quietly becomes a restatement nobody agreed to.

Chain the proof so a break shows you the year it happened

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).

Build it as a sheet, not a table in a document. 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. That single sentence is why this page exists. A typed number looks identical to a formula on screen, it agrees with whatever was true on the day somebody typed it, and it goes on agreeing forever while the year beneath it moves. Select C4:E4 and press Ctrl+` (the grave accent, left of 1) to show formulas: every opening cell in that row should read =B7, =C7, =D7 and nothing else.

The chain also tells you when. Add a row under the schedule comparing each year's closing balance to the retained earnings on that year's signed accounts. When this year will not tie, extend the comparison back a year at a time until a year does tie — the first year that ties is the year after the error, and you have just turned "retained earnings is out by 3,200" into "something happened in 2024", which is a question you can actually answer.

Back-solve the dividend to find what really moved through equity

Because the equation has four terms, knowing any three gives you the fourth — and that is a diagnostic, not an exam trick. If you have an opening balance you trust, a profit you trust and a closing balance from the ledger, the formula will tell you what the ledger thinks the dividend was. Where that disagrees with the board minutes, the residual is your reconciling item. The rearrangement is the one people need most anyway, 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.

A back-solved dividend of 12,750 against minutes that record 12,000 is not a mystery, it is a shortlist. Take the 750 to the four causes above: an equity account that received a drawing it should not have, a late journal into the prior year, an unclear opening balance from a migration, or a prior-year item sitting in this year's profit. One of them will hold it, and whichever one does becomes a line in the reconciling block with a reference beside it.

Wire the three-way tie into the workbook

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"))

That is one leg of three, and on its own it can read OK while the schedule is built on an opening balance nobody agreed. Paste the ledger's own closing retained earnings into C22 beside your reported figure and let the check name the leg that broke instead of just the amount:

=IF(ROUND('Retained Earnings'!B17,2)<>0,"OPENING: ledger vs signed accounts unexplained",
 IF(ROUND(B22-'Retained Earnings'!B10,2)<>0,"CLOSING: balance sheet vs schedule",
 IF(ROUND(B22-C22,2)<>0,"CLOSING: balance sheet vs ledger export","OK")))

Three legs, three named failures: the opening balance you started from, the schedule you built, and the ledger you are reporting. A reviewer who sees "CLOSING: balance sheet vs ledger export" knows in one glance that the arithmetic is fine and the argument is with the software, which is a different afternoon from the one where the arithmetic is wrong.

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.

When the proof lands negative: accumulated deficit and distributable profits

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.

While you are in this section with a director, two cells answer the question they are really asking. In B12, =-B9/B8 returns the payout ratio — 12,000 ÷ 41,750 ≈ 28.7% of this year's profit distributed. In B13, =1+B9/B8 returns the retention ratio, ≈ 71.3% kept back. Format both as percentages with Ctrl+Shift+%. A company running close to 100% payout has no cushion the next time the proof lands negative, and if you go on to project equity forward, the retention ratio is the driver a financial model in Excel rolls the balance with.

"We made 41,750 — can we take 30,000 out?"

This is the question the schedule gets asked in the room, and the honest answer needs two tests rather than one. The obstacle is that 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 the question at the top of this section 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.

Where HISAB 360 fits

The slow part of this proof is not the arithmetic, it is the ledger column: exporting a trial balance, finding the equity rows in it, and retyping them beside your schedule — every month, for every entity. HISAB 360 is a paid Excel add-in with an AI assistant docked in a panel inside Excel. It connects to QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks and pulls the trial balance, the general ledger and closing balances straight onto a sheet, so the Per ledger export column is data you refreshed rather than data you copied. Ask the panel in plain English and it will lay out the schedule, the reconciling block and the check formulas as real formulas on a real sheet — which means you can trace the retained earnings cell back to its inputs and change the presentation afterwards, exactly as you would with a schedule you built yourself.

Honest limits: it is Windows desktop Excel only — Microsoft 365 or Excel 2016+ on 64-bit Windows — not Mac and not Excel on the web, and it is paid, from $7/month if you bring your own AI provider key, with a 15-day free trial of every feature, 50 AI credits and no card required. It is a copilot you review, not something that posts on its own. And no tool removes the judgement calls above: which of two figures is right, whether a difference is material, and whether a dividend is lawful and affordable all stay human decisions.

The sign-off pack: what goes in the file

A retained earnings figure is only as good as its ties, and a tie nobody can find later is not evidence. Five checks, and all five leave something in the file:

Three things go in the file, and they take about a minute to assemble once the schedule exists: the proof schedule itself with B18 reading OK, the reconciling-items list with a reference against every line, and the page of the prior-year signed accounts the opening balance was taken from. That last one is the one people skip and the one a reviewer asks for, because it is the only document in the pack that was not produced by the person doing the reconciling.

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 and FreshBooks. The 15-day trial is the full product, no card required.