Locked lesson.
About this lesson
In this lesson, we learn how to use Excel formulas to calculate your tax liability.
Quick reference
Income Tax calculator
Learn to identify the elements required to calculate tax based on brackets.
When to use
This becomes handy 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
- 00:04 In this lesson, I'd like to help you set up your own income tax calculator.
- 00:09 Just note that this example works with net taxable income.
- 00:13 It does not take into account any allowable tax deductions.
- 00:17 It will calculate gross tax, and
- 00:19 you can apply tax credits to that to get your net tax.
- 00:23 This example uses US Federal Tax, but it would work for any tax system that charges
- 00:28 tax in brackets or bands or increments, whatever you want to call them.
- 00:33 Taxable income of 50,000 in this example would result in a tax payable of 6,790.
- 00:42 Taxable income of 85,000 should result in a tax of about 14,500 for the year.
- 00:49 Okay, well, first we need to understand the manual calculation.
- 00:54 This is our exercise that we're looking at.
- 00:57 Let's jump over to the tax calculator sheet.
- 01:01 We'll collapse this for now and open this up.
- 01:06 Many countries have this type of tax system where you earn income and
- 01:10 then the tax payable on that income is calculated in brackets,
- 01:14 or let's call them buckets for the purpose of this example.
- 01:18 You earn income up to a certain point.
- 01:21 9,875 is the last drop of income in this first bucket, and
- 01:25 you pay 10% of tax on that.
- 01:28 The moment your income spills into the next bucket,
- 01:31 you pay a higher percentage of income tax of the income in that new bucket.
- 01:36 So if you earn somewhere around $50,000,
- 01:40 let's see if we can plot that, 50,000 should be around here.
- 01:47 So you would pay 10% on the first bucket of income, 12% on the next bucket,
- 01:52 and then 22% just on that portion of the 50,000 in the final bucket.
- 01:59 So, let's see, what do we need for this calculation to work?
- 02:04 We need the marginal tax rate, the 22%.
- 02:09 Then you need to know what the spill point is because you need to take all
- 02:13 the previous buckets income out.
- 02:16 So the top of the last bucket, the spill point to that bucket is 40,125,
- 02:22 and then we need to know how much is the full 12% tax.
- 02:27 And how much is the 10% tax in these previous buckets.
- 02:32 If you Google a tax table,
- 02:33 those full tax increments will probably be indicated like this list up here.
- 02:38 You pay 10% of the bracket plus in amount,
- 02:42 that's the first bracket, so there's no amount there.
- 02:48 12% on the next bracket plus 98,750.
- 02:51 Now the 98,750 is the full tax from the first bucket.
- 02:56 It's 10% of 9,875.
- 03:00 That gives you 98,750.
- 03:04 The 4,617 on the next interval is the 40,125,
- 03:11 minus the 9,875, times 12%, and
- 03:16 then plus the 98,750 from the first bracket, okay?
- 03:23 Let's put this in our bracket or bucket.
- 03:26 Full tax on the previous buckets is 4,617.50.
- 03:30 Now let's calculate the tax, the tax payable
- 03:36 is your $50,000 minus the 40,125.
- 03:41 And that gives me this red portion of income that will get taxed to 22%.
- 03:45 So we multiply by 22%, plus that tax from
- 03:50 the previous brackets, which was 4,617.50.
- 03:55 So our total tax is 6,790.
- 04:01 So far, so good.
- 04:02 All right, in the next lesson,
- 04:05 let's convert this into x lookups and this into an x lookup table.
- 04:11 So we don't have to go through all these effort again.
Lesson notes are only available for subscribers.