Employee Retention Credit Calculation Spreadsheet Excel

COVID-19’s pandemic caused unimaginable hardships to many organizations and businesses around the globe. Many employers have experienced reduced revenues, higher expenses, and disruptions to their operations because of lockdowns, distancing from social media, and health-and-safety measures.

The Employee Retention Tax Credit (ERC) is a refundable credit that employers can use to offset payroll costs.

The ERC was first enacted by the CARES Act in 2020 and was later extended and modified by subsequent legislation in 2021 and 2023. This article will describe what the ERC does, how it operates, and explain how to claim it.

For a brief reading of what the Employee Retention Credit or ERC is, take a look at this video from the YouTube channel “ERC Specialists”. You can also continue below to read an in-depth explanation of ERC.

What is the Employee Retention Credit? Employee Retention Credit Calculation Spreadsheet Excel

Employee Retention Credit is a tax credit that can be refunded to businesses and tax-exempt organizations who had employees affected by COVID-19. The ERC was established by the CARES Act of 2020 and extended and modified in subsequent legislations in 2021 and in 2023. The ERC encourages employers to maintain their workers and to provide health benefits to them during the crisis.

Main Features & Benefits

Credits are equal in percentage to the wages and insurance costs that employees who qualify for them have paid, but there is a maximum per employee.
The credit limit and percentage are dependent on the period of time for which you claim the credit. For 2020 the percentage is set at 50%, while the maximum per employee is set at $5,000. For 2021, there is a 70% percentage and a limit of $7,000 per employee per quarter. For 2023, there will be a 70 percent percentage for the initial two quarters of the year and a 40 percent percentage for the last two. There will also be a limit of $10,000 per employee each quarter. Employee Retention Credit Calculation Spreadsheet Excel
The credit amount is fully refundable, meaning if the credit exceeds your employer’s tax liability on payroll, you will receive the excess as a reimbursement.
The credit can be claimed by employers who experienced a significant decline in gross receipts or a full or partial suspension of operations due to a qualifying government order related to COVID-19. Employers who are considered to be recovery startup businesses may also claim this credit, but only for 2023.
The credit can be claimed by filing an amended employment tax return (Form 941-X) or by reducing employment tax deposits in anticipation of the credit. Employers can also request an advance payment of the credit by filing Form 7200.

> > Click Here to Find Out if You are Eligible for ERC < <

Eligibility Criteria

In order to qualify for Employee Recruitment Credit (ERC), a company must meet the following criteria:

A government order has suspended or halted the business or organization of an employer due to COVID-19 in a calendar year 2020 or 2021.
The employer’s gross revenues for a quarterly calendar period in 2020, 2021 or both were less that 50% (for the 2020 quarter) or 80% (2021 quarter) of its gross revenue for the same year-ago quarter.

The recovery startup rule also applies to businesses that began operating after February 14, 2020 and had average annual gross receipts not exceeding $1 million. These businesses are eligible for the ERC, regardless of whether their business has been suspended or if revenue has declined.

Business Suspension

A government order can either suspend or fully suspend a company or organization if the following conditions are met:

The order prohibits travel, group meetings, and commerce due to COVID-19
The order has an impact on the business or organization
The order applies to all calendar quarters in 2020 and 2021

Examples of government orders which can lead to a suspension of business include:

Orders to stay at home that prevent non-essential companies from operating
Curfews that limit the hours of operation for certain businesses
Capacity limits that reduce the number of customers or clients that can be served by a business
Travel restrictions or travel bans that limit the ability of businesses to transport products or services

To determine if the business was partially or fully suspended by an official order, employers must consider:

The scope and nature of the order as well as how it impacts the business.
The length and frequency of your order and the way it corresponds to the calendar quarters
The impact and magnitude of the order to the business’s revenues and costs

Revenue Decline

A business or organization is considered to have experienced a significant decline in gross receipts if:

The gross receipts of any calendar quarter in 2020 are less than half the gross receipts of the same quarter in 2019.
The gross receipts for any calendar quarter in 2021 were less than 80% of its gross receipts for the same quarter in 2019

Gross receipts can be defined as all the money received by an organization or business from any source during their annual accounting period, without deductions. Gross receipts can include:

Sales of goods and Services
Rents, dividends, and annuities are examples of income streams that include interest, dividends.
Contributions are gifts, donations and grants Employee Retention Credit Calculation Spreadsheet Excel
Membership fees and dues
Gross profits from trades and businesses

To compare gross revenues for different quarters an employer can use:

The same method of accounting (cash or accrual) that it used to file its federal income tax return for 2019
The same calendar year quarters that it used to file its federal employment tax returns (Form 941) for 2019 and 2020/2021
It is the same income sources that were reported on the federal income tax returns for 2019.

Recovery Startup Business

The recovery startup business is one that:

Begun carrying on any business after February 15th, 2020
Average annual gross receipts not exceeding $1 million during the three-year period ending on the tax year immediately preceding the calendar quarterly for which the credit will be determined

The ERC is available to a recovery startup business regardless of whether or not it meets the criteria for business suspension or revenue decrease. There are certain limitations and rules that apply to recovery startups businesses.

Maximum credit per quarter: $50,000
The credit can only be used for wages paid between the third and the fourth quarters of 2020
Credits for recovery startups are subject to a maximum of $250 million.

> > Click Here to Find Out if You are Eligible for ERC < <

Credit Amount and Calculation

ERCs have different rules and amounts depending on the length of time and type of employer. The main factors that affect the ERC are:

How much an employer’s company was affected by the pandemic.
How many employees an employer had in 2019, 2020/2021 or whether they worked, or did not work during the pandemic
How much each employee received from their employer and how they were covered by health insurance in the pandemic

The employer has to fill out some forms and send them to the IRS to claim the ERC. The forms must include the total amount paid by the employer to employees, their health insurance coverage and the reasons why they are eligible for the ERC. The IRS will check the forms and give the money to the employer. The employer could use this money to pay health insurance for employees or to get refunds and credits for payroll taxes.

The ERC is not available forever. It started in March 2020 and will end in September 2022. The employer has to claim the ERC before it expires or becomes unavailable. The employer also has to use the money wisely and not waste it. Employee Retention Credit Calculation Spreadsheet Excel

Below is more detailed information on the credit amount and calculation of ERC.

Time Period

In 2020, 2021, & 2022, different laws were passed to introduce, amend, and terminate the ERC. The amount of the credit varies according to the time period that it is applied for. The following table summarizes and compares the ERC’s main features for each period:

Time Period	Law	Eligible Employers	Credit Rate	Qualified Wages
2020	CARES Act	Employers with business suspension or revenue decline of more than 50%	50% of qualified wages up to $10,000 per employee per year	Wages paid from March 13 to December 31, 2020
Q1-Q3 2021	CAA and ARPA	Employers with business suspension or revenue decline of more than 20%	70% of qualified wages up to $10,000 per employee per quarter	Wages paid from January 1 to September 30, 2021
Q3-Q4 2021 (Recovery Startup Business)	ARPA	Recovery startup businesses with average annual gross receipts of no more than $1 million,	70% of qualified wages up to $10,000 per employee per quarter (subject to a $50,000 cap per quarter),	Wages paid from July 1 to December 31, 2021,
Q4 2021 – Q3 2022 (Severely Financially Distressed Employer)	ARPA and IIJA	Employers with a revenue decline of more than 90%	70% of qualified wages up to $10,000 per employee per quarter	Wages paid from October 1, 2021, to September 30, 2022

Number of Employees

The number of eligible employees will affect the calculation and definition of health insurance and qualified wages. An employer is considered a small or large employer depending on the time period and the number of full-time employees (FTEs) it had in 2019. The table below summarizes all the rules and thresholds that determine an employer’s size.

Time Period	Small Employer Threshold	Large Employer Threshold
2020	Less than or equal to 100 FTEs in 2019	More than 100 FTEs in 2019
Q1-Q2 2021	Less than or equal to 500 FTEs in 2019	More than 500 FTEs in 2019
Q3-Q4 2021	Less than or equal to 500 FTEs in any calendar quarter in either calendar year beginning after December 31, 2019, and ending before July 1, 2021. If an employer did not have in either calendar year beginning after December 31, 2019, and ending before July 1, 2021, the employer is treated as a small eligible employer if it had less than or equal to 500 FTEs in any calendar quarter beginning after June 30, 2021. For recovery startup businesses, the employer size is irrelevant. For severely financially distressed employers, the employer size is irrelevant if the employer had a revenue decline of more than 90%. Otherwise, the same rules as Q1-Q2 2021 apply.	More than 500 FTEs in any calendar quarter in either calendar year beginning after December 31, 2019, and ending before July 1, 2021. If an employer did not exist in either calendar year beginning after December 31, 2019, and ending before July 1, 2021, the employer is treated as a large eligible employer if it had more than 500 FTEs in any calendar quarter beginning after June 30, 2021.

To count FTEs for a given year or quarter, an employer must use the following steps:

Count the number of employees who worked at least 30 hours per week (or at least 130 hours per month) for each month in the year or quarter
Add up the total hours worked by all other employees (who are not counted as FTEs) for each month in the year or quarter
Divide the total hours by120and round down to the nearest whole number
Add the number of FTEs from Step One and Step Three for each month in the year or quarter
Calculate the average number of FTEs by adding up the monthly totals and dividing by 12 (for a year) or 3 (for a quarter)

Qualified Wages and Health Insurance Costs

Qualified Wages are wages that eligible employees receive during periods of suspension or decline in revenue. The list of qualified wages includes tips, bonuses, commissions, and severance payments, as well as sick leave, family leave, severance, and other compensation. Qualified wages also include the cost of providing health insurance to eligible employees, such as premiums, deductibles, co-pays, and co-insurance.

The calculation of qualified wages, health insurance costs and employer size depends on the time period. The following table provides a summary of the rules for different scenarios. Employee Retention Credit Calculation Spreadsheet Excel

Employer Size	Time Period	Qualified Wages and Health Insurance Costs	Example
Small	2020	All wages and health insurance costs paid to any employee, regardless of whether the employee worked or not	An employer with 80 FTEs in 2019 paid $8,000 in wages and $2,000 in health insurance costs to an employee in 2020. The employer had a revenue decline of more than 50% in Q2 2020. The qualified wages and health insurance costs for Q2 2020 are $10,000.
Small	Q1-Q3 2021	All wages and health insurance costs paid to any employee, regardless of whether the employee worked or not	An employer with 400 FTEs in 2019 paid $12,000 in wages and $3,000 in health insurance costs to an employee in Q1 2021. The employer had a revenue decline of more than 20% in Q1 2021. The qualified wages and health insurance costs for Q1 2021 are $15,000.
Small	Q3-Q4 2021 (Recovery Startup Business)	All wages and health insurance costs paid to any employee, regardless of whether the employee worked or not (subject to a $50,000 cap per quarter)	A recovery startup business that began operations in March 2020 paid $9,000 in wages and $1,000 in health insurance costs to an employee in Q3 2021. The business had average annual gross receipts of $800,000. The qualified wages and health insurance costs for Q3 2021 are $10,000.
Small	Q4 2021 – Q3 2022 (Severely Financially Distressed Employer)	All wages and health insurance costs paid to any employee, regardless of whether the employee worked or not	An employer with 600 FTEs in Q2 2019 paid $11,000 in wages and $4,000 in health insurance costs to an employee in Q4 2021. The employer had a revenue decline of more than 90% in Q4 2021. The qualified wages and health insurance costs for Q4 2021 are $15,000.
Large	2020	Wages and health insurance costs paid to an employee for the time that the employee did not work (up to the amount that the employee would have been paid for working an equivalent duration during the 30 days immediately preceding the period of economic hardship)	An employer with 120 FTEs in 2019 paid $10,000 in wages and $2,000 in health insurance costs to an employee who worked full-time (40 hours per week) in 2020. The employer had a business suspension due to a government order in April 2020. The employee did not work for two weeks in April 2020. The qualified wages and health insurance costs for April 2020 are $2,308 ($10,000 x2/52+$2,000 x2/52).
Large	Q1-Q3 2021	Wages and health insurance costs paid to an employee for the time that the employee did not work (up to the amount that the employee would have been paid for working an equivalent duration during the 90 days immediately preceding the period of economic hardship)	An employer with 550 FTEs in 2019 paid $15,000 in wages and $5,000 in health insurance costs to an employee who worked full-time (40 hours per week) in Q1 2021. The employer had a revenue decline of more than 20% in Q1 2021. The employee did not work for three weeks in Q1 2021. The qualified wages and health insurance costs for Q1 2021 are $5,769 ($15,000 x3/13+$5,000 x3/13).
Large	Q3-Q4 2021 (Severely Financially Distressed Employer)	All wages and health insurance costs paid to any employee, regardless of whether the employee worked or not (only if the employer had a revenue decline of more than 90%. Otherwise, the same rules as Q1-Q32021 apply.)	An employer with 700 FTEs in Q4 2019 paid $12,000 in wages and $6,000 in health insurance costs to an employee who worked full-time (40 hours per week) in Q4 2021. The employer had a revenue decline of more than 90% in Q4 2021. The qualified wages and health insurance costs

> > Click Here to Find Out if You are Eligible for ERC < <

Claim the Credit and Report It

To claim the Employees Retention Credit, an employer must file with the Internal Revenue Service a federal Employment Tax Return (Form941) or a adjusted Employment Tax return (Form941X). The employer is required to report the qualified wages, health insurance costs and credit claimed by each quarter.

Form 941

Form 941 is used by employers to report their quarterly federal tax liabilities, which includes income tax, Medicare tax, and social security tax. Form 941 also allows the employer to claim the ERC for current or future quarters. Form 941 is used by employers to:

ERC reduces the amount that employers must deposit with the IRS in order to pay taxes.
If the ERC is greater than the tax that the employer must deposit, you can request an advance payment. Employee Retention Credit Calculation Spreadsheet Excel
Carry over any excess credit into the following quarter

The employer should:

Use the most recent version of Form 941, which reflects any changes or updates to the ERC laws.
Use the IRS worksheets and instructions to calculate and report the ERC
Use Line 1c to report on the health insurance and wages that eligible employees have received.
Use Line 13d when reporting the credit for each quarter.
Line 13f should be used to report any advance payments made by the IRS.
If you need to receive an advance payment, use Line 24.
Report any credit balance that may be carried forward into the next quarter using Line 25
Sign and date Form 941, attaching any supporting documents, schedules, or schedules.

You can find some helpful tips on how to fill out Form 941 here:

Use electronic filing (e-file) or online services to submit Form 941 faster and more securely
Updates, FAQs, and guidance about Form 941, the ERC, and other IRS forms can be found on the IRS website.
You can also contact a tax expert or the IRS for clarifications and assistance if you need it.

Form 941-X

The Form 941X can be used to make corrections or adjustments on an earlier Form 941. Form 941-X allows employers to claim ERC retroactively. Employers can use Form 941/X for Employee Retention Credit Calculation Spreadsheet Excel

Claim a refund or credit for overpaid taxes due to claiming the ERC
Report additional qualified earnings and health benefits paid to eligible employee that weren’t reported on Form 941.
Correct any mistakes or omissions made on Form 941 that affect the amount of credit claimed

To avoid making common errors and fill out the Form 941-X correctly, employers should:

Use the latest form 941X that reflects changes to laws that are applicable to the ERC.
The IRS has provided worksheets to help you calculate the ERC.
Use Part 2 to indicate which lines of Form 941 are being corrected or adjusted
Use Part 3 of Form 941 to explain why it is being amended or corrected
Line 24 is used to report additional wages and health insurance premiums paid to eligible employees.
Use Line 25 for any additional credit claimed each quarter.
You can use Line 26 to request a refund or credit due to claiming ERC.
Sign and date Form 941, and attach any supporting documentation or schedules

Here are some tips and resources to help you fill out Form 941X:

For each quarter to be adjusted or corrected, you must submit a different Form 941X. Employee Retention Credit Calculation Spreadsheet Excel
Fill out Form 941-X immediately after you find an error in Form 941
Visit the IRS website to get the latest updates, FAQs, and guidance regarding Form 941-X, the ERC, and other forms.
Need clarification? Contact an IRS agent or tax professional.

Deadline and Statute of Limitations

Form 941 must be filed by the last date of the month that follows the end each quarter. For example, Q1 2020 (January-March) Form 941 will be due on April 30, 2021. If an employer has made all the required deposits for the quarter in a timely manner, they can file Forms 941 on the 10th of the second month. After the end of the quarterly period. For Q1 2021 (January-March), form 941 must be submitted by May 10, 2020, Employee Retention Credit Calculation Spreadsheet Excel

The deadline for submitting Form 941X is usually three years following the original date of Form 941 or two after the date on which the tax was paid. For example, for Q1 2020 (January-March), Form 941 was due by April 30, 2020. If an employee filed Form 941 in April 2020 and paid their tax in April 2020, the deadline to file the Form 941 X is April 30 2023. If an employer files Form 941 in April 2020 and pays the tax on June 15 2020, they have until June 15 2022 to file Form 941.

> > Click Here to Find Out if You are Eligible for ERC < <

Conclusion

The Employee Retention Credit (ERC) is a valuable tax benefit that can help employers who were affected by the COVID-19 pandemic keep their employees on the payroll and reduce the impact of the pandemic on their businesses or organizations.

The ERC (Eligible Employees Credit) is a tax credit that can vary depending on the time frame, the number and type of employees employed, and the amount paid in wages and insurance to employees eligible for the credit. The ERC credit can be claimed with IRS Forms 941 or 941X by reporting to them the qualified health insurance and wages costs as well as the amount claimed each quarter.

Do not miss out on this opportunity if you’re an employer that meets the ERC eligibility criteria. The ERC does not last forever. It has a deadline, and there is a statute of limitations for claiming the ERC. Use the resources and tips provided in this article to ensure that you fill out your forms correctly and avoid common mistakes. If you need clarification or assistance, you can contact the IRS.

ERCs can be a huge help to your organization or business and its employees. It can be used to help retain your employees, maintain your cash flow, and recover in the event of a pandemic. We hope that this article helped you to understand more about ERC and the claim process. Thank you for reading. Stay safe.

> > Click Here to Find Out if You are Eligible for ERC < <

Employee Retention Credit Calculation Spreadsheet Excel

What is ERC and what does it do?

Employee Retention Credit: This is a credit that employers can claim if they retained employees during the COVID-19 pandemic.

The CARES Act, passed by Congress in March of this year, was amended in December of that year by the CAA Act. In March 2021, the ARPA Act (American Rescue Plan Act of 2021), was extended.

Is everyone eligible for the ERC?

Not everyone is eligible for the ERC. Only employers who paid wages and retained employees between March 13, 2019, and December 31, 2020, are eligible.

You can read more about the criteria here. Here are some highlights.

The business or organization was suspended (fully or partially) by government order due to the COVID-19 pandemic.
Their gross revenues for a quarter calendar in 2020 or in 2021 were lower than a percentage compared to their gross revenues for the same period in 2019.
They are a recovery startup business that began operations after February 15, 2020, and has average annual gross receipts of no more than $1 million.

How much does the ERC cost?

The amount of ERC that a company will receive depends on a number of factors.

Some of these include the time period and number of employees. Others are the amount paid in qualified wages or health insurance to eligible employees. To learn more about how ERCs are calculated, please read the article.

How to claim ERC

To claim the ERC, an employer must file a federal employment tax reform or an adjusted employment tax return (Form 941-X) with the IRS.

Employers must declare the wages and costs of health insurance paid to employees who qualify and the credit claimed each quarter.

When is the Deadline for Filing the ERC Forms?

There are two different deadlines to file the ERC Forms: Form 941 (Form 941-X) and Form 941 (941).

The last day to submit Form 941 for each quarter is the last calendar month. While the deadline for the Form 941-X will be three years after you filled out the original Form 941. It can be as late as two years after you paid the tax, but the later date is the preferred date.