Working backwards to calulate a date (Excel 2003)

[dmcnab]

Hi all....I am attaching a small worksheet that I need some help with....briefly, the question is: I acquire pension while working; my pension 'maxes out' at the 70% level; as of a certain date, I have (for example) 55%. How much more do I have to work until I reach 70% (acquiring pension at the rate shown in my sample). I then need to have that "reach 70% level" translated into a date, so that I know the optimum date to retire. It sounds pretty simple, but I cannot be sure that I am approaching it correctly.....thanks....ps: see better explanation in the worksheet.

[HansV]

See attached version.

PS were the replies to <post:=646,936>post 646,936</post:> and <post:=652,500>post 652,500</post:> helpful? You didn't provide any feedback on them, so other Loungers reading those threads don't know if the problem was solved.

[dmcnab]

Good morning, Hans.....sorry that I didn't reply to posts 646,936 and post 652,500....yes, both answers were exactly what I needed. Your answer to 646,936 was a very good tip for people using drop-down menus a lot....the other ties in with this particular post....the formula you added to the sample that I attached to this post provides the exact date...I think I was not getting the date b/c of the order in which the calculation was being done -- I knew I should arrive at 3/11/09, but it was always much later in the month....thank you again...ps: if I have any more questions relative to other aspects of this project, I suppose it is best to stay with this thread (so Loungers can get the complete picture), rather than start a new thread ??

[HansV]

If the new question is related to this one, it's best to post it in this thread.

[dmcnab]

...thank you...I do have 1 more question before I head to work....I calculate the time between May 1, 1979 & Dec 31, 1991 (inclusive) as being 12.6680 years.....I used 12.666 in my sample worksheet b/c that is what the actuary is telling me, and that small difference equals 1 day (March 10, 2009 versus March 11, 2009 as being the date that pension maxes out)...so, am I calculating that time difference properly or is that actuary....ps: I think that the actuary 'ignores' leap years, so could that be the explanation ?

[HansV]

My guess is that the actuary calculates the difference as 12 years and 8 months = 12 2/3 years, instead of looking at the exact number of days in between.

[dmcnab]

...OK...but would I get the same result in calculations of this nature if I multiplied (or divided, as necessary) by 365, instead of 365.25, b/c I don't know that Excel uses something like 12 & 2/3 years..??

[HansV]

No, if you use 365, the result would be off by an amount that increases with the number of years.
If you want to get the same results as the actuary, you'll have to find out the exact method they use for calculating intervals. It's not necessarily a "logical" definition.