Automatically Calculate Receivable Payment Due Date Based on Invoice and Payment Terms (Excel /Ragic Formula Tutorial)

In B2B transactions, payments are usually not collected at the time of transaction. This gives the customer convenience and allows for a turnaround time. Instead, these "payments" are recorded as Accounts Receivables, with a future collection date set based on agreed terms by both parties (eg. net 30 days).

It is crucial for companies to keep track of payment due dates and know when follow up is necessary. This helps prevent delayed payments that could disrupt the company's cash flow and financial stability.

If you've handled receivables before, you probably already know that the calculation for due date is often more complex than simply adding N number of days. For instance, if our shipment date is March 16, and we have a net 30 term, the payment would be due on April 15. However, the calculation may be more complex. In some cases, the payment due date must align with the company's fixed "cutoff date" and "collection date."

In this article, we'll cover the calculation of payment due dates using Excel formulas. As a bonus, we'll also show you how to apply these formulas in Ragic to make receivable management more flexible. Ragic is a tool that is similar to Excel, except that it is more efficient for building data relationships. Its subtables make it easier to list purchased items, and you can set up automatic reminders to notify customers of their payment due dates via email. If these features might benefit your business, feel free to give Ragic a try. If you're already a Ragic user, this tutorial will be useful for you.

Let's dive in and see how to reduce manual work and errors by automating due date calculations using Excel and Ragic!

Definition of Terms

Before we start writing formulas, we need to first understand the commonly used terminologies when calculating payment due dates to make sure we're on the same page. Understanding these terms will also help us understand the logic behind the calculations.

Invoice Date

The invoice date is the date when the company issues the invoice. This is typically the date when goods are shipped or services are completed, essentially marking the completion of the transaction. This date serves as a key reference for calculating the payment due date for accounts receivables, since customers' payment terms are usually based on this date.

Cutoff Date

The cut off date is the date your company regularly settles accounts, usually set on a specific day of each month (eg. The 25th day of each month). If the invoice date falls before the cut off date, the payment will be included in the current month's accounts. If the invoice date is after the cut off, it will be carried over to the next month's accounts.

Payment Terms

The buyer and the seller will typically agree on specific payment terms, which dictate how and when the payment should be made. Common payment terms include net 30, 60, 90, or 120 days. The payment due date for Accounts Receivable is calculated based on the agreed payment period.

Collection Date

To simplify account management, companies often set a fixed collection date each month (eg. End of each month). If the both parties agree to this, the date will serve as the payment date for the receivable amount.

3 Methods of Calculating the Payment Due Date

There are three commonly used methods in calculating Accounts Receivable due date:

1. Invoice Date + Days

In this scenario, the cutoff date is equal to the end of the month, and there is no unified collection date.

This is the most intuitive method of calculating for the payment due date, as it is based on the invoice date and payment terms. For example, if the invoice date is on July 11 and the payment terms is 30 days, then the due date will be on August 10.

2. Fixed Cutoff and Collection Date (Most Common)

In order to simplify accounting, many companies set a fixed cutoff date (eg. 25th of every month) and a fixed collection date for their accounts payable to customers. The fixed collection date can be a certain day of each month (eg. 5th of every month) or the end of the month, which are the most common industry practices.

Taking the diagram above as an example, let's assume that Company A's cutoff date is on the 25th of each month and their collection date at the end of each month. If the invoice date is July 11, it belongs to July's accounts since it falls before the cutoff date. Assuming the payment term is 60 days from cutoff, the payment date would be September 23rd. However, since the closest collection date is on September 30th, the payment due date for this transaction is on September 30.

3. Variable Cutoff and Collection Date

The collection date is usually set by the seller when calculating receivable date. However, this may not apply to larger, well-known customers with strong bargaining power. In these cases, the seller would usually need to adjust their terms or calculation to the payment terms required by the other party.

For example, if a well-known customer, Company B, set the 10th of each month as their fixed payment date, Company A would usually still need to adjust their collection date to October 10, even if Company A has calculated the payment due date on September 30th, as seen in the previous example. These are usually special cases that only apply to a few specific accounts.

Additionally, if Company A's cutoff date and collection date are not fixed on the 25th and end of the month, we can assign fields for these dates and let the formula perform flexible calculations based on different cutoff and collection dates. This approach is more flexible and can be adjusted according to different circumstances of the company.

Formula Calculation Tutorial

1. Invoice Date + Days

1.1. Using Excel

(1) If you already have an Excel spreadsheet that records the invoice dates and payment terms, you can add a column for "Estimated Payment Date" to calculate the payment due date.

(2) When calculating the expected payment date, you can add the corresponding number of days to the invoice date according to the payment terms. Assuming the invoice dates are stored in column A and the payment terms are stored in Column I, you can set up the following formula to calculate the expected collection or payment date:

=IF(I2="Net 30", A2+30, IF(I2="Net 60", A2+60, IF(I2="Net 90", A2+90, IF(I2="Net 120", A2+120,""))))

Once complete, Excel will automatically calculate and display the expected payment date based on the payment terms.

1.2. Using Ragic

In the following section, we use the "Bill" quick template as an example, but you can also apply these steps to invoices and sheets you create.

(1) In this template, the issue date is in field A2 and the payment terms are in field A4. Entering design mode, we can use a conditional formula adding the corresponding number of days to the invoice issue date for Ragic to automatically calculate the due date based on the payment terms:

IF(A4.RAW="Net 30", A2+30, IF(A4.RAW="Net 60", A2+60, IF(A4.RAW="Net 90", A2+90, IF(A4.RAW="Net 120", A2+120,""))))

(2) Once the formula is saved, simply enter the invoice issue date and payment terms into the sheet, and the system will automatically calculate the corresponding due date.

2. Fixed Cutoff and Collection Date

If the company's cutoff date is on the 25th of each month and the collection date is at the end of each month, how do we calculate the payment due date based on the invoice date and payment terms?

We can use a conditional formula and day formula DAY() to determine whether the invoice date has exceeded the cutoff date. If it has exceeded, it will be included in the next month's accounts and the due date will be postponed by a month, then calculated according to the payment terms. For example, if the payment terms is Net 30, we add one month. If the payment terms is Net 60, we add two months. The due date will be the end of that month. If the invoice date is on or before the 25th, the amount will be included in the current months' accounts and due date will be calculated based on the payment terms.

Here is an illustration: Suppose we have 2 invoices, both with Net 30 payment terms.

To ensure that the due date falls at the end of the month, we can use the EOMONTH function, which is supported by both Excel and Ragic. This function calculates and returns the last day of the month based on a specific date and the number of months added.

2.1. Using Excel

(1) We can set the formula in the "Expected Payment Date" column as follows:

=IF(DAY(A2)>25,EOMONTH(A2, IF(I2="Net 30", 2, IF(I2="Net 60", 3, IF(I2="Net 90", 4, IF(I2="Net 120", 5, 0))))), EOMONTH(A2, IF(I2="Net 30", 1, IF(I2="Net 60", 2, IF(I2="Net 90", 3, IF(I2="Net 120", 4, 0))))))

This formula first checks whether the invoice date exceeds the cutoff date. If exceeded, an additional month will be added according to the payment conditions and the due date will be set to the end of that month. If the invoice date does not exceed cutoff, the due date is calculated directly according to the payment terms and ensured to fall at the end of the month.

(2) Once complete, Excel will automatically calculate the expected payment due date.

2.2. Using Ragic

(1) Using the same template that we've previously used, we can set the formula for due date as follows:

IF(DAY(A2)>25,EOMONTH(A2, IF(A4.RAW="Net 30", 2, IF(A4.RAW="Net 60", 3, IF(A4.RAW="Net 90", 4, IF(A4.RAW="Net 120", 5, 0))))), EOMONTH(A2, IF(A4.RAW="Net 30", 1, IF(A4.RAW="Net 60", 2, IF(A4.RAW="Net 90", 3, IF(A4.RAW="Net 120", 4, 0))))))

The logic of this formula is similar to Excel. After the system determines whether the invoice date exceeds the cutoff, the due date is calculated based on the payment terms.

(2) Once saved, you can enter the invoice date and payment terms. Ragic will then automatically calculate the payment due date.

3. Variable Cutoff and Collection Date

3.1. Date Adjusted Based on the Customer

Larger and more well-known customers with stronger bargaining power may have more rigid company requirements for their payment dates, in which case we'd need to adjust our due date to their payment date. How can we let the system automatically calculate the due date?

Usually, the payment date of these customers will be later than the original due date set by the company, so they key is to determine whether the new due date should fall in the originally set month or be postponed to the next month. You can follow the steps below:

(1) Add a new Number field in A5 and name it "Customer Payment Date" to set the customer's payment date.

(2) Add a new Date field in H2 and name it "Original Due Date" and set the following formula. This formula is the same as the due date formula in section 2.2.

IF(DAY(A2)>25,EOMONTH(A2, IF(A4.RAW="Net 30", 2, IF(A4.RAW="Net 60", 3, IF(A4.RAW="Net 90", 4, IF(A4.RAW="Net 120", 5, 0))))), EOMONTH(A2, IF(A4.RAW="Net 30", 1, IF(A4.RAW="Net 60", 2, IF(A4.RAW="Net 90", 3, IF(A4.RAW="Net 120", 4, 0))))))

(3) Add a new Number field in H3 and name it "Original Due Date Day" and set the formula DAY(H2) to extract the day from the original due date.

(4) Set the following formula in the Due Date field:

IF(A5>=H3, DATE(YEAR(H2), MONTH(H2),A5), DATE(YEAR(H2), MONTH(H2)+1,A5))

Here's the logic of this formula:

With this setting, even if the "Customer Payment Date" is not entered, the system can still calculate the receivable date correctly.

(5) Once the formula is saved, simply enter the invoice issue date, monthly settlement terms and the customer's specified payment date to automatically calculate the due date.

3.2. Set Custom Cutoff and Collection Date

If the company's cutoff date is not the 25th of each month and the collection date is not at the end of the month, you can also set the cutoff date and collection date in separate fields. Then add some fields for the system to determine the number of months that should be added to the invoice date under each condition and calculate the payment due date. The conditions are as follows:

You can refer to the following steps in designing your sheet:

(1) Add two number fields: Cutoff Date (A5) and Collection Date (D5) to the sheet.

(2) Add a number field in H2 for "Past Cutoff?" and set the formula to:

IF(DAY(A2)>A5,1,0)

This means that if Invoice Date goes past the cutoff date, it will be displayed as 1, indicating that it is due for the next month and the the number of months to be added will increase by 1. If it does not exceed the cutoff date, it will display 0, which indicates that it is due for the current month.

(3) Add a new number field in H3 for "Payment Terms Months" to determine how many months should be added based on the payment terms. For example, if payment term is Net 30, the field will display 1, since 1 month will be added. If the payment term is Net 60, the field will display 2, and so on. The formula can be set as:

IF(A4.RAW="Net 30", 1, IF(A4.RAW="Net 60", 2, IF(A4.RAW="Net 90", 3, IF(A4.RAW="Net 120", 4, 0))))

(4) Add a new number field in H4 for "Collection date earlier than cutoff?" If the collection date is earlier than cutoff, the field will return "1" since the due date will be increased by 1 month. Otherwise the field will return 0. The formula can be set as:

IF(D5 < A5,1,0)

(5) Set the due date formula:

DATE(YEAR(A2),MONTH(A2)+H2+H3+H4,D5)

This formula will determine the total number of months to be added based on the 3 previous conditions, calculate the due date after invoice issue date, and set the payment due date.

(6) Once the formula is saved, Ragic will automatically calculate the payment due date based on the company's closing and collection date.