Salary and Pension Increases
 

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

Salary and Pension Increases
 

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

Salary and Pension Increases
 

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

Salary and Pension Increases
 

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

Salary and Pension Increases
 

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

Salary and Pension Increases
 

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