Locked lesson.
About this lesson
In this lesson, we create a lookup table and use lookup formulas to calculate tax liability.
Quick reference
Income Tax calculator
Learn to create your own tax table using mainly lookup formulas.
When to use
This is very useful when you want to calculate your tax payable for the year.
Instructions
- Tax systems often use a system where an individual get taxed at a higher percentage for each higher increment of income
- Understand the elements required for the calculation so that it can be converted into a tax table to automate the process
- Tax =
- (total income – highest value in previous tax bracket)
- x percentage tax in highest bracket
- + total tax on all previous brackets
- Create a lookup table that you could use to calculate tax per bracket
- First income in new bracket is in the first column (this is your lookup column)
- The other column order does not really matter
- Marginal income tax rate (rate for the bracket)
- Top value for the previous bracket
- The total tax on all previous brackets
- Tax calculation:
- (supplied value (income) – xlookup (income, lookup column, top value in previous bracket column)
- * Marginal income tax rate
- + total tax in previous brackets
- 00:04 The next step in creating our own income tax calculator is to convert
- 00:09 the manual calculation into one that uses X lookups.
- 00:13 The solution also includes a V lookup.
- 00:15 So if you can only work with V lookups it is in the solution, but
- 00:18 we're going to concentrate on X lookups for the purpose of this video.
- 00:24 Now a tax payable formula that we used in the last
- 00:28 lesson is exactly the same one we're going to use and this one.
- 00:32 Quick recap, to calculate tax on $50,000,
- 00:36 that's your 50 less the last income in the previous bucket
- 00:41 times 22% gives you the tax in the last bucket,
- 00:45 plus all the tax from the previous buckets.
- 00:50 And we'll use the same formula to get our tax in the calculator so we can copy and
- 00:55 paste it over here.
- 00:57 But what we don't want is to manually look up the values.
- 01:02 We can use X lookup to find the values for us.
- 01:05 But we need to create a table that the X lookup can use.
- 01:09 So how do we complete this tax table?
- 01:11 The first column is an indicator where the bracket or the bucket would start.
- 01:17 The first bucket of income starts at zero.
- 01:20 The next bucket starts at 9876, then 4126, 85,000 and so on.
- 01:27 Let's just fill these in, referring to our original table on the right.
- 01:34 Next column is marginal tax rates.
- 01:36 Now, the tax rate that's valid for the bucket that starts at zero is 10%.
- 01:41 Tax rate for the next bucket that starts at 9876 is 12%.
- 01:47 And again, we could fill this in by referring to our original table,
- 01:50 on the right.
- 01:54 To get the top of the previous bracket, or the spill point for the previous bracket,
- 01:58 the first one is zero because there's no previous bracket.
- 02:02 But the next one, the value is just one less than where the new bracket begins.
- 02:07 And we can complete that with a simple formula,
- 02:10 it's just Bracket Start minus one.
- 02:13 And I can copy and paste special.
- 02:16 And we'll use formulas so that we don't mess up any formatting here.
- 02:23 So now tax on previous brackets, there's no previous brackets for the first one.
- 02:27 So again, that one is zero.
- 02:29 And then we can type in these values from the right hand table, the 987.50, 4617.
- 02:36 However, it's never a good idea to hard code values when we can just create
- 02:41 a formula to compute them.
- 02:43 So let's do that here.
- 02:46 9875 less the top of the previous bracket,
- 02:50 times the previous bracket's marginal tax,
- 02:55 plus whatever the tax from the previous brackets
- 02:59 ended up being, and I've got my 987.50.
- 03:04 So that works but let's do it again just to be sure.
- 03:08 Top of the previous bracket minus the one before that,
- 03:13 multiply by the previous marginal tax rate, and
- 03:18 then add the tax from the previous buckets and we get 4617.50.
- 03:25 All right, that checks out.
- 03:28 And now that we've double checked our calculations and see they agree with
- 03:32 the tax table on the right, we can copy and paste our formulas down.
- 03:36 And again, let's use Paste Special in formulas to preserve our formatting.
- 03:42 Okay, so now that our tax table is ready, let's put some formulas in place for
- 03:47 the values down below.
- 03:49 To get the relevant spill point for our income,
- 03:52 we can X lookup the income in the Bracket Start column.
- 03:57 Let's do that, X lookup.
- 04:00 Select our income.
- 04:01 And the lookup array is the Bracket Start column.
- 04:07 We want to return the top of previous bracket value that's in this column.
- 04:11 We don't need an if not found message here because we are going to
- 04:14 do an approximate match.
- 04:16 And what we want to match is the exact value or next smaller item.
- 04:21 So the match mode is -1 in this case.
- 04:25 We hit Enter and there is 40,125.
- 04:29 And that is indeed the top of the previous bracket.
- 04:33 So, now marginal tax rate.
- 04:34 That's basically the same formula.
- 04:37 X lookup, my income.
- 04:39 The lookup array is the Bracket Start.
- 04:42 Return array is marginal tax.
- 04:46 Don't need an if not found, again because we're doing an approximate match.
- 04:50 Same -1 lookup type again.
- 04:52 And we get 22% and the tax on previous brackets is also the same deal.
- 04:58 X lookup, my income, look up array as the bracket start,
- 05:03 return array is the tax on previous brackets and again,
- 05:08 a -1 to get exact or next smaller item.
- 05:12 And there's our tax payable, 6790.
- 05:16 So that looks great.
- 05:18 And if we want, we can update our income to 95,000 for
- 05:21 the year and all the relevant values update along with it.
- 05:26 So this looks fantastic.
- 05:27 And I really hope this tax calculator helps you out.
Lesson notes are only available for subscribers.