R help - Jul 2010 - Vintage Attrition Curves

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.

Any suggestions are most appreciated.  Thanks!!

Kevin

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