Business is Personal (Finance)

One of the exercises I do is periodically calculate my estimated tax bill for the coming year to fine-tune my withholdings.  Now there are several easy ways to do this.  If you used a tax program to do taxes the previous year, you can make a new tax return with rough numbers for next year and you will be just a bit over (inflation decreases nominal taxes paid).  There are also online tools to estimate taxes:

http://turbotax.intuit.com/tax-tools/calculators/taxcaster/
http://www.calcxml.com/do/inc02
http://www.dinkytown.net/java/Tax1040.html

But what if you want to estimate past taxes paid?  I ran into this question when auditing my past spending and savings.  While I do a much better job of tracking my finances today, my older numbers are mostly guesses and my older tax returns are sitting in boxes in storage.  However, I do have records of my investment transactions and the annual Social Security+Medicare earnings record.  And from this, I could in theory estimate spending with the formula:

SPENDING = EARNINGS - TAXES PAID - SAVINGS

Luckily, my taxes were pretty straightforward as I have yet to claim a mortgage deduction.  (I have a rental property which means it's slotted as an investment expense instead of a personal deduction.)  This means the only possible itemized deduction I could have taken was for state taxes so we have the following sequence:

1) Calculate State Taxes
2) If State Taxes > Standard Deduction, Use State Taxes, Otherwise Standard Deduction

The state of California keeps their older forms online at http://www.ftb.ca.gov/forms/priorYR.shtml.  To calculate taxes, I grabbed the following info for the past years -- standard deduction, personal exemptions and tax rate schedule.  Then I set up formulas in OpenOffice for each year like so:

A1 = 1996 (year)
A2 = $2527 (standard deduction single)
A3 = $66 (personal exemption credit)
A4 = 1 (number of adult exemption credits)
A5 = $106 (child exemption credit)
A6 = 0 (number of child exemption credits)
A7 = $25484 (tax threshold)
A8 = 8% (tax rate above threshold)
A9 = $880.18 (cumulative tax below threshold)
A10 = $40000 (income)
A11 = $5000 (401K/IRA deductions)
A12 = ((A10-A11-A2*A4) - A7) * A8 + A9 - A3*A4 - A5*A6

The federal formulas are similar except the personal exemptions are deductions instead of credits.  A good place to get consolidated prior year tax info is at http://www.unclefed.com/IRS-Forms/index.html.

B2 = $4000 (standard deduction single)
B3 = $2550 (personal exemption credit)
B4 = 1 (number of exemption credits)
B5 = $1000 (child tax credit)
B6 = 0 (number of child exemption credits)
B7 = $0 (tax threshold)
B8 = 15% (tax rate above threshold)
B9 = $0 (cumulative tax below threshold)
B12 = ((A10-A11-IF(A12>B2;A12;B2)-B3*B4) - B7) * B8 + B9 - B5*B6

The formula looks pretty similar except for the IF(A12>B2; A12; B2).  This says if A12 (state taxes) is larger than B2 (standard deduction), use A12 (state taxes) -- otherwise use B2 (standard deduction).

Now for both state and federal tax rate schedules, I took a short-cut and eye-balled the charts to find out where my income fell before setting up the formulas.  You can make formulas that handle all income ranges with the following:

IF(income < bracket1; income * 0.10;
  IF(income < bracket2; (income - bracket2) * 0.15 + cumulative2;
    IF(income < bracket3; (income - bracket3) * 0.25 + cumulative3;
      IF(income < bracket4; (income - bracket4) * 0.28 + cumulative4;
        IF(income < bracket5; (income - bracket5) * 0.33 + cumulative5;
          (income - bracket6) * 0.35 + cumulative6
        )
      )
    )
  )
)

The formula will get very complicated very quick but is doable if you pay careful attention to the embedded parenthesis.  For my own purposes, it seemed like wasted effort since I don't need to know "what if I made more money in the past or what if I was married with kids in the past?"

(Filed in taxes, tools)