The 2020 budget season is rapidly approaching. Calculating payroll tax expense presents some unique issues. When budgeting for highly compensated employees, they may reach the limit during the year, at which point, no more expense is incurred. For example in 2020, the Social Security Trustees Project projects the FICA wage cap to be $136,800; and it is expected to gradually increase in future years. The concept is the same with unemployment tax, but the limit could be much lower. For example, Arizona’s unemployment only applies to the first $7,000 of wages paid to a worker.
Calculating these limits can be a challenge in Excel. If you are having this issue, hopefully this post will help. Following is an budget calculation with three employees, two of which reach the example limit of $136,800. The same method can be used to calculate federal or state unemployment taxes simply by changing the amounts in the two cells in column E. (Note that the example uses two named ranges, FICAPercent and FICALimit).
Here is the Excel Formula in cell C16, which must be copied down:
It says: Take the lesser of the first month’s salary, or the FICA limit, times the FICA rate.
Months two through twelve:
Here is the Excel Formula in cell D16, which must be copied across and down to months two through twelve:
=+MIN(SUM($C8:D8),FICALimit)*FICAPercent – MIN(SUM($C8:C8),FICALimit)*FICAPercent
Here’s what it does: The FICA limit formula is different for months two through twelve and has two sections, separated by the minus sign. The first section determines the lesser of the year to date salary, or the FICA limit, times the FICA rate. The second section determines the lesser of the prior month’s year to date salary, or the FICA limit, times the FICA rate. Lastly, the formula subtracts the prior year to date result from the current month one, to arrive at the FICA expense for the month. Once the limit is exceeded, no more tax is calculated.
“Make everything as simple as possible, but not simpler.” – Albert Einstein