For the last couple of years, I've been obsessed with personal financial planning. I have credit card bills, a second mortgage, a car payment, and a daughter taking voice and dance lessons. Later I'll need to send her to college, marry her off, and then retire comfortably in hurricane country. So I've got bills to pay now, and much saving for the future to do, and I've been working in my spare time on plans and budgets to make it happen.

I started out with a budget spreadsheet that took my salary and did a simple subtraction of 401k contributions and multiplied the remainder by an arbitrary percentage that represented the rough difference between my gross and net pay at that time. With that, I was able to do a simple progression of expected salary, 401k and savings growth, and future expenses. As it turned out, provided I didn't get fired, AEP didn't go belly-up, and that I wasn't subject to continuous emergency expenses, I would be able to meet all my goals provided I stuck to the plan.

I didn't expect the plan to be exact. Obviously I can't predict what my exact merit increases will be, or bonuses, or emergency expenses. I also don't know what the housing market will be like in 30 years when I retire, or what college tuitions will be like in 8 years when Stacey graduates high school. So for the big picture all I can do is put in my educated guesses, and refine them as more information becomes available.

One thing bugged me, though, and that was my inability to do a decent estimation of my net pay on future salaries. I know that insurance and local tax rates will fluctuate slightly, but my equation of "(gross - 401k) * .72 = net" never really satisfied me, and after my first merit increase at AEP I realized that the ".72" multiplier was definitely not constant. Recently my recollection of the discrepancy bugged me enough to investigate.

So I went back to the basics: Gross - Deductions - Taxes = Net

Deductions include 401k contributions, insurances, automatically deducted charitable donations, and parking (I'm sure there are others, but these are the ones I use). These are all decided at the beginning of the year and remain constant, unless a life event happens (new baby = more insurance, for example). That was the easy part, just fill in the blanks. What is left over after deductions is taxable income (or adjusted gross) - so far, so good. Next comes the taxes, the hard part that involves some math and investigation.

Taxes come in several shapes and sizes. In my case they are Federal Withholding, Social Security, Medicare, Ohio Withholding, and Columbus city tax. Social Security, Medicare, and Columbus tax were all easy, they are just percentages of adjusted gross (6.2%, 1.45%, and 2% respectively). The state and federal withholding involved using lookup tables and subtracting an additional withholding allowance based on the number of exemptions I put on my W-4. For 2006, each allowance subtracts $3300 from your annual adjusted gross income for the purpose of calculating Federal Withholding. For Ohio in 2006, each allowance subtracts $650 for calculating Ohio Withholding. The annual salary left over can be compared to the charts below to calculate withholding.

Federal Withholding table (annual) (single)     
Salary Minimum    Base withholding    Percent of excess
       $0.00           $0.00          0%
   $2,650.00           $0.00          10%
  $10,000.00         $735.00          15%
  $32,240.00       $4,071.00          25%
  $73,250.00      $14,323.50          28%
 $156,650.00      $37,675.50          33%
 $338,400.00      $97,653.00          35%
                                      
Federal Withholding table (annual) (married)    
Salary Minimum    Base withholding    Percent of excess
       $0.00           $0.00          0%
   $8,000.00           $0.00          10%
  $22,900.00       $1,490.00          15%
  $68,040.00       $8,261.00          25%
 $126,900.00      $22,976.00          28%
 $195,450.00      $42,170.00          33%
 $343,550.00      $91,043.00          35%
                                      
Ohio Withholding table (annual)       
Salary Minimum    Base withholding    Percent of excess
       $0.00        $0.00             0.774%
   $5,000.00       $38.70             1.547%
  $10,000.00      $116.05             3.094%
  $15,000.00      $270.75             3.868%
  $20,000.00      $464.15             4.642%
  $40,000.00    $1,392.55             5.416%
  $80,000.00    $3,558.95             6.189%
 $100,000.00    $4,796.75             7.736%

I deduced from this some basic rules worth considering. First, if you're on the border of two tax brackets, trying to squeeze in more deductions and allowances is an exercise in diminishing returns. There is a slight hop in withholding when crossing from one bracket to the other, but it is not a big one.

Second, a dollar lost to 401k deductions is not a dollar out of your paycheck. For me it is about 60 cents.

Third, more deductions means lowering withholding at the cost of lowering your net pay (again, at about 60 cents on the dollar), whereas more allowances means lowering withholding at the cost of an increased tax burden. In theory, your annual tax returns should claim the same allowances as your W-4, which should balance this out, giving you free money on your paycheck for claiming all the allowances you are eligible for. Some people fond of micro-managing their money claim higher allowances on their W-4s (up to 10) to reduce federal withholding, and claim the correct number of allowances on their 1040s, and pay the difference.

In a risk-free world, this makes sense. In our world, it is a recipe for disaster. What happens if you lose your source of income before you've settled your tax burden? You get to add Uncle Sam to your list of other creditors that are already hounding you. I'm all for correctly estimating your tax burden and withholding no more than that (see the numerous "Giving the government an interest-free loan" arguments made daily in financial discussion groups), but taking out a loan from the government to give yourself a fatter paycheck is a needless risk.

Lastly, married people have much less federal withholding than single people. They also have a slightly higher tax burden, but over the next few years this descrepency will be reduced until there is no more "marriage tax". Also, married people usually have houses, the interest on which is deductable, offsetting the reduced withholding. Running the numbers gives you the clear revealed preference that the federal government wants you to be married and own a home - the American dream.

Anyway, after my research, I was able to construct an Excel spreadsheet that is accurate to within a penny or two of how my company calculates my paycheck. This gives me a better approximation of how much money I'll make in the future, and how much I'll have to save. The spreadsheet is available below. Enjoy!

Paycheck estimator spreadsheet