Trial balance spreadsheet template for statutory accounts
A trial balance for statutory accounts lists every nominal account with its closing balance at the financial year end: an account code, an account name, and the balance as a debit or a credit, or as one signed amount. The debits must equal the credits. Use one sheet for this year and one for last year, with the same accounts.
Key facts
| Columns | Account code; account name; debit; credit (or one balance column, debits positive and credits negative) |
|---|---|
| Rows | One per nominal account with a balance; no subtotals |
| Date | The last day of the financial year |
| Totals | Debits equal credits; a signed column sums to zero |
| Comparatives | A second sheet at last year's year end |
| File type | Excel (.xlsx) or CSV |
As at 28 October 2026.
The layout
| Column | What goes in it | Example |
|---|---|---|
| Account code | The nominal code from your books, if you use codes | 1200 |
| Account name | The name of the account as in your books | Bank current account |
| Debit | The closing balance if it is a debit (assets, expenses) | 30,000 |
| Credit | The closing balance if it is a credit (liabilities, capital, income) |
Put each account on one row and leave out headings, blank rows and subtotals between the accounts; accounts software reads the rows as accounts. If you prefer one column, show debits as positive and credits as negative amounts.
A worked example that balances
| Code | Account | Debit £ | Credit £ |
|---|---|---|---|
| 0030 | Plant and equipment: cost | 20,000 | |
| 0031 | Plant and equipment: depreciation | 8,000 | |
| 1100 | Trade debtors | 16,500 | |
| 1110 | Director's loan account | 1,500 | |
| 1120 | Prepayments | 1,000 | |
| 1200 | Bank current account | 30,000 | |
| 2100 | Trade creditors | 8,000 | |
| 2200 | VAT | 4,500 | |
| 2210 | PAYE and National Insurance | 2,000 | |
| 2220 | Corporation Tax | 2,500 | |
| 2300 | Other creditors | 4,000 | |
| 2400 | Accruals | 2,000 | |
| 2500 | Bank loan (repayable after more than one year) | 10,000 | |
| 3000 | Ordinary share capital | 100 | |
| 3200 | Profit and loss account brought forward | 17,400 | |
| 4000 | Sales | 180,000 | |
| 5000 | Materials | 62,000 | |
| 7000 | Wages and salaries | 63,000 | |
| 7006 | Employer's National Insurance | 7,000 | |
| 8000 | Depreciation | 3,000 | |
| 8200 | Other costs: rent, insurance, motor, office | 32,000 | |
| 9000 | Corporation Tax charge | 2,500 | |
| Totals | 238,500 | 238,500 |
Mapped to the micro-entity format, these balances give fixed assets of £12,000, current assets of £48,000 (debtors, the director's loan and the bank), prepayments of £1,000, creditors due within one year of £21,000, accruals of £2,000, the bank loan of £10,000 after more than one year, and capital and reserves of £28,000: £100 share capital, £17,400 brought forward and the profit of £10,500.
Check before you upload
| Check | Why |
|---|---|
| The report date is the last day of the financial year | The accounts are made up to the accounting reference date, or a date up to 7 days either side of it |
| Year-end journals are posted | Depreciation, accruals, prepayments, stock and the tax charge belong in the trial balance you export |
| Debits equal credits | If the totals differ, something is wrong in the books; the accounts cannot balance |
| Last year's closing balances are the opening balances | The comparatives must agree with last year's accounts |
| The director's loan account is right | An amount a director owes the company is disclosed in the notes and may lead to s455 tax |
| VAT and PAYE accounts agree to the returns | They are creditors for taxation and social security, which the balance sheet formats show separately |
| Suspense and clearing accounts are empty | A balance there is an unexplained item that has to be cleared before the accounts are prepared |
In a spreadsheet nothing enforces double entry: add a total row and a check cell that shows the difference between debits and credits, and do not upload until it is zero.
Work out any s455 tax on a director's loan with the s455 calculator, and the filing date with the filing deadline calculator.
Common mistakes
- Subtotals and headings in the rows, which then count twice or are read as accounts.
- Signs mixed: a credit balance entered as a positive amount in a single column.
- A closed year: if the profit and loss accounts were already cleared to the reserve, the profit and loss account has no detail; export before the year is closed, or keep the closed-year report.
- Different accounts in the two years: keep the same account codes in this year's and last year's sheets, so the comparatives map the same way.
- Netting: an overdraft netted against a bank balance, or the director's loan netted against other creditors. Keep each account separate; the formats do not allow assets and liabilities to be set off.
Using the export with StatutoryAccounts.io
StatutoryAccounts.io is built to read a trial balance export as Excel, CSV or PDF, suggest the Companies Act line item for each nominal account with AI and let you confirm the mapping. Save the sheet as Excel or CSV. Keep last year's trial balance as well, unless the comparatives come from the accounts filed at Companies House.
Frequently asked questions
What columns does a trial balance need?
An account code if you use one, the account name, and the closing balance as a debit or a credit. A single signed balance column works too.
Should the trial balance include the profit for the year?
No. It lists the income and expense accounts themselves and the reserve brought forward; the profit is worked out from them when the accounts are prepared.
Can I put last year's figures in the same sheet?
You can add them as extra columns, but a separate sheet with the same accounts is clearer and harder to mix up.
Why must debits equal credits?
Every entry in double-entry books has equal debits and credits, so the closing balances must agree. A difference means a missing or one-sided entry.
Sources
Prepare your statutory accounts
Create an account and set up your company from the Companies House register; your first set of accounts is free. The trial balance import and the CT600 are in preparation.
Join the waitlistGeneral information about UK company law and tax, not legal or tax advice. The directors remain responsible for the accounts and returns they approve and file.