Results 1 to 7 of 7

Thread: Present value

  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi all....this is my first time online since the change to the new servers and this new look....very nice and thank you to all who worked on it and made it happen.....the HELP files in Excel are pretty poor and they have been of no use in heloing em figure out how PV formula works.

    Here is my question/example: at present, I have 30 staff who will get a monthly pension, at age 70. I assume they will have a normal lifespan for a male and a female, based on actuarial charts. The monthly amounts paid to each person will vary, depending on length of service etc etc. The pension plan is funded by a monthly employee payroll deduction and a monthly employer payment....right now, everyone acquires pension at the rate of 3% for each year of service. However, in the past, people acquired it at the rate of 2%/month of service and then at 2.67%/month of service. As of year 2000, everyone is the same at 3%/month of service. These differences in accumulation rate can produce different payout amounts for people, even if their length of service & age is the same, b/c the accumulation rate was 2% until 1998 and then went to 2.67% until 2000 and then went to 3%.

    If I want the people with pre-2000 service to have a pension equal to the post-2000 staff (ie: have ALL service at 3%) I will have to inject a load of money into the fund now, in order to have it grow and be available to pay out down the road. In order to have these 3-tier pensions (2%, 2.67%, 3%) pay out like a single-tier (3%) pension, I believe that I have to make some assumptions (eg: that each person works til age 70, rather than retire early); I also will assume normal life expectancy for each male and female; I will then figure out the monthly difference between what the current plan will pay for each 3-tier person if they work till 70 versus what the plan will pay if they were a single-tier person and then total up all the 'differences' for all employees to come up with the total cost of increasing the pension payouts.....I think this will give me the total amount of money that I have to pay into the pension fund today in order to make the monthly payments as they start to come due when the 3-tier people start to retire at 70.............does this approach sound proper, is my first question?

    Secondly, what I am trying to figure out is a present value formula (??) that will tell me the total amount of money needed today in order to fund that total 'difference' so that the 3-tier people & the employer can figure out how to divide it b/w employer and each 3-tier employee (each staff's liability will be different, depending on their start date and whether they have 2% and 2.67%, or just 2.67%)

    I don't understand the PV formulae and so I am wondering if someone can tell me if my approach is correct in principle, and, whether there is a formula that will assist in coming up with the amount of money needed today in order to make up for the differences in accumulations rates.....as always, thanks for any help.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good afternoon

    Speaking from personal experience I have found the new search feature much more intuitive and in fact have had to ask less questions. Perhaps the subject of PV has not come up before or that abbreveation was not used. Like buses and trains I am sure that an expert will come along soon.

    Whilst waiting for one though perhaps this MSKB might help which I Googled for my typing in using the PV function in Excel

    Using the PV Function in Excel

    This site also has some examples

    Examples of the PV function

    HTH for now
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....thank you for that suggestion....I have made up a sample file of what I am trying to do....I am loking for a PV formula that will tell me how much money I need to set aside today, assuming a given interest rate over a given time period (6 years) in order to arrive at the sum I need......see sample
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The interest rate is in G13, the additional amount in E13. The amount you need to set aside is given by the formula

    =PV(G13,6,0,E13)

    where 6 is the number of years (you could place this number in a cell too). The result of the formula will be negative since it's an amount you pay (credit = positive, debit = negative). If you'd rather see a positive number, use

    =-PV(G13,6,0,E13)

    or

    =PV(G13,6,0,-E13)

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hello Hans....thank you for the great work on the re-design of the webpage....I saw your reply to my post by re-loading the page....under the old webpage, I was set up to get email notification of replies....it seems that did not carry over to this rendition of the website; where can I find 'my profile' to double-check that setting?

    RE: the Present Value calc that I am working on, do I gather that your formula tells me the amount of money I need today, assuming it earns interest at 5%/annually for 6 years in order to come up with the $59,461 in my example that is needed to pay the additional amount of $3,964 over 15 years, with the payout starting in 2015 (6 yrs hence) ?

    And are you saying that the full amount of $59,461 gets paid out all at once, or minimally will sit there to be paid out at the rate of $330/mthly for 15 yrs (180 payments), or perhaps 12 annual payments of $3,964.....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    My role in the redesign of the Lounge was relatively small.
    Your control panel can be reached by clicking Controls in the bar near the top of each Lounge page.
    There's a link Email Settings on the left hand side in the control panel where you can specify how you want to be notified.

    The PV formula that I posted calculates the amount you have to put down now in order to get the additional amount at the end of the six year period (i.e. all at once) with the specified yearly interest. There are no yearly payments, either by you or to you.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Hans, for your help...I gather that there are a variety of different 'payment' scenarios that could be assembled depending on the situation...for the moment, this is the information that I was looking for....thanks again.

Posting Permissions

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