| Company Contribution to Pension - Shortfall [message #373479] |
Mo, 13 März 2006 12:12 |
|
Hi
Can someone advise me how I would calculate the following?
For the last two years the organisation my wife works for has not increased
the company contribution to her pension, inline with her salary increases
etc. They have continued to pay the initial amount.
My wife brought this to their attention when it initially happened and has
raised it a number of times since. Each time being assured that it would be
sorted out, but it never is. We aren't talking great sums of money - but its
now beginning to become annoying.
My wife is not the only employee affected and one of the others has taken
formal advice and was told that not only should the company pay the
backdated monies immediately but that they should also additionally
reimburse the lost investment. How do I calculate this?
Record of Pension Payments/ShortFall can be found at
http://www.djpce.orchidserve.com/PenShortfall.xls
I have obtained the following growth data from the Pension company:
Oct to Apr 03 -0.99%
Apr to Oct 03 13.42%
Oct to Apr 04 6.5%
Apr to Oct 04 3.71%
Oct to Apr 05 5.54%
Apr to Oct 05 12.21%
Any suggestions
tia
|
|
|
| Re: Company Contribution to Pension - Shortfall [message #373489 ] |
Mo, 13 März 2006 13:57 |
|
OK, I'm assuming that the type of pension in question is a Group
Personal Pension or some sort of money purchase plan.
An easy way would be to take an average of the growth rate which
equates to about 6.71%, and project this forward on top of the existing
fund plus the ongoing contributions. i.e. for three years (a *
1.0671^b)
where a = starting amount or premium, and b is the number of years.
This is not too accurate. For a more accurate estimate you need to do
is open an excel sheet, and do a seperate projection for each time
period, (which I assume is monthly). Put the starting figure is cell
A1, project it forward by -0.99% in cell B1 - then cell A2 is the sum
of cell A1 + B1 + the new monthly premium - then keep doing this for
each month, adjusting the growth rates as necessary.
If you want to be really accurate, you need to factor in the effect of
the charges on the pension - although the fund may have grown by 6.7%,
the effect of charges may drag this down.
So if an annual management charge was 1% p.a., you would need to alter
the projected figure to show something like (if the growth rate is
13.42%)
(1.1342*((1 - (0.01 / 365) ^ (365 - 1)))
Therefore, enter the starting figure in A1
B1 should have the formula:
=(A1*((0.99)*((1-(0.01/365)^(365-1)))))
Cell A2 should be something like:
=(B1+100) // where 100 can be changed to the monthly premium.
Now, assume the return is now 13.42 instead 0.99, cell B2 would be:
=(B1*(1.1342*((1-(0.01/365)^(365-1)))))
I'm not sure if the first figure projecting negative numbers is the
correct method for Excel (I don't use it that often) - maybe someone
can correct me on this.
However, this is the best I can think of off the top of my head, given
that I don't know the ins and outs of the particular scheme.
Rgds
Neil
Financial Calculators & Tools
www.invidion.co.uk
|
|
|
| Re: Company Contribution to Pension - Shortfall [message #373490 ] |
Mo, 13 März 2006 14:01 |
|
Sorry, I just noticed my typo:
>> then cell A2 is the sum
>> of cell A1 + B1 + the new monthly premium
Cell A2 should be the sum of just B1 + the new premium.
|
|
|
| Re: Company Contribution to Pension - Shortfall [message #373492 ] |
Mo, 13 März 2006 14:05 |
|
Sorry, just realised another mistake. I obviously rattled the first
message off too fast.
You would obviously need to project on a monthly basis (the above
assumes an annual projection).
You would probably need another column to calculate the assumed growth
after charges (which is the what the formula above does) then project
each column with the assumed growth rate divided by 12.
|
|
|
| Re: Company Contribution to Pension - Shortfall [message #373493 ] |
Mo, 13 März 2006 14:05 |
|
Sorry, just realised another mistake. I obviously rattled the first
message off too fast.
You would obviously need to project on a monthly basis (the above
assumes an annual projection).
You would probably need another column to calculate the assumed growth
after charges (which is the what the formula above does) then project
each column with the assumed growth rate divided by 12.
|
|
|
| Re: Company Contribution to Pension - Shortfall [message #373506 ] |
Mo, 13 März 2006 16:48 |
|
<neil [at] invidion.co.uk> wrote in message
news:1142255149.597157.22230 [at] u72g2000cwu.googlegroups.com...
> Sorry, just realised another mistake. I obviously rattled the first
> message off too fast.
>
> You would obviously need to project on a monthly basis (the above
> assumes an annual projection).
>
> You would probably need another column to calculate the assumed growth
> after charges (which is the what the formula above does) then project
> each column with the assumed growth rate divided by 12.
>
Thanks for that Neil - I appreciate it
|
|
|
