Compounding income with a reducing earning base

just a guess => is it not

$100,000 x 1/( 5^(1+0.1))

You did not add the desired result, so I could not test it.

Thanks for the suggestion. I had a closer look at my increasing growth formula and it is wrong so ignore it. It had fields back to front and doesn't include the compounding element.

Going back to the drawing board - what I need is a simple way to calculate:

$100,000 + $100,000x(1-0.1) + $100,000x(1-0.1)^2 + $100,000x(1-0.1)^3 + $100,000x(1-0.1)^4 = $368,559.

I need to be able to have a variable number of years (5 above), retention percentage (90% above) and earnings ($100,000 above) so managers can do a whatif analysis. The formula needs to generate the total over the period of time.

Originally Posted by borderex

I needed to reverse the first field in the suggestion to B1-1 as the value is diminishing. The final formula is =FV(B1-1,B2,-B3,0,1).

Glad you caught my typo. I had originally written -10% as a constant, then made a mistake when I substituted "variables" in the posting, without testing.

However, looking back, I wonder if my suggestion is still wrong.

I gave you a formula (or tried to) that results in $368,559, given your parameters (-10%, 5, 100000).

But your mathematical expression:

$100,000 + $100,000x(1-0.1) + $100,000x(1-0.1)^2 + $100,000x(1-0.1)^3 + $100,000x(1-0.1)^4

is equal to $409,510, not $368,559.

The FV usage that matches your mathematical expression is:

=FV(B1-1,B2,-B3)

The 4th and 5th parameters are zero by default.

PS.... The mathematical expression that results in $368,559 and matches my original FV usage (as corrected by you) is:

100000*(90%)+100000*(90%)^2+100000*(90%)^3+100000*(90%)^4+100000*(90%)^5

Only you can determine which mathematical expression and total you intended.