Calculating total future earnings having applied a cancellation rate

[Snowboyd]

This is not the easiest thing for me to put into words but I'll give it a go:

Clients pay a fixed monthly fee. I'll call this f and give it a value of £50
We also know that 3% of clients cancel each month, or to put it another way, 97% of clients remain from the previous month. I'll call this c.

Therefore we can calculate the total income from any given client as:
(I can't see how to do superscript so fc2 means f multiplied by c to power 2)

f + fc + fc2 + fc3 + fc4 + fc5 ... adinfinitum

or

f ( c + c2 + c3 + c4 + c5 ... adinfinitum)

given that at some stage a client would stop paying regardless, life is after all finite, my final calculation would have to have a place to stop. lets say this is after 120 months and give this the value n

so the formula i need to be able to calculate is:

f ( c + c2 + c3 + c4 + c5 ...c120) or f ( c + c2 + c3 + c4 + c5 ...cn)

If anyone can tell me how this is done in Excel (or even what the mathematical name is for this type of formula so I can look it up) then I would appreciate it.

[shg]

It's the sum of the geometric series 1 + x + x^2 + x^3 + ..., which for x < 1 converges to 1 / (1-x)

So for monthly payments of $50 and a 97% retention rate,

= 50 / (1-97%) ~ $1667

[Snowboyd]

Thanks shg, I had a feeling there was a simple solution to at least the first part of the solution somewhere (I just couldn't find it).

So if the solution to convergence is 1/(1-x) is there any way we can amend to calculate as far as n (i.e. up to a fixed number of sums) as opposed to infinity?

[shg]

The sum of the first n terms = f*(1 + c + c^2 + ... + c^(n-1)) is

=f*(1-c^n)/(1-c)

[Snowboyd]

shg gave me enough info to start to search for the answer I was looking for so for the benefit of the community:

sum of a finite power series:

(x^n+1 - x) / (x-1)

So for my needs, the formula is:

f (c^n+1 - c) / (c-1)