Income Tax 2021 2022 Excel worksheet, create a tax formula worksheet using Excel port number four, get ready to get refunds to max diving in to income tax 2021 2022. Here we are in our Excel worksheet continuing on with the work to our income tax formula we started in prior presentation starting out with a blank sheet.
00:25
If you missed those, you might want to go to those prior presentations without taking a look at each of the line items in our formula,
00:32
adding at least a precursor to some of the other tabs that will feed into the summary sheet, the summary sheet, this first sheet and essence being similar to the form 1040, as we have here in relation to other schedules that feed into the 1040, the 1040 being a little bit more expansive, but the general idea is the same.
00:54
And this is the way that I think it’s going to be easiest to visualize the actual forms and how they all fit together so that you can make projections into the future and answer questions people might have, like, what if this happens, what if that happens, and so on, and so forth.
01:08
And it’s also a good worksheet to kind of double check the numbers that are being input into the software, given the fact that we do not have a double entry accounting system here, we don’t have debits and credits, we don’t have a balance sheet tying ourselves out, having to double check on a data input is useful.
01:23
Okay, so now we’re going down to We’re on the second half of the formula. Now we double checked all these top line items, looking at at different tabs that will feed into, for example, that cache line item, the adjustments to income,
01:36
the above the line adjustments, the adjusted gross income is, of course, the subtotal here, then we have the greater of the itemized or the standard deductions,
01:45
the itemized deductions being pulled from a schedule to the right, the standard deduction being pulled from a schedule below, we made the standard deduction blue, because although we’re not hard coding this number, here, we are doing some data input, and have to manually choose which of these items down below will be applicable, single married,
02:04
and so on. And then we’ve got the the qualified business income, which we haven’t done anything to yet, we might, we might take a little little more detail on that later.
02:14
And then we have the taxable income, calculated with just a formula, the average tax is calculated using a formula so it’s not going to be data input, we’re going to be picking up the actual tax then from the tax from the actual tax return,
02:29
because we can’t figure the tax in Excel without a pretty complex set of tables, to do so because it’s the progressive income tax system. So we’ll double check this number, or implement the tax.
02:39
So for example, if I went to my tax return, I entered the same information for my Adam Smith, single tax payer living in Beverly Hills 90210, with the 100,000. And then I’ve got this adjustment down here, let’s take that adjustment out. Take that adjustment out, it’s on schedule one. So unsafe adjustments, let’s remove that for now and make it as easy as possible.
03:05
And so that’s going to give us the 12,005 50. And the standard deduction here for the 87 for 50. On the tax return for the taxable income, then I’m going to get the tax calculation actually from the tax software page number two in this case, and that’s going to be the 15,015.
03:26
So there’s the 15,015. Now we’re down to the other kind of things, the credits, and the payments that are down below, the credits and payments can get a little bit complex, because they have phase outs,
03:37
and you’ve got all these kinds of random types of credits that can be taken into place, the payments are too bad, the payments are pretty straightforward.
03:44
But the credits are a little bit confusing, you might try to put like a bunch of credits, onto one worksheet, but they all have different kind of phase out types of items. And they’re on different line items sometimes on the tax return. So we’ll take a look, I’ll look at that in more detail now will give us an idea of what’s going to happen. And then we’ll add to it as we dive into some more detail about those particular credits.
04:07
So for example, if I go on over here, now we’re going to say okay, this is the this is where they actually calculate the tax, you would think, then after that, you would just have how much you paid how much you withheld from your W two,
04:17
for example. And that would be it to give you the refund of the amount that you owe, but no, we got all these other kind of credits that are involved. So for example, if you go to the line 17, it says you’re going to be picking something up from the schedule two and three.
04:31
So if I look at schedule two and three, then we’ve got the the added taxes. So this is the other taxes that would be involved, including an alternative minimum tax, and then we’ve got the self employment tax.
04:45
So if I go back on over here, we’ve got the taxes and credits and then we’ve got the other taxes. So let’s let’s add that. I’m going to add another one over here. I’m going to grab it, I’m going to pull it to the right,
04:57
pull it to the right, and I’m going to double click on that Name and I’m going to call it S schedule three, let’s call it other taxes, other taxes on the schedule three, or additional taxes, they call it, let’s call it additional, let’s call it other taxes. But I’ll select the whole thing, I’m going to go up there and make additional, I don’t know, I’m kind of going back and forth.
05:19
Sorry about that additional dish, no tax, I’m not I’m not sure I can spell additional correctly, there’s two Ds in it, see, there’s two Ds, two Ds in additional ad is the root of the word an ad has to do.
05:35
Okay, so I’m going to select the whole thing with the with the caret, up top right click, and let’s format this thing. Let’s format it, shall we currency bracketed and red numbers hit in the drop down, we got none on the symbol ticking down the decimals.
05:53
And okay, let’s make the whole thing in bold and go into the Font group and bold, define it, holding down control scrolling up a bit, so that we can see more detail on it, I’m not going to get into the detail of of these credits right now, we might talk about them more later.
06:08
But the main two items you would have here of the alternative minimum credits. So and that could get confusing. So if that’s going to be applicable, then it could be a confusing component. But for now, all term alternative minimum credit.
06:26
And so we might have a calculation that would be involved with it. So I’ll leave a little space. With a calculation, it could be a fairly in depth calculation.
06:35
So let’s leave some more space, I’ll make that the brackets and put some color on it with the blue, the blue is right there. If you don’t have that blue, it’s in the more colors here, standard and then blue.
06:49
And then maybe I should have another heading, I’ve been putting another heading, I’m going to, I’m going to add some rows above it by selecting row one and row two, right click on it and insert. And I’ll call this schedule three additional tax taxes.
07:09
And I’ll make that black and white, which is our header thing, Home Tab Font group, making it black and white.
07:16
And this header, we can make that black and white to black and white. And so let’s just pretend we had something here, we’ll calculate it, we might go into calculating it more in depth in the future.
07:28
But let’s just for now, just say that’s going to be the total total, we’ll say a total total Alternative Minimum Tax I’ll, I’ll turn that to minimum tax in this is going to be summed up on the outside, sum it up. So there we have it.
07:51
And then the other big one is the self employment tax, so self employment tax. And we could get into some detail in terms of how we’re going to calculate that we might want to recalculate it.
08:04
Or we might kind of just want to note what it is and basically pull that from the tax return. We’ll talk more about that later. But let’s leave a bit of space here. In case we want to do some calculations on it.
08:15
And we’re going to put some brackets around that, let’s just pretend it came out to one six, for example. And then I’ll go down here and say this is going to be the total self employment tax or meant tax. And I’ll sum this up equals the sum and sum that up.
08:35
So when the when these things take effect on the tax return, for example, when I’m filling out the taxes, and I say oh, schedule two has been impacted.
08:44
And that’s having an impact on page two of the 1040. I can then go into the sub schedules and see if I can recreate what is happening in Excel to get an idea to myself, what you know, what is the tax return actually doing?
08:58
So I could talk to someone about what it’s doing. And so this is going to be I’m going to call this the dish and let’s call it total, total additional taxes. And let’s sum it up on the outer columns, sum it up, sum it up in our call 2600, we’re going to put that to the first tab now.
09:24
First tab will bring it on over to the other taxes. So instead of hard coding zero here, I’m going to say that that’s going to be equal to the additional taxes of the 2006.
09:36
And then I can go into that sub ledger for more detail. And that’ll mirror kind of what we do what we’re going to do here and we’ll get it like I said we’ll get into more detail in that when we start to look at like self employment tax that usually comes into play with a Schedule C for example, but just to get an idea of it. So this is going to be the also note this is where it gets a little bit confusing, because this item is From scheduled to line three, and you also have then the the self employment tax,
10:06
which is down here other taxes including self employment scheduled to line 21. So they’re actually calculated on two different components and have different subtotals that are happening.
10:16
So I could have made, for example, my my tax formula a little bit longer to kind of to kind of add the subtotals, where it says add line 16, and 17. Or I’m going to do what I’m going to do now, which is basically kind of combine those together, so that the whole schedule flows into one line item on our formula, which I think would be easier to visualize,
10:39
just in general than having more line items on the on the formula. So I’m going to go back on over and say Okay, so we’ve got that. And then we’ve got add lines 16 and 17.
10:50
These are the non refundable child tax credit or credit for other dependents from schedule 8812. Now this one’s a little bit confusing, because you have a refundable portion of it and a non refundable portion. So you see it here on this form 8812. And you can see down here, you have this this refundable item here.
11:10
So if I was to add a dependent, so I added a dependent here on page one, then you could see how that that will flow in, I can flow through and say okay, now there’s a child.
11:20
Now, there’s a refundable Child Tax Credit. Now, it’s a little confusing this year, because in 2021, I think they made the Child Tax Credit totally refundable. But the dependent, other dependent might have an unrefined on a non refundable component to it still.
11:35
So the point is that you got these two items for that schedule 8812. So how do you basically fill that out? On on your worksheet?
11:43
So if I was to reconstruct that, I’d say okay, well, now I’m going to make another another tab over here, I’m going to pull that all the way to the right, and say this is going to be form, let’s call it form 812. Double click, so we’re going to say form a one, two.
12:02
And so we’re going to say Child Tax, credit and dependent credit, something like that child tax credit, and child tax credit, and for other dependents.
12:14
So we’ll keep it at that I won’t get into details on the calculation, but let’s just format this and see how it would pull over. So I can then select the whole worksheet, I’m going to right click on it format this thing.
12:24
And then I’m going to say currency, let’s put brackets in red for the for the negatives, no dollar signs, get rid of the decimals, and okay, make the whole thing in bold and the page has been in bold and it walks with its head up proudly, it’s bold, it’s been in bolded. And then we’re going to say that this is going to be the form form.
12:50
This was form, what was I looking at schedule 812 should be schedule 8812, or form a one, two. And let’s go to that form 812, which is called credit for qualifying children and other dependents Simscape 1812. So we’ll call it schedule a one, a one, two, and then it’ll be cold.
13:21
That so I spared you the typing of it. So I’m going to make that a little bit larger here, we’ll make it we’ll make it black and white on the header. So black and white for the header. And then we might have the child tax credits, let’s say this is the refund? Well, we’re not going to have well, let’s just do this for now we’re gonna say child tax credit.
13:50
And I don’t think there’s a refundable portion at this point in time. So we’ll have that. And then the other other dependent credit. So this is going to be other dependent credit. And so let’s, let’s give it a little bit more space, there could be a lot of children,
14:11
there might be a lot of children, some families have a lot of children. So I’m going to say we’ll pick up at least that many, we’re going to say brackets, and then the bucket is going to be blue child tax credit.
14:24
So then we would get you know, I’m just going to put a random number of 1000. And this will be then the total child tax credit. And it’s all refundable at this point.
14:37
So I’m going to sum that up equals the sum. And so we have that and then the other dependent. Let’s add a couple of these. I don’t think this is refundable at all we’ll go into it in more detail, but we’ll just say that the other dependent credit and let’s just put like 500 for practice,
14:56
and I’m going to say this is going to be the the total Other dependent total, other dependent credit. And we’ll say we’ll say that this is all non refundable equals the sum of these items.
15:14
So then if I go back to my first tab then and I might want to make this black and white, that’s what we’ve been doing, hasn’t it for the subheads down here.
15:24
So if we go back then to the first page back to the first page, we’re gonna say that we’ve got the other taxes. And then we’ve got the the tax credits, these are the non refundable ones,
15:35
because the refundable ones they added down here along with the payments. So I’m going to go up top and say this equals then the non refundable ones, I’m going to go all the way to the right.
15:45
And I’m just going to include this one the other dependent credit at this point. And we’ll get more into it later just to get an idea of it. And then this one down here and notice that I’ve combined these two together, taxes and payments into one item.
16:01
So I’m gonna have multiple things feeding into this one is going to be the the refundable credits, because those are basically as good as as if we actually made the payment, because they’re going to allow us to get a larger refund, even if the tax is due, we’re at zero instead of a refund, it’ll be a benefit type of thing. So they’re kind of equivalent to payments in that way.
16:21
So I’ll pick that one up here. So there we’ve got that so that that’s that little calculation between breaking out the credits can be a little bit confusing. And it can be a little confusing to tie into the subtotals that we see on page two as we get as we get down here for the for the non refundable and refundable. So we got the non refundable child tax credit.
16:44
And then we’ve got the subtotal add, add the lines together. So we got a subtotal subtract line 21, from line 18, if zero or less, enter zero, then we’ve got the other taxes, which is scheduled to line 21, we already looked at that schedule, too, because that’s going to be things like the self employment taxes, then we’ve got the total tax at the 1515. Still in our case.
17:10
So if we bring it back on over here, we’ve got the total tax different because we’ve added a couple things in and then we’ve got the total tax, and then you got the federal tax withheld from w 210 99,
17:24
and so on and so forth. And then you’ve got the estimated tax payments that are going to be down here as well. So in other words, there’s two normal ways that we make payments.
17:34
So we say that we make payments to the government either through withholdings, or we make payments through actual quarterly payments, which you might do if you had a Schedule C for example. So I’m going to say okay, now I’m going to I’m on this line.
17:47
Now looking at the payment side. So I’m going to put both those things on one worksheet, I’m going to make another worksheet and I’m going to grab it and drag it get up, go over here worksheet,
17:57
I want you I told you to go to the right side, and you went in the middle get get so you got to drag it over there, make me drag you all the way over here like that.
18:06
So then I’m going to double click on it, these are going to be the payments. So the payments, I’ll once again highlight the whole thing, right click and format this thing for Matt, numbers, brackets and red.
18:20
And then we’re going to get rid of the decimals and Enter. And let’s embolden the whole thing again, holding control scrolling up. So we’re going to say these are going to be payments, tax payments.
18:34
And let’s make that bold, or let’s make that black and white, because it’s our header. So then we’ve got we’ll say these are going to be the W two w two payments.
18:45
So these are payments that we would get from the W two forms, we could have multiple w two. So let’s say we got multiple W twos when you have the WTO, you’re going to enter the income side of the income line over here, and then jump on over to the payment line at the bottom of the tax return.
19:01
We’ll see that later. We’re going to go up top and say this is going to be blue and bracketed. And so we might have different w two incomes.
19:10
This is on the payment side of things, the withholdings and also just know that you could have Social Security and Medicare and so on. But really, hopefully those things are calculated properly.
19:20
And we’re really only concerned with our recalculation of the actual federal taxes, although you can overpay say social security, for example, possibly if you have multiple jobs, and so on. We’re not going to get into that now. Just we’re not getting into it, man.
19:35
I told you we weren’t getting involved. So anyways, you could have two of these or more. And then we’re going to go down this is going to be the total w two payments, summing it up in the outside, summing it up, sum it up,
19:54
and then we’re going to go down, how come that’s not the right proper formatting. I’m going to select the whole thing simple wrong with my formatting.
20:02
And I want it currency currency, brackets, no dollar sign I need to comma. Much better mucho Matt hoarder, then we’ve got the estimated tax payments. Usually there’s four quarters of them.
20:18
So you need maybe for sales. But let’s give it one more in case something funny happens, people are doing crazy stuff and kaftan make more payments or something.
20:27
And we’re going to go down here, and this is going to be total estimated payments, summing it up on the outsides, sum it up some ah, and let’s say we paid like 5000 estimated payments, then we’d have the total payments, total payments, summing up the outer column equals the sum of the outside some of the outside.
20:57
There it is. And then we’re going to pull that over to line to the our formula on the front page, which is going to be in here. So something’s already in there, because the refundable credits are in there too, which is kind of funny.
21:08
Those refundable credits are kind of funny credits in general are funny, refundable moreso than most. So then we’re going to go to the payments and say we’re going to add that in there, too. And so there we have it. So there we go, there we have it.
21:21
And again, if our payments were large enough, like let’s say our payments, here, were 20,000, then we’d get some of the money back, then we’d get a refund. So then that would make it green at the bottom. So you can see our formula.
21:37
Now, we will still keep on building these other these other pages to support any of the calculations that are pulling into the front page of the 1040. You can see how that can be useful. Hopefully, when you’re trying to imagine this in your mind, this is the structure you would think about in your mind, you’re not going to imagine in your mind,
21:55
like box one is going to be adding line six, a six, B, and so on in order to get right you’re not going to imagine that way.
22:04
That’s how you see it in the tax form. You can imagine a formula and then the detail expanding on that in like another schedule that feeds into the to the primary formula,
22:15
which is similar to the 1040 and the other schedules that will be involved. And then when we actually do the tax return with tax software, will do the data input we’ll let the software compute the taxes, the actual forms,
22:29
and then we can go back and re compute some components of it in our Excel worksheet more transparently, more intuitively, and see if we can better understand helping us to double check, fix any data input errors, and basically be able to understand hopefully what is happening so we can explain it to someone someone such as the clients