Results 1 to 7 of 7
Thread: Present value

20090322, 11:52 #1
 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 pre2000 service to have a pension equal to the post2000 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 3tier pensions (2%, 2.67%, 3%) pay out like a singletier (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 3tier person if they work till 70 versus what the plan will pay if they were a singletier 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 3tier 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 3tier people & the employer can figure out how to divide it b/w employer and each 3tier 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.

20090322, 14:11 #2
 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 nowCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20090323, 09:06 #3
 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

20090323, 09:30 #4
 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)

20090323, 15:50 #5
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Hello Hans....thank you for the great work on the redesign of the webpage....I saw your reply to my post by reloading 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 doublecheck 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.....

20090324, 05:55 #6
 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.

20090324, 06:21 #7
 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.