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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.