Calc Future Value with Changing Interest rates

[John Flaherty]

This is probably a simple problem, but one i have had to battle for
three days with no luck

Monthly deposit =100
IR when balance is 0-$500 = .4%
IR when balance is 501-1000= .5%
IR when balance is 1001-5000= 1.0%
interest computed monthly

I am trying to find the future value in 1,3 and 5 years when $100 is
deposited monthly. The interest rate will change as the account balance
grows. Is it possible to do this in a one cell formula? I keep thinking
it is some sort of "If then" logic but so far it will not work. If
anyone has a formula I could use you would make my weekend!!!

Clueless in Cleveland

 

[Jerry W. Lewis]

Use NPER() to determine how many payments must be made at one interest
rate in order to reduce the balance to the next interest rate boundary.

Jerry