Accounts Receivables Aging

How Accounts Receivable Aging Syncs with FedNow® and RTP®

Every RfP transaction is reflected in your AR aging report the moment it’s completed by the payer.

Once a Request for Payment is created and sent using ISO 20022 standards (pain.013, pain.001, etc.), it is routed through the FedNow® or RTP® network. As soon as the payer responds and submits payment, your payee dashboard updates in real time—showing the amount received, time settled, and payer alias.

You can import, upload, and download AR reports filtered by due date, invoice number, alias, and payment status—all in Excel or live dashboard view.

Using RfP Tools for Real-Time A/R Reconciliation

Request for Payments (RfPs) make aging reports dynamic and instant.

Rather than relying on static invoices or delayed batch updates, businesses can automate RfP generation and response tracking. Every RfP includes a hosted payment link for the payer and real-time status updates for the payee. Once payment is received, the entry moves from “outstanding” to “cleared,” with download-ready reporting available for accounting and treasury teams.

Excel files can be downloaded daily, weekly, or monthly—each fully timestamped and reconciled against FedNow® or RTP® transaction records.

To create an automated Accounts Receivable Aging report using Excel, you can leverage formulas and functions to dynamically calculate the aging of your outstanding invoices. Here's a step-by-step guide:

1.     Data Structure:

·        Set up your data with columns such as "Customer Name," "Invoice Number," "Invoice Date," "Due Date," and "Total Amount." Ensure that each row represents a unique invoice.

2.     Calculate Days Overdue:

·        Create a new column for "Days Overdue" and use the following formula to calculate the number of days each invoice is overdue:

excelCopy code

=IF([Due Date Cell]<TODAY(), TODAY()-[Due Date Cell], 0)Date Cell]<TODAY(), TODAY()-[Due Date Cell], 0)

3.     Categorize Aging:

·        Create columns for different aging categories such as "Current," "1-30 Days," "31-60 Days," "61-90 Days," and "Over 90 Days." Use the following formulas to categorize invoices:

·        Current:

excelCopy code

=IF([Days Overdue Cell]=0, [Total Amount Cell], 0)

·        1-30 Days:

excelCopy code

=IF(AND([Days Overdue Cell]>0, [Days Overdue Cell]<=30), [Total Amount Cell], 0)

·        31-60 Days:

excelCopy code

=IF(AND([Days Overdue Cell]>30, [Days Overdue Cell]<=60), [Total Amount Cell], 0)

·        61-90 Days:

excelCopy code

=IF(AND([Days Overdue Cell]>60, [Days Overdue Cell]<=90), [Total Amount Cell], 0)

·        Over 90 Days:

excelCopy code

=IF([Days Overdue Cell]>90, [Total Amount Cell], 0)

4.     Total Outstanding Amount in Each Category:

·        Create a summary table to calculate the total outstanding amount in each aging category using the SUM function.

excelCopy code

=SUMIF([Aging Category Column], ">0", [Total Amount Column])

5.     Conditional Formatting:

·        Apply conditional formatting to highlight overdue invoices or aging categories for better visualization.

6.     Regularly Update Data:

·        Keep the data updated by adding new invoices and marking payments received.

7.     Automate with Macros (Optional):

·        If you're comfortable with Excel VBA, you can create a macro to automate the process of updating and categorizing invoices.

8.     Review and Analysis:

·        Regularly review the aging report to analyze the status of your accounts receivable and make informed business decisions.

By setting up these calculations and categories, your Accounts Receivable Aging report will dynamically update as you enter new data, providing you with an up-to-date view of your outstanding invoices.

Track, Reconcile, and Report—All in Real Time at TodayPayments.com

Tired of waiting for your receivables to catch up?

✅ Create and send Request for Payments using FedNow® and RTP®
✅ Access real-time Excel reports for aging receivables
✅ Get instant bank reconciliation—free for all clients
✅ Track payments by alias, MID, division, or payer
✅ Automate uploads, downloads, and status updates
✅ Eliminate delays, chargebacks, and manual reconciliations

Sign up at https://www.TodayPayments.com
Accounts Receivable Aging – Powered by Real-Time Payments, Delivered with Zero Delay.