Estimating Savings When The Interest Rate Changes Based Upon Savings Amount

G

GEORGE

I want to design a spreadsheet where I can enter in my estimated savings
per year, and it will output my estimated returns, at current interest
rates, at the age of 65. The issue arises because the interest rate
changes at various savings levels. The details are:

$0 - $50000, r=.01
$50001 - $100000, r=.015
$100001 - $125000, r = .0228
$125001 - $150000, r for the first 100000 = .0412, r for the remainder
= .01
$150001 - $200000, r for the first 100000 = .0412, r for the remainder
= .015
$200001 - $225000, r for the first 100000 = .0412, r for the remainder
= .0228
$225001 - $250000, r for the first two 100000's = .0412, r for the
remainder = .01

and so on, in a fashion that reaches at least to one million
(hopefully!).

Ideally, this would be something that could be easily updated with
changes in annual savings potential and interest rates. That's where I
am unable to continue my design. Is there a function that is smart
enough to subtract out 100000 from the principle when it reaches 125000
and will pull out 200000 when the principle reaches 225000? I tried to
split the principle into different categories, but every time I change
the savings potential, the categories fall apart. Ideas?
 
P

Paul Corrado

=100000*INT((C11-25000)/100000)*0.0412+CHOOSE(ROUNDUP((C11-100000*INT((C11-2
5000)/100000))/50000,0),0.01,0.015,0.0228)*(C11-(100000*INT((C12-25000)/1000
00)))

PC
 
Top