percentage decrease in population each month

[j.farr3ll]

Hi all,

I'm trying to calculate how many people will quit a service each month based upon a known annual cancellation rate.

I've tried using the annual rate/12, but the problem is that because in each month you have the amount of people left from the last month less the cancellation rate, applying this rate to a decreasing population does not return the correct results.

I believe a similar approach to calculating compound interest needs to be taken and I've been trying the following formula but to no success..

=((1+canc_percent/12^month number)-1)

Where canc_percent is the percentage of people canceling.

Any help would be appreciated.

Please see the attached dummy spreadsheet.

Many Thanks,
James

dummy.xlsm

[Ace_XL]

Due to the compounding effect you need to calculate the effective monthly rate which can be done by

=(1+canc_percent)^(1/12)-1

This might give you a start

[j.farr3ll]

Ok so using your corrected formula for the compound interest rate I get a month drop out rate of 5.9463% when the annual rate is set to 100%.

Then I have tried to apply this to each month using a formula for decreasing population growth:

population in month = rounddown(((population start of year*(1-compound rate)^months since start of year)),0)

i.e. if the compound rate is in cell G9 of the dummy sheet the formula for February year 1 would be:

=ROUNDDOWN(((B17*(1-$G$9)^2)),0)

However, as a sense a sense check, I'd expect everyone to drop out by the end of year 1 if the annual dropout rate is 100%. Here I'm only seeing about 53% of people dropping out.

Any further help would be greatly appreciated.

Thanks,
James

[j.farr3ll]

Found the solution....

Used the compounded rate as described above,

then worked out the cumulative number of people dropping out at the end of each month using: Pq=Pi(1-(e^(r*t)))
where Pq = cumulative population quit at time,
Pi = initial population,
e = exponential
r = compound rate
t = time periods since initial population.

As this was a negative number, the population remaining at the end of each month could be found using the following formula:

=if(Pi+Pq<0.0,Pi+Pq)

Cheers,
James