 Income tax 2021-2022 Excel worksheet create a tax formula worksheet using Excel part number two. Get ready to get refunds to the max diving into income tax 2021-2022. Here we are in Excel where we started to put together our income tax formula in a prior presentation. If you missed the prior presentation, you can take a look at there where we put this together from a blank sheet from scratch from nothing. Now we're going to continue to adjust it, put some formatting in it and also be adding some other sheets, which will act in a similar way as other schedules and forms will to the main form, that being the form 1040. So the first thing I'm going to do, I'm going to add another column to the left and I'm going to try to indicate the activity that will be happening, including adding or subtracting, for example, each of these line items to make it a little bit more clear just at a glance. So I'm going to put my cursor on column A, right click on the whole column area or the selected area and insert, which will push a column to the left on the left hand side here. And then I'm just going to basically say what the activity is going to be. So this is going to be a subtraction. So I'm going to put a minus sign and enter. Be careful as you enter like a minus or equals because you could hit minus. And if you start moving, it'll try to put a formula in place, which is not what we want. We just want, for example, the equal sign here to indicate that that's going to be an equal component in our formula. And then we're going to be subtracting. So I'm going to put a minus sign, the greater of the itemized deductions or the standard deduction. And then we're going to get down to the qualified business income, which we're going to be subtracting as well. And that will equal the taxable income. So this is going to be an equal area. And then we're going to have the, hold on a sec, equal and then enter. And then we've got the average tax rate. So this would be kind of like a multiplication, which I'll put an X for here. Although it's not exactly that because we're actually backing into this number. This being a number that we're not inputting ourselves to calculate the tax, but rather one that we're going to back into because we don't have the actual rate because it's not a flat tax, but a progressive tax. And that will then give us, then I'm going to say this would be equal to even though this is a number that we're going to be getting from the actual tax software, because we can't really calculate it without a fairly complex calculation within Excel. And then we've got the tax credits. So I'm going to subtract the tax credits and then other taxes. We would add the other taxes because, and it gets a little confusing down here as well. Note that up top, you're talking about an income statement. So you've got income as the positive numbers minus all the deductions to get to, in essence, the taxable income item. And then once we calculate the tax, this is kind of like the liability, kind of like the bad thing. And then we would be taking that and subtracting out the credits, which are kind of goods, but we're taking it out of the tax that we would owe. We add other taxes, which would be something like possibly self-employment tax, alternative minimum tax, possibly. And then we would get then to the taxable, the total tax equals the total tax then. And then we've got the tax payments and refunds. So these are both going to be subtracted. The tax payments and the refunds will then be subtracted to give us the tax due or refund tax due or refund at the bottom line. So now let's go ahead and center these. I'm going to select this whole column A, and I'm going to go to the hometown alignment and center those, possibly making them a little larger too, so we can emphasize those actions that are being taken place there. So we can do that. And then we might want to try to add, say, colors involved to indicate whether basically it's a positive or negative. So may or may not be beneficial to you. I think the colors sometimes can add some benefit. So I might say like the income line, for example, I might make those green by going to the font group up top. And I want to enter a green here. So maybe I'll use, not on the bucket on the lettering, I'll make that like a dark green, for example. And then on the adjustments to income, so the adjustments to income, let's make this a red. So this is going to be a subtraction or red. And I won't put red always for just a subtraction, possibly. So we'll take a look at it later. It's a little bit weird when we use the colors like this, because on top we're looking at an income statement. So I've got the income up top, in essence, an income statement, modified income statement. And then I've got the adjusted gross income. And then we should still have basically an income line item. So the adjusted gross income, I would still typically say is going to be a green item here, right? We're going to say that's going to be green typically. That's kind of our top line number. Then we would be subtracting deductions, which are kind of good for us. But I'm going to put them as red because they're going to be basically decreases here. So I'm going to select these items and say this is going to be red, red for the deductions. And then we also have this one's going to be red for the deduction as well. And that'll give us our taxable income, which if we have any leftover will still be a green one typically. So I'm going to say item up top will make that then green. We could do a conditional formatting here and say something like it's going to be green if it's positive or red if it's negative. And then we've got the average tax rate, which is going to be actually something that we're going to back into. And then notice down here, we've got the tax. Now the tax that has actually been calculated based on the taxable income, I'm going to think of on the bottom half of this as bad, right? The tax is something that we owe. So I'm going to think about the tax as red. This is where it gets kind of flipped and a little confusing font group. I'm going to say that's going to be red down here. And then the credits are good because that's lowering the amount of liability. So we're going to reduce the amount of the liability by the credits and the payments that we made. So this is going to be good. I'm going to make that green. And then the other taxes that we owe, which would be something like self-employment tax or possibly alternative minimum tax is going to be bad. So I'm going to make that red. And then we're going to have the total tax, which is still bad. I'm going to make that red at the subtotal. And then we've got the tax liability and refund tax liability and refundable credits. Those are good. So I'm going to make those green. And then the bottom line here is going to be if there's tax due, it'll actually be a positive number. And if you get a refund, it's going to be a negative number, which sounds a little bit flipped. You could kind of adjust the signs if you wanted to, meaning you could calculate this as a negative number and have the credits be positive eating down the negative number. That's another way that you can kind of see this. But you could use a conditional formatting down here to kind of remind you that we're kind of looking at this as a refund, which would be a negative number would be would be good. So you might use something like conditional formatting up top home tab. We'll get a little fancy here styles and go to conditional formatting. And I'm going to hit the dropdown and say, we're going to say if the thing is greater than zero, if it's greater than zero, I'm going to say zero. But then we're going to use it. We're going to use that formatting. We want it to be light red with the fill red red. So it looks like that. So that's a bad thing. I'm going to say and then I'm going to say, okay, and then I'll use another conditional formatting conditional formatting dropdown. And we'll say that if it's if it's less than less than zero, then I want to make it green dropdown. We're going to make it green if it's less than zero. And so I'll say, okay. So then, for example, if I had payments of 20,000, then it would turn green down there and that would be good even though it's a negative number, the way the format of the formula is being broken out here. So that's going to be the general idea there. We might also want to put brackets around this. So I'll select the whole thing and say, let's put some brackets around it possibly go to the home tab font group, hit the dropdown. We're going to put some brackets in there. Some all brackets and then I'll make this one a little bit smaller. You could double click on it and that'll make it like kind of long enough just to have the actual function involved. So there we have that and then we could have some underlines. So I've got an underline here, the greater ofs. We've got an underline here. We might want an underline here, font group underline, even though this one's a little backwards, a little reversed. Now this one right here, this number, I'm going to be pulling that in from the tax software. So you'll remember that I can judge, I can verify this number up top by doing the data input. But this number down here, I'm going to pull from the tax return and back into the average tax. So what I'm going to do on this number, I'm going to indicate that this is a data input field for me and I do that usually with this blue color. So I right click on it, hit the bucket up top and I go to more colors if you don't have it here yet, standard and I use that blue. Why? Because the Excel is fun guy use that and so I copy that blue. So there's that blue for my data input field. And then we've got the credits are going to be subtracted other and then this is a subtotal. So I might want a underline here, font group and underline. So there we have that that'll give us the total tax and then we're going to say minus the tax payments and credits to get to the bottom line. We might want an underline there. So I'll go to the font group and underline it. We might want to double underline down here. So I'll go to the font group and put a double underline here. Now the reason I'm not going to make all the top part blue. You might say why don't I make that blue? I did the data input directly in here. I directly put in 100,000. Most of this other stuff we're actually not going to be entering directly into this page. We're going to be pulling it from sub sub ledgers. So I'm going to double click on the name down here and call this double click on this. We can call this the tax formula or in essence form 1040 right like we say we can call this the tax formula. Let's say F 1040 for form 1040. I'm not trying to be mad at the 1040. Not trying to say F 1040. I'm just saying it's the form F standing F standing for the form. So now I can pull this one to the left. I'm going to pull this to the left. I could do that by I usually say cut it like right click and cut and put that over here and I'd like to center it across the top. So I'm not going to do that. I'm going to select these and you might say I can merge it alignment and merge but I don't really like that because I can't because then it makes this one big sale that messes me up sometimes. So I like to right click on it the selected area and format it and then go to the alignment and then in the horizontal will hit the horizontal and center it across like so like so and then OK. So there it is and then let's make the whole thing black and white home tab and let's make the whole thing bucket black and letters white letters white bucket black. So there we have it now it's centered across up top. So let's add at least one more of these of these line items to show you how this these these will be broken out. So you could make it a like a simplified thing and say everything that's income or most of it I'll put it in another tab. So I'm going to make another tab and put it as income or you can try to you could try to like line it up a little bit more to the tax return. So for example on the tax return you're going to say well some of the income line items they have on the first page of the 1040 and some of the income line items they have on schedule one additional income and some of them might be on Schedule D or Schedule E or Schedule D at E F so on. So so we might try to we might try to mirror that so it'll tie in a look to give us a better idea of what's on the income statement. So over here I'm going to say let's let's make this income let's say this is going to be an income line detail income line detail and I'm going to say F 1040 not like swear it at the form 1040 I'm not mad at the 1040 I don't like paying taxes but I'm not like I'm not like angry I'm putting that there because this is the income stuff that I'm going to say is on the form on actually the front page of the 1040 which generally includes something like the wages which is also on another schedule or worksheet oftentimes so you can see it here but they basically put that on the first page of the 1040 so so that'll be our most common one I'll start out with so I got a new sheet I'm going to go ahead and format the whole sheet by putting my cursor on the arrow up top or you can select control a right click and format it and then we're going to go I like going to the first tab and say I want to make it currency and and this time I'll make the negative numbers bracketed that's my normal default if I'm not going to do any other colors to it because the red number can make it mess up the colors so I'm going to hit the drop down and say we want none on the dollar sign and I like to get rid of the decimals and OK I'll scroll up on it make it a little bit larger and I'll start to put categories here but the major category I'm going to start off with is just W to income right W to income colon and I'm going to I'm going to leave some space because I could have multiple W to incomes down below right I'm going to select the whole thing and make it bold to so I could have I could have and then I can like list the income so this is you know employee employer one you know whatever the employer is and I'll put the 100,000 there now I'm also going to have you'll note with with like W to income with holdings that I'll have to put somewhere else on the payment line item that will go on the bottom I'll put I'll make another worksheet on where that's going to go in the payment area so if I go back on over so what I'm going to do is I'm going to leave a little space and say OK how many people might I have I mean possibly you're not going to have too many people more than has more than let's bring it down to there that seems like enough room for W to income so let's make that I'm going to put the data input here so that's why I'm going to make these items the blue I'm going to go up top and say let's make this my blue and then brackets so there it is and then and then this is basically the header you could make the header like a like a header label going up top and maybe go into the to the formatting formatting let's not do that let's just do our normal kind of let's do our our black and white thing I'll go up top and say this is going to be font group let's make it black and white for each of our categories and then I'm going to have the total down here so this is going to be the total W to income income and I'm going to put that on the outside so that I can so they're going to put it outside here so I'm going to equal the sum trusty sum formula of all this stuff there it is and I'll make that I'll make that should that be blue now keep it as white because I'm not going to change it and then down here I'm going to have the I'll call this the total income form 1040 the total income of the income lines I'm trying to indicate that are actually on the face of the 1040 we'll get into more of them later possibly but I'm just going to sum them up for now right here and I'll add more as needed as we go through our practice problem but it's going to be the sum of everything up top which is just the W to income right now and I don't need to make it blue because that number is not going to change the only place I'm going to do the data input is going to be right here when I add new employers then if I go to the first tab I no longer want to just manually put in that 100,000 I want this to be coming from this tab so I'm going to say equals this tab not that 100 not that 100 the total of all the 100s I want to be pulling in to that number so that's what we'll start to do later as we add more as we add more kind of detail now obviously I'm this line item on incomes can be quite complex because it's going to be pulling in the stuff that's on the first page of the 1040 it will also be pulling in stuff from schedule one so we'll have to look into that make a schedule one for the income schedule one part one it'll also be pulling stuff in from possibly schedule C if there is one schedule D schedule E schedule F possibly if there is one right so there's a lot of stuff that will feed in to this to this first line item which we can basically mirror with other tabs which are supplemental tabs which will pull into the initial formula we'll continue on those next time