Calculating Cumulative Contributions

What is the figure that you have worked out by hand?

If you are following a normal exponential growth rate with no trail, then your formula could just simply be the exponential growth equation. This would be something along the lines of:

(A1)*(1+A3+A4)^(A2)

But again, I'm not sure if this is matching what you are calculating by hand.

Sorry for not being clearer, each year the employer will pay 10% of the current salary into a staff pension. We'd want to calculate the total that the employer would be expected to pay if the employee worked to full retirement age.

The calculation we did by hand to verify is to work out the salary in one column which increases by 2% each year. We then calculated the 10% each year that the employer would need to contribute and summed up the total contribution column over the 16 years.

i.e

Year	Salary	Contribution
1	25000	2500
2	25500	2550
3	26010	2601.10

etc.

So the example you just posted is missing the inflation cost column as well, no? If that is the case, the formula I posted above will work. It is a simple exponential growth with cummulative interest equation. It would look like this:

P(t)=P_o(1+r)^t

Where:
t is time (in years)
P(t) is the final amount at time t
r is the rate (cumulative of 10% plus 12% since they are both percentages of the yearly salary)

In your example, your calculation should be 25000 is Year 0, not 1, since the interest rates don't apply to that value. Otherwise, you can extend your time period to 17 years.

See attached for a worked out solution. The data in columns F:U are from what you provided. U2 contains the ultimate payout the employee can expect. E6 shows the calculation that is achieved using the exponential growth formula. They are the same values.

Hope this helps!

Thanks for your help, I'll have a look when I get back to work tomorrow.

Edit, just checked the attachment, the salary should only increase by the inflation rate of 2% each year, the 10% paid into the company pension is separate from the salary.