Everyone in this video viability and other use case of Excel this time we will get laid.
Income tax.
What your net income in most of the countries the governments specify tax slabs the presence of tax
slabs makes tax computation difficult.
Note that we will not see how to calculate net income in this video.
This is an excellent material with the objective of showcasing used cases of excel in different fields
in this tutorial.
We will see application of functions like some and if and features like absolute self referencing and
conditional formatting No let us look at the tax labs available with us so we have seven labs here.
As an example the first lab is of 10 percent for income between zero dollar to nine thousand seven hundred
dollars for income more than nine thousand two hundred dollars up to thirty nine thousand four hundred
seventy five dollars.
The tax bracket is twelve percent what this means is if your income is twenty thousand dollars your
nine thousand seven hundred dollars will be taxed at 10 percent rate.
And the additional ten thousand three hundred dollars will be taxed at 12 percent rate.
So this is all the tax budgeting system works as you go further.
What income more than thirty nine thousand four hundred seventy six new tax bracket is 22 percent going
further.
It is 24 percent 32 percent and in the end you have a tax bracket of thirty seven percent but income
more than five hundred ten thousand three hundred one dollars.
Now what we want to achieve in this tutorial is whenever I enter the value of income in this sale I
should get the total tax to be payable.
In this it let us see how we will achieve this.
The first thing I'm going to do is segregate these two values into two different columns.
The limits have lower limit and upper limit in the first column.
I add the lower limit and in the second column I add upper limits.
So the first one is zero order to 9700 the lower limit is zero.
Upper limit is nine thousand seven hundred or second guess nine thousand seven hundred is lower limit
and thirty nine thousand four hundred seventy five is the upper limit.
And so on in the last case on which the lower limit is five hundred and thousand three hundred dollars.
But there is no upper limit.
What we need to specify an upper limit.
So we have selected a very huge number and most probably no one will be earning more than this value.
Also since we need the values in dollars and we are currently having these values as numbers so we need
to change this to dollar values.
How to do that.
There is a dollar symbol here which will convert it to accounting system which automatically adds a
dollar symbol in front of the number.
You can note that the data remains intact the number of values not changed only the presentation of
values changing.
That is a dollar symbol is added in front of that numerical value.
Next we need to do three different calculations I'll first explain you the concept behind these calculations.
And in the last exercise sheet we will actually write these formulas so that we get these values.
The first calculation is calculating the maximum tax of that bucket.
What this means is if you're earning more than the upper limit of that tax bracket how much tax are
you liable to pay as per that bucket.
So for example if you are earning more than nine thousand seven hundred dollars you're definitely going
to pay nine hundred seventy dollars as tax are we are getting this value.
This value is simply upper limit.
Minus the lower limit into the X percentage in that tax bracket.
You can see the formula in the formula but also it is specified the same thing so while the first bucket
nine thousand seven hundred minus the lower limit into 10 percent gives us 970 for the second bracket
the upper limit is thirty nine thousand four hundred seventy five minus the lower limit which is nine
thousand seven hundred multiplied by the well person.
That's where you why we have to practice law limit because we have already paid 10 percent tax on this
nine thousand seven hundred value income on beyond nine thousand seven hundred.
Only that will be taxed at twelve percent rate so three thousand five hundred seventy three is the maximum
tax you will pay as but this tax bracket similarly we calculate all these values for all the other tax
brackets once we have this maximum value the second resolution that we need to do is to find out how
much of that bucket is actually food if you're earning between zero to nine thousand seven hundred.
This first bucket will be partially filled and we need to find out how much of that bucket is filled.
So supposing you are earning five thousand dollars how much of the first bucket will be paid that you
can find out by dividing five told and minus the lower limit divided by upper limit minus the lower
limit.
So it would be five thousand minus zero divided by nine thousand seven hundred minus zero if you are
earning more than nine thousand seven hundred.
This bucket will be completely filled and the next bucket will be partially filled.
If you are earning less than thirty nine thousand four hundred seventy five.
If you are earning more than thirty nine thousand four hundred seventy five.
Even the second bargain will become delivered and little more than the next bucket and so on.
In that example if I am using a value of eighty thousand dollars my first mortgage is completely free
because eighty thousand dollars is more than nine thousand seven hundred.
My second bucket is going to be livid because it is more than thirty nine thousand four hundred seventy
five.
But my third bucket is not completely filled because it is not more than eighty four thousand two hundred
I need to find the percentage of this bucket bucketful that I will go by dividing 80000 minus thirty
nine thousand four hundred seventy five by eighty four thousand two hundred my minus forty nine thousand
four hundred seventy five.
This will give me a value of 1 9 0 6 0 9 2 7.
Once we have the bucket filled with and we have the maximum tax of that bucket we just need to multiply
these two to get the tax of that bucket that we have to pay.
And if we add all these taxes for all the tax slabs we will get the total tax to be paid which is calculated
in that in this silly.
So this is all but we really calculate the maximum tax of each bucket.
Then we will find out how much each bucket is filled.
Then my multiplying these two values will get the actual tax to be paid in each slab and then we add
all these tax values to get the total tax to be paid to now let us go to the SSA sheet and start writing
these formulas so here we will separate the lower limit from upper limit and then multiply this difference
with deep tax bracket so e 9 minus B 9 multiplied by the percentage is just 10 percent.
Now you can see that this formalized e 9 minus the 9 multiplied by B 9 if you go to the bottom right
corner of the cell click and dragged down leave the left leg so when it but I don't e 90 automatically
becomes eaten in the formula bar.
This is because Excel automatically identifies that you are trying to copy this formula from one side
to another and it automatically changes the value inside that formula to help us get the required values
the next step is to get how much of the bucket is filled by the income value that we fill in this C
fossil or this.
We need to use the if statement to relate it equal to f and you can see that it has three bottom widows
the first barometer will be a logical test which means that we will check whether a given condition
is true or false.
If this condition given head is true Excel will the deserve that we gave after the first coma.
So will give the value.
If this condition is true and this will be displayed if this condition is actually true.
After this second coma will be mentioned.
What happens if this condition is false.
So if the condition is false we will get this value in return so we need to smartly write this formula.
So that it gives us value of 1 if the income is more than the upper limit.
So that is the first condition if income more than this value than it is one that is definite if it
is not more than the upper limit.
Then there are two scenarios.
Either it is between those two limit or it is even lower than the lower limit if it is between those
two limit so for that we will put another if statement.
This is if this value is greater than the lower limit
then you find the ratio the ratio is C for
minus the lower limit divided by upper limit.
Minus lower limit
and is it will be lower than the lower limit which means that slab has no value to be taken from.
So it should be easy to so you get this formula.
If this income is greater an upper limit then it is definitely 1.
If it is greater than the lower limit but less than upper limit then it should be a ratio of income
minus lower limit divided by upper limit minus lower limit.
If it is even lower than the lower limit then it should be little plus ended and we get one because
it is greater than the upper limit.
You move down by dragging the bottom right corner.
You can see in the settings in this C4 also becomes CFA.
But here we actually wanted this to remain C4 which contained the income value so while dragging if
you want to maintain the value of a particular sale we go to the formula and at dollar signs in front
of these two variables so when we are going to extend this formula down the column value is going to
increase.
That is this nine will increase to ten if you observe in this formula also it is C five disease meaning
same forward is becoming five because we are moving down from nine to 10 so if you want to maintain
Ford as Ford you will put a dollar symbol in front of Ford
so we'll put a dollar symbol in front of Ford off see Ford every bit if we would have wanted to extend
this formula to the right or to the left then instead of the draw value to be changing this column variable
will be changing.
So when we are extending down we'll put a dollar symbol in front of this number.
If we were extending it to the right or left we will put a dollar symbol in front of this letter C or
this letter E whichever we want to keep as constant this is known as absolute referencing if you want
to know more about it you can google about absolute defensive so we'll look on in that
now then we'll extend this we'll go to the bottom right corner and go to the L till the end of the stable.
Now this column is completely filled.
You can see the third row now has 90 percent nearly 1 9 0 so 80000 value fills the third bucket nearly
90 percent now.
Third step which is tax calculation is simply the product of bucket fill it with the maximum tax of
that bucket.
We will also extend this by double clicking on the bottom right corner of this
now to visualize this bucket full rate better that we can use conditional formatting conditional formatting
is done by selecting the cells you want to format then going to conditional formatting in the home menu
and selecting the type of drill that you want to get.
So here I'm going to use these solid fill data but so does this hundred percent fill.
So this is completely blue.
This is 90 percent full.
This is 90 percent blue and the other ones are 0 now to calculate total tax.
I will add all these sales so we'll use some formula and we will add all these sales to a the total
tax on the income if you want to calculate the effective tax rate for you you can do that by dividing
total tax the debt income.
If you want to change this value in two percentage you can just go and click on this percentage symbol.
So effectively on an income of 80000 you are being charged a tax at a rate of 17 percent.
So you may fall in deep tax bracket of 22 percent but effective tax rate for you is 17 percent.
Now since we used sales differences everywhere by just changing the value of income we can get the total
tax value and the effective tax rate in an instant.
So we will change just to two hundred thousand dollars
and you can see you'll have to pay a tax of forty five thousand two hundred sixteen point five dollars.
And that is an effective tax rate of gonna be 3 percent if you like this video.
We have a complete course on Microsoft Excel.
It's a short quick course it will refresh audio concepts and you will definitely learn some new tricks
also you can find the link with a pretty applied discount coupon in the description below if you like
this video.
Don't forget to like share and subscribe.
Thank you for watching this video.