Maths help! : Annual to Monthly rates of retention / churn

[kcmuppet]

Hi, not sure if this is an appropriate place to post the question, but here goes:

I'm having trouble converting an annual customer retention rate to a monthly one in preparing a customer forecast:

if I’ve got 192 new customers each year, and I expect to keep 25% (48 customers) into the next year,
and I assume my 192 new customers are acquired uniformly at 192/12 =16 per month,

...how do I calculate the monthly retention rate from the annual retention rate?

I tried 1-25% ^(1/12)= 10.9% for monthly retention, multiplied by 16 =1.75 retained customers each month from the 16 new one.

But 1.75 x 12 months =21 retained customers p.a. not the 48 I was expecting.

Where am I going wrong?

 

[jasonb75]

Where am I going wrong?

Looks like you're just trying to over complicate it.
If you're basing the monthly retention on the annual figure of 192 then it will be 192/12*25%
If you're basing it on the 16 new per month then it's just 16*25%

 

[kcmuppet]

Looks like you're just trying to over complicate it.

You're probably right, but

If you're basing the monthly retention on the annual figure of 192 then it will be 192/12*25%
If you're basing it on the 16 new per month then it's just 16*25%

Those 2 obviously both come to the same figure, 4, but it won't be 4 each month.

i.e.
If I get 16 new users in month one and keep 4 (25%) of those and then I've got 16 new ones in month 2 I'll then have 16+4=20 and keep 5 (25%) of those giving me 16+5 in month 3 and so on.

In a year I'd have 60 retained users over 12 months, not the 48 I would be expecting if I knew I'd retain 25% of the annual figure.

 

[jasonb75]

in month 2 I'll then have 16+4=20 and keep 5 (25%) of those giving me 16+5 in month 3 and so on.

I see what you mean, if your'e compounding the retained users with the new users for the current month then it's not the same, from your original question I was under the impression that you were only looking at the new users for the month, not all users. You would need to subtract the total number of retained users from previous months before calculating the 25% then add it back on afterwards. Naturally, this will be 0 in the first month but for the remaining months it would depend on whether you use a fixed assumed figure as per your example, or an actual figure from your data.

=(current month new users - sum(previously retained users)) * 25% + sum(previously retained users)

 

[kcmuppet]

Thanks
I'm not able to make it work. I thought I misinterpreted what you meant by sum(previously retained users), but either way I'm not getting to 48 retained in month 12

Monthly Retention from Annual.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2
3		Total Users acquired in year	192
4		Retained annually	25%	48
5		Annual Growth rate	-75%
6		Monthly Growth rate	-10.91%	-10.9%
7
8
9		Months	1	2	3	4	5	6	7	8	9	10	11	12
10		Users acquired	16	16	16	16	16	16	16	16	16	16	16	16
11		Cumulative	16	32	48	64	80	96	112	128	144	160	176	192
12
13			16	14.25	14.44	14.42	14.43	14.43	14.43	14.43	14.43	14.43	14.43	14.43	<-(monthly growth rate * previous month retained users) + this month new users <-expecting last value to be 48
14
15		2nd attempt for active this month:	16	17.75	19.68	21.83	24.21	26.85	29.78	33.03	36.63	40.63	45.06	49.98	<-should be 48
16
17		?	16	16	28	49	86	150	263	460	804	1407	2463	4310	=(current month new users - sum(previously retained users)) * 25% + sum(previously retained users)
18
19		?	16	24	34	46.5	62.13	81.66	106.1	136.6	174.7	222.4	282	356.5	=(current month new users - previously retained users) * 25% + previously retained users
20
Calculator

Cell Formulas
Range	Formula
D4	D4	=C3*C4
C5	C5	=1-(1-C4)-1
C6	C6	=((C3*C4)/C3)^(1/12)-1
D6	D6	=RRI(12,C3,(C3*C4))
C10:N10	C10	=$C$3/12
C11	C11	=C10
D11:N11	D11	=C11+D10
C13	C13	=C10
D13:N13	D13	=(C13*Growth_pcm)+D10
C15	C15	=C10
D15:N15	D15	=C15*(1-Growth_pcm)
C17	C17	=C10
D17:N17	D17	=(D10-SUM($C17:C17))*$C$4+SUM($C17:C17)
C19	C19	=C10
D19:N19	D19	=(D10+C19)*$C$4+C19

Named Ranges
Name	Refers To	Cells
Growth_pcm	=Calculator!$C$6	D15:N15, D13:N13

 

[jasonb75]

Based on your mini sheet, post 2 was correct. If this is not what you want then I suggest that you populate your mini sheet with the results that you expect instead of random useless formulas.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Total Users acquired in year	192
4	Retained annually	25%	48
5	Annual Growth rate	-75%
6	Monthly Growth rate	-11%	-11%
7
8
9	Months	1	2	3	4	5	6	7	8	9	10	11	12
10	Users acquired	16	16	16	16	16	16	16	16	16	16	16	16
11	Cumulative	16	32	48	64	80	96	112	128	144	160	176	192
12
13		4	8	12	16	20	24	28	32	36	40	44	48
Sheet3

Cell Formulas
Range	Formula
D4	D4	=C3*C4
C5	C5	=1-(1-C4)-1
C6	C6	=((C3*C4)/C3)^(1/12)-1
D6	D6	=RRI(12,C3,(C3*C4))
C10:N10	C10	=$C$3/12
C11	C11	=C10
D11:N11	D11	=C11+D10
C13:N13	C13	=C11*25%