# How do I calculate bad debt on revolving credit?

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.

• I for one would appreciate an example, and some data. I kind of understand what you are describing but there is also a good chance I have misunderstood the description and so attempting an answer under those circumstance would be a waste of everyones time
– Attack68
Aug 1 '18 at 20:23
• @Attack68, I've added some data to to the question. Please let me know if you need anything else. Aug 3 '18 at 0:17

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.