reducing charge to be applied as fund gets bigger

[Steveandshelly]

Hi,
I am trying to project the value of a pension forward to retirement
age.
The charge applied in the fund is 1% per annum,
However it reduces to

0.95% from 10,001 to 19999
0.9% From 20,000 to 49,999
0.8% From 50,000 upwards

I know that to run the current value (which is 9,000) forward at a 1%
charge for 20 years with 7% growth would be

=9000*(A-B+1) ^T
Where
A = assumed growth (7%)
B = Charge (1%)
T = Term (20)

What I cannot do is apply the reducing charge as the fund grows to the
calculation.

Can anyone help?

 

[Niek Otten]

Although this may be possible to do in one array-formula, it is probably
easiest to set up a time table with the addition of interest and subtraction
of cost per year.
For the cost formula, see

http://www.mcgimpsey.com/excel/variablerate.html

 

[Steveandshelly]

Hi,
Thanks for that Niek.
I was hoping for a 1 cell solution that could pick up all the data,
inorder that i could easily vary the assumptions.

do you have any idea how we could do that?

 

[Niek Otten]

No. But you can still have all your assumptions in one place, so you can
easily change them and see the effects.