indexing formula

[richard oldcorn]

Using Vista and Excel 2007.

The figure 25,000 is to be indexed annually by 3%, but the new figure only changes when the indexing has increased the value by an increment of 5000. Once the indexing has increased the value to 30,000 the figure 30,000 is used. The 30,000 value then remains until the annual indexing at 3% has increased the value to 35,000, at which point this figure will be used, etc. Thus:-

0 25000.00 25000
1 25750.00 25000
2 26522.50 25000
3 27318.18 25000
4 28137.72 25000
5 28981.85 25000
6 29851.31 25000
7 31827.00 30000
8 32781.81 30000
9 33765.26 30000
10 34778.22 30000
11 35821.57 35000
12 36896.22 35000
13 36713.34 35000
14 37814.74 35000
15 38949.19 35000
16 40117.66 40000

The tricky part I find is resetting the indexing back to 3% once a new threshold is reached
Help with the formula would be much appreciated
regards

Richard Oldcorn, Sydney, Australia

 

[jasonb75]

This is probably not the "correct" way to do this, but it works (if I'm reading your question correctly).

=FLOOR($E$1*(1.03^ROWS($E$2:$E2)),5000)

Where E1 holds the initial value of 25000 and E2 holds this formula.

 

[richard oldcorn]

Jason

Brilliant!

Many thanks

Richard