Excel formula help needed
 

This thing is killing me. There must be an easy way to make this work.

Trying to have the CURRENT INTEREST RATE increase by .1% every 5th year.

I have tried I don't know how many different functions, wildcards.........

Is it possible to make a formula that can be copied down for 30 years worth or am I just way out of the ballpark?

Thanks

http://forums.pelicanparts.com/uploa...1268103344.jpg

Some things are faster through brut force and ignorance. I think 0.1% every 5 years is one of those.

If you are just doing 30 years ie 6 iterations copy paste is your friend In other words do by brute force.

If it must be in formula =IF(("year"-"original year")>=5,TRUNC(("year"-"Original Year")/5)*Interest rate increment"+"Original Interest Rate","Original Interest Rate")

Year varies all other variable are fixed ( ie $C$3)

What you are doing is dividing the difference in the years by 5 truncating the result to an integer and multiplying the rate increment by the integer.

easy peasy

My idea only works if there is an integer tag, like "INT".

In column G, the first line is 7.0. From there on, you create an IF statement in column G that says the interest rate is the cell above + 0.1, IF the cell for years (column B) divided by 5 is an integer, or else it's the column above plus zero. Every time you get an integer, it will go up by 0.1 and stay that way until the next integer.

I do have to use a formula... :mad:

Totally lost on the TRUNC thing. I see what it is trying to do, but it never changes the answer in the column.

How can I put an INT tag, or what is it? That way seems pretty easy, if I can get the INT tag to work.

We have done a couple of these retirement planning type spreadsheets, but never had anything increment like this.

ONE MORE WEEK !! and done with this class.

I think I got enough to get by. Thanks

I found out that INT rounds down to the nearest integer. For example INT(8.1) =8, and INT(8.9)=8.

This equation will work in cell G2, with years in B2, and Interest Rate in G2:

=IF(INT(B2/5)-B2/5=0,G2+0.1,G1)

It will then work in all lower rows if you copy and paste.

So, if Bx is 5 or 10, or 15, etc, then Bx/5 is an integer. And INT(Bx)=Bx. Therefore, INT(Bx)-Bx=0.