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


--
pgruening
------------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011
View this thread: http://www.excelforum.com/showthread...hreadid=465683

This should work for you:

=IF(A141100,(41100*0.045)+((A1-41100)*0.06),A1*0.045) where A1 holds
the salary to calculate.

Values less than or = to $41,100 will calculate at 4.5%

HTH


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465683

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(A241100,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


--
GeorgeF
------------------------------------------------------------------------
GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124
View this thread: http://www.excelforum.com/showthread...hreadid=465683

Thanks Bruce worked perfect,

Peter


--
pgruening
------------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php...o&userid=26011
View this thread: http://www.excelforum.com/showthread...hreadid=465683

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.