Aged Receivable/Payable Spill PRO

On this Page:
 Total Age Rec./Payable
 Drill Down
 Age Rec./Payable

Using AgeDebtTotalSpill() and AgeContactDebtSpill() functions hot link age debt analysis for cashflow predictions.

You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.

   The Wizard "Age Debt/Forc. Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard and then customise it.

Aged Customer Receivable/Vendor Payable (Pro)

The following functions get Aged Customer Receivable/Vendor Payable Aged Analysis. This function will also list the value of transactions in Draft/Not Authorised (Xero 'Awaiting Approval' Status)/Committed status used for Cash Flow analyisis (Xero Approved Status).

Example:

if AgeDays=7, PrePeriods is 2 and NoPeriods=6 then we get:

Age Debt and Forecast Debt

First line is Age Debt aged in 7 day Periods, with 2 periods shown before due date
The second is a Forecast based on the either Contact Average Pay days from History
Or overall company average

Syntax: =CXL.AgeDebtTotalSpill( CompCode, [Type], [AgeDays], [NoPeriods], [Options],[PrePeriods] )
Performance Rated:A+

   For a tutorial on this function, click here

Example

Get Age Debt Summary for COMP_A:
  age by 30 days, in 6 cols: =CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 30, 6 )
  age by 10 days, in 8 cols: =CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 10, 8, 2 )
And included 2 columns before due date aging
  For only Xero Draft trans: =CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 10, 8, 2, "Draft" )
And included 2 columns before due date aging
Get Agee Payable Summary for COMP_A:
  age by 30 days, in 6 cols: =CXL.AgeDebtTotalSpill( "COMP_A", "Payable", 30, 6 )

   Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company
Type Optional
Defaults to get Receivable
"Payable" returns Payable
"Receivable" returns Receivable
[AgeDays] Optional
Defaults to 30
Sets the age in days of each column (NoPeriods and PrePeriods)
[NoPeriods] Return x Periods, the Period duration is set in company settings
Hence if 7 days, MaxPeriods=6, gives break down of the next 6 weeks, with the last period being every thing >= 6 weeks
Click here for an explaintion of these periods.
max value 42.
[Options] Optional - Comma delimited list
"NoHeading" removes headings
"NoTitle" removes Title in first row
"Submitted" lists Total for Submitted(Xero status) Transactions
"Draft" lists Total for Draft(Xero status) Transactions
"Committed" lists Total Outstanding Transactions
"All" lists Totals for Committed,Submitted,Draft
"HideForecast" removes forecast lines
[PrePeriods] Optional
1..6 break down of periods before due date
so 1 gives week before, 2 gives 2 weeks before...

Age Debt Total Drill Down (Pro)

Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts:

Age Debt and Forecast Debt

Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the Debt and the forecast Debt payments in a chart.
The "Trans" menu shows all outstanding transactions for the current selected period.
Clicking on the chart columns wil drill down to the transactions in each period.

Aged Customer Receivable/Vendor Payable (Pro)

The following functions get Committed Aged Customer Receivable/Vendor Payable by Customer/Vendor table broken down for a single company.

Age Debt and Forecast Debt

Contact Age Debt aged in 7 day Periods, with 2 periods shown before due date
If type is set to "ForecastReceivable" then gives forecast pay dates based on Contact average
Or overall company average

Syntax: =CXL.AgeContactDebtSpill( CompCode, [Type], [AgeDays], [NoPeriods], [Options],[PrePeriods], [Filter] )
Performance Rated:A+

   For a tutorial on this function, click here

Example

Aged Debtors for Customers for COMP_A:
  age by 30 days, in 6 cols: =CXL.AgeContactDebtSpill( "COMP_A", "Receivable", 30, 6 )
  age by 10 days, in 8 cols: =CXL.AgeContactDebtSpill( "COMP_A", "Receivable", 10, 8, "", 2 )
And included 2 columns before due date aging
et for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1":
  age by 30 days, in 6 cols: =CXL.AgeContactDebtSpill( "COMP_A", "Payable", 30, 6, "",0,"Vendor1" )

   Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company
Or a list to get a list of all GL codes from multiple companies.
[Type] Optional
Defaults to get Receivable
"Payable" returns Payable
"Receivable" returns Receivable
"ForecastReceivable" returns Forecast Receivable
"ForecastPayable" returns Forecast Payable
[AgeDays] Optional
Defaults to 30
Sets the age in days of each column (NoPeriods and PrePeriods)
[NoPeriods] Return x Periods, the Period duration is set in company settings
Hence if 7 days, MaxPeriods=6, gives break down of the next 6 weeks, with the last period being every thing >= 6 weeks
Click here for an explaintion of these periods.
max value 42.
[Options] Optional - Comma delimited list
"NoHeading" removes headings
"NoTitle" removes Title in first row
"SortByCode" Sort By Code
"SortByName" Sort By Name
"SortByOldest" Sort By Oldest Debt
"SortBySlowest" Sort By Slowest payer based on avg. days
"FilterContact" Filter by Contact code in Filter

"ShowInCurrency" Show all Contacts in Currency
"FilterCurrency" Filter by Contacts to Currency entries only
[PrePeriods] Optional
1..6 break down of periods before due date
so 1 gives week before, 2 gives 2 weeks before...
[Filter] Optional
if Options contains "FilterContact" then Filter = Contact Code
it will then return both All contact data.

OutStanding "Receivable" or "Payable" Transactions (Pro)

The following function will get OutStanding "Receivable" or "Payable" Transactions for a single company.

Syntax: =CXL.ContactTransSpill( "COMP_A", "Receivable" )

Syntax: =CXL.ContactTransSpill( "COMP_A", "Payable" ) Performance Rated:A

   more about Transaction Spill

Example

Get for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1":
  Outstanding Transaction list: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 0 )
  Transaction list from year 2026: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026 )
By default returns 5 years of data from financial year 2026
  only 24 periods back: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 24 )
  restrict to last 50 transactions: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 0, 50 )
All Outstanding Transactions
  All Debts/Receivables: =CXL.ContactTransSpill( "COMP_A", "Receivable" )
  All Creditors/Payable: =CXL.ContactTransSpill( "COMP_A", "Payable" )

   Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.