Tax Spreadsheet
Figuring taxes is one of those things that seems like it should be pretty simple, but gets kind of complicated. I have tried coming up with a spreadsheet in the past and always get bogged down in tax brackets, long term gains rates, deductions, and personal exemptions. Since I was changing my withholding allowances this year and the official IRS calculator is so unfathomable, I thought it might be easier to try a spreadsheet again.
The biggest problem is dealing with the different tax brackets. For instance in 2008, the first $8,025 is taxed at 10%, then a 15% bracket kicks in until $32,550 when the rate goes to 25%. If you make $40,000 you pay 10% of $8,025, 15% of $32,550 minus $8,025 and 25% of $40,000 minus $32,550. So I came up with a spreadsheet with five nested IF statements to handle the six different tax brackets (rather than trying some sort of lookup function). Then I had to look up tax brackets and rates for the last few years since each year is different.
Then it was a matter of dealing with qualified dividends and long terms gains which are taxed at 15% (20% before 2003). Unless you have losses in which case it counts against your income (but only up to $3,000 which the spreadsheet doesn't deal with; you just have to enter a maximum of $3,000). Those amounts, if positive, are subtracted from your income and taxed at a lower rate. Your reduced income is taxed from the tax tables. There are actually a couple of different capital gains rates for lower incomes, but I skipped that part.
So far so good, but I was still getting the wrong answer. That's because the tax is based on your income in the tax tables, rather than the actual percentage of your income. For instance, if your income was $40,005, you would go into the table for the amount for $40,000 to $40,050. And that tax amount is actually based on $40,025. So I rounded down to the nearest $50 and added $25.
Using the same formulas for every year, I was able to accurately generate my taxes going back to 2001. Then I was able to project forward to 2009 and figure out about how much I would owe or get back next year. I am probably not withholding enough taxes if I want to hit my target of a $200 refund. So I think later in the year I will reduce my withholding.
| Permalink
| 