[R] Vintage Attrition Curves

[Gabor Grothendieck]

On Thu, Jul 22, 2010 at 7:45 PM, Kevin T. Ryan <kevin.t.ryan at gmail.com> wrote:
> Hi There -
>
> New to the list, so hopefully I don't mess this up too much ... I am
> hoping maybe some of you can help me.  I have a problem I'm trying to
> solve regarding customer accounts, attrition and forecasting.
> Basically, here is what I want to do (not sure of the best way):
>
> 1. Track accounts by vintage (i.e., the month of the date of sale).
> 2. Forecast total accounts at a given time utilizing 1. and attrition rates.
>
> So, for example, I have a vector of accounts:
>
> a <- c(346, 742, 447);
>
> 346 represents accounts sold this month (i.e., 0 months on book), 742
> represents accounts sold last month (1 month on book) and 447
> represent accounts sold 2 months ago (2 months on book).  I also have
> a vector of monthly attrition rates ('h' below refers to monthly
> hazards):
>
> h <- c(0.06, 0.04, 0.05, 0.03, 0.03, 0.03, 0.04, 0.07); # etc.
>
> I'd like to forecast how many accounts I'll have on book in another
> month (or 2 months or 3, etc.).  So manually what I'm doing for
> estimating next month is:
>
> 346 * (1 - 0.06) + 742 * (1 - 0.04) + 447 * (1 - 0.05); # ie, a * (1 - h[1:3])
>
> So that's not so hard.  But, if I want to do 2 months out, I have to
> do something like:
>
> 346 * (1 - 0.06) * (1 - 0.04) + 742 * (1 - 0.04) * (1 - 0.05) + 447 *
> (1 - 0.05) * (1 - 0.03); # not sure of R code from here on out
>
> I know I can do a loop, but I'm expecting to have to do this for 100's
> of customer segments for multiple states and many more vintages so I'm
> wondering if there is syntax or if anyone has any ideas on how I can
> solve more elegantly.
>


Try this:

> apply(embed(1-h, 3), 2, cumprod) %*% rev(a)
         [,1]
[1,] 1462.210
[2,] 1400.845
[3,] 1352.575
[4,] 1311.998
[5,] 1268.762
[6,] 1209.641

Compare first two entries to:

> 346 * (1 - 0.06) + 742 * (1 - 0.04) + 447 * (1 - 0.05)
[1] 1462.21


> 346 * (1 - 0.06) * (1 - 0.04) + 742 * (1 - 0.04) * (1 - 0.05) + 447 *
+ (1 - 0.05) * (1 - 0.03)
[1] 1400.845