Calculating Consumer Payment Cycles

[Anjaney Singh]

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 id int month_stata double collection_not_infl_adj

"1442106-1106000046" 757 0
"1442106-1106000046" 758 0
"1442106-1106000046" 759 0
"1442106-1106000046" 760 700
"1442106-1106000046" 761 0
"1442106-1106000046" 762 0
"1442106-1106000046" 763 0
"1442106-1106000046" 764 0
"1442106-1106000046" 765 105
"1442106-1106000046" 766 0
"1442106-1106000047" 757 95
"1442106-1106000047" 758 95
"1442106-1106000047" 759 95
"1442106-1106000047" 760 95
"1442106-1106000047" 761 294
"1442106-1106000047" 762 333
"1442106-1106000047" 763 205
"1442106-1106000047" 764 0
"1442106-1106000047" 765 521

end
format %tm month_stata



I have a panel dataset which includes consumer id, month and collection. I have been struggling to find a way to generate a variable that will give me a sense of the payment cycle for consumers.

E.g. id" 1442106-1106000046" makes the first non zero payment in month "760". The next non zero payment is made in month "765". This tells me that the consumer is paying once every 5 months.
But, in the case of id "1442106-1106000047", the consumer pays every month apart from month "764".

I have been essentially trying to get one number that associates the payment pattern of a consumer
Any ideas as to how this can be achieved?

[Clyde Schechter]

Well, I'm not sure how you plan to use this and how payment cycles might relate to other things that you will be using in your analysis. But here's an idea that might work for you. My guess is that, at least over long periods of time, very few people are completely regular. They may tend to be every N months, but there will be times when they miss a payment, but then maybe make the next one early. So I think I might look at the intervals between successive non-zero payments and then use the most frequently occuring interval to characterize that. And if there are two or more intervals that occur with equal frequency, I would use the smaller one, on the assumption that that is the intended frequency, and the longer one represents some missed payment.

Evidently this does not capture all the possibilities. But I would say that there is no meaningful way at all to characterize all of the possibilities with a single number. This might at least be a workable number that characterizes the customer's "most usual" payment interval.

drop if collection_not_infl_adj == 0
by id (month_stata), sort: gen interval = month_stata[_n+1] - month_stata
by id interval (month_stata), sort: gen freq = _N
gsort id freq -interval, mfirst
by id: gen wanted = interval[_N]
by id: keep if _n == _N
keep id wanted

list, noobs clean

I hope this helps you.

[Anjaney Singh]

Thank you Clyde!