How do I calculate bad debt on revolving credit?

Question

I have a revolving credit system. A customer purchases and then has 6 months to pay off the purchase. i.e. Purchase for 60 in Jan and then need to pay 10 a month in Feb, Mar, Apr, May, Jun, Jul.

The aging of account is kept as current, 30 days, 60 days, 90 days, 120 days and 150 days +.

At the beginning of every month, my software takes all the amounts in each period and moves it one period older. e.g. 30 -> 60, 60 -> 90, etc It then takes the pre-calculated amount owing for that month, for each account, and puts it into the current balance of the account.

I obviously have customers re-buying all the time. Some after their original amount has been paid off and some after only a certain amount of the original purchase has been paid off.

What is the best way for me to calculate what the bad debt has been over the past year? (Keeping in mind some accounts are in 90 days and will still get paid and some accounts in current are from new customers that will never get paid.)

I don't think that there is a way to insert a table, so I've pasted the data in CSV format.Here are the sales and payments as well as the totals of the buckets for Jan - Dec 2017:

    Month, total,current,30 ,60 ,90 ,120 ,150 ,sales,pay
 Jan ,140422628 ,7281546 ,5705615 ,4684113 ,4615482 ,4360849 ,94074615 ,7543717 ,-8568162 
 Feb ,143324665 ,7362944 ,5574404 ,4526285 ,4042478 ,4216614 ,97382041 ,9993857 ,-8762434 
 Mar ,145702145 ,7520503 ,5578599 ,4376838 ,3828715 ,3532749 ,99492168 ,11007918 ,-9242764 
 Apr ,150914995 ,7114145 ,5528774 ,4311449 ,3786774 ,3471839 ,101951718 ,13570121 ,-9948512 
 May ,153498743 ,7162139 ,5391142 ,4327766 ,3716374 ,3327988 ,103380754 ,11564184 ,-9575771 
 Jun ,155889045 ,7759287 ,5394046 ,4155481 ,3803228 ,3423377 ,105815682 ,10027996 ,-9265012 
 Jul ,158453744 ,8371916 ,5709239 ,4029464 ,3512949 ,3337981 ,107431457 ,11896567 ,-10131923 
 Aug ,161301534 ,8614500 ,6227544 ,4410486 ,3517605 ,3232267 ,109948251 ,10710184 ,-9551682 
 Sep ,165307654 ,8625254 ,6338409 ,4738558 ,3820820 ,3215644 ,112276387 ,12918026 ,-10564499 
 Oct ,173744849 ,8966799 ,6374958 ,4789648 ,3982816 ,3338196 ,113804865 ,19517180 ,-11876328 
 Nov ,180674495 ,9724925 ,7109244 ,4855723 ,4098669 ,3563787 ,115933379 ,16659417 ,-11042206 
 Dec ,180928310 ,9954910 ,7536676 ,5328046 ,4240225 ,3749598 ,118665330 ,9359356 ,-10942224 

The data won't tie up exactly as there are other transactions not included here (such as interest, account charges, etc.)

Please let me know if you need any further data and if so, what data you need.

Answer 1

You could assign a probability $p_i$ that a payment currently in bucket $i$ will eventually not get paid. For example if $i=30$ days, $p_i=0.01$ whereas if $i=150+$days, $p_i=0.5$. Use your judgement to set these probabilities. Then, you calculate your total expected future defaults by performing the sum $$\sum \text{payment}_j\times\text{default probability}_j$$. The change in this number over the past year is the amount of extra defaults you have suffered over the last year.

Answer 2

I agree with @dm63, the way you put it seems more an accounting problem. Check documentation about IFRS 9. Given the current changes, you will find lots on the internet. Where follows a piece of literature that might help.

https://www.pwc.com/gx/en/audit-services/ifrs/publications/ifrs-9/ifrs-9-understanding-the-basics.pdf

Normally i follow this rule for accounts receivable impairment 6 mth- 12mths » 25% 12 mths - 18 mths » 50% 18 - 24 » 75% above 24 » 100%

We always make this analysis for invoice and not for a customer but if a significant amount has a delay of 24mths for a given customer we recognise 100% for the entire account and cut supply.