# Progressive Tax Rate

November 07, 2017 - by Bill Jelen

Excel formula for a progressive tax rate. This article shows you how to build a formula to calculate a progressive tax rate in Excel.

## Watch Video

- Formula to calculate a progressive tax rate
- Set up a table in ascending order with the amounts from your income tax card
- You will do three Approximate Match VLOOKUPS to get the base tax, the percentage and the start of the level
- Subtract the Start Level from the income for the period. Multiply by the percentage.
- Used
`Ctrl + '`to copy a formula down one cell without changing the references - Using the
`F9`trick from Mike Girvin to embed the lookup tables in the formula.

### Auto-Generated Transcript

- Learn Excel from MrExcel Podcast
- episode 2065 a formula for progressive
- tax rates alright so I got this comment
- on YouTube from Assad 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 coin crossing we're trying
- to figure out the tax for this alright
- so to get started the first thing we
- have to do is take Assad'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 three
- columns alright so if you're at zero
- dollars you pay zero tax and zero
- percent of any amount over zero but once
- you hit 400,000 then you're paying seven
- percent of the amount over four hundred
- thousand at five hundred thousand you're
- paying seven thousand dollars plus ten
- percent of the amount over five thousand
- dollars so this lookup table here it's
- going to become crucial towards solving
- the problem alright well hey let's just
- dive in straight away
- and do this so we're gonna 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 version so
- we go look for this about two hundred
- forty nine thousand three hundred fifty
- one in this table and I'll 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 I and see
- that's coming up with just this amount
- the seven thousand the thirty two
- thousand and 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 gonna be very clever here
- and copy the formula except for
- equal sign so 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 all right and
- the percentage is over in column three
- of the table so right there is the
- percentage the percentage of the amount
- in b16 that is greater than the amount
- in column one of the table so times in
- parenthesis be 16 - and again we're
- going to put another vlookup in there
- that this time is using table column
- number one 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 all right so I pause the video
- there and here's my set of check
- formulas basically just breaking down
- this form that it has one two three four
- components and doing it one at a time so
- here is the base rate using vlookup
- comma two here's the percentage using
- vlookup comma three here's the the
- initial amount using vlookup comma one
- and then calculating the amount over
- that would be this amount hundred the
- income up there in M 16 - the base doing
- the calculation of the percentage
- fifteen percent times that amount and
- then finally just a simple addition to
- add the tax and so hopefully all of
- these forms are going to match those
- formulas let's put a little formula here
- where say is the result of all those
- smaller formulas equal to the one big
- formula we're hoping to get there is all
- throughs 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 for them to check out
- this cool trick control and the
- quotation mark
- I guess control ' it 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'll 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 all right way to learn all
- sorts of formulas about Excel check out
- my new book power excel with the mr.
- excel 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 the table in ascending order with
- the amounts from your income tax card
- three columns you'll do three
- approximate match if you look up stick
- it at 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 control I call this control
- ditto control ' to copy form it down one
- cell without changing the references and
- leave it in edit mode and then finally
- the f9 trick from my current Excel is
- fun to embed the lookup table into the
- formula I don't wanna think is soft for
- sending that question in I want to thank
- you for stopping by we'll see you next
- time for another net cast from MrExcel

Download File

Download the sample file here: Podcast2065.xlsm

*Title Photo: Foto-Rabe / Pixabay*