* Author: Calvin Price * Companion program for presentation "Estimating the Average Life of Non-Maturity Deposits" * Stata Conference 2018 * Email: caprice@us.mufg.jp * * Purpose: Create toy data on customer balances over time, aggregate balances by customer account age, estimate decay rate of balances * Written with Stata/IC 14.2 * ============================================================= * Create customers * ------------------------------ clear set obs 36 generate tmo=tm(2015m1)+_n-1 format %tmm_CY tmo tsset tmo set seed 5678 foreach var of newlist z1-z200 { // number of customers gen `var' = sum(round(10*runiform())) // increasing balance for every customer local k1=36*runiform() // starting month of account local k2=3*rchi2(1) // age of account, choosing this distribution is what really forces our result replace `var' = 0 if !inrange(tmo, tmo[`k1'], tmo[`k1'+`k2']) } br tmo z1 z2 z27 z44 z45 z47 * To aggregate by account age, you need long shape data (panel data) * Reshape from wide to long reshape long z, i(tmo) j(Customer) gsort Customer tmo drop if z==0 bys Customer: gen Age = _n * Plot individual customers (Plot vs Time) tsset Customer tmo xtline z if Customer <=50 , overlay xsize(9) xlabel( , labsize(small)) ylabel( , labsize(small)) xtitle(" ") ytitle( "\$ Dollars") legend(off) subtitle("Toy Data Example: Individual Customer Balances" "Plot vs Time") /// caption("Sample of 50 customers is shown. By design each customer is given an increasing balance with time." "When balances are aggregated by age the result will still be exponential decay.", size(vsmall)) * Plot individual customers (Plot vs Age) tsset Customer Age xtline z if Customer <=50 , overlay legend(off) xlabel( , labsize(small)) ylabel( , labsize(small)) xtitle(" " "Age") ytitle( "\$ Dollars" " " ) subtitle("Toy Data Example: Individual Customer Balances" "Plot vs Age") /// caption("Sample of 50 customers is shown. By design each customer is given an increasing balance with time." "When balances are aggregated by age the result will still be exponential decay.", size(vsmall)) * Group balances by age * ------------------------------ collapse (sum) z , by(Age) scatter z Age , ms(oh) \$g2 ytitle("Grouped Balance" " ") ylabel( , format(%8.0fc)) xtitle(" " "Account Age") * Do the nl estimation * ------------------------------ rename (Age z) (age GroupedBalance) nl (GroupedBalance = {b1}*exp({b2}*age)) * Plot result * ------------------------------ predict MyFitted, yhat scatter GroupedBalance age , xscale(range(0 40)) mcolor(*.50) xtitle(" ") || line MyFitted age, xscale(range(0 40)) xtitle(" " "Account Age (months) ") ytitle("Grouped Balance" " ") lwidth(medium) legend(off) /// ylabel( , format(%8.0fc)) title("Balance Decay Function" ) /// caption(" " "Note: Decay Parameter = `: disp %5.3fc `=[b2]_cons'' " , size(small) ) /// ttext(18000 4.0 "Average life = 3.38 months" , place(e) color(gray) j(left) size(small)) /// xline(3.38 , lcolor(gray*.50) )