Excel formula help needed

[desertt5]

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

[David]

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

[rhogg]

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

[ckissick]

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.

[desertt5]

I do have to use a formula...

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.

[desertt5]

I think I got enough to get by. Thanks

[ckissick]

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.