How to calculate pension contribution on salary?

[pgruening]

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

 

[GeorgeF]

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

 

[pgruening]

Thanks Bruce worked perfect,

Peter

 

[joeu2004]

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.