Budgets | Calculating a Paycheck for a New Pay Schedule

A little while ago I wrote a post about how I almost lost control of my bowels when I got my first paycheck on a new pay schedule. It was about a $100 less than I had expected and it was going to destroy our financial plans for the year. Fortunately, I didn’t really make a mistake - it was just an administrative solution to help my company’s employees transition better to the new pay schedule that I had forgotten about. Let’s rehash the lessons I learned after getting my last paycheck:

Lesson learned: pay attention to company communications, they are important once in a while.

Lesson learned: I never really make a mistake when using a spreadsheet. It may seem like I made a mistake, but in the end I will always be right.

The first lesson was a no brainer and I thought the only good lesson I actually learned. The second was just ridiculous arrogance that was more about being funny than about portraying reality. But then I get my most recent paycheck and lo and behold, the predictions that I made on the spreadsheet were 0.002% off of what actually occurred. That is pretty good … and proof that I never really make a mistake when using a spreadsheet.

Let’s examine my methods for new pay schedule calculating success:

1. Create a spreadsheet - you’ll never not make a mistake if you don’t make a spreadsheet.
2. Divide all the money that was taken from your paycheck into two categories: pre-tax deductions and taxes - you can label these as I have done in the image below or in the categories you will actually see on your pay stub. I think mine might be a little bit more useful linguistically.
3. Determine what your taxable income is - you do this by subtracting all your pre-tax deductions from your gross pay. I like entering the deductions as negative numbers so that way I can use the handy sum function on Excel and OpenOffice.
4. Determine what percentage of your taxable income goes to each of the specific taxes mentioned on your pay stub. One of my assumptions in calculating my new paycheck is was that my tax rate would stay relatively untouched. This was because my pre-tax deduction changes were not very significant and my annual salary was remaining the same. If you are seeing a substantial increase in the amount deducted before taxes or a substantial increase in pay using your old percentages will only be a good starting place. In the case of the pre-tax deduction increase your tax percentage may decrease while in the case of the pay increase your portion going to Uncle Sam and friends will increase.
5. Create a copy of this spreadsheet for your new information. I like to just open a tab on my spreadsheet and use the ‘paste special’ option. You can do this by right clicking (sorry Mac users) on the cell that you want to be your first cell and choosing the ‘paste special’ option. From there I pick the ‘values’ button and you will get something that looks like this:
6. Calculate how much your gross pay will be on the new schedule. If you are a salaried employee, like myself, the simplest thing to do is find out how much you get paid annually and go from there. You will divide your annually salary by 12 if you are going to be paid monthly, by 24 if semi-monthly, and by 26 if bi-weekly. This will be your new Gross Pay amount.
7. Add any changes to your pre-tax deductions. Mine changed because I started carrying my wife on my insurance. It also changed because my yearly premiums now come out in 26 chunks rather than the 24 that it previously did. Chances are if you are changing your pay schedule your deduction amounts will change.
8. Re-insert all your sum functions and delete your old tax numbers. This will set the stage for figuring out your new take home pay.
9. Insert this equation into your various post-tax deduction cells - % tax * taxable income. This will end up looking something like “=E34*C29″. Advanced info: I like to add a $ on the to the taxable income reference (turns C29 into C$29) because I can utilize the fill command to its highest potential. Simply highlight the cell that contains your equation and all the cells that you want to have the same equation in it and press ctrl+d and presto! you got all your equations. My finished product looked like this:
10. See your new take home pay and plan accordingly. This process got me within 0.002% accuracy and I hope it works out for you.

There are plenty of payroll calculators out there but I found them hard to use and even harder to understand. I mean who knows what their tax bracket is? Or how much they have in pre-tax deductions off the top of their head? When it comes to DIY, spreadsheets are king.

If you're new here, and want to get My Family's Money delivered to you for free then subscribe to my RSS feed or get updates delivered to your inbox via email.

Related Posts:

Share This

Categories: Budgets ~ Tags: excel formulas, frugal failures, income, spreadsheets ~ Trackback