John also mentioned payroll. I will admit that this is an area where I am especially gear-headed, so skip this unless you enjoy the dorky energy of ‘Newton’s method applied to payroll deductions’.

First thing: if you have many employees (>5), or pay payroll in multiple provinces/states, or actually pay hourly workers with variable numbers of hours, or overtime, or union dues, or anything like that, then for the love of Cthulhu, buy the payroll tax updates and just use your accounting software. There’s no point messing around with Excel when someone has already figured out how to do everything.

Quick start for Americans: CPP = Canada Pension Plan (think Social Security). Medicare – not a payroll tax in Canada. EI = Employment Insurance, think Unemployment insurance. There is no weird state/federal system for EI here like there is for UI in the States; it’s just a federal responsibility. Also, owners and their family members are not eligible to receive EI payments, so they’re not required to pay EI premiums.

If you have few employees on a regular (eg monthly or biweekly) salary schedule, and the salary doesn’t change, then it’s easy to do the payroll deductions manually. Here’s what I used to do:

- Go to the online payroll deductions calculator
- Enter the gross salary and deductions status (CPP not exempt, EI exempt for owners; nothing exempt for everyone else)
- Click through two pages to the results
- Print out and file this calculation so you can refer to it at year-end (I generally scrawl the employee name at the top)
- Create a Purchase (not payroll!) transaction. The accounts you want to hit (in stock Simply Accounting) are:
- Gross pay – 5410 Wages & Salaries
- Employer CPP – 5430 CPP Expense
- minus 2 times CPP amount – 2185 CPP Payable
- minus Tax amount – 2190 Federal Income Tax Payable

The balance of the transaction will be the net pay. Make it payable to the employee name and then store the transaction (Purchase | Store) as ‘[Employee Name] Payroll 2013’, recurring monthly (or biweekly, whatever).

- To use the transaction, recall it (Purchase | Recall) at the beginning of the month and post it, due at the end of the month. Post all your payroll transactions, then note the total amount of CPP and Income tax payable (balance of 2185 and 2190). Enter a transaction for the 15th of the next month paying that amount to CRA. I have a stored transaction for this as well, since it doesn’t change.
- Payroll is done. Oh yeah, make sure you write the checks and put in the online transfer to the CRA tax account. But the difficult part is done.

That’s what I **used** to do. If you thought that was bad, then quit reading now, because the dorkiness has not yet begun!

The thing is, I don’t like budgeting with odd amounts. So I wanted my take-home pay to be a round number. But because of the dumb way payroll tax deductions are calculated, it’s not obvious how to make that work out. So there’s a spreadsheet (sadly, not a multi-colored one)…

Here’s a link to the online version (Google Docs) and here’s an Excel version, which is what I actually use. Explanation follows, you can follow along if you’re so inclined. This work replaces steps 2 and 3 above with an iterative process until we get the take-home pay we want. There’s also a calculation of marginal tax rate.

Start with a target take-home salary that we want (in the worked example, this is $3,000). In the example I use this as my initial guess, which is dumb-but-good-enough. In real life I use the gross salary that I calculated from the previous year. Enter the initial guess into column B (cell B6). Click through the two pages of PDOC and get the total deductions (sum of CPP, EI, and FITW); that’s all you need for now. Enter this into cell D6. Cells E-I are now calculated for you and a new guess is calculated and entered into B7. On the PDOC, click “Modify the Current Calculation”, and enter the new guess as the salary; click through to get the updated deductions, and then repeat the process until the net salary in column F converges to the desired net salary.

This is just Newton’s method, and as you can see from the worked example, it has excellent convergence – we’d expect at least quadratic, but the actual convergence is better because the underlying formulas are mostly linear. Fprime is calculated as the local first derivative of the deduction function based on the previous guess, and the new guess is just linearly extrapolated. It’s simple and it’s good enough.

An iterative approach like this is necessary because the tax calculations are complicated and non-linear, and quite hard to invert. Initially (15 years ago) I tried to find a closed-form or even table-based lookup. But once we started making contributions to retirement accounts (on which payroll tax but not income tax is assessed), I gave up on a closed form and switched to this iterative system.

Having finished the calculation, I generally do some basic checks. I enter the calculated amounts, line by line, in B14-B20. (Using cell references is evil because the guess amounts are not actually round cents.) This then calculates the annual gross and net pay. I check out the CPP maximum for the current year and make sure I’m not overpaying CPP, since the CRA will not refund the employer portion of overpaid CPP. The annual CPP paid in the sample calculation is $2078.04, which is less than the 2013 CPP max of $2356.20, so it’s fine. If the calculated CPP came out to be more, then I’d just reduce the CPP payment to the calculated max monthly amount and increase the federal withholding by the same amount to keep the net pay where I want it.

The next bit is the monthly contribution to the CPP and FIT Payable accounts, AKA the monthly payment to the Receiver General. This is double the CPP plus the tax, plus both sides of EI. I don’t know much about EI because in Canada owners are not required to pay EI premiums. The total salary expense is the cost to the company to have the employee, and as such, it provides a floor for the amount of value the employee should be creating. I allow about $1,000 a month for overhead, so I set my monthly minimum billing goal to be $1000 + salary expense.

Finally, there’s a marginal tax calculation. Go back to PDOC one last time and enter the gross salary you calculated plus one dollar and copy the relevant withholding amounts into G28:G35. Now your marginal CPP, EI, Federal, and Provincial tax rates are shown. Our imaginary sample employee lives in Alberta (10% flat income tax), is still paying CPP on the marginal dollar earned (gross pay is <$51,100), and is in the 21% federal bracket. The average (effective) tax rate is 21% overall, which is not bad; the marginal tax is 36%, but what can you do.

What you can do is make some RSP contributions (Retirement Savings Plan, think Roth IRA or 401(k)). But this post is already long enough, that can wait until next time.

@sammikes It’s the secant method if you approximate Fprime using the first difference #pedant

Excel has Goal Seek – would that work?

@John – Goal seek will work if you reproduce the tax tables in Excel. It can’t iterate over the CRA web forms (at least not in their current, manual form).