How to calculate pension contribution on salary?

I'd appreciate help on a formula to calculate pension contributions based on annual salary where contributions are based on 4.5% of the first $41,100 of salary PLUS 6% on the balance of the salary. For example annual salary $50,000 (I wish!!!) Contributions of $1,849.50 (4.5% of $41,100) PLUS $534.00 (6% of $8,900) equal $2,383.50 NOTE While this is example is for a salary > $41,100 the formula who also need to calculate for salaries below $41,100.

Thanks for any help Peter

Hi pgruening,
If salaries are listed in column A starting in A2, then in B2 place the following equation:
IF(A2<=41100,.045*A2,IF(A2>41100,1849.5+.06*(A2-41100)))
then sweep B2 down column B until the end of the salaries. This should account for salaries from $1 to greater than $50K, unless there are other factors.

I hope this helps. GeorgeF

Thanks Bruce worked perfect,

Peter

pgruening wrote:
> I'd appreciate help on a formula to calculate pension contributions
> based on annual salary where contributions are based on 4.5% of the
> first $41,100 of salary PLUS 6% on the balance of the salary. For
> example annual salary $50,000 (I wish!!!) Contributions of $1,849.50
> (4.5% of $41,100) PLUS $534.00 (6% of $8,900) equal $2,383.50 NOTE
> While this is example is for a salary > $41,100 the formula who also
> need to calculate for salaries below $41,100.

First, I suggest that you put the threshold ($41,100) into
a cell (e.g, A1). You might also put your salary into
another cell (e.g, A2). Then the pension contribution can
be computed as follows:

=4.5%*MIN($A$2,$A$1) + 6%*MAX(0,$A$2-$A$1)

This would be more readable if $A$2 and $A$1 are named
cells (e.g, Salary and Threshold).

If Salary is less than Threshold, MIN() will compute
only 4.5% of Salary, and MAX() will compute 0.