Why do I have to repeat the as-of date in both start and end arguments for Balance Sheet accounts?

XO.BALANCE shares one signature across P&L and Balance Sheet accounts because the underlying Xero report endpoint expects a date range. For Balance Sheet accounts (Asset, Liability, Equity), Xero ignores the start date and returns the balance AS OF the end date. Repeating the as-of cell in both positions makes the intent explicit and keeps the formula stable when copy-pasted between P&L and Balance Sheet contexts.

Can I generate Balance Sheets for multiple organizations at once?

Yes. Drop =XO.ORG() into a setup cell to spill every connected organization, then place one column per entity in your workbook. Each XO.BALANCE call accepts a different org reference as its first argument, so a multi-entity Balance Sheet is a matter of dragging the formula across columns. The cross-entity SUM is just Excel arithmetic — XO Report supplies the data layer.

My Balance Sheet workpaper needs drillable line items — can I open the source rows?

For drill-down to individual transactions, open the inserted task pane Balance Sheet report and click into the figures from Xero’s standard report viewer — every row preserves the link to the source transactions. The cell-level XO.BALANCE formula returns the aggregate balance; transaction-level detail stays inside Xero.

Does the Balance Sheet handle period-end versus interim dates differently?

No — Xero treats every as-of date the same way. The Balance Sheet computes balances by aggregating posted transactions up to and including the date you provide. You can pull a 31 December snapshot, a 15-March mid-period snapshot, or yesterday — the API call is identical and the report respects whatever as-of date you pass.

How do I find the account code I need to reference in XO.BALANCE?

Use =XO.COA(org_id) to spill the full chart of accounts into a sheet — column 3 is the account code, column 4 is the name, column 6 is the account class. Filter for ASSET, LIABILITY, or EQUITY to find Balance Sheet accounts. Alternatively, look up a known code with =XO.LACCOUNT($A$2, "1100", 0) (property 0 returns the name) to confirm you have the right account.

Can I build a 12-month rolling Balance Sheet trend in one sheet?

Yes. Place month-end dates across a row (Jan-end, Feb-end, …) and a column of account codes down the left. Each cell becomes =XO.BALANCE($A$2, $B3, C$1, C$1) with the date and account references locked in the appropriate axes. Drag the formula across the grid and you have a live 12-month snapshot table that refreshes any time you click Refresh in the task pane.

Xero Balance Sheet in Excel — Live Add-In

What it is

The Balance Sheet is the point-in-time snapshot of what a business owns and owes — assets on one side, liabilities and equity on the other, always equal. It answers questions Profit & Loss cannot: how much cash is on hand, what does the receivables ledger look like as of period end, how much debt has been taken on, what is retained earnings carrying from prior years.

Auditors, banks, and boards read the Balance Sheet alongside the P&L — and in many firms it is the primary input to covenant testing, cash forecasting, and capital allocation decisions. The trouble is that Xero produces the Balance Sheet for one as-of date at a time, and meaningful analysis usually needs a sequence of snapshots side by side.

Why analysts want it in Excel

Excel is where the Balance Sheet becomes useful for management. Once it is live in a workbook, you can compare period-end positions across months, surface trends in working capital, build coverage and liquidity ratios, and feed cash-flow forecasts that reference the actual closing balances. None of that exists inside Xero's standard viewer.

Live Balance Sheets also unlock multi-entity views. A group CFO can place each entity in a column, sum to a consolidated total, and apply FX adjustments — all from the same workbook that the audit team gets every quarter. Period-end packs that used to require ten separate exports now refresh in seconds from a single Refresh button.

For lenders, the same live workbook becomes the covenant pack. Debt service ratios, current ratios, and net working-capital figures all reference the same XO.BALANCE cells, so the next reporting cycle is a re-run rather than a rebuild. Stale figures stop creeping into investor updates because every refresh re-anchors the workbook against the live ledger.

Why the manual method breaks

Generate the Balance Sheet for each as-of date in Xero, export, copy into Excel, line up the row labels — repeat for every period and every entity.
Account renames or chart-of-accounts changes silently break the manual mapping; row labels stop matching across periods.
Post-close adjustments and late journals do not propagate to the exported workbook — what you presented last month no longer matches the live ledger.
Multi-entity groups multiply the work linearly: ten subsidiaries means ten exports per period.
Cash balances and receivables totals on the workbook lag the actual position by the time between export and presentation — usually days.

The live add-in method

XO Report covers Balance Sheets two ways. Pick the surface that matches your workbook shape.

Primary: insert the full Balance Sheet via the task pane. Open Tables and Reports, choose Balance Sheet (it is an As of Date report — pick a single date), and click Insert. The full Balance Sheet lands on a new sheet named “BalanceSheet”, with assets, liabilities, equity, and the balancing totals exactly as Xero presents them. Add comparison columns on the configuration step if you need multi-period side-by-side snapshots.

Complementary: pull individual account balances with XO.BALANCE. For dashboard tiles — Cash, Accounts Receivable, Accounts Payable, total debt — the cell-level formula returns the balance of a single GL account as of a date. This is the right tool when you want a tightly composed dashboard rather than the full report block.

# Single account balance as of the date in $E$1
=XO.BALANCE($A$2, "1000", $E$1, $E$1)

# Dashboard tiles for Cash, AR, AP
| Account             | Code | Balance as of $E$1                       |
| Cash                | 1000 | =XO.BALANCE($A$2, "1000", $E$1, $E$1)    |
| Accounts Receivable | 1100 | =XO.BALANCE($A$2, "1100", $E$1, $E$1)    |
| Accounts Payable    | 2000 | =XO.BALANCE($A$2, "2000", $E$1, $E$1)    |

# For Balance Sheet accounts (Asset, Liability, Equity), XO.BALANCE
# returns the balance AS OF end_date — repeat the date cell in both
# start and end positions.

XO.BALANCE takes four required arguments: org_id (lock with $A$2 so the formula can be dragged across cells), the GL account code as a string in quotes ("1000"), a start date, and an end date.

For Balance Sheet accounts (Asset, Liability, Equity), Xero returns the balance as ofthe end date — the start date is effectively ignored. The convention is to pass the same as-of date twice, both into the start and end argument; that keeps the formula consistent with P&L use cases (where the date range matters) and makes the intent obvious in audit review.

Account codes are strings, not numbers — Xero allows alphanumeric codes (e.g., "201A") and using string quotes is the only way to support those without an Excel auto-convert stripping the leading zeroes. To discover an account code or its type, use =XO.LACCOUNT($A$2, "1100", 1) (property 1 returns the account type per FUNCTIONS.md) or =XO.COA($A$2) to spill the full chart.

Sign convention is automatic: assets show as positive when they hold a normal debit balance; liabilities show as positive when they hold a normal credit balance. You do not need to multiply by minus one or wrap in ABS().

Related docs

Frequently asked questions

Why do I have to repeat the as-of date in both start and end arguments for Balance Sheet accounts?: XO.BALANCE shares one signature across P&L and Balance Sheet accounts because the underlying Xero report endpoint expects a date range. For Balance Sheet accounts (Asset, Liability, Equity), Xero ignores the start date and returns the balance AS OF the end date. Repeating the as-of cell in both positions makes the intent explicit and keeps the formula stable when copy-pasted between P&L and Balance Sheet contexts.
Can I generate Balance Sheets for multiple organizations at once?: Yes. Drop =XO.ORG() into a setup cell to spill every connected organization, then place one column per entity in your workbook. Each XO.BALANCE call accepts a different org reference as its first argument, so a multi-entity Balance Sheet is a matter of dragging the formula across columns. The cross-entity SUM is just Excel arithmetic — XO Report supplies the data layer.
My Balance Sheet workpaper needs drillable line items — can I open the source rows?: For drill-down to individual transactions, open the inserted task pane Balance Sheet report and click into the figures from Xero’s standard report viewer — every row preserves the link to the source transactions. The cell-level XO.BALANCE formula returns the aggregate balance; transaction-level detail stays inside Xero.
Does the Balance Sheet handle period-end versus interim dates differently?: No — Xero treats every as-of date the same way. The Balance Sheet computes balances by aggregating posted transactions up to and including the date you provide. You can pull a 31 December snapshot, a 15-March mid-period snapshot, or yesterday — the API call is identical and the report respects whatever as-of date you pass.
How do I find the account code I need to reference in XO.BALANCE?: Use =XO.COA(org_id) to spill the full chart of accounts into a sheet — column 3 is the account code, column 4 is the name, column 6 is the account class. Filter for ASSET, LIABILITY, or EQUITY to find Balance Sheet accounts. Alternatively, look up a known code with =XO.LACCOUNT($A$2, "1100", 0) (property 0 returns the name) to confirm you have the right account.
Can I build a 12-month rolling Balance Sheet trend in one sheet?: Yes. Place month-end dates across a row (Jan-end, Feb-end, …) and a column of account codes down the left. Each cell becomes =XO.BALANCE($A$2, $B3, C$1, C$1) with the date and account references locked in the appropriate axes. Drag the formula across the grid and you have a live 12-month snapshot table that refreshes any time you click Refresh in the task pane.

Xero Balance Sheet in Excel