|
|
|
|
| Author |
|
|
Desertt5
Join Date: Jul 2008
Location: ABQ, NM
Posts: 391
|
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 
__________________
Semper Fi |
||
03-08-2010, 05:56 PM
|
|
|
Registered
|
Some things are faster through brut force and ignorance. I think 0.1% every 5 years is one of those.
__________________
2014 Cayman S (track rat w/GT4 suspension) 1979 930 (475 rwhp at 0.95 bar) |
||
|
03-08-2010, 06:12 PM
|
|
|
Registered
Join Date: Jul 2008
Location: Calgary, Alberta
Posts: 76
|
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 |
||
|
03-08-2010, 06:21 PM
|
|
|
Registered
Join Date: Jan 2003
Location: the beach
Posts: 5,149
|
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.
__________________
Charlie 1966 912 Polo Red 1950 VW Bug 1983 VW Westfalia; 1989 VW Syncro Tristar Doka |
||
|
03-08-2010, 06:29 PM
|
|
|
Desertt5
Join Date: Jul 2008
Location: ABQ, NM
Posts: 391
|
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.
__________________
Semper Fi |
||
|
03-08-2010, 07:16 PM
|
|
|
Desertt5
Join Date: Jul 2008
Location: ABQ, NM
Posts: 391
|
I think I got enough to get by. Thanks
__________________
Semper Fi |
||
|
03-08-2010, 08:01 PM
|
|
|
|
Registered
Join Date: Jan 2003
Location: the beach
Posts: 5,149
|
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.
__________________
Charlie 1966 912 Polo Red 1950 VW Bug 1983 VW Westfalia; 1989 VW Syncro Tristar Doka Last edited by ckissick; 03-09-2010 at 06:10 AM.. |
||
|
03-08-2010, 08:14 PM
|
|
1979 Porsche 930 3.4
