1 00:00:01,760 --> 00:00:08,740 Everyone in this video viability and other use case of Excel this time we will get laid. 2 00:00:08,780 --> 00:00:09,710 Income tax. 3 00:00:09,740 --> 00:00:18,400 What your net income in most of the countries the governments specify tax slabs the presence of tax 4 00:00:18,400 --> 00:00:21,460 slabs makes tax computation difficult. 5 00:00:23,490 --> 00:00:27,020 Note that we will not see how to calculate net income in this video. 6 00:00:27,600 --> 00:00:34,200 This is an excellent material with the objective of showcasing used cases of excel in different fields 7 00:00:35,480 --> 00:00:36,430 in this tutorial. 8 00:00:36,440 --> 00:00:43,880 We will see application of functions like some and if and features like absolute self referencing and 9 00:00:43,880 --> 00:00:51,300 conditional formatting No let us look at the tax labs available with us so we have seven labs here. 10 00:00:51,340 --> 00:00:58,750 As an example the first lab is of 10 percent for income between zero dollar to nine thousand seven hundred 11 00:00:58,750 --> 00:01:05,390 dollars for income more than nine thousand two hundred dollars up to thirty nine thousand four hundred 12 00:01:05,390 --> 00:01:06,610 seventy five dollars. 13 00:01:06,710 --> 00:01:15,370 The tax bracket is twelve percent what this means is if your income is twenty thousand dollars your 14 00:01:15,490 --> 00:01:20,130 nine thousand seven hundred dollars will be taxed at 10 percent rate. 15 00:01:21,540 --> 00:01:27,860 And the additional ten thousand three hundred dollars will be taxed at 12 percent rate. 16 00:01:27,940 --> 00:01:32,960 So this is all the tax budgeting system works as you go further. 17 00:01:33,460 --> 00:01:40,380 What income more than thirty nine thousand four hundred seventy six new tax bracket is 22 percent going 18 00:01:40,380 --> 00:01:40,910 further. 19 00:01:40,920 --> 00:01:48,770 It is 24 percent 32 percent and in the end you have a tax bracket of thirty seven percent but income 20 00:01:49,340 --> 00:01:54,070 more than five hundred ten thousand three hundred one dollars. 21 00:01:54,080 --> 00:02:02,460 Now what we want to achieve in this tutorial is whenever I enter the value of income in this sale I 22 00:02:02,460 --> 00:02:04,670 should get the total tax to be payable. 23 00:02:04,770 --> 00:02:12,490 In this it let us see how we will achieve this. 24 00:02:12,520 --> 00:02:18,730 The first thing I'm going to do is segregate these two values into two different columns. 25 00:02:18,730 --> 00:02:22,270 The limits have lower limit and upper limit in the first column. 26 00:02:22,270 --> 00:02:27,190 I add the lower limit and in the second column I add upper limits. 27 00:02:27,190 --> 00:02:32,130 So the first one is zero order to 9700 the lower limit is zero. 28 00:02:32,140 --> 00:02:37,830 Upper limit is nine thousand seven hundred or second guess nine thousand seven hundred is lower limit 29 00:02:38,490 --> 00:02:41,520 and thirty nine thousand four hundred seventy five is the upper limit. 30 00:02:42,920 --> 00:02:51,720 And so on in the last case on which the lower limit is five hundred and thousand three hundred dollars. 31 00:02:51,940 --> 00:02:53,980 But there is no upper limit. 32 00:02:53,980 --> 00:02:55,570 What we need to specify an upper limit. 33 00:02:55,600 --> 00:03:06,770 So we have selected a very huge number and most probably no one will be earning more than this value. 34 00:03:06,910 --> 00:03:16,030 Also since we need the values in dollars and we are currently having these values as numbers so we need 35 00:03:16,030 --> 00:03:18,920 to change this to dollar values. 36 00:03:18,940 --> 00:03:19,800 How to do that. 37 00:03:19,810 --> 00:03:25,180 There is a dollar symbol here which will convert it to accounting system which automatically adds a 38 00:03:25,180 --> 00:03:27,020 dollar symbol in front of the number. 39 00:03:27,190 --> 00:03:33,040 You can note that the data remains intact the number of values not changed only the presentation of 40 00:03:33,040 --> 00:03:34,170 values changing. 41 00:03:34,240 --> 00:03:43,350 That is a dollar symbol is added in front of that numerical value. 42 00:03:43,350 --> 00:03:51,170 Next we need to do three different calculations I'll first explain you the concept behind these calculations. 43 00:03:51,170 --> 00:03:59,310 And in the last exercise sheet we will actually write these formulas so that we get these values. 44 00:03:59,450 --> 00:04:05,080 The first calculation is calculating the maximum tax of that bucket. 45 00:04:05,090 --> 00:04:12,870 What this means is if you're earning more than the upper limit of that tax bracket how much tax are 46 00:04:12,870 --> 00:04:15,240 you liable to pay as per that bucket. 47 00:04:16,890 --> 00:04:22,740 So for example if you are earning more than nine thousand seven hundred dollars you're definitely going 48 00:04:22,740 --> 00:04:29,450 to pay nine hundred seventy dollars as tax are we are getting this value. 49 00:04:29,450 --> 00:04:32,470 This value is simply upper limit. 50 00:04:32,600 --> 00:04:37,760 Minus the lower limit into the X percentage in that tax bracket. 51 00:04:37,760 --> 00:04:44,800 You can see the formula in the formula but also it is specified the same thing so while the first bucket 52 00:04:45,300 --> 00:04:51,320 nine thousand seven hundred minus the lower limit into 10 percent gives us 970 for the second bracket 53 00:04:52,410 --> 00:04:57,990 the upper limit is thirty nine thousand four hundred seventy five minus the lower limit which is nine 54 00:04:57,990 --> 00:05:03,310 thousand seven hundred multiplied by the well person. 55 00:05:03,330 --> 00:05:11,980 That's where you why we have to practice law limit because we have already paid 10 percent tax on this 56 00:05:11,980 --> 00:05:18,170 nine thousand seven hundred value income on beyond nine thousand seven hundred. 57 00:05:18,720 --> 00:05:26,800 Only that will be taxed at twelve percent rate so three thousand five hundred seventy three is the maximum 58 00:05:26,800 --> 00:05:35,430 tax you will pay as but this tax bracket similarly we calculate all these values for all the other tax 59 00:05:35,430 --> 00:05:44,280 brackets once we have this maximum value the second resolution that we need to do is to find out how 60 00:05:44,280 --> 00:05:51,790 much of that bucket is actually food if you're earning between zero to nine thousand seven hundred. 61 00:05:51,850 --> 00:05:57,940 This first bucket will be partially filled and we need to find out how much of that bucket is filled. 62 00:05:57,950 --> 00:06:04,530 So supposing you are earning five thousand dollars how much of the first bucket will be paid that you 63 00:06:04,530 --> 00:06:10,860 can find out by dividing five told and minus the lower limit divided by upper limit minus the lower 64 00:06:10,860 --> 00:06:11,500 limit. 65 00:06:11,520 --> 00:06:18,430 So it would be five thousand minus zero divided by nine thousand seven hundred minus zero if you are 66 00:06:18,430 --> 00:06:20,760 earning more than nine thousand seven hundred. 67 00:06:21,390 --> 00:06:26,690 This bucket will be completely filled and the next bucket will be partially filled. 68 00:06:26,730 --> 00:06:29,430 If you are earning less than thirty nine thousand four hundred seventy five. 69 00:06:30,090 --> 00:06:32,730 If you are earning more than thirty nine thousand four hundred seventy five. 70 00:06:32,880 --> 00:06:38,440 Even the second bargain will become delivered and little more than the next bucket and so on. 71 00:06:39,710 --> 00:06:46,720 In that example if I am using a value of eighty thousand dollars my first mortgage is completely free 72 00:06:46,930 --> 00:06:50,770 because eighty thousand dollars is more than nine thousand seven hundred. 73 00:06:50,770 --> 00:06:55,120 My second bucket is going to be livid because it is more than thirty nine thousand four hundred seventy 74 00:06:55,120 --> 00:06:55,420 five. 75 00:06:56,380 --> 00:07:01,270 But my third bucket is not completely filled because it is not more than eighty four thousand two hundred 76 00:07:02,700 --> 00:07:11,010 I need to find the percentage of this bucket bucketful that I will go by dividing 80000 minus thirty 77 00:07:11,010 --> 00:07:17,520 nine thousand four hundred seventy five by eighty four thousand two hundred my minus forty nine thousand 78 00:07:17,520 --> 00:07:18,450 four hundred seventy five. 79 00:07:19,410 --> 00:07:24,740 This will give me a value of 1 9 0 6 0 9 2 7. 80 00:07:24,870 --> 00:07:32,340 Once we have the bucket filled with and we have the maximum tax of that bucket we just need to multiply 81 00:07:32,370 --> 00:07:37,260 these two to get the tax of that bucket that we have to pay. 82 00:07:37,620 --> 00:07:44,820 And if we add all these taxes for all the tax slabs we will get the total tax to be paid which is calculated 83 00:07:44,820 --> 00:07:48,340 in that in this silly. 84 00:07:48,360 --> 00:07:53,110 So this is all but we really calculate the maximum tax of each bucket. 85 00:07:53,110 --> 00:07:56,510 Then we will find out how much each bucket is filled. 86 00:07:56,680 --> 00:08:03,610 Then my multiplying these two values will get the actual tax to be paid in each slab and then we add 87 00:08:03,640 --> 00:08:12,630 all these tax values to get the total tax to be paid to now let us go to the SSA sheet and start writing 88 00:08:12,630 --> 00:08:21,210 these formulas so here we will separate the lower limit from upper limit and then multiply this difference 89 00:08:21,420 --> 00:08:36,930 with deep tax bracket so e 9 minus B 9 multiplied by the percentage is just 10 percent. 90 00:08:37,010 --> 00:08:45,200 Now you can see that this formalized e 9 minus the 9 multiplied by B 9 if you go to the bottom right 91 00:08:45,200 --> 00:08:58,620 corner of the cell click and dragged down leave the left leg so when it but I don't e 90 automatically 92 00:08:58,620 --> 00:09:01,040 becomes eaten in the formula bar. 93 00:09:01,080 --> 00:09:06,840 This is because Excel automatically identifies that you are trying to copy this formula from one side 94 00:09:06,840 --> 00:09:14,010 to another and it automatically changes the value inside that formula to help us get the required values 95 00:09:17,460 --> 00:09:25,220 the next step is to get how much of the bucket is filled by the income value that we fill in this C 96 00:09:25,290 --> 00:09:28,670 fossil or this. 97 00:09:28,780 --> 00:09:37,510 We need to use the if statement to relate it equal to f and you can see that it has three bottom widows 98 00:09:39,440 --> 00:09:45,710 the first barometer will be a logical test which means that we will check whether a given condition 99 00:09:45,710 --> 00:09:47,280 is true or false. 100 00:09:47,570 --> 00:09:55,670 If this condition given head is true Excel will the deserve that we gave after the first coma. 101 00:09:55,980 --> 00:09:57,620 So will give the value. 102 00:09:57,720 --> 00:10:01,910 If this condition is true and this will be displayed if this condition is actually true. 103 00:10:03,540 --> 00:10:06,480 After this second coma will be mentioned. 104 00:10:06,600 --> 00:10:09,150 What happens if this condition is false. 105 00:10:10,140 --> 00:10:17,070 So if the condition is false we will get this value in return so we need to smartly write this formula. 106 00:10:18,170 --> 00:10:25,870 So that it gives us value of 1 if the income is more than the upper limit. 107 00:10:27,660 --> 00:10:36,090 So that is the first condition if income more than this value than it is one that is definite if it 108 00:10:36,090 --> 00:10:39,420 is not more than the upper limit. 109 00:10:39,450 --> 00:10:41,130 Then there are two scenarios. 110 00:10:41,150 --> 00:10:49,340 Either it is between those two limit or it is even lower than the lower limit if it is between those 111 00:10:49,340 --> 00:10:55,520 two limit so for that we will put another if statement. 112 00:10:55,580 --> 00:10:59,840 This is if this value is greater than the lower limit 113 00:11:03,580 --> 00:11:09,540 then you find the ratio the ratio is C for 114 00:11:14,430 --> 00:11:21,840 minus the lower limit divided by upper limit. 115 00:11:23,070 --> 00:11:24,510 Minus lower limit 116 00:11:29,290 --> 00:11:37,530 and is it will be lower than the lower limit which means that slab has no value to be taken from. 117 00:11:37,660 --> 00:11:41,080 So it should be easy to so you get this formula. 118 00:11:41,990 --> 00:11:46,640 If this income is greater an upper limit then it is definitely 1. 119 00:11:47,480 --> 00:11:57,080 If it is greater than the lower limit but less than upper limit then it should be a ratio of income 120 00:11:57,170 --> 00:12:00,890 minus lower limit divided by upper limit minus lower limit. 121 00:12:00,890 --> 00:12:08,920 If it is even lower than the lower limit then it should be little plus ended and we get one because 122 00:12:09,010 --> 00:12:10,600 it is greater than the upper limit. 123 00:12:10,600 --> 00:12:16,600 You move down by dragging the bottom right corner. 124 00:12:16,770 --> 00:12:24,360 You can see in the settings in this C4 also becomes CFA. 125 00:12:25,230 --> 00:12:35,580 But here we actually wanted this to remain C4 which contained the income value so while dragging if 126 00:12:35,580 --> 00:12:45,820 you want to maintain the value of a particular sale we go to the formula and at dollar signs in front 127 00:12:45,820 --> 00:12:54,310 of these two variables so when we are going to extend this formula down the column value is going to 128 00:12:54,310 --> 00:12:55,150 increase. 129 00:12:55,150 --> 00:13:05,080 That is this nine will increase to ten if you observe in this formula also it is C five disease meaning 130 00:13:05,080 --> 00:13:12,960 same forward is becoming five because we are moving down from nine to 10 so if you want to maintain 131 00:13:13,230 --> 00:13:18,030 Ford as Ford you will put a dollar symbol in front of Ford 132 00:13:25,150 --> 00:13:34,880 so we'll put a dollar symbol in front of Ford off see Ford every bit if we would have wanted to extend 133 00:13:34,880 --> 00:13:44,270 this formula to the right or to the left then instead of the draw value to be changing this column variable 134 00:13:44,270 --> 00:13:45,510 will be changing. 135 00:13:45,590 --> 00:13:51,530 So when we are extending down we'll put a dollar symbol in front of this number. 136 00:13:51,530 --> 00:13:57,850 If we were extending it to the right or left we will put a dollar symbol in front of this letter C or 137 00:13:57,860 --> 00:14:05,390 this letter E whichever we want to keep as constant this is known as absolute referencing if you want 138 00:14:05,390 --> 00:14:11,540 to know more about it you can google about absolute defensive so we'll look on in that 139 00:14:14,360 --> 00:14:23,370 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. 140 00:14:23,500 --> 00:14:26,270 Now this column is completely filled. 141 00:14:26,320 --> 00:14:38,280 You can see the third row now has 90 percent nearly 1 9 0 so 80000 value fills the third bucket nearly 142 00:14:38,280 --> 00:14:41,260 90 percent now. 143 00:14:41,300 --> 00:14:48,670 Third step which is tax calculation is simply the product of bucket fill it with the maximum tax of 144 00:14:48,660 --> 00:14:51,460 that bucket. 145 00:14:51,730 --> 00:14:58,080 We will also extend this by double clicking on the bottom right corner of this 146 00:15:02,290 --> 00:15:08,620 now to visualize this bucket full rate better that we can use conditional formatting conditional formatting 147 00:15:08,650 --> 00:15:14,500 is done by selecting the cells you want to format then going to conditional formatting in the home menu 148 00:15:15,340 --> 00:15:20,560 and selecting the type of drill that you want to get. 149 00:15:20,560 --> 00:15:28,030 So here I'm going to use these solid fill data but so does this hundred percent fill. 150 00:15:28,930 --> 00:15:30,670 So this is completely blue. 151 00:15:30,760 --> 00:15:32,030 This is 90 percent full. 152 00:15:32,080 --> 00:15:41,490 This is 90 percent blue and the other ones are 0 now to calculate total tax. 153 00:15:41,510 --> 00:15:52,800 I will add all these sales so we'll use some formula and we will add all these sales to a the total 154 00:15:52,800 --> 00:16:03,180 tax on the income if you want to calculate the effective tax rate for you you can do that by dividing 155 00:16:03,180 --> 00:16:10,480 total tax the debt income. 156 00:16:10,790 --> 00:16:15,070 If you want to change this value in two percentage you can just go and click on this percentage symbol. 157 00:16:15,200 --> 00:16:23,080 So effectively on an income of 80000 you are being charged a tax at a rate of 17 percent. 158 00:16:23,450 --> 00:16:30,160 So you may fall in deep tax bracket of 22 percent but effective tax rate for you is 17 percent. 159 00:16:31,090 --> 00:16:38,680 Now since we used sales differences everywhere by just changing the value of income we can get the total 160 00:16:38,680 --> 00:16:43,120 tax value and the effective tax rate in an instant. 161 00:16:43,120 --> 00:16:46,270 So we will change just to two hundred thousand dollars 162 00:16:50,090 --> 00:16:55,590 and you can see you'll have to pay a tax of forty five thousand two hundred sixteen point five dollars. 163 00:16:55,700 --> 00:17:01,390 And that is an effective tax rate of gonna be 3 percent if you like this video. 164 00:17:01,470 --> 00:17:04,990 We have a complete course on Microsoft Excel. 165 00:17:05,640 --> 00:17:11,400 It's a short quick course it will refresh audio concepts and you will definitely learn some new tricks 166 00:17:11,400 --> 00:17:19,140 also you can find the link with a pretty applied discount coupon in the description below if you like 167 00:17:19,140 --> 00:17:20,010 this video. 168 00:17:20,040 --> 00:17:22,590 Don't forget to like share and subscribe. 169 00:17:23,160 --> 00:17:24,570 Thank you for watching this video.