Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Working backwards to calulate a date (Excel 2003)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working backwards to calulate a date (Excel 2003)

    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.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Working backwards to calulate a date (Excel 20

    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 ??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working backwards to calulate a date (Excel 20

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

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Working backwards to calulate a date (Excel 20

    ...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 ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working backwards to calulate a date (Excel 20

    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.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Working backwards to calulate a date (Excel 20

    ...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..??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working backwards to calulate a date (Excel 20

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •