Home / Report Templates / Aged Payables Reports for Xero in Excel
The Aged Payables Reports for Xero in Excel is a simple dashboard that allows you to report and reconcile money you owe to your suppliers.
It reproduces much of the functionality available in the Aged Payables Summary and Aged Payables Detail report in Xero directly within Excel.
This report can also consolidate data from multiple Xero Organisations (data files) when using the Consolidation OData Feed.
While this report a fully working and standalone report, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.
Also available are Aged Payables Reports for Xero in Power BI for those that prefer using Power BI. There is also an Aged Receivables Reports for Xero in Power BI and an an Aged Receivables Reports for Xero in Excel which allows you to reconcile your outstanding customer invoices.
Reports Includes
Consolidated Payables Summary
This report provides the aged payables summary for multiple data files when using a Consolidation OData Feed. Each company will be listed separately down the page.
Payables Summary by Months
This report provides the aged payables summary for all suppliers.
Payables Detail by Months
This report provides the aged payables detail for all suppliers. It lists all supplier invoices outstanding.
Payables Detail by Months (%)
This report provides the aged payables detail for all suppliers. Rather than showing the dollar value, it instead shows the percentage split of the outstanding amount.
Payables Summary by Days
This report provides the aged payables summary for all suppliers. This report is similar to the Payables Summary by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.
Payables Detail by Days
This report provides the aged payables detail for all suppliers. This report is similar to the Payables Detail by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.
Requirements
To use the report, you will need to:
- Have an OdataLink Subscription or Trial Account.
- Connect OdataLink to your Xero Organisation.
- Configure a Model that includes the following endpoints:
Download and Setup
We provide 2 versions of the reports to download.
Aged Payables Reports For Xero
This version of the report allows you to report on your payables as at any date required. This does come at a small increase in performance as additional data must be downloaded.
Download the Aged Payables Reports For Xero
Aged Payables Reports For Xero (Current)
This version of the report allows you to report on your payables as of right now. It only looks at currently outstanding invoices and as such does not need to download a lot of information out of Xero.
Download the Aged Payables Reports For Xero (Current)
Once downloaded, you can connect it to your own data using this article.
How to Refresh the reports
To refresh the reports, follow these steps.
- Go to the Inputs sheet to enter the Parameters driving this report.
- Enter the As At Date for which to calculate the age.
(this parameter does not apply to the Aged Receivables Reports For Xero (Current) report) - Choose whether to calculate the age using the Invoice Date or the Due Date.
- Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
- Select a Worksheet and use the Filters to display the data you want.
Find an issue with the template?
Let us know of any issues you find with the template. We built it to use the least amount of data possible but may have this might not suit everyone.