I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks

To demonstrate the formula I've entered the actual values within the formula
but in practice it would be better to use cell addresses for the values:-

To calculate final salary:-

=20000*(1+3%)^5

Where 20000 is todays salary, 3% is the annual adlustment and 5 is the
number of years to work.

To work out total pension contributions put the starting salary in say A1
and in A2 type

=A1*1.03

Drag this down. In B1 type
=A1*0.16 to get the pension contrinbution for that year and drag down and
sum the column.

Mike

Mike


"tartan tim" wrote:

I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks

1) If S is the initial salary, R the rate of increase, and N the number of
years, then the final salary is S*(1+R)^N. Or use the FV function with
"payments" of zero.

2) This function called with =PENSION(salary, pay-increase-rate,
pension-rate, years) seems to do it. Pension are paid at end of year hence
N+1

Function pension(S, r, p, n)
For j = 1 To n + 1
pension = pension + (S * p)
S = S * (1 + r)
Next j
End Function

New to VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"tartan tim" wrote in message
...
I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final
Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks

Thanks Mike - You learn something everyday. Consider yourself a teacher.

"Mike H" wrote:

To demonstrate the formula I've entered the actual values within the formula
but in practice it would be better to use cell addresses for the values:-

To calculate final salary:-

=20000*(1+3%)^5

Where 20000 is todays salary, 3% is the annual adlustment and 5 is the
number of years to work.

To work out total pension contributions put the starting salary in say A1
and in A2 type

=A1*1.03

Drag this down. In B1 type
=A1*0.16 to get the pension contrinbution for that year and drag down and
sum the column.

Mike

Mike


"tartan tim" wrote:

I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks

Thanks Bernard - You got there the as did Mike so many thanks to you both.

"Bernard Liengme" wrote:

1) If S is the initial salary, R the rate of increase, and N the number of
years, then the final salary is S*(1+R)^N. Or use the FV function with
"payments" of zero.

2) This function called with =PENSION(salary, pay-increase-rate,
pension-rate, years) seems to do it. Pension are paid at end of year hence
N+1

Function pension(S, r, p, n)
For j = 1 To n + 1
pension = pension + (S * p)
S = S * (1 + r)
Next j
End Function

New to VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"tartan tim" wrote in message
...
I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final
Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks

Mike, just to continue your version, how can I get the pensions total to
calculate without dragging down and summing the column. I'm trying to show
this as a one page spreadsheet and want the total pension for each employee
totalled on the same line that has other details about them. eg age etc

Cheers

John
"Mike H" wrote:

To demonstrate the formula I've entered the actual values within the formula
but in practice it would be better to use cell addresses for the values:-

To calculate final salary:-

=20000*(1+3%)^5

Where 20000 is todays salary, 3% is the annual adlustment and 5 is the
number of years to work.

To work out total pension contributions put the starting salary in say A1
and in A2 type

=A1*1.03

Drag this down. In B1 type
=A1*0.16 to get the pension contrinbution for that year and drag down and
sum the column.

Mike

Mike


"tartan tim" wrote:

I have a group of employees who are on different salaries and different
percentage contributions from the employer to their pension fund - some 6%
some 18%. I need to project the total cost of each employees pension upto
their retirement age of 65 years. So the formula I need has to include an
annual pay award each year over that time while calculating the pension
contribution as salary rises.

The formula would look something like this:

1. Salary + say 3% pay award each year for say 30 years - Show final Salary
2. Employer pension contribution (of Salary) for same period - Show total
employers pension contributions for that period.

Any help ? Thanks