Hopefully, you’ve managed to have your PPP application approved and the funds have been disbursed. If not, don’t lose hope. Round two is coming!
I’ve put together an Excel spreadsheet to help in calculating the amount of a PPP loan that may be forgiven. I say “may” because the final rules on forgiveness aren’t published yet, so the spreadsheet is based on my current research and published guidance so far.
It’s a pretty basic sheet. The amounts you need to enter are shaded in green. Here are some tips:
- Enter the total amount of your PPP loan in the “PPP Loan Amount” field. This should be your total/net PPP disbursement.
- Enter the date the funds hit your account in the “Date funds disbursed” field. Current guidelines are that you have eight weeks to accumulate the amounts used to calculate the forgiveness component of your loan. This (hopefully) will change, but at the moment, you have 56 days from disbursement to allocate the funds.
- Enter your average number of full-time-equivalent employees (FTEs) for the period January 1, 2020 through February 29, 2020 in the “Avg FTEs 1/1 to 2/29” field. Calculate your average FTEs by averaging the number of FTE employees for each pay period in the look-back period. If you employ part time employees, it is possible this number could half a half FTE (i.e., 3.5). The spreadsheet will calculate your average number of FTEs during the eight week measurement period.
- Begin entering your eligible expenses! I’ve broken the categories into broad sub-categories based on the PPP guidelines published by Treasury, and included a line for each date in the eight week measurement period, since you most likely pay various expenses on different days of the month.
Some general thoughts on expense categories, based on my reading of the guidance from Treasury and a couple of accountant websites.
Allowed Payroll Costs
Salary and wages are pretty self explanatory. Use the gross wages paid to your team members (including yourself if you take a paycheck).
Commissions are again self explanatory. Be careful to only include commissions you’re normally pay. There are restrictions on front-loading payroll costs to get them into the eight week measurement period, the specifics of which I’ll leave for you to research.
Tips should be included if you report your team member’s tips as part of payroll.
Number of FTEs should be the number of full-time-equivalent employees on that payroll run.
Healthcare costs should be the amount the company pays for your team member’s healthcare – be sure to not include an amount the employee contributes out of their check toward the healthcare costs. For example, if the company pays half of the cost of insurance, and the employee has an amount deducted from their check to cover the other half, you can only include the amount the company pays.
Retirement costs would be any amount the company contributes toward the employee’s retirement costs, such as a 401k or SIMPLE-IRA contribution. Again, only include the actual amount the company pays, and not any amounts deducted from your team member’s payroll.
State Taxes would be an amount levied against the company based on the amount of compensation. The best example of this would be state unemployment insurance contributions. This amount should not include any state withholding taxes deducted from your employee’s checks – only an amount the company is required to pay as a result of compensation.
Local Taxes follow the same guidelines as the state taxes.
The spreadsheet will calculate the the total payroll cost for the line, and accumulate payroll costs for the eight week measurement period.
Mortgage Interest is pretty self explanatory – include the amount of mortgage interest you pay toward occupancy of your building. Do not include the principal portion. You also can’t front-load this expense by paying months ahead, in order to increase the forgiveness amount of your PPP loan. If you’d normally make two mortgage payments in eight weeks, only include two payments worth of mortgage interest in the spreadsheet.
Rent is obviously the amount of rent you pay for your business (or, the amount of your home office deduction if you regularly include home office deductions on your federal income tax return). Again, if you’d normally only pay two months of rent in the eight-week period, you can only include two months of rent here.
The spreadsheet will calculate the total occupancy costs for the eight week measurement period.
There are multiple interpretations of what counts as a utility for purposes of PPP forgiveness, and I expect you’ll see this change over the next few months. In general, the consensus across the articles I read align with what I’ve included in the spreadsheet.
Electricity is the gross amount of your electric bill.
Gas is the gross amount of your natural gas bill.
Water is the gross amount of your water bill. There’s no specific guidance on this, but if your municipality bills quarterly for water (as mine does), and you don’t get a bill during the eight week measurement period, I think it would be a fair interpretation to use two-thirds of the water bill in your forgiveness calculation. I am NOT an accountant and I could be entirely wrong on this. Use your judgment or that of your accounting professional!
Transportation/fuel has been interpreted to mean fuel costs for business vehicles during the measurement period.
Telephone would be your landline, VoIP and cell phone costs during the eight week measurement period.
Internet is your gross Internet access costs incurred during the measurement period.
Forgiveness calculations (the good stuff!)
These are tough calculations to do in a spreadsheet, because there are a bunch of if/then requirements. I’ve done my best, and I think the calculations will work and be accurate in most cases except where you’ve had a reduction of more than 25% in the total payroll costs for your team. For example, you’ve maintained FTE head count, but everyone has gotten a 50% pay cut. If that’s the case, stop here because this spreadsheet doesn’t take that into account at all.
The spreadsheet calculates the total payroll cost by summing the totals of each payroll line during the eight week measurement period, and generates a percent of total PPP loan for informational purposes.
Likewise the spreadsheet sums all non-payroll costs and calculates the percent of total PPP loan. This calculation is important, because only 25% of the PPP loan may be forgiven for non-payroll costs.
The amount forgiven for payroll costs will be the total of your payroll costs, up to the amount of your PPP loan less allowable non-payroll costs.
The amount forgiven for non-payroll costs is calculated as the total of all non-payroll costs up to 25% of the PPP loan amount. Amounts above 25% of the PPP loan amount are discarded.
The total forgivable amount is calculated as the sum of your total payroll costs plus the allowable amount of your non-payroll costs, up to the amount of your PPP loan.
HOWEVER, the forgivable amount is reduced using the forgiveness calculation.
The forgiveness calculation is computed simply as the number of FTEs during the period of January 1, 2020 through February 29, 2020 divided by the average number of FTEs during the eight week measurement period. If your forgiveness calculation is above 75%, you’re in good shape! If the forgiveness calculation is below 75%, the amount calculated as potentially forgivable is computed as the forgivable amount times the forgiveness calculation. For example, if you had 10 full time equivalent employees before the pandemic, and during the eight week measurement period you had only 5 FTEs (meaning you only had 50% of your FTE staff during the eight week measurement period), the forgivable amount of your PPP loan is only 50% of the potential forgivable amount.
The final calculation is the amount you may owe – computed as the total of your PPP loan less the potential forgivable amount.
The important disclaimer
I am not an accountant or an attorney!
I put the spreadsheet together based on my reading about the PPP and WSJ articles about the forgiveness component, some accounting blogs, and the websites of some of the banks and fintech companies participating in the PPP program.
This spreadsheet is likely not 100% accurate, and may, in fact, be 100% wrong.
Please only use it as a guide for helping you and your financial professional to track expenses related to your PPP loan.
In our office, we are keeping track of the expenses in this spreadsheet, but I am also keeping copies of everything related to any expense I’m counting. So, paper copies of payroll journals, copies of payroll checks, copies of utility and health care bills along with the checks used to pay them (or if paid online, the credit card receipt). The goal for my company is to keep a super-clean journal of every expense, followed up with physical proof of the expense.
That way, when “they” come – and, we know they will – I can simply hand whomever is auditing the forgiveness component a copy of the spreadsheet and a folder/binder of every check, invoice and receipt as backup for the expense.
I hope you find the spreadsheet and information useful, and please let me know if you find any errors or updates!
Download the spreadsheet here: PPP_Loan_Forgiveness_Calculator