This article covers every formula available in ExtendInsights Financial Reporting. All formulas begin with the XAVI. prefix and pull live data from your connected NetSuite account.
Important Note About Dates
Dates are passed as parameters to XAVI formulas. We wrote an entire article on how XAVI formulas work with dates. It covers the basics as well as how to handle custom fiscal calendars (4-4-5, 4-5-4, etc.).
📋 TL;DR
XAVI period arguments accept an Excel date, text like
Jan 2025, or your period name.Point all period arguments at a single year cell and use
DATE($B$1, month, 1)so you only change one cell to roll to a new year.If your company uses a custom fiscal calendar (4-4-5, 4-5-4, etc.), use period names as dates, and month names won't resolve correctly.
Get your period names fast via Bulk Add Periods and GL in the CloudExtend Excel add-in
XAVI Formula Reference
XAVI.BALANCE
The most used formula, XAVI.BALANCE, returns the GL account balance for a specific period or date range. This is the core formula for building financial reports.
Syntax
For Income, COGS, Expense Accounts
=XAVI.BALANCE(account, fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook],[customSegment])
For Balance Sheet Accounts
=XAVI.BALANCE(account, fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook],[customSegment])
⚠️ Important Note About Balance Sheet Accounts
The From Period should always be skipped (replace it with a comma) since Balance Sheet Accounts sum from the beginning of time.
Parameters
Parameter | Required | Description | Example |
| Yes | Account number or wildcard pattern |
|
| Yes | Start period |
|
| Yes | End period |
|
| No | Subsidiary name or ID |
|
| No | Department name or ID |
|
| No | Location name or ID |
|
| No | Class name or ID |
|
| No | Accounting book ID |
|
| No | Custom segment name |
|
Examples
Single month
=XAVI.BALANCE("4010", "Jan 2025", "Jan 2025")
Full year
=XAVI.BALANCE("4010", "Jan 2025", "Dec 2025")
All revenue accounts (wildcard)
=XAVI.BALANCE("4*", "Jan 2025", "Dec 2025")
Q1 2025 for a specific subsidiary
=XAVI.BALANCE("4010", "Jan 2025", "Mar 2025", "Celigo Inc.")
Q1 2025 for a specific subsidiary and custom segment
=XAVI.BALANCE("4010", "Jan 2025", "Mar 2025", "Celigo Inc.",,,,,"CS Test 1")
Balance Sheet Account for balance at the end of January 2025
=XAVI.BALANCE("10010",,"Jan 2025")
Dynamic with cell references
=XAVI.BALANCE(A2, B$1, B$1, $P$3, $Q$3, $R$3, $S$3)
XAVI.BALANCECURRENCY
Returns the GL account balance with explicit currency control. Use this for multi-currency consolidation scenarios where you need to specify the target currency.
Syntax
=XAVI.BALANCECURRENCY(account, fromPeriod, toPeriod, subsidiary, currency, [department], [location], [class], [accountingBook],[customSegment])
Parameters
Parameter | Required | Description | Example |
| Yes | Account number or wildcard pattern |
|
| Yes | Start period |
|
| Yes | End period |
|
| No | Subsidiary name or ID |
|
| No | Currency code for consolidation |
|
| No | Department name or ID |
|
| No | Location name or ID |
|
| No | Class name or ID |
|
| No | Accounting book ID |
|
| No | Custom segment name |
|
Important: Note that currency is in position 5, between subsidiary and department. This differs from XAVI.BALANCE.
Examples
India subsidiary balance reported in USD
=XAVI.BALANCECURRENCY("60010", "Jan 2025", "Jan 2025", "Celigo India Pvt Ltd", "USD")
All subsidiaries reported in EUR
=XAVI.BALANCECURRENCY("60010", "Jan 2025", "Mar 2025", "", "EUR")
Dynamic formula with cell references
=XAVI.BALANCECURRENCY($A5, C$4, C$4, $M$2, $O$2)
Why You Can't Convert to Just Any Currency
It's natural to expect the currency parameter to work like a general-purpose FX converter — pass any ISO code and get a translated balance. That is not what it does. XAVI.BALANCECURRENCY translates balances using NetSuite's consolidated exchange rates, the same rates NetSuite itself uses to build consolidated financial statements, so your Excel report always ties out to what NetSuite reports.
In NetSuite OneWorld, consolidated exchange rates only exist along the subsidiary hierarchy. For each accounting period (and accounting book), NetSuite stores a rate record between a child subsidiary and its parent, and derives indirect rates further up the chain — for example, the grandchild-to-parent rate is the grandchild-to-child rate multiplied by the child-to-parent rate. NetSuite maintains no consolidated rate between a subsidiary and any currency outside its ancestor line. If no subsidiary above (say) your UK entity uses AUD as its base currency, there is simply no consolidated rate available to translate that entity into AUD for the period.
This is why the currency must be a valid consolidation root: the base currency of the filtered subsidiary itself, or of a subsidiary above it in the hierarchy (its parent, grandparent, and so on up to the top-level consolidated parent).
Why doesn't XAVI fall back to NetSuite's regular currency exchange rate table? Because consolidation translation is not a single spot rate. NetSuite applies a different consolidated rate type depending on the account: Current (period-end) for most balance sheet accounts, Average for income statement accounts, and Historical for equity accounts — with the differences flowing to the Cumulative Translation Adjustment (CTA). Translating with one generic rate would produce balances that don't reconcile to NetSuite's consolidated reports, which is exactly the guarantee XAVI.BALANCECURRENCY is designed to provide.
⚠️ Requesting a currency that is not a valid consolidation root for the filtered subsidiary returns INV_SUB_CUR (balance = 0).
📖 Learn more in NetSuite's documentation: Consolidated Exchange Rates, Consolidated Exchange Rate Types, and Viewing Consolidated Exchange Rates.
💡Tip
The best way to see which currencies are available for a subsidiary is to:
Use the task pane, then go to Filters.
Select a Subsidiary Context, and then select a currency to see which currencies are available for the BalanceCurrency formula.
BALANCECURRENCY applies NetSuite's consolidation logic at the presentation layer. It does not override transaction posting logic or introduce alternate FX assumptions.
XAVI.BUDGET
Returns the budget amount for an account and period. Supports wildcards and all the same-dimensional filters as XAVI.BALANCE.
Syntax
=XAVI.BUDGET(account, fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [budgetCategory])
Examples
Annual budget for account 5000
=XAVI.BUDGET("5000", "Jan 2025", "Dec 2025")
Sum of all expense budgets
=XAVI.BUDGET("6*", "Jan 2025", "Dec 2025")
Q1 budget for Sales department
=XAVI.BUDGET("5000", "Jan 2025", "Mar 2025", "Celigo Inc.", "Sales")//
Budget vs. Actual comparison
=XAVI.BALANCE("6*", "Jan 2025", "Jan 2025") - XAVI.BUDGET("6*", "Jan 2025", "Jan 2025")
XAVI.NAME
Returns the account name for a given account number. Useful for building dynamic report labels.
Syntax
=XAVI.NAME(account)
Example
=XAVI.NAME("4010") → "Product Revenue"
XAVI.TYPE
Returns the account type (Income, Expense, Bank, etc.) for a given account number.
Syntax
=XAVI.TYPE(account)
Examples
=XAVI.TYPE("4010") → "Income"
=XAVI.TYPE("1000") → "Bank"
XAVI.PARENT
Returns the parent account number for a sub-account. Useful for navigating account hierarchies.
Syntax
=XAVI.PARENT(account)
Example
=XAVI.PARENT("4010-1") → "4010"XAVI.TYPEBALANCE
Returns the total balance for all accounts of a specific account type or special account type. The CFO Flash Report uses this formula and is ideal when you want to summarize by financial category rather than by account number.
For full documentation on XAVI.TYPEBALANCE, including account type values, special account type values, and examples, see the dedicated XAVI.TYPEBALANCE Reference article.
Special Formulas
These formulas calculate values that NetSuite computes dynamically — they are not stored as standard account balances.
Formula | Purpose | Supports Custom Segment | Example |
| Cumulative P&L through prior year-end | Yes |
|
| Net Income for a period range | Yes |
|
| Cumulative Translation Adjustment | No |
|
⚠️ Important note on XAVI.NETINCOME
This formula always computes a period range. If you supply only one period, XAVI treats it as a single-period range (from = to), returning only that month's P&L — which will typically not match Balance Sheet Net Income. To match year-to-date Net Income, always pass both periods explicitly:
=XAVI.NETINCOME("Jan 2025", "Feb 2025")
Error Codes
Error | Formula | Meaning |
| XAVI.BALANCE | Account is not the expected type for this formula |
| XAVI.BALANCE | Account number doesn't exist in NetSuite |
| Any | NetSuite query timed out |
| XAVI.BALANCECURRENCY | Invalid currency/subsidiary combination |
See Also
Need Help?
🔍 Visit the Help Center: https://www.cloudextend.io/support/
💬 Use the in-app chat
✉️ Email cloudextend-support@celigo.com