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.