FICA tax calculation with wage cap

[EHC]

Good afternoon,

First time poster and would appreciate some guidance on this topic.

I have to calculate the FICA tax liability for an institution. I have the salary information for all the employees. I need to figure this out on a monthly basis. The issue is that FICA is calculated using two separate rates. The first $106,800 is taxed at 7.65% and the remaining income is taxed at 1.45%. So just for illustration purpose, lets say the person makes $50,000 per month.

The first two months of income will be taxed at 7.65% but then in the third month, some of the income (up to $106,800 cumulatively) will be taxed at 7.65% and the remainder of the income in the third month (and going forward since they hit the $106,800 cap) will be taxed at 1.45%.

Is there a way to set up a formula for this? My issue, in this illustration, is that in the third month i can either tax it at one of the two rates and not a portion of the income at one rate and the other portion at the other rate.

Thanks!

 

[dave3009]

Hi and Welcome

Something like this?

Excel Workbook
	A	B	C
1	Month	Payment	Tax
2	1	50000	3825
3	2	50000	7650
4	3	50000	8796.6
5	4	50000	9521.6
Sheet3

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(SUM($B$2:B2)<106800, SUM($B$2:B2)*0.0765, (106800*0.0765)+(SUM($B$2:B2)-106800)*0.0145)

 

[JoeMo]

Here's another approach if you want to breakout the tax by month:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ann Salary	$600,000		Threshhold	$106,800		Below	7.65%	Above	1.45%
2
3		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
4	Mnthly	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000	$50,000
5	cum	$50,000	$100,000	$150,000	$200,000	$250,000	$300,000	$350,000	$400,000	$450,000	$500,000	$550,000	$600,000
6	FICA	$3,825.00	$3,825.00	$1,146.60	$725.00	$725.00	$725.00	$725.00	$725.00	$725.00	$725.00	$725.00	$725.00
Sheet14

 

[EHC]

Thanks for the help guys. JoeMo, i think you showed what i'm looking for. I'm going to give it a try, but it looks good. Thanks to both of you!