employee retention credit calculation spreadsheet

Navigating the complexities of the Employee Retention Credit (ERC) can feel like a full-time job. While the IRS has provided various guidelines, having a reliable employee retention credit calculation spreadsheet or interactive tool is essential for business owners and CPAs alike. Use the calculator below to get an instant estimate of your potential credit based on the 2020 and 2021 program rules.

Understanding the Employee Retention Credit (ERC)

The Employee Retention Credit is a refundable tax credit for businesses that continued to pay employees while shut down due to the COVID-19 pandemic or had significant declines in gross receipts from March 13, 2020, to Dec. 31, 2021.

2020 vs. 2021 Rules

The rules for the ERC changed significantly between the two years. A proper employee retention credit calculation spreadsheet must account for these differences:

• 2020 Credit: 50% of qualified wages paid up to $10,000 per employee for the entire year. The maximum credit is $5,000 per employee.
• 2021 Credit: 70% of qualified wages paid up to $10,000 per employee per quarter for the first three quarters of 2021. The maximum credit is $7,000 per employee per quarter ($21,000 total for the year).

How to Use a Calculation Spreadsheet

When building your own spreadsheet, you should include columns for the following data points to ensure IRS compliance during an audit:

• Employee Name and SSN: Essential for record-keeping.
• Qualified Wages: Gross wages paid during the eligibility period.
• Qualified Health Plan Expenses: The portion of health insurance premiums paid by the employer can often be included in the wage calculation.
• PPP Loan Forgiveness: You cannot "double dip." Wages paid with forgiven PPP funds cannot be used to claim the ERC.

Eligibility Requirements

To qualify for the credit, your business must meet one of two tests:

1. The Government Mandate Test: Your operations were fully or partially suspended due to orders from an appropriate governmental authority.
2. The Gross Receipts Test: For 2020, a 50% decline in gross receipts compared to the same quarter in 2019. For 2021, a 20% decline in gross receipts compared to the same quarter in 2019.

Common Pitfalls to Avoid

Many business owners make the mistake of over-calculating their credit by failing to exclude owners' wages or relatives' wages, which are generally ineligible. Furthermore, the interplay between the ERC and the Research and Development (R&D) tax credit is complex and requires careful manual adjustment in your spreadsheet.

Always ensure that your documentation is robust. The IRS has extended the statute of limitations for audits related to ERC claims, meaning you should keep your employee retention credit calculation spreadsheet and all supporting documents for at least seven years.