Month Formula
Posted by Chris on June 13, 2001 9:49 AM
I'm creating a calculator that will track an investment and return date.
(A1)Start date July 2001
(A2)Investing $300. per Month...
(A3)Goal $4500...
(A4)WHAT MONTH & YR WILL THAT GOAL BE REACHED by investing the amount in (A2) per month?
How do you get it to take Jul 2001 and count forward til' you reach the MONTH & YR that is (= to or above $4500.)
The answer is 15 months from July 01'... which would put him at Sept 02' I believe as the result in cell (A4)
I just don't know how to get a formula to do all that.
THANKS ! Chris
Posted by IML on June 13, 2001 11:06 AM
On the surface,
=EDATE(A1,A3/A2)
will get you your date. Not many investors will be interested without some sort of assumed return figure unless this is mattress money, though.
good luck.
Posted by Chris on June 13, 2001 12:13 PM
****** Thanks so much for your help, but it's not working... I'm getting a #NAME? result...
I went in and made sure the cell format for Cell A1 and A7 ( where your formula is ) are both in the "DATE March-98" format...should I be using another format for these 2 cells in order to have the result come out properly?
Yeah, I think this is a mattress money deal - a THICK mattress... 8-) Thanks again ! Chris
Posted by IML on June 13, 2001 12:27 PM
Hmm, that formula may be a part of an add in. go to tools, add in, and make sure the analysis tool pack is checked. The format you are using should be fine. Hope that helps.
Posted by Chris on June 13, 2001 12:55 PM
*** I hate it that this simplistic thing seems to have turned out complicated.... The add in block was not checked, I checked it ...saved, closed & reopened and it appears there's no change in the result. I removed the space just before the = sign... anything else you can think of to make it look at the above cells and calculate?
OH WOW ! I went in again, placed my curser in front of the =sign, and when I did the cells above "lit up"... I clicked elsewhere to pull out of that A7 cell and it popped up "OCTOBER 02" !
Yeah ! It appears to be working ! ( well, it's a month off - how can I fix that? ) 15 months * 300. ='s $4500.exactly so July 01-Sept 02 is 15 months...
"September 02" should be the result....
We're SO CLOSE !
Thanks !!!!!
Posted by Russell on June 13, 2001 2:32 PM
It's adding 15 months and coming up with the right answer. In your formula, just put:
=EDATE(<July2001 cell>,<4500>/<300>-1)
(Add the minus one).
Posted by IML on June 13, 2001 2:53 PM
Russel is right...but I'm not sure if the whole edate formula will produce the results you really want. For instance, type in an investment goal of 599 and this would result in a July date returned. The whole edate thing may be way off base. You may also want to consider something like
=EDATE(A1,A3/A2-1)+((A3/A2)-INT(A3/A2))*30
and use a format showing the day as well. This would return a 7/30/01 with a $599 investment goal. Sorry if I got you started on the wrong track...
Posted by IML on June 13, 2001 3:01 PM
OR Maybe
or you could also just bump it by one month to cover overages with an if statment, like
=IF(A3/A2=INT(A3/A2),EDATE(A1,A3/A2-1),EDATE(A1,A3/A2))
and preserve your mm/yy format.
Posted by Aladin Akyurek on June 13, 2001 4:09 PM
Re: OR Maybe
Ian & Co
I believe it's rather a Solver thingo.
Aladin
