 Income tax 2021-2022, Excel worksheet. Create a tax formula worksheet using Excel part number four. Get ready to get refunds to the max. Diving in to income tax 2021-2022. Here we are in our Excel worksheet. Continue it on with the work to our income tax formula. We started in prior presentation starting out with a blank sheet. If you missed those, you might want to go to those prior presentations. We're now taking a look at each of the line items in our formula 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. And this is the way that I think it's gonna 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. 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 a double check on the data input is useful. 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 different tabs that we'll feed into. For example, the cash line item, the adjustments to income, the above the line adjustments, then the adjusted gross income is of course a subtotal here. Then we have the greater of the itemized or the standard deductions, 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, and so on. And then we've got the qualified business income, which we haven't done anything to yet. We might take a little more detail on that later. 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 because we can't figure the tax in Excel without a pretty complex set of tables to do so because it's a progressive income tax system. So we'll double check this number, we'll implement the tax. So for example, if I went to my tax return, I entered the same information for my Adam Smith single taxpayer living in Beverly Hills 90210. We've got 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 I'm gonna say adjustments. Let's remove that for now and make it as easy as possible. And so that's gonna give us the 12,550 in the standard deduction here for the 87,450 on the tax return for the taxable income. Then I'm gonna get the tax calculation actually from the tax software page number two in this case. And that's going to be the 15,015. 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 and you've got all these kind of random types of credits that can be taken into place. The payments are too bad, the payments are pretty straightforward, 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 at that in more detail now. We'll give an idea of what's gonna happen and then we'll add to it as we dive into some more detail about those particular credits. So for example, if I go on over here, now we're gonna say, okay, 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-2 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 gonna be picking something up from the schedule two and three. So if I look at schedule two and three, then we've got 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. 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 add that. I'm gonna add another one over here. I'm gonna grab it. I'm gonna pull it to the right. Pull it to the right. And I'm gonna double clip on the name. I'm gonna call it 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. Let's also like the whole thing. I'm gonna go up there and make additional. I don't know. I'm going back and forth. Sorry about that. Additional tax. I'm not sure I can spell additional correctly. There's two D's in it. See, there's two D's. Two D's in additional. Add is the root of the word. And add has two D's. Okay. So I'm gonna select the whole thing with the carrot up top. Right click and let's format this thing. Let's format it. Shall we? Currency, bracketed and red numbers. Hitting the dropdown. We got none on the symbol. Taking down the decimals. And okay, let's make the whole thing emboldened. Going to the font group and boldifying it. Holding down control, scrolling up a bit. So that we can see more detail on it. I'm not gonna get into the detail of these credits right now. We might talk about them more later. But the main two items you would have here are the alternative minimum credit. So, and that could get confusing. So if that's gonna be applicable, then it could be a confusing component. But for now, alternative minimum credit. And so we might have a calculation that would be involved with it. So I'll leave a little space with the calculation. It could be a fairly in depth calculation. 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. And then maybe I should have another heading. I've been putting another heading. I'm gonna 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 taxes. And I'll make that black and white, which is our header thing, home tab font group making it black and white. And this header, we can make that black and white too, black and white. And so let's just pretend we had something here. We'll calculate it, what we might go into calculating it more in depth in the future. But let's just, for now, just say that's gonna be the total. Total, we'll say a total, total alternative minimum tax. And this is gonna be summed up on the outside. Sum it up. So there we have it. 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 gonna calculate that. We might wanna recalculate it, or we might kinda just wanna note what it is and basically pull it out. So we're gonna go ahead and do that. And we're gonna go ahead and do that. And 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 wanna do some calculations on it. And we're gonna 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 gonna be the total self-employment tax. Employment tax. And I'll sum this up. Equals the sum. And sum that up. So when these things take effect on the tax return, for example, when I'm filling out the taxes and I say, oh, schedule two's been impacted and that's having an impact on page two of the 1040, I can then go into these sub-schedules and see if I can recreate what is happening in Excel to get an idea to myself what is the tax return actually doing so I could talk to someone about what it's doing. And so this is gonna be, I'm gonna call this the addition, let's call it total, total additional taxes. And let's sum it up on the outer column, sum it up, sum it up in the outer column. 2,600, we're gonna put that to the first tab now. First tab will bring it on over to the other taxes. So instead of hard coding zero here, I'm gonna say that that's gonna be equal to the additional taxes of the 2006. And then I can go into that sub-ledger for more detail and that'll mirror kind of what we do, what we're gonna 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 schedule two line three and you also have then the self-employment tax which is down here, other taxes including self-employment schedule two line 21. So they're actually calculated on two different components and you have different subtotals that are happening. So I could have made, for example, my tax formula a little bit longer to kind of add these subtotals where it says add lines 16 and 17 or I'm gonna do what I'm gonna 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 just in general than having more line items on the formula. So I'm gonna go back on over and say, okay, so we've got that and then we've got add lines 16 and 17. 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's here on this form 8812 and you can see down here you have this refundable item here. So if I was to add a dependent, so I added a dependent here on page one, then you could see how that will flow in and I can flow through and say, okay, now there's a child, 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 a non-refundable component to it still. So the point is that you got these two items for that schedule 8812. So how do you basically fill that out on our worksheet? So if I was to reconstruct that, I'd say, okay, well now I'm gonna make another tab over here. I'm gonna pull that all the way to the right and say this is gonna be form, let's call it form 8812, double click. So we're gonna say form 8812. And so we're gonna say child tax credit and dependent credit, something like that. Child tax credit and child tax credit and for other dependents. 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 gonna right click on it, format this thing. And then I'm gonna say currency, let's put brackets in red for the negatives, no dollar signs, get rid of the decimals and okay, make the whole thing emboldened. The page hasn't been emboldened. It walks with its head up proudly, it's bold, it's been emboldened. And then we're gonna say that this is going to be the form, this was form, what was I looking at? Schedule 8812, it should be schedule 8812 or form 8812. And let's go to that form, 8812, which is called credit for qualifying children and other dependents. So I'm gonna skate 1812, so we'll call it schedule 8812. And then it'll be called that. So I spared you the typing of it. So I'm gonna make that a little bit larger here. 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 credit. Let's say this is the refund. Well, we're not gonna have, well, let's just do this for now. We're gonna say child tax credit. And I don't think there's a refundable portion at this point in time, so we'll have that. And then the other dependent credit. So this is gonna be other dependent credit. And so let's give it a little bit more space. There could be a lot of children. There might be a lot of children. Some families have a lot of children. So I'm gonna say we'll pick up at least that many. We're gonna say brackets and then the bucket is gonna be blue child tax credit. So then we would get, you know, I'm just gonna put a random number of a thousand and this will be then the total child tax credit. And it's all refundable at this point. So I'm gonna 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. And I'm gonna say this is gonna be the total other dependent credit. And we'll say that this is all non-refundable equals the sum of these items. So then if I go back to my first tab then, and I might wanna make this black and white, that's what we've been doing, hasn't it? For the subheads down here. 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 tax credit. So we're gonna go back to the subheads. The tax credits, these are the non-refundable ones, because the refundable ones, they added down here along with the payments. So I'm gonna go up top and say this equals then the non-refundable ones. I'm gonna go all the way to the right. And I'm just gonna 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, notice that I've combined these two together, taxes and payments into one item. So I'm gonna have multiple things feeding into this. One is gonna be the refundable credits because those are basically as good as if we actually made the payment because they're gonna allow us to get a larger refund even if the taxes do were a zero. Instead of a refund, it'll be a benefit type of thing. So they're kind of equivalent to payments in that way. So I'll pick that one up here. So there we've got that. So 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 down here for the non-refundable and refundable. So we got the non-refundable child tax credit and then we've got the subtotal 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 schedule two line 21. We already looked at that schedule too because that's gonna be things like the self-employment taxes. Then we've got the total tax at the 15, 15 still in our case. So if we bring it back on over here we've got the total tax different because we've added a couple of things in. And then we've got the total tax and then you got the federal tax withheld from W2 1099 and so on and so forth. And then you've got the estimated tax payments that are gonna be down here as well. So in other words, there's two normal ways that we make payments. So 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 gonna say, okay, now I'm on this line now looking at the payment side. So I'm gonna put both those things on one worksheet. I'm gonna make another worksheet and then I'm gonna grab it and drag it. Get over here worksheet. I told you to go to the right side and you went in the middle. Get it, so you gotta drag it over there. Make me drag you all the way over here like that. So then I'm gonna double click on it. These are gonna be the payments. So the payments, I'll once again highlight the whole thing. Right click and format this thing. Format numbers, brackets and red. And then we're gonna get rid of the decimals and enter and let's embolden the whole thing again. Holding control, scrolling up. So we're gonna say these are gonna be payments, tax, payments. 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 gonna be the W2, W2 payments. So these are payments that we would get from the W2 forms. We could have multiple W2s. So let's say we got multiple W2s. When you have the W2, you're gonna enter the income side on the income line over here and then jump on over to the payment line at the bottom of the tax return. We'll see that later. We're gonna go up top and say this is gonna be blue and bracketed. And so we might have different W2 incomes. This is on the payment side of things, the withholdings. And also just note that you could have social security and Medicare and so on. But really, hopefully those things are calculated properly 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 gonna get into that now. Just, we're not getting into it, man. I told you, we weren't getting involved. So in any ways, you could have two of these or more and then we're gonna go down. This is gonna be the total W2 payments, summing it up in the outside, summing it up, sum it up. And then we're gonna go down. How come that's not the right proper formatting? I'm gonna select the whole thing. Something's wrong with my formatting. And I want it currency, currency, brackets, no dollar sign. I need the comma, much better. Mucho mejor. Then we've got the estimated tax payments. Usually there's four quarters of them. So you need maybe four cells, but let's give it one more in case something funny happens. People are doing crazy stuff and have to make more payments or something. And we're gonna go down here and this is gonna be total estimated payments. Summing it up on the outside, sum it up, sum it up. And let's say we paid like 5,000 estimated payments. Then we'd have the total payments. Summing up the outer column equals the sum of the outside, sum of the outside. There it is. And then we're gonna pull that over to line to our formula on the front page, which is gonna be in here. So something's already in there because the refundable credits are in there too, which is kind of funny. Those refundable credits are kind of funny. Credits in general are funny. Refundable more so than most. So then we're gonna go to the payments and say we're gonna add that in there too. And so there we have it. So there we have it. 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 that would make it green at the bottom. So you can see our formula now, we'll still keep on building these other pages to support any of the calculations that are pulling into the front page of the 1040. You can see how that could 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 gonna imagine in your mind. Like box one is gonna be adding line 6A, 6B, and so on in order to get right. You're not gonna imagine that way. That's how you see it in the tax form. You're gonna imagine a formula and then the detail expanding on that in like another schedule that feeds into the primary formula, 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, we'll do the data input, we'll let the software compute the taxes, the actual forms, and then we can go back and recompute 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.