percentage decrease in population each month

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