Printer Friendly

The best little payment performance formula you'll ever need.

All too often, in the internal workings of software packages, the formulas used to calculate a customer's average time of payment are woefully inadequate. This can be true whether the software is home grown or purchased from third-party providers.

One common problem is that any particular invoice cleared by a series of payments is often reported as having been paid entirely on the date of the last payment. For example, if 90% of an invoice is paid in 30 days and the remainder in 60 days, the resultant reported time of payment in some systems would be 60 days, based on the last payment date. If the terms of sale were 30 days, that customer would be reported as 30 days late. Another common inaccuracy is that using the payment application date rather than the payment receipt date could negatively impact the reported timing of payment.

Over the years, there has been an obvious need for a formula that would produce the true dollar weighted average of a customer's payments, regardless of how and when they were received and applied. But let's stop for a minute and clearly define what we are actually trying to formulate here. We are not looking for a customer's DSO, which would include unpaid as well as paid invoices. Rather, we are seeking a set of formulas that would accomplish the following:

* Step One: Compute the dollar weighted average days to pay (or days late/early) for a given set of invoices and payments applied together on a given date in a single application. If applicable, any credit and debit memos and journal entries closed by this application would be included.

* Step Two: Do this for all the customer's payments over the period measured.

* Step Three: Aggregate these averages to determine the dollar weighted average for all of the customer's payments over a given time period, i.e., year-to-date.

* Step Four: Do this for all customers to obtain each customer's average days to pay or average days late/early.


[summation] ($ Amount of Item* Closed with the Application)
x (Settlement Date - Due Date)/[summation]($ Value of all
Invoices and Debits Closed with the Application)

* Credit memos and payments should be treated as
"minuses," invoices and debits should be treated as "plusses."

Formulas to Accomplish the Four Steps

For Step One, if calculating average days late, use the formula in Figure 1.

In other words, we are going to take the dollar amount of each item closed with the application and multiply it by the number of days from settlement date to due date. (Credit memos and payments should be treated as "minuses" invoices and debits should be treated as "plusses") Then we are going to take the sum of all the individual application amounts (as calculated above) and divide this by the sum of the dollar value of all invoices and debits closed with the application.

Figure 4

Cumulative ADL =
[summation][{$ Value of Invoices & Debits Closed in a Given
Application} x {Weighted ADL for That Application}]/
[summation][$ Value of All Invoices & Debits Closed]

Note: A debit memo is considered an invoice.

Let's apply this formula to the single application in Figure 2.

$1,263,987.67/$24,000.00 = 53 Days Late

In this situation, we had two invoices that were paid with three "on account" payments, then a journal entry was issued to clear the remaining balance. You will notice also that the "settlement date" (the date the payment was applied) was five days after the final payment was received and two days after the journal entry. Yet our formula still yielded an answer that is intuitively correct, 53 days late, and is in fact mathematically correct.

The idea of our formula is, of course, to obtain the dollar weighted days caused by each "plus" tug created by the application (invoices, debit memos), and each "minus" tug (credit memos, payments). The image in Figure 3 presents the idea visually, with the sizes of the circles representing the amount of the item involved, and the colors representing the direction of the tug (red = minus, black = plus), and the length of each arrow being the passage of time involved.

Without filling pages with all imaginable types of examples, let me just say that you can try this yourself with different scenarios, and you can see that it will work universally. To get average time to pay rather than average days late/early, you can just substitute Item Date for Due Date in the numerator of the formula.

Now that we have our formula to establish the days late for individual applications, we can apply the following formula to derive average days late for a customer for a given period of time (Step Three) (see Figure 4).

If we apply this formula to the scenario in Figure 5, we again see an answer that is both intuitively and mathematically correct for a given customer's payments over a defined period of time.

Of course, the larger items, having been paid satisfactorily, work to diminish the negative impact of the smaller invoice paid quite slowly. So, if you are interested in a formula that reliably tells you how a customer has paid his bills, and can adapt this to your software, give it a try and see how it works for you.

"Stump the Formula": If you find any situations where the formula doesn't work, or any other point you would like to discuss, please email me at the address below.

Norman Taylor, CCE is a credit consultant in the Evans, GA area. He can be reached at or 706-854-8263.

"The Best Little Payment Performance Formula You'll Ever Need" or "TBLPPFYEN" is copyrighted by Norman Taylor.


Figure 2

                   Item         Due                      "Settlement
Scenario          Date *      Date *      Item Amount       Date"

On Acct Pay      7/13/2000   7/13/2000    $(10,000.00)     7/25/2000
On Acct Pay      7/17/2000   7/17/2000    $ (4,000.00)     7/25/2000
Invoice          4/21/2000   5/21/2000    $   7,030.73     7/25/2000
Invoice          4/28/2000   5/28/2000    $  16,518.78     7/25/2000
On Acct Pay      7/25/2000   7/25/2000    $(10,000.00)     7/25/2000
Journal Entry    7/28/2000   7/28/2000    $     450.49     7/25/2000


                  Days From                          Dollar
                 Due Date to        Dollar         Amount of
                 "Settlement       Weighted        Invoices/
Scenario            Date"          Average       Debits Closed

On Acct Pay                12    $(120,000.00)
On Acct Pay                 8    $ (32,000.00)
Invoice                    65    $  456,997.45
Invoice                    58    $  958,089.24
On Acct Pay                 0              --
Journal Entry               2    $      900.98

                                 $1,263,987.67       $24,000.00   53

"Settlement Date" = Application Date

* For credits and journal entries, the date of issuance = Due Date.
For payments, the date of receipt = Due Date.

Figure 5

              A             B              C

           Invoice      Wtd Days      Inv Amts *
Item       Amounts         Late          WADL         C/A

1         $ 24,000.00          53    $ 1,271,673.54
2         $ 60,000.00           1    $    60,000.00
3         $ 90,000.00           4    $   360,000.00
Total     $173,549.51                $1,691,673.541    9.7
COPYRIGHT 2013 National Association of Credit Management
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2013 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:SELECTED TOPIC
Author:Taylor, Norman
Publication:Business Credit
Date:Jul 1, 2013
Previous Article:NACM is pleased to announce its first CCRAs!
Next Article:Do the right thing: can paying better attention to ethics truly create a better bottom line for credit and business?

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters