Excel VLOOKUP for Tax Brackets Year 2021 with Examples - YouTube

Hello, I'm Chris Menard.

In today's Excel video, we're going to do an Excel exercise using the United States

tax brackets.

This is a great exercise for accounting students, MBA students, anyone that just wants to know,

hey, how much tax am I going to have to pay on my taxable income?

I've done this video before, I did it with the XLOOKUP function, but not everyone has

XLOOKUP, so it was requested that I come back and say, is there another way to do this,

I don't have the same Excel version you were using.

The answer's yes.

We're going to do it with VLOOKUP, so this will work in any version of Excel.

The United States has a progressive tax system.

I'm going to keep this simple, I'm just doing this for married, filing jointly.

Right below it though is single.

So for 81,000, 50 bucks of taxable income, which is cell F2, that puts you in a 22% tax

bracket.

So your tax liability is $9,328.

I've already made this little table over here.

So you don't pay 22% on all 81,000, you pay 10% on a portion, 12% on a portion, 22%, and

it keeps going.

There are seven different brackets.

So here we go with VLOOKUP.

Before I start, F2 is taxable income.

If you had your 1040 Tax form out, this'll be line number 15.

So it's not your gross income, it's not your adjusted gross, that is taxable income.

But here we go with VLOOKUP.

Four arguments are in VLOOKUP, first one is what is it you're trying to look up?

That's cell F2.

Where's your table array?

I just selected A2 to C9, comma.

Inside of A2 to C9, which has three columns.

Notice it says column index number.

I'm trying to figure out how much tax I owe, so this not column C, it's the number three,

cause it's the third column from A3 to C9.

And then this is an optional one, but true is what we want.

I actually get 9328.

I'm going to do Control Enter, and I got 9328.

Here's the issue, we're in that 22% tax bracket at 81,050.

So for every thousand dollars over 81,050, that should be $220 of tax.

A thousand dollars times 22%.

So I'm going to just throw some math over here.

If we have income of 82,050, we should end up with 9528.

The issue is, I go change it to that number in F2, but F3 didn't change, because I need

to figure out the additional thousand dollars over the 81,050 at 22%.

So I'm going to do the VLOOKUP, plus F2 minus VLOOKUP of F2.

Back to the VLOOKUP, you got to do all the arguments, one, comma, true.

I'm not done here.

What we just figured out from the plus sign over to the right is 82,000 minus whatever

the VLOOKUP is, which should be 81,050.

That's a thousand dollars.

Let's see if this comes out right.

It came out to 10,328, In case you're wondering, 9,328 and 10,328, there's your thousand dollars.

So I've got the thousand dollars now isolated, and now I need to multiply that times the

22%.

So back in here and we're going to do plus VLOOKUP, so I'm just kind of walking you through

this.

Comma, last one here by the way.

I need to find that percentage.

Comma, 2, cause the percentage is the second column and what I've got selected.

Comma, true, this should give us 9,328 plus a thousand dollars times 22% is $220.

95, got one thing wrong in here.

Let me look real quick.

A3 comma, one, true, oh, I got plus my bad, times the 22%.

9,528, which is 9,328 plus 220 bucks.

There you go.

One more item.

If I change this to 172,750 of taxable income, that should give you 29,502, and it does.

Now every thousand dollars over that number, isn't 220 bucks, it's 240 bucks.

Real quick test, 1 3, 1 7 3 7 5 0, there we go.

Undo, perfect, redo.

I'm just checking the numbers.

Alrighty, by the way, meat real quick, the marginal tax rate, VLOOKUP, that number, comma,

your table array, go ahead and finish this one.

This one's easy comma, that should be the second column, true, I should see 24%, and

I do.

Your effective tax rate is just a formula, it is the amount of tax divided by the taxable

income.

17%, let's just keep it at 17%.

I'm going to take this back to 81,050, perfect.

82,050, we're in business, there you go.

Copy this.

Control C. You're saying Chris, I'm not married filing jointly, I'm single.

There you go, right there.

Let's just test this number 86 3 7 5.

14 7 51 87, 3 7 5, a thousand dollars times 24% is 240 bucks.

There we go.

Anyway, there's your VLOOKUP.

I will put this file so you can download it.

Again, this is 2020, that is taxable income line 15.

Your tax is on your 10 40 line 16.

It doesn't matter whether you're married or single, line 15 and 16.

Let me know if you like this video, feel free to subscribe.

Have a great day.

Thank you.