Excel & Business Math 35: VLOOKUP & MAX Functions for Percentage Method Federal Income Tax Deduction


Welcome to Excel and Business
Math, video number 35. Hey, in this video, we got to
talk about the amazing VLOOKUP function with the MAX function
to calculate federal income tax payroll deductions. Now we’ll talk about what a
federal income tax withholding deduction is. We’ll see how to do the
calculation long hand. And we’ll see how to do
it with VLOOKUP and MAX. And then we’ll make
the calculation on a full payroll table. Now let’s start
off by going over to the sheet, Fed Income Tax. Federal income tax withholdings,
it’s called withholdings, because guess what? Federal income taxes have to
be collected by the employer. They have to withhold
some money and then send it to the federal
government on behalf of the employees. Now federal income taxes go by
the concept of pay as you go. That means that as you make
money throughout the year, you have to pay
your income taxes. Now when we calculate a
federal income tax withholding, it’s only an estimate. We’re not sure
exactly how much money we’re going to make by
the end of the year. We don’t know if we’ll
get a raise, whether we’ll lose our job, get a different
job, whether we’ll be sick. So all the calculations
are an estimate. Now every employee
has to fill out a W-4. That’s like this form down here. And on that form, you have
to claim whether you’re single or married,
because whether you’re single or married will determine
a different tax table for us to look up how much
tax we have to pay. Also, we have to claim
number of allowances. The more allowances we have,
the more of our earnings are not taxed. And allowances can be
thought of as the number of people who you support. One allowance equals
one personal allowance or an allowance for a dependent. So the more allowances
we have, the more we’ll have to subtract
from gross pay to get actual taxable pay. Now there’s a few
different ways we can estimate our federal
income tax withholdings. There’s the wage bracket method
and the percentage method. And these methods are defined
by the Internal Revenue Service Publication 15, Circular E. Now the one we’re going to
use is the percentage method. So we want to go over to the
sheet, Percentage Method. Now on this sheet,
there’s a link at the top. And I’m going to click it
and go to the IRS site. This, I just downloaded. It just is not even a month
old, these tax tables. Here’s the table we’re
going to use to determine what is the allowance. For us, we’re only doing weekly. But companies could pay
biweekly, semimonthly, monthly, quarterly, and so on. For us, every time someone
claims one allowance, we subtract $79.80
from their gross pay. That means we reduce
gross pay before we make our tax calculation. Now if we scroll
further down, here are the tables where
we get our tax rates. Now notice this,
weekly payroll table. And then within this
weekly payroll table, there’s a table with tax rates
for single persons and married. Down here, there’s a
biweekly table, down here, semimonthly, all sorts
of different tables. So whatever business
you work for, you have to figure out
what your pay period is. Our pay period that we’re
going to assume is weekly. Now these tables are fine, but
they’re created by the IRS. And they’re not very easy to
use for making calculations. So I went ahead and
downloaded these. And back over here, we can
see here’s the lower limit. Here’s the upper limit. That means for our
taxable pay, we have to look up and see
which category we fit into. So if we’re in this
category right here, we simply use the tax
rule from this column. Now what that means is if
we had $900 taxable pay, we would take the $85.62. That’s all of the tax from
the previous categories. So we take that, add it to the
employee’s taxable pay, $900. We’d have to subtract
the $815, which is the upper limit from
the previous category. And it also represents all
of the earnings tax so far. So we do that subtraction. And then we multiply by
the 22% for this category. Now notice that
calculation right there is the same calculation we
did for variable commission and piecework back
in video number 33. So we will be able to
use the same formulas we did in video 33 to make
our income tax withholding calculations. Now, this table right here
is for marital status single and weekly pay period. Down here, this table is
for marital status married and weekly pay period. Now depending on
the employee status, either single or
married, we’re going to have to pick the
right lookup table, because the numbers are
totally different in each. Now this table up here allows
us to pick a particular payroll period and then pick the
amount for one allowance. Because all of our
employees get paid weekly, we’re always going to be using
$79.80 for one allowance. All right, now we need to go
see how to use these tables and calculate our federal income
tax withholding deduction. I want to go over
to the sheet, PM, for percentage method,
single, weekly. Now on this sheet, we have
our single allowance table. But we’re just going
to be using the $79.80. Over here, we have the
correct lookup table for a single marital status
and weekly payroll period. We’ll make our calculations
here long hand. And then we’ll create an
automatic method using VLOOKUP and the MAX function. And here’s our employee,
Atta Kali, gets paid weekly. There’s the gross, $1,900. Marital status, single. And there’s the number
of withholdings, 2. Now if we’re going
to do it long hand, the first thing we have to
calculate is total allowance. This is the amount of
earnings that are not taxed. To do that, we simply
say, hey, there’s the claimed
allowances, two of them times the amount they
allow for each allowance. When I enter, that’s the amount
for Atta that’s not taxed. Now taxable pay,
that’s the amount that we need to calculate
that will allow us to look up, find the right
category, and then figure out what our rule is
for calculating fed income tax. Taxable pay equals,
well, there’s the whole gross minus the amount
that the government is not going to tax, our
total allowance. And Enter. Now we can calculate
our federal income tax. With our taxable pay, we can
look through and find out what category. That number fits between
this lower and upper limit. If we were thinking like
the VLOOKUP function does, then we’d look at this, race
through the first column, find the first number
bigger, jump back. And that would tell us that
this is the correct category. So we can read this rule and
build our income tax formula. Equal sign. It looks like it says $271.08. That’s the cumulative tax from
all the previous categories. To that, I have to
add in parentheses. It says employee taxable pay. There it is, that
amount right there. From that, I subtract
the upper limit from the previous category. Then I close parentheses,
times, and I get the tax rate. So once I figure out the
row, that’s the hard part. Read the rule. Click on the cells. And when I hit Enter, that’s the
federal income tax withholding. That’s the amount
that we would deduct from the employee’s paycheck. Now the problem with
that method, of course, is if I change this amount here,
that number is not correct. Control-Z. The other
problem is if you have to do this formula over
and over for many employees, it takes too long. It’s prone to errors. So let’s see how to do
this the automatic way. We want a formula
for taxable pay and for income tax that will
calculate correctly no matter what the gross pay is. Well, we can calculate
taxable pay easy enough. It’s simply the gross pay. And then in our
formula, we’ll have to subtract whatever
the allowances are times the allowance amount. So for taxable pay, equals gross
pay minus number of allowances times the amount of an allowance
for our weekly time period and Enter. That is the correct amount. Now there’s a problem with the
formula as it sits right there. And the problem comes
from if gross pay is less than the
total allowances, then that becomes negative. And the subsequent
formula will not work. For example, if I change
Atta’s gross pay to $55. She only came in for
a few hours on a day. Wow, that’s not going to work. Now instead of
building an IF function to test if this is less than the
total allowances, guess what? Let’s be smart here. That’s negative. What do I really want there? I want 0, because
the taxable pay is 0. Well, which number is bigger,
this negative number or 0? Well, of course, 0 is bigger. Now I’m going to
Control-Z and ask the same question right now. Which number is bigger,
that taxable pay or 0? Well, the taxable pay is. So that’s an alternative
to building an IF here. I can simply say
F2, please give me the bigger number
between whatever that formula calculates and 0. And the function that
finds the biggest value or the maximum
value is the MAX function. Tab. So the MAX function is
an aggregate function. Like we’ve seen so many
aggregate functions before, we’re allowed to put
multiple items in. And it will do whatever
aggregate calculation the function does, in our
case, calculate the max. So at the end, I’m
going to comma 0. And now max will always find the
biggest number between whatever that calculates, 2. And it has three possibilities. It can be negative,
0, or positive. So we’ll find the max
between that and 0. And that’s our formula
for taxable pay. So when I enter this,
now, if I put 55, well, these formulas don’t work. But that’s
calculating perfectly. Now I’m going to Control-Z.
So now we have taxable pay. Federal income tax. Well, we did this
formula, video 33. I want to come back up
here, F2, and notice. What are we doing here? Well, if we have that
value right there, $1,740, taxable pay as our
lookup value, what is J15? J15 is VLOOKUP
looking up that value and getting the value
from the fifth column. What is that value? Well, that’s just
the taxable pay. How about that one, I15? Well, it’s VLOOKUP. Please go get the item
from the fourth column. And finally, H15,
that’s VLOOKUP, go get the item from
the third column. So really, it’s the same formula
we did a couple different times back in video 33. Now there’s a few ways we
could build this formula. And in video 33,
we saw how we put multiple pieces of the
formulas in separate cells. And once we got
that all working, we copied, and pasted,
and mashed it all together in a single cell. This video, I’m going to show
you a slightly different way. We’re still going
to rely on our note over here, just like
we did in video 33. VLOOKUP 5 plus taxable
pay minus VLOOKUP 4. That’s got to be in
parentheses, times VLOOKUP 3. Then after we do that,
we’ll worry about the round. All right, so you ready? Equals VL tab. The lookup value when we’re
doing federal income tax is taxable pay. Comma, the table. First column’s always
got to be sorted from smallest to biggest. And it’s got to be the lower
limit for each category. Now we’re going to highlight
all the way over to column 5. We do not need that column. That just helps us when
we were doing it manually or when we were trying to
build the formula initially. So there’s the table. Comma, column index. That just tells VLOOKUP
which one of the columns has the thing you
want to go and get. So we’re starting
with 5, because we want to get cumulative tax
from previous categories. I’m even going to close
parentheses and Control-Enter. So I see so far, it’s working. It went and got the right
cumulative previous tax, F2. Now the rest of this gets
a little bit dangerous, because we’re trying to
build it all together. But when we’re
done, I’ll show you a new trick for verifying
that everything worked fine. Now we’re going to copy
that, because we’re going to use VLOOKUP a few times. So Control-C in edit mode. Now I’m looking at
my note over here. Plus, open parentheses. There’s taxable pay. Now I need to minus VLOOKUP 4. So I subtract, Control-V. That
5 has to be edited so it’s a 4. Now that piece, we’ll
go and get upper limit from the previous category. We have to make sure and
put a close parentheses. Now notice that’s orange. I need to make sure it’s
the same color as that one. So I close parentheses. And I can see
they’re both black. Now I need to multiply,
times VLOOKUP 3. Control-V. Very carefully, I’m
going to change that 5 to a 3. Now I’m going to Control-Enter. And it’s looking like it’s
working, and I think it is. I want to make sure
and add the round, because later when we
get to our payroll table, we’re going to use it in
a subsequent calculation. So F2. I want to put the round
just on that part. Inside the parentheses
is an amount times a percentage that might
have an extraneous decimal. Now I’m going to type my
ROUND and an open parentheses. I want to make sure that
orange parentheses is still there for that little bit. And it is. Now I come to the very end. And with not my selection
cursor, my I-beam cursor, I click. Comma 2. We’re rounding to the penny. Now we’re rounding to the penny. Final income taxes that
are paid are always rounded to the dollar. Some businesses might do that. I’m going to keep it
rounded to the penny here. Close parentheses. If it was rounded
to the dollar, we know we have to put a 0 there. Now Control-Enter. And that is our formula. Now we can check this and
step through each part of the calculation and
verify that the numbers are the same as in our table. The way we’re going
to do that is I’m going to select that cell. Go up to the
Formulas ribbon tab. Over in Formula
Auditing, I’m going to click on Evaluate Formula. Now I can step through this
and watch the entire formula calculate, making sure each
time one of the VLOOKUPs is evaluated, that it’s the
correct number from the table. So now I’m going to
click Evaluate or Enter. Enter. Enter. So it looks like
VLOOKUP 5, that’s the same number as there. Now it’s going to
go inside ROUND. Enter. Now it’s looking at
the second VLOOKUP. Enter. Enter. So $1,658, that’s
the correct amount. Now it’s going to evaluate
that entire amount. Enter. Wow, you can see there is– just from internally, Excel’s
calculating that there’s a decimal way out there. Now Enter. Now it’s going to get
the third VLOOKUP. Enter. Enter. It got 0.24. I’m verifying. Sure enough, that is working. Enter. Enter. And Enter. There’s the correct amount. And Escape. All right, so our
first example here, this is definitely easier to
do, especially with a tax table where they have the
rule as the last column. But if we want to
automate it, we need to use the max and
a bunch of VLOOKUPs. Now this was for single marital
status, pay period weekly. Let’s go look at a married
example, PM married, weekly. Ming Pham, there’s the gross
earnings, 4 allowances. Now total allowances, if
we’re doing it long hand, and we have to say 4 times
allowance for a week. And there we go. Taxable pay, I’m going to say
gross minus total allowances, and Enter. Now I can do fed income tax. That’s the lookup value. Look at that. I can just look at this rule. Federal income tax equals. There’s all the taxes from
the previous brackets. Plus. There is taxable pay. And I need to subtract
from it, upper limit from the previous category. Close parentheses, times
the correct tax rate. All I did is read the
formula and type it in. There it is, $6,883. Now taxable pay for
the automatic method. We want to make our
main calculation, which is gross pay, minus,
and we’re going to calculate total allowances. There’s the number
allowances, times allowances for a single person
for the week. We know there’s a problem with
that when gross pay goes down below total allowances. So our trick is is to
use the MAX function. And I’m going to take the
maximum between whatever that calculates to, comma and 0. So there’s two numbers. MAX will pick the
biggest, and Enter. Federal income
tax equals VL tab. Lookup value, it’s going
to be our taxable pay. Comma. Here’s the entire married
weekly table all the way to the fifth column. Comma 5, close parentheses. Now I’m going to copy that. Control-C plus in parentheses. There’s the taxable
pay minus Control-V. We got to go to VLOOKUP 4, close
parentheses, times Control-V. The tax rate is in
the third column. And change that to a 3. Control-Enter. That is working. I still need to add ROUND. F2, very carefully after
the plus sign, ROUND. At the end with my
I-beam cursor, comma 2, close parentheses. And there is our formula. We can test it. 55. That is working. $13,000. Man, I’d love to make
$13,000 in a week. Ming must be working at
Goldman Sachs or something, trading for the week. I’m going to Control-ZZ. All right, so we saw two
examples– married and single, using by hand and automatic. Now we want to go over and look
at our example, Weekly Payroll Table Single. Now here’s our
employee payroll table. And what we’ve done is because
we have two different tax tables, one for single
and one for married, these employees are only
marital status single. And then over on a different
sheet, we have married. So what we’ll do is– and we can go look at this other
sheet, Weekly Payroll Table Married. It’s set up exactly the same. So what we’ll do is
we’ll create the formulas on the other sheet,
then paste them here. And the only difference is
there’s some different numbers over here. But the structure of the
template is exactly the same. So back over on
Weekly Payroll Table, we want to create our formulas. Here’s the tax table. Here’s the amount of an
allowance for a single week, employee’s gross
pay, and allowances. Taxable pay equals gross pay
as a relative cell reference minus number of allowances as a
relative cell reference times– and I’m going to go all
the way over here and get allowances for a single week. F4 to lock it. By the way, we don’t
have to round that. We are multiplying
pennies times an integer, so we’re not going to
have a rounding problem. Control-Enter. Double-click and send it down. Go to the last cell and hit F2. That’s looking good. All right, cumulative tax
from the previous category. Oh, look at this. I’m going to go one, two,
three different columns. I’m going to do three
different VLOOKUPs and then the federal income
tax withholding. Equals VL tab, lookup
value, taxable pay, comma. Here’s the table,
all five columns. I need to lock this
using the F4 key, comma. This column index
5, so I put a 5. Close parentheses. Now before I enter it, I’m
going to copy it in edit mode. Control-C. And I’m
going to use tab, because I want to enter the
formula and move to the right. F2, Control-V. I need to get upper
limit from column 4. So I’m going to
change it to a 4. Tab. Control-V. F2, and I’m
going to change this to a 3. And Enter. Now I’m going to highlight
all three and double-click and send it down. Uh-oh. I have a problem here. First I’m going to go down
to the bottom and hit F2. OK, it’s not with relative
cell reference and table. All of these should be the same. So F2, it’s looking good. F2 looking good. What’s going on here? Oh, look at that. There’s that employee
who has gross pay less than the total
amount of the allowance. So I have to come
up and amend this. I just forgot. F2. Remember, I need to take
the max of taxable pay or 0. So right after the equal
sign, I’m going to say MAX. There’s number one. That’s just the formula. Comma 0, close parentheses. Now that will come out less
than 0, 0, or positive. For all three cases,
MAX will calculate the correct taxable pay. Control-Enter. Double-click and send it down. And there we go. All right, so we have our three
pieces from our lookup table. Now we can create our
federal income tax formula. Equals, we need cumulative
tax from previous bracket. Plus, in parentheses
there’s taxable pay. Minus upper limit from
the previous category. Close parentheses, times. There’s my tax rate. I remember I have to round. So after the plus,
I’m going to round. Open parentheses, comma
2, close parentheses. Control-Enter. Double-click and send it down. Go to the last cell and hit F2. That is looking good. Now net pay, equals. It is the gross pay minus. So many steps to get our
federal income tax withholding. But there. And this is just net pay just
after taking out federal income taxes. We didn’t do any of the
taxes we did the last video. Double-click and send it down. Go to the last cell and hit F2. And as one last approach
to mashing it all together, I’m taking this formula. Control-C. Escape. and I’m going to paste it here. Oops, I forgot the equal sign. So I put my cursor before
the E and type an equal sign. I’m going to cheat. I’m going to come over here
and copy this in edit mode. Control-C Escape. Put it in edit mode. All right, the blue one
is column 5, so Control-V. That’s right. Double-click the
lavender or purple one. That’s column 4. Control-V. Now
very carefully, I’m going to change that 5 to a 4. Now that G12 is column 3. Double-click. Control-V. Change
it to column 3. And that will work. Control-Enter. Double-click and send it down. Oh, there’s nothing to the
left, to the right, or below. So I have to manually
copy it down. Go to the last cell. Hit F2. And Enter. Now what do we do about
over here Weekly Payroll Table for Married? Well, guess what? We were smart. We set up this template
exactly the same as this one. Now all of these
formulas, notice that this has relative cell
references and absolute cell references. The VLOOKUP’s totally the same. But we can copy this. And relative and
absolute will be pointing to exactly the same
place on the other sheet. So I’m going to copy
all of these formulas. Control-C. Go over
to weekly payroll. Click in the upper left. And when I Control-V,
you’ve got to be kidding me. I can go to the last
cell and hit F2. Wow, it got exactly the
right cell references. I can check every one. And they are working, even
this huge one over here. All right, so that was a
little fun with federal income tax and MAX and VLOOKUP. There are definitely
some homework problems. There should be four homework
problems and answers. All right, so in this video we
talked about federal income tax calculations. We saw a few different examples. But the main formula
is taxable pay. We saw how to use
the MAX function. And continuing on video 33
where we did variable piece work and commission with lookup,
here for federal income taxes, we used the same VLOOKUP
three different times to retrieve the correct tax
data to make our calculation from three different columns. We did our federal
income tax formula and even mashed
them all together. All right, if you
like that video, be sure to click that thumbs up. Leave a comment and sub,
because there’s always lots more videos
to come from Excel is Fun, including next
video, video number 36, where we’ll do a
comprehensive payroll table with all the different
calculations we’ve seen. All right, we’ll
see you next video.

You May Also Like

About the Author: Oren Garnes

Leave a Reply

Your email address will not be published. Required fields are marked *