How to Run a Cash Basis Trial Balance in Xero

I love Xero, but like all great love stories, there are a few things about it that drive me crazy.

One of those things is Xero’s Trial Balance report.

Sadly, there’s no option to run the Trial Balance report on a cash basis.

Xero's Trial Balance report doesn't have a cash basis option

This presents a challenge for U.S. tax accountants and the bookkeepers who have to work with them. The vast majority of small businesses file on a cash basis, yet we can only produce an accrual basis trial balance report.

Fortunately, there’s a simple workaround.

Here’s how to produce a cash basis trial balance in Xero:

Where to find the General Ledger report

Settings to use on the General Ledger Report

  1. Go to Reports > All Reports

  2. Select General Ledger

  3. Use the following settings:

    1. Dates: Select the appropriate fiscal year date range. In this example, I’m using a 2017 calendar year.

    2. Sort by: Account Code

    3. Check the Cash Basis and Show YTD boxes.

  4. Select Update

You should be looking at a General Ledger Summary report with six columns.

The YTD Balance column represents the cash basis trial balance values. Positive values are debits, and negative values are credits.

The Opening Balance column represents the trial balance at the beginning of the period, which you could use to make a comparative trial balance report.

The YTD Balance column represents the trial balance

To separate the trial balance into separate debit and credit columns, use Excel or Google Sheets.

Select Export on the bottom right of the screen to view the report in Google Sheets.

Add columns for debits and credits, and use a formula to populate it.

You can separate the YTD Balance columns into Debit and Credit columns in Excel or Google Sheets

In this case, I used:

  • H8: =IF(F8<0,"",F8)
  • I8: =IF(F8>0,"",-F8)

And then I copied the formula down the rows.

Let’s hope Xero upgrades the Trial Balance report soon to enable cash basis.

If you’d like to add your voice to this feature request, head over to the Xero Business Community topic, “Trial Balance - Option to run on a cash basis” to add your vote and comments.

Blake Oliver