Progressive Tax Rate

Learn Excel from MrExcel Podcast, Episode 2065: A Formula for Progressive Tax Rates

Alright, so I got this comment on YouTube from Asad, who is trying to come up with a formula that will calculate his income tax.  He says he has an income tax statement they download from office.com.  I just made a quick little one here with income and income year-to-date, going across.  So, we're trying to figure out the tax for this, alright. 

So to get started, the first thing we have to do is: take Asad's Income Tax card and build a little table somewhere out of the way, alright?  So, I just decided to come down here, I might go over to call them, you know, Z or something like that.  And you take this information and you convert it into a table with 3 columns, alright.  So, if you're at 0 dollars, you pay 0 Tax and 0 percent of any amount over 0.  But once you hit 400,000, then you're paying 7% of the amount over 400,000.  And 500,000, you’re paying $7,000 plus 10% of the amount over 500,000.  So, this lookup table here is going to become crucial towards solving the problem.

Alright, well hey, let's just dive in straight away and do this.  So, we're going to use a function called VLOOKUP.  And I use VLOOKUP a lot, the other version, the exact match version.  But in this case, we're going to use the, what they call the Approximate Match version.  So we go look for this amount, 249,351, in this table.  Now, press F4 there to lock that table down.  In other words, put the dollar signs in.  And then, what I want is I want the base tax rate, so that's the second column and then comma, True.  You can leave the True out but I always put the True in, just that way I know that it's there, alright?  And see, that's coming up with just this amount, the 7,000, the 32,000.  If I copied across, you’ll see how that part is working.  That certainly isn't the whole formula.  I just wanted to show what that one piece is doing.  And I'm going to be very clever here and copy the formula except for the equal sign.  So I selected - because I'm going to reuse that formula over and over and over again. 

So, in addition to that, we have to use the percentage, alright?  And the percentage is over in Column 3 of the table.  So, right there is the percentage.  That percentage of the amount in B16 that is greater than the amount in Column 1 of the table.  So, *(B16- , and again, we're going to put another VLOOKUP in there, this time it’s using table Column number 1.  Alright, and there's our formula.  We'll copy that formula across like that, alright?  And that should be the right answer; although, you know, I realized that's probably a pretty complicated formula.  And if this is something really important, you know, like homework, then you would want to at least do some checks to make sure that it's going to work. 

Alright, so I paused the video there and here's my set of checked formulas.  Basically just breaking down this formula that has – 1, 2, 3, 4 components and doing it one at a time.  So here is the base rate using VLOOKUP,2.  Here's the Percentage using VLOOKUP,3.  Here's the Initial Amount using VLOOKUP,1. And then, calculating the amount over, that would be this amount 100 – Ah, the income up there in M16 minus the base, doing the calculation of the percentage: 15% times that amount, and then finally, just a simple addition to add the Tax.  And so, hopefully, all of these formulas are going to match those formulas.  Let's put a little formula here where we say is the result of all those smaller formulas equal to the one big formula.  We're hoping to get there is all TRUES, all the way across.  And sure enough that works.  So at that point, we know that our big formula up there is working.

Now, the problem is about- with this is that we have that table sitting off to the side.  And if you're looking for a single formula to do this, it would be possible to do that.  So I'm going to do one formula, check out this cool trick: Ctrl+’ .  I guess Ctrl apostrophe, really brings the formula down, doesn't change any of the references and leaves the formula in Edit Mode.  And what I can do here is click right there and press the F9 key that will embed that table right in the formula.  And then here, this lookup table, choose those characters, press the F9 key.  And finally here, select the Lookup table one more time and press the F9 key, and we get one monster formula that will do everything without having the Lookup table in the workbook.         

Asad, if this actually is homework for you, I'll just go ahead and turn that one in and I'll make your professor's head spin.  You probably, you know, get high marks for the exercise.

Alright, way to learn all sorts of formulas about Excel.  Check out my new book, Power Excel with MrExcel, the 2017 Edition.  Includes some Power BI, and Power Query and things like that.

Wrap up of this episode:  We tried to create a formula to create a progressive tax rate.  The most important thing is to set up that table in ascending order with the amounts from your income tax card, 3 columns.  You'll do 3 Approximate Match VLOOKUPS to get the base tax, the percentage and the start of the level.  You subtract that Start Level from the income for the period, multiplied by the percentage and then add the base.  Then near the end there, use Ctrl - I call this Ctrl ditto, Ctrl+’ to copy a formula down one cell without changing the references and leave it in Edit Mode.  And then finally, the F9 trick from Mike Girvin, Excel Is Fun, to embed the lookup table into the formula.

Alright, I want to thank Asad for sending that question in.  I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.