MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Progressive Tax Rate


November 07, 2017 - by Bill Jelen

Progressive Tax Rate

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