Skip to main content

XAVI Formula Reference

ExtendInsights FRE | Complete reference for all supported XAVI formulas, including BALANCE, BALANCECURRENCY, BUDGET, NAME, TYPE, PARENT, RETAINEDEARNINGS, NETINCOME, CTA, and TYPEBALANCE

This article covers every formula available in 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.).

LEARN MORE ABOUT DATES

📋 TL;DR

  • XAVI period arguments accept an Excel date, text like Jan 2025, or a numeric Internal ID — Internal IDs are the most reliable.

  • 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 Internal IDs — dates and month names won't resolve correctly.

  • Get your Internal IDs fast via Bulk Add Periods and GL in the CloudExtend Excel add-in.

  • Best workbook pattern: put Internal IDs in a hidden Row 1, period display names in visible Row 2, then use =XAVI.BALANCE($A3, B$1) — write once, fill everywhere. Hide, don't delete, Row 1.

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])

For Balance Sheet Accounts

=XAVI.BALANCE(account, fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook])

⚠️ 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

account

Yes

Account number or wildcard pattern

"4010" or "4*"

fromPeriod

Yes

Start period

"Jan 2025"

toPeriod

Yes

End period

"Dec 2025"

subsidiary

No

Subsidiary name or ID

"Celigo Inc."

department

No

Department name or ID

"Sales"

location

No

Location name or ID

"US"

class

No

Class name or ID

"Enterprise"

accountingBook

No

Accounting book ID

"1"

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.")

Dynamic with cell references

=XAVI.BALANCE(A2, B$1, B$1, $P$3, $Q$3, $R$3, $S$3)

Balance Sheet Account for balance at the end of January 2025

=XAVI.BALANCE("10010",,"Jan 2025")

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])

Parameters

Parameter

Required

Description

Example

account

Yes

Account number or wildcard pattern

"60010"

fromPeriod

Yes

Start period

"Jan 2025"

toPeriod

Yes

End period

"Dec 2025"

subsidiary

No

Subsidiary name or ID

"Celigo India Pvt Ltd"

currency

No

Currency code for consolidation

"USD", "EUR", "GBP"

department

No

Department name or ID

"Sales"

location

No

Location name or ID

"US"

class

No

Class name or ID

"Enterprise"

accountingBook

No

Accounting book ID

"1"

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)

⚠️ Not all currency/subsidiary combinations are valid. The currency must be a valid consolidation root for the filtered subsidiary. Invalid combinations return INV_SUB_CUR (balance = 0).

💡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. This formula is used by the CFO Flash Report 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

Example

XAVI.RETAINEDEARNINGS

Cumulative P&L through prior year-end

=XAVI.RETAINEDEARNINGS("Dec 2024")

XAVI.NETINCOME

Net Income for a period range

=XAVI.NETINCOME("Jan 2025", "Mar 2025")

XAVI.CTA

Cumulative Translation Adjustment

=XAVI.CTA("Dec 2024")

⚠️ 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

INVALIDACCT

XAVI.BALANCE

Account is not the expected type for this formula

NOTFOUND

XAVI.BALANCE

Account number doesn't exist in NetSuite

TIMEOUT

Any

NetSuite query timed out

INV_SUB_CUR

XAVI.BALANCECURRENCY

Invalid currency/subsidiary combination

See Also

Need Help?

Related Articles
End-Users: Getting Started with Financial Reporting
Using Wildcards for Summary Reports
Filtering by Subsidiary, Department, Class, and Location
Pre-Built Reports
FAQ: Financial Reporting Engine Common Questions
Did this answer your question?