OOo Spreadsheet for Canadian Payroll Tax

[Michael D. Crawford]

I thought I could stimulate helpful discussion by posting the crucial
elements of my spreadsheet, the tax table and TD1 exemption lookups.

I've been kicking myself for a couple days for not having figured out
how to do this *years* ago.  I didn't know how to use the IF statement
in the spreadsheet I used back in Maine.  Instead, when my gross pay
crossed tax bracket boundaries, I had to manually adjust my rate.  But
as long as I was doing all the other work of setting up my Canadian
accounts, I figured it was time to take the plunge and actually Read The
Fine Manual.

The short time it took me to do so and get my first table lookup working
makes me feel like an idiot for not doing this the very first day I
created my Maine spreadsheet.

I invite you to tell me a better way to do this, maybe by definining
some kind of function instead of copying the following godawful
calculation into each spreadsheet cell that needs it.

I'd also like to know if there is some way to "freeze" a calculation in
OpenOffice, that is to replace the formula for a calculation with its
fixed numerical result, so that changes to the cells that factor into a
calculation won't change the result after it's been frozen.  The reason
is that once a paycheck is written, it would be a Bad Thing to alter any
of its items.

What I do now is calculate the check on one page, and record it by hand
on another, along with a running balance of the tax due at the bank on
the fifteen of the following month, less any payments I've made.  Yes, I
know I should just use GnuCash to record all this, but I'm a
belt-and-suspenders kinda guy.

Another thing I'd like to know is if there is some way I can calculate a 
cell's address on the fly rather than hardwiring it.  That way I can key 
the TD1 exemptions and even the provincial calculations by employee 
number rather than hardwiring them by employee name as I do now.  That 
is, I'd like to do something like

=$Q($4 + $Z3)

where column Z contains the employee number and column Q contains the 
TD1 (or W-4) exemption, with 3 being the row in the employee table for a 
particular employee.

Here is the Canadian federal tax table.  It's on a page called
"Formulas".  "Tables" or "Constants" would have been a better name:

    B                C             D        E
   ------------------------------------------------
4 |                             Rate    Constant
5 |                             R       K
6 |      $0.00   $35,595.00     16.00%      $0.00
7 | $35,595.00	 $71,190.00     22.00%  $2,136.00
8 | $71,190.00	$115,739.00     26.00%  $4,983.00
9 |$115,739.00                  29.00%  $8,455.00

The letters R and K correspond to the factors listed in the Glossary on 
page 26 of the Canada Revenue Payroll Deductions Formulas for Computer 
Programs:

http://www.cra-arc.gc.ca/tax/business/topics/payroll/formulas-e.html

Each deduction and many intermediate results have such a letter code in 
the booklet.  This made it a lot easier to construct my spreadsheet than 
would have otherwise been the case.

Factor K is the amount by which the tax calculated with the rate R 
exceeds the amount that really should be withheld when considering the 
portions of your annualized pay which fall into the smaller tax 
brackets.  That is, the annualized basic tax is ( Annual Pay * R ) - K. 
  The full calculation is much more complicated though because one has 
to also factor in deductions for payments to Employment Insurance and 
the Canada Pension Plan.

Having to use a different spreadsheet cell for the rate depending on the 
amount of pay means that one cannot just get the rate by referring to a 
fixed cell address.  What I've been kicking myself for spending so many 
years doing is adjusting the rate and constant manually as my pay 
fluctuated rather than just looking up the documentation for the IF() 
function:

   IF( condition; then_result; else_result )

Yes, Reading The Fine Manual really is worth your while!  I am such a moron.

So, without further ado, here is how I do my tax table lookup.  My 
annualized pay amount is in cell L3.  Are you ready?

=IF($L3<$Formulas.$C$6;$Formulas.$D$6;
     (IF(($L3>=$Formulas.$B$7)AND($L3<$Formulas.$C$7);$Formulas.$D$7;
      (IF(($L3>=$Formulas.$B$8)AND($L3<$Formulas.$C$8);$Formulas.$D$8;
       IF($L3>=$Formulas.$B$9;$Formulas.$D$9))))))

Factor K is obtained by replacing $D in the above with $E.  The Nova 
Scotia factors are obtained in an identical fashion from a different 
table that's also on the Formulas sheet, but in my improved version will 
be on a separate provincial tables sheet, with one set of tables and one 
calculation page for each province.

I also need to lookup the amount of the TD1 exemption from the Employees 
sheet.  In the US this would be the number claimed on your W-4.  My wife 
and I are both employees, but she has a bigger exemption than I do 
because, being a student, she can deduct tuition and gets an education 
credit as well.

Here's how I look up our TD1 exemptions:

=IF($C4="Mike";$Employees.$C$2;$Employees.$C$3) * $Formulas.$C$11

So you see it's hardwired for two employees, one of whom must be named 
"Mike".  The test is also case-sensitive.

Our TD1 exemptions are in column C of the employees sheet, while C11 or 
Formulas has the Federal Non-Refundable Credits rate, which is 16%. 
There is a similar calculation for Nova Scotia's nonrefundable credits.

You know, I wrote my first computer program in FORTRAN at the tender age 
of twelve in 1976.  But I resisted ever figuring out how to use a 
spreadsheet until the early 90's, when I finally used one for the data 
analysis of the last lab experiment I ever had to do for my Physics 
degree.  Since then I've used them extensively, but until this last week 
I never figured out how to do anything fancier than address simple 
spreadsheet cells.  I never even figured out how to address cells on 
different pages before a couple of years ago.

It's really not that hard though.  I can really see why spreadsheets 
were one of the first killer applications when microcomputers first 
appeared.  Which was the first?  Visicalc on the Apple II I think. 
Lotus 1-2-3 is what made the IBM PC the success that it was.

I once heard a talk by Jean-Louis Gassee, who at the time was the VP of 
Engineering at Apple.  He told about the days when he had to work out 
the company budget back when he was the President of Apple France.  He 
used a "spreadsheet" way back then too, only it was a paper one, a great 
big sheet with gridlines printed on it.

Intrepidly,

Mike
-- 
Michael D. Crawford
GoingWare Inc. - Expert Software Development and Consulting
http://www.goingware.com
crawford at goingware.com

   Tilting at Windmills for a Better Tomorrow.

     "I give you this one rule of conduct. Do what you will, but speak
      out always. Be shunned, be hated, be ridiculed, be scared,
      be in doubt, but don't be gagged."
      -- John J. Chapman, "Make a Bonfire of Your Reputations"
         http://www.goingware.com/reputation/