XO.PROFIT
Get Net Income (Profit/Loss) from the P&L report for a date range. Returns the bottom line from your Profit & Loss statement. Optionally filter by tracking categories.
Syntax
Parameters
| Parameter | Required | Description |
|---|---|---|
| org_id | Yes | Organization ID from XO.ORG() |
| start_date | Yes | Period start date. Must be a cell reference containing a date (e.g., C1) or an Excel serial date number. Typed text dates like '2024-01-01' will cause an error. |
| end_date | Yes | Period end date. Must be a cell reference containing a date (e.g., D1) or an Excel serial date number. This ensures dates work correctly across all locale settings. |
| category1 | No | First tracking category name |
| option1 | No | First tracking option |
| category2 | No | Second tracking category name |
| option2 | No | Second tracking option |
Not sure of the exact category or option names? List them with XO.TRACKING or insert the Tracking Categories table.
Returns
- Positive number = Profit (revenue exceeds expenses)
- Negative number = Loss (expenses exceed revenue)
- Zero = Break-even
Date Range Limit
The date range cannot exceed 365 days (Xero API limitation on P&L reports). The formula wizard blocks longer ranges, and the function returns a #N/A error if the limit is exceeded.
Examples
Total profit for period:
=XO.PROFIT(A2, C1, D1)Gets total net income for the date range in C1 to D1.
Profit by region:
=XO.PROFIT($A$2, C1, D1, "Region", "North")Profit by region and department:
=XO.PROFIT(A2, C1, D1, "Region", "North", "Department", "Sales")Use Cases
Dashboard KPIs
Monthly Profit: =XO.PROFIT(A2, DATE(2025,1,1), DATE(2025,1,31))YTD Profit: =XO.PROFIT(A2, DATE(YEAR(TODAY()),1,1), TODAY())Profit Margin Calculation
Revenue: =XO.BALANCE(A2, "4000", C1, D1)Profit: =XO.PROFIT(A2, C1, D1)Margin %: =B3/B2*100Using [none] as a Tracking Option
You can pass [none] as a tracking option value to retrieve net income for transactions that have no tracking category assigned:
=XO.PROFIT(A2, C1, D1, "Region", "[none]")Returns net income for transactions where no Region tracking option is assigned.
- Calculates: Total Net Income minus Sum of all tracked option Net Incomes
- Useful for finding untracked P&L impact across your organization
- Works with both category1/option1 and category2/option2 parameter pairs (one dimension at a time)
[none]usually uses one grouped report call. Additional calls are needed when that report cannot be used.
Troubleshooting
If you see an error, check the troubleshooting guide. XO Report shows standard Excel errors (#VALUE!, #N/A) for connection and data problems. To show a blank or your own message instead of an error, wrap the formula in IFERROR. Hover over the cell to see the specific message.
For a complete overview of building Xero reports in Excel, including how this function fits into a full workflow, see our complete Xero to Excel guide.