Income Tax 2021 2022 Excel worksheet, create a tax formula worksheet using Excel port number two, get ready to get refunds to the max dive in into income tax 2021 2022.
00:15
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.
00:27
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 be in the form 1040. So the first thing I’m going to do,
00:43
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,
00:57
or 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.
01:14
Be careful as you enter like a minus or equals because you can 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,
01:31
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 that taxable income. So this is going to be an equal area.
01:49
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,
01:59
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.
02:14
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 fairly complex calculation within Excel.
02:28
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 it gets a little confusing down here, as well.
02:40
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.
02:52
And then once we calculate the tax, this is kind of like the liability kind of like a 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,
03:04
we add other taxes, which would be something like possibly self employment tax alternative minimum tax, possibly, and then we would get them to the taxable, the total tax equals the total tax then, and then we’ve got the tax payments and refunds.
03:20
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.
03:33
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 Home tab alignment and center those possibly making them a little larger too. So we can emphasize those, those actions that are being taken place there. So we can do that.
03:50
And then we might want to try to add say colors involved to indicate whether basically, it’s a positive or negative, so might may or may not be beneficial to you, I think the color 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.
04:08
So maybe I’ll use a knot on the bucket on the 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.
04:24
And I won’t put red always for just a subtraction possibly. So we’ll take a look at it later. It gets a little bit weird when we use the colors like this, because on top we’re looking at an income statement.
04:34
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 income line items to the Adjusted Gross Income I would still typically say is going to be a green item here, right? We’re gonna say that’s going to be green. Typically, that’s kind of our top line number.
04:53
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 good Basically decreases here. So I’m going to select these items and say this is going to be read, read for the deductions.
05:07
And then we also have, this one’s going to be read 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, items up top,
05:20
we’ll 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 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,
05:41
I’m going to think of on the bottom half of this as as bad, right, the tax is something that we owe. So I’m going to think about the tax as read this is where it gets kind of flipped in a little confusing. Font group, I’m going to say that’s going to be read down here.
05:55
And then the credits are good, because that’s lowering, 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 gonna be good, I’m gonna make that green.
06:08
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,
06:21
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.
06:36
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,
06:46
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.
06:58
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,
07:11
we’ll get a little fancy here styles and go to Conditional Formatting. And I’m going to hit the drop down 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.
07:26
And then we’re going to use it, we’re going to use that formatting, we want it to be light red with a 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 40 formatting drop down.
07:42
And we’ll say that if it’s, if it’s less than, less than zero, then I want to make it green dropped down, we’re gonna 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.
08:02
And that would be good. Even though it’s a negative number, the way the format of the formula is, is being broken out here. So that’s going to be the general idea there,
08:10
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 drop down,
08:21
we’re going to put some brackets in there, some all brackets, and then 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.
08:33
So there we have that, and then we could have some underline. So I’ve got an underline here, the greater of we’ve got an underline here, we might want an underline here,
08:43
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.
08:52
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.
09:07
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?
09:17
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.
09:35
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.
09:49
So 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 directly put in 100,000. Most of this other stuff, we’re actually not going to be entering directly into this page,
10:07
we’re going to be pulling it from sub sub ledger. 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 said, we can call this the tax form you love, let’s say f 1044, form 1040.
10:26
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 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.
10:47
So 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.
10:56
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, we’ll hit the horizontal and center it across like so, like so. And then Okay, so there it is. And then let’s make the whole thing black and white Home tab.
11:15
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.
11:31
So you could make it a like a simplified thing and say everything, it’s income, or most of it, I’ll put it in another tab. So I’m gonna make another tab and put it as income.
11:40
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 can say, well, some of the income line items they have on the first page of the 1040.
11:51
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 scheduled, D, E, F, so on. So so we might try to we might try to mirror that. So it’ll tie in a little bit, give us a better idea of what’s on the income statement.
12:13
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 swearing at the form 1040.
12:25
I’m not mad at 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, I’m actually the front page of the 1040,
12:37
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 at the 1040.
12:47
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 to go into the first tab and say I want to make it currency.
13:06
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 then red number can make it mess up the colors.
13:17
So I’m going to hit the drop down and say we want known on the dollar sign and I like to get rid of the decimals. And okay, I’ll scroll up on it make it a little bit larger.
13:28
And I’ll start to put categories here. But the major category I’m going to start off with is just w two income right w two in comm. Colon. And I’m going to I’m going to leave some space because I could have multiple w two incomes down below, right, I’m going to select the whole thing and make it bold too.
13:48
So I could have I could have and then I can like list the income. So this is you know, employ employer one, you know, whatever the employer is, and I’ll put the 100,000 there.
14:03
Now I’m also going to have you’ll note with with like w two income withholdings that I’ll have to put somewhere else on the payment line item that will go on the bottom, I’ll make another worksheet on where that’s going to go in the payment area.
14:16
So if I go back over on over, so what I’m going to do is I’m going to leave a little space and say okay, how many people might I have, I mean, probably you’re not going to have too many people more than has more than let’s bring it down to there. That’s seems like enough room for w two income.
14:30
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.
14:40
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.
14:53
But 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.
15:05
So this is going to be the total W, two, 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,
15:19
trustees some 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,
15:35
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.
15:45
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 two income right now.
15:55
And I don’t need to make it blue because that numbers not going to change, the only place I’m going to do the data input is going to be right here.
16:02
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.
16:12
So I’m going to say equals this tab, not that 100 Not that 100 the total of all the one hundreds, 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.
16:26
Now, obviously, 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.
16:38
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,
16:51
right so there’s so there’s a lot of stuff that will feed in to this to this first line on 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.