Excel Template: Tax Liability Estimator

As we get close to the end of the year, one of the things I always make sure to check before January is my overall tax liability.  From a financial perspective, my preference is to owe the government a little bit of money.  If at the end of the year, the government owes me a refund check, it’s the same as giving the US Treasury a free loan on my money.  If, on the other hand, I owe the government, then it equates to getting a free loan from the government.  The tricky part about the latter scenario is that you can’t go overboard with it; if you owe the government too much money, you’ll be subject to an underpayment penalty, which punishes you with fees and interest based on the amount you owe.

Click Here to Download the MBA Excel Tax Liability Estimator

The Underpayment Penalty

The underpayment penalty is levied when you’ve paid the government too little in taxes over the year.  The entire process of determining whether you are subject to an underpayment penalty begins with the W-4 form, which can be found in the link below.

W-4 Withholding Form

The W-4 Withholding Form is the tax document that allows you to tell your company how much money to withhold from your paycheck.  The amount withheld is used as a pre-payment to your overall tax liability.  You can use the W-4 to tell your company to withhold an exact dollar amount each pay cycle.  However, what most people do is complete the worksheet on the form, which comes up with a certain number of allowances.  These allowances are then submitted to your company, and eventually determine how much is withheld from each of your paychecks.

The problem with this W-4 allowance calculation is that, regardless of the number of allowances you put in the form, your company is the one that ultimately handles the calculation.  While there are generic estimators out there that can guess how many allowances you should put in, unless your company is the one that provides the allowance to withheld calculation, there’s potential that the calculation could be off.

Overall, many things can change over the course of the year that make planning and estimating your tax liability difficult.  Regardless of the amount of preparation and analysis you do beforehand, it’s a good idea to use Excel to perform a check towards the end to see how you are tracking.

Template Purpose & Goal

The Tax Liability Estimator has two primary goals:

• To estimate your tax liability and determine if you may be subject to an underpayment penalty
• To calculate the amount you’d need to pay the IRS to avoid the underpayment penalty

Please note that If you did not owe the IRS any money in the prior year, you will not be subject to an underpayment penalty for the current tax year.  Basically, if you got a refund last year, there’s no risk of having an underpayment penalty and you won’t need this template this year.

The Tax Liability Estimator Template

Using the template is relatively simple and has four primary steps:

1. Enter the required IRS information
2. Enter the information from your latest paystub
3. Enter any additional information about income
4. Check the calculation to verify your underpayment status

Click Here to Download the MBA Excel Tax Liability Estimator

Step 1: Enter the required IRS information

There are only two pieces of information required from the IRS:

• The standard deduction for your filing marital status
• The tax brackets for your filing marital status

Both pieces of information are generally published on the IRS official website as well as several other tax resources.  For the tax brackets, if you already know what bracket you fall into, you only have to input the information for that bracket for the template to work.  (ie you can leave all the other brackets blank to save time)

Below is an example filled in with the 2015 information for someone filing as Single.

Step 2: Enter the information from your latest paystub

Your paystub is a great source of information because it will provide you both the amount you earned during your most recent pay period, as well as the year to date value for each line item.  Make sure to pull from that document the following items:

• All Earnings
• Deductions
• Taxes Paid

You should also be able to determine how many pay periods you have left based on the date of your paystub.  Once that is input, the total year estimate is calculated.

Step 3: Enter any additional information about income

Repeat the same process as Step 2, but this time with any information you have outside of the items on your pay stub.  Usually this is just any investment earnings or side income that you might have account for.

Step 4: Check the calculation to verify your underpayment status

Once your values are input, the template pretty much does the rest of the calculations for you.  For reference, the calculation used to determine your tax bracket and the amount you owe is done with the MATCH formula.  A tutorial for the method of using an approximate MATCH formula to assign values is located here.

If you check the very last section, the template will calculate how much of your tax liability you’ve already paid to the IRS.  There are two conditions that would prevent you from having the underpayment penalty, even if you owe the government money:

• If your unpaid tax liability is less than $1,000
• If you’ve already paid 90% of your tax liability

The template checks for these conditions and assesses whether you are likely to be subject to the underpayment penalty.

In the case that you are subject to the underpayment penalty, the template provides you the dollar amount you would need to pay to the IRS to bring your paid amount to 90% of your total tax liability.  Paying the government this amount will waive you from being subject to the underpayment penalty.

In the example below, we’ve only paid 84% of our tax liability and are therefore subject to the underpayment penalty.  Based on our income situation, we’d need to pay an additional $2,535 to the IRS to not be subject to the penalty.

Template Caveats

• I am not a certified financial planner and have no tax planning training.  Therefore, please take all of this information with a grain of salt.  For formal tax advice, please consult a certified financial planner (CFP) or a certified public accountant (CPA).

• This template is an estimator and is only intended to perform a quick and dirty calculation of your tax liability.  The formal / detailed calculation of your tax liability can be fairly complex depending on your income situation and this template is NOT intended to replace that.

• Use of this template assumes that you are like most people and have a salaried job as your primary source of income.  Having a job and a paystub from your company makes the estimation process significantly easier.

• This template is for US taxpayers only.  I personally don’t have much knowledge of the tax laws and requirements of any other countries, so you’ll have to look elsewhere for tax liability calculations outside of the US.

What Should I Do If Owe?

If your estimate shows that, based on your go forward earnings and tax payments, you’ll be subject to the underpayment penalty, you can basically bypass your employer and pay taxes to the government directly.  The IRS offers a direct pay service that can debit a tax payment from your checking account.  There is no transaction fee for this service if you use your checking account.

IRS Direct Pay

The final deadline for making direct tax payments to the government is January 15th of the following year, though this deadline date can be specifically adjusted by the government for special circumstances.  Regardless, you should ensure that your tax payment goes through before that date for it to count for the tax year in question.

Click Here to Download the MBA Excel Tax Liability Estimator