Calculating Your Annual Salary With Increases With Excel

Calculating Your Annual Salary With Increases With Excel
••• AndreyPopov/iStock/GettyImages

When you receive an annual salary increase, your income tax liability may or may not increase depending on a number of factors. If you want to know how your annual bump in compensation will affect your take-home pay, you can run some numbers using an Excel spreadsheet.

You’ll need to have several pieces of information available to do this, but calculations should be fairly simple if you know how to use Excel.

Gross or Net?

You might just want to know your annual salary increase percentage and nothing else. That just requires a quick calculation, which you can repeat each year. You can even run a simple average salary formula in Excel to track your pay over a number of years. If you want to know how much take-home pay you’ll have each week or month, you’ll need more information.

Find Your Tax Bracket

How much you’ll pay in federal income tax depends on your tax bracket, which is determined by how much money you earn. You can find your 2021 tax brackets here. Once you determine your tax bracket, you can use that percentage to help calculate your federal taxes (you’ll need to factor in any deductions you have).

Consider Your W-4 Form

When you get hired, you provide your accounting or HR department with a W-4 form for tax purposes. This asks questions about yourself, a spouse and any dependents and will affect how much in taxes gets taken from your paycheck. You can ask your company about the information provided on that form and how this affects your tax deductions from your paycheck. There are changes to the form in 2020 and 2021, so don't assume you're familiar with it if you haven't completed one lately.

Factor in Your Benefits Deductions

If you have benefits deducted from your paycheck, this reduces your tax burden. For example, if you purchase dental insurance or contribute to a health savings account or 401(k) match, the amount of your income you use to pay for those isn’t taxed.

Don’t Forget State Taxes

When considering your taxes, don’t forget to include state income tax if you live in a state that collects that. You can find your rates with a quick online search of your state’s name and the words “income tax rate.”

Create Your Spreadsheet

For a simple percentage increase calculation of your annual pay (e.g., this year compared to last year), enter last year’s gross salary in a cell in the spreadsheet. For this example, we’ll use cell A1. If you made ​$60,000​ last year, enter 60000 in cell A1. If you make ​$65,000​ this year, enter 65000 in cell B1.

To find out your percentage increase in pay, enter the following formula in cell C1: =sum(a1/b1). You can also use the formula a1/b1. After you click on the return key, you should see 0.923076923. This means that you received an approximate 8 percent pay increase this year from last year.

If you want to see what increase you will make next year, repeat this process. For example, if you’ll be making ​$72,000​ next year, enter ​$72,000​ in any cell (for example, D1), then in the next cell, enter the formula =sum(b1/d1). This will return 0.902777778, showing your annual increase is approximately 10 percent.

You can add additional information in your cells. For example, you can enter the year above each salary amount in the line 1 boxes and enter your salary amounts directly underneath each year on line 2. You can put “% +” in the line 1 box directly above those numbers.

Other Calculation Options

If you want to see what this year’s new take-home pay percentage increase will be each week, you can divide last year’s annual take-home pay by 52, do the same for this year’s salary, then use the formula formatting above to get your weekly (or bi-weekly or monthly) increase numbers. You can run your numbers for gross pay or after all of your deductions have been taken out of your check.

If you know your weekly pay after deductions and want to see what you’ll take home annually, you can enter your gross or net weekly pay in a cell, then in the next cell, multiply it by 52 (or 26 or 12 if you have bi-weekly or monthly pay numbers).

Let’s say you make ​$60,000​ and get paid 26 times per year. Use the formula =sum(60000/26) to get a bi-weekly paycheck amount of ​$2,307.69​. If your take-home pay after taxes is ​$1,725​, multiply that by 26 using the formula =sum(1725*26) to get your annual take-home pay of ​$44,850​.