 Income tax 2021-2022. Create a tax formula worksheet using Excel. Get ready to get refunds to the max. Dive in in to income tax 2021-2022. Here we are in our Excel worksheet where we will be building our tax formula from scratch from a blank sheet. I think this will give people a better understanding of the tax formula, how to visualize it, a better understanding of the 1040 and how the related schedules, including schedules and forms, tie into, in essence, the 1040. However, I know this is not an Excel course, so I'm gonna do this relatively quickly. And if you don't want to be building this and you just want to use, for example, an Excel template to check your work, we'll provide you with the Excel template as well. So I'm in Excel. First thing I'm gonna do, I'm gonna increase the size of the sheet a little bit down here. We're gonna increase the size a bit. Bringing it up, let's bring it, I'm gonna bring it up to 160. Then I'll typically format the whole sheet in the general format that I would like to see it in. And I do that by saying control A, or I'm gonna put my cursor on the little triangle up top, selecting the whole sheet. Then I'm gonna right click on the sheet and I'm gonna go into the format of the cells. We wanna format the cells. I usually go to currency. I put some brackets around it and make them red if they're negative. And then actually I'm just gonna do the brackets and not make them red, because I might use a red color from time to time in the formula. Then I'm gonna hit the dropdown and say, I don't want the dollar sign. I'm gonna put it to none. And then I'm gonna start out removing the decimals and only add the decimals if I want to. Part of the rationale is that taxes is somewhat of an imperfect science. I'm gonna take those off and surrounding to the dollar is oftentimes good enough. And it can make things look a little bit cleaner, not seeing the pennies on there, although you do have to deal with that rounding kind of thing, but there's always gonna be a rounding thing that will be involved. Also, if you look at tax software, generally they'll round it to the dollar, for example, if I jump over to the tax software. Now if I jump to the tax software, we could use the tax software to build this thing out, either looking at the Form 1040. The first thing we're gonna think about is in essence the 1040. However, we're gonna simplify it a little bit, trying to condense it down to just a formula. It might be easier to look at tax software in the tax summary. Oftentimes many tax alters have this, not just with cert, I'm looking at with cert here. But here's the tax summary, which basically breaks this down into a formula type of basis. And so you could use something like that to build your worksheet. At the end result of this, we're gonna build something that looks something like this, we're majorly focused on this first page, just the formula. And then after that, we'll start to add these other worksheets, which you can think of as similar to schedules and other forms that feed into the first page, in essence, the summary page, which you can think of as, in essence, the Form 1040. So that's gonna be our general idea. So let's go back on over to our worksheet over here. I'm gonna say, okay, we've got everything formatted. I'm gonna make cell A a little bit longer and I'm just gonna list out our formula. I'm gonna put my cursor in between A and B, so it looks like that, not like that, but like that. And left click and drag it to the right. Drag it to the right. That looks like enough space. And then on the top, I'm gonna put my heading, which is a tax formula 2021. I'm gonna keep it at that. I'm gonna format this one up top so I can see that it is a heading type of thing. And so I'm gonna go up top and I usually make this in the home tab font group bucket, make it black and make it white. That's what I'll usually do to just show that it is part of the heading. I'm not gonna center it or anything right now. We'll get into that later once we add the rest of the data. I'm also gonna select the whole sheet and make it bold so that hopefully you can see it a little bit more easily. I'll scroll in a little bit more. I'm at the 205, I'm quite squirreled in at this point. So the first line item is gonna be income. You might call it tax income, for example, this is gonna be the income line item similar to what we see here in Excel. Now I'm not gonna list out the different kinds of income underneath it, like W2 income, interest income, and so on and so forth because I'm gonna put that in another schedule. I'm gonna put all types of income somewhere else. And you might say, well, why would you do that? Because it's on the first page of the 1040. A lot of the income stuff is here and some of it's in another schedule over here that pulls in on schedule one. So why don't you list it all out like it's on the 1040? And the reason is because the 1040 is not, in my opinion, optimized optimally. The 1040 was built over time and they optimized it a little bit more a couple of years ago adding that schedule one and so on, which I think was probably makes sense to me. But in any case, I'm gonna put all the other income line items on another thing, including the W2 income. And so I'm gonna start with just the income and let's just put in 100,000 just to practice, just to imagine a number, or I can just put a zero. Let's put a zero here, let's put 100,000. And then I'll add the income later and let's say that's our only line, we'll add the other forms later after this. And then we're gonna have the adjustments. So adjustments to income, which you could call the above the line deductions. So that's the same with our tax formula here. We've got the above the line, the decimates or adjustments to income. So adjustments to income. And so I'm gonna put a zero here and again, we'll add this to another form, another sheet that we'll add later. But for now, I'm just gonna put a zero there. And then that's gonna give us our adjusted gross income. Adjusted gross income. It's not really gross like nasty gross. It's gross like kind of, that name is supposed to indicate that's like our top line of the income statement because we're building an income statement. So there it is, just like we have here. So we've got the adjustments to income gives us the adjusted gross income. So there we go. I'm gonna say, well, let's put an underline here by going to the homepage, font group and underline and then we'll subtract it out. I'm gonna subtract it out with a formula equals, we're gonna take the 100 minus the zero, nothing's in it right now. But if there was something in it like a thousand, then of course it would be a decrease. So we're gonna see that, okay. Now here's where it gets messy. This is the messy time. Now we've got the greater of either, I'm gonna put a colon, the itemized deductions or the standard deduction. So that's where it gets messy. So I'm gonna put a little asterisk here to say where are the standard deductions, which I'm gonna put down below. I'm gonna try to put them down below somewhere. I wanna get myself plenty of room, but I'm gonna say let me put a table for like the standard deductions. What are those standard deductions? We could find those in general on the form 1040 and we could go down and say, okay, the standard deduction for single finalers, it's 12,500 married, 25, head of household, 18, eight. So let's put a little table down here and say, okay, I can put a table down here and say that this is gonna be the standard deductions. And put a little colon and I could say single and then married filing, married filing a joint. So I'll say married filing joint which, and so, and that we'll usually put as married filing joint, something like that. And then we've got the head of house, household, which sometimes abbreviated as H-O-H. And then you could have changes if they're over 65 or blind, which we'll take a look at in a second. So the first one is single or let's say, married filing separate. So single or married filing separate, sometimes named as married filing separate. So it could be either of those two. We'll get into what those mean a little bit later more, more so at least. So single or married filing separate, it's gonna be the 12,500 and married filing joint is gonna be twice that generally this times two, which kind of makes sense, right? Because now you got two people that are on one return, which if they both made the same income, you would think they would get a standard deduction of twice that. Head of household is a little bit of a bump up from single because usually there's a dependent involved. And then if they're over 65 or blind, if we jump over to our Form 1040 instruction, we see here, exception, if you're born before January 2nd, 1957 or blind, then you can go through this worksheet basically and it goes down here and you can see that then the chart on the last page of Form 1040 SR. So that would then qualify you for 1040 SR. And so if I was to check the Form 1040 SR and look at the last page, page four, you can see that you have some more variance on basically the standard deduction. So for single, for example, you could say, okay, well, it would be one or two, there's an increase in the deduction. So it was at before 12,550. So if it's 14,250 minus the 12,550, then it would be a difference of 17. Let's do that again. It was at 12,550 minus 14,250. That's gonna be a change of 1,700 if either over the 65 or blind. And if both, then of course you would increase it by the 1,700 twice times two if they were single. In other words, it would go up to 15,950 minus the 14,250. So we have incremental increases based on age and whether or not blind of 1,700. So I'll put that in. If married, the old married, if they weren't, if none of those conditions were met was at 25,100. And then it goes up to the 26,450. That would be a 1,350. Now there could be four circumstances here because now you got aged and blind and two people. So you could have one person over the age limit or you can have two people over the age limit or you can have two people over the age limit and one blind or so on and so forth. So you could see the different variants there. And then, so if I jump back over to the worksheet, I'm gonna try to put just something, a simplified version of that. I'm gonna say married filing joint. And then we've got this single and head of household, head of household variance. I'm gonna center that. I'm gonna center that. I'll make it black and white for a header. And then I'll say that they're gonna be possibly incremental increases if married filing joint of 1,350. And then I could multiply it times two depending on how many factors are gonna be applicable. And then here, we're gonna say that it's gonna be 1,700 that we'll have here that will be incremental changes depending on those conditions, age and whether or not blind or not. Now we could get more fancy than that but we'll keep it at that for now. So I'm gonna scroll back up and say, okay, that means that the standard deduction is gonna be equal to let's start it off at the single. I'm always gonna say equals and I'm gonna pull him down to the single and I'm gonna change this number based on the filing status and then those other two conditions. I'm gonna do it manually at first. We could put like a dropdown if we want to get fancy in Excel. Sometimes manual is actually better because that makes you think about it, right? It makes you go down and see how it's working. But in the case, there's that. I'm gonna indent these two to say that it's an indentation because we got these two conditions which are the greater of. I'm gonna do that by going to the home tab, alignment and indent and then the itemized deductions I'll put zero for now. They're gonna pull in from a worksheet. So we'll take a look at that. We'll take a look at that in the worksheet. Now notice that I put these two numbers on the inside here so that I can kind of see. I can compare them out and see what both of them are and then I wanna pull the one that I'm actually gonna use to the outside. So I'd like to actually see the one I'm gonna be using over here. So I'm gonna put my, I could use, I could do that with like an if, then formula. So for example, I'm gonna make this one zero. Let's say this one was 200 to make my formula easier. I want to use this one if it's greater than this number. If it's not greater than this number, I want to put a zero here. That's gonna be like an if, then logic function formula which looks something like this. Equals if I'm gonna do my condition. I'm gonna double click on the if function. I'm gonna say the logic test will be if this condition is met, if this cell right there, the 200 is greater than, greater than this number, then comma, that's what you say when then if that condition is met, what I want you to do is use this cell. So I want you to put that 200 in place. In other words, if not comma, that's what that comma means when I talk this through in my mind, then I want you to put a zero in place of it. So one more time, I'm gonna close it up. I'm gonna, well, let's not close it up yet. I said if the logic test of what's in this cell is greater than what's in this cell, then that's what the comma is. We want you to pull in this cell B6 or the 200. If not, that's what the next comma is and the next argument is if not, then put a zero there. So if I hit enter, it puts a zero there. I could test it. If I make this over this number, this number over that number, I could say, what if this was 14,000? Now it pulls over the 14,000. So on the other side, I could say, okay, well, let's do the same thing here. I'll bring this back down to zero and now I deleted my formula. I don't wanna delete the formula. So there it is. I wanna make this back down to zero. Let's do it here. This is gonna be equals if brackets, this number, if this number is greater than this number, then comma, I want you to use this number. If not, which is a comma, what do I want you to do? I want you to put a zero in there. So on this one, I'm gonna do the same kind of thing. It's gonna say I want a logic test to say that if this cell is greater than this cell, then comma, I want you to use this number, 12,500 or what's in cell B7. If not, comma, I would like you to put a zero there. So if I hit enter, now it's gonna pull in that number. Now it's a negative number. So I'm gonna go back in there. I put a negative sign in front of it for some reason. I don't know why, deleting that. There it is, testing it out. What if this cell was 14,000? Now this one pulls over and this one is zero. I like that format just so I can see what is actually happening on these two cells and I can still compare and contrast myself what's happening over here. So you could format that different ways. For example, you could just pull over the total down here, whichever one is higher and have one if then formula. But in any case, that's how I'm gonna put it. I'm gonna put a bracket here. So we'll put a bracket there or an underline there. And then we also have this thing. We'll talk about a bit later, which is a qualified business income deduction. Now this comes into place typically when you have a schedule C. So we'll talk about that later. So I'll put a zero on it. I'm gonna un-underline this one, un-underline. I'll put the underline there. That's where I want the underline. And this one is a relatively new thing, that other line item I put there. So you could see it on the 1040. It's gonna be here. You've got then the qualified business income deduction here. So we'll talk more about that later before you get down to the taxable income. So you could add some subtotals and whatnot before you get there. But I'm gonna keep it at this format for now. So we're gonna go back on over and say, okay, we'll take a look at that later. That'll give us then the taxable income. Taxable income. So this is kind of like the net income on which you would apply the tax. That would be equal to the prior subtotal that we had, which was the adjusted gross income, minus, I'm gonna take out the standard deduction or the greater of the standard or the itemized. So I'm gonna subtract both of these out, minus this, minus this, and then minus, minus this. And then I'm also gonna subtract out the qualified business income deduction. And so that's gonna be our taxable income. Or you can write it this way, equals this number minus the sum of these three numbers. And notice I'm taking either the itemized or the standard deduction because only one of these are gonna be zero given the if then formula that we put in place. So I'll put a bracket around that. So that's gonna be our taxable income. So now we're on the bottom half of the tax return. We're gonna apply the actual tax now, which I can't actually get because it's a progressive tax. So this is something that I typically get from the computer. It's gonna be the tax, so this will be the average tax rate. So this is gonna be the average tax rate or from tax tables, right? We're gonna count, in other words, I'm trying to say that we're calculating that I'll just, we're calculating the tax with the tax tables. I don't know what that is because I can't do it manually because it's too complex for me to want to do an Excel. I could figure it out and try to approximate it in Excel and that might be worthwhile. But I'm gonna rely on the software. I'm gonna check this number, rely on the software to calculate the actual tax. And then I'll put in the average tax here to give me at least that double check. So then we're gonna have the tax. That's gonna give us then the tax before, before credits and other taxes. So in other words, if I was to look at this example, for example, and if I go back on over here and I say if I had 100,000, 12,558, that would give us the 87,5, 87,450, which is gonna be, which is going to be this 87,450, I'm not gonna put the rate here. What I'm gonna do is get the tax now from the software. So this is something I'd have to go back to the software each time and say, okay, they calculated it at 15,15. So I'm gonna say, all right, the tax is actually 15015. Now I could go back in here and back into the average tax, which is what's actually used to calculate the tax, what's used to calculate the tax, progressive tax tables. But I can back into the average tax now that I have that, which would be this number, the 1515 divided by the taxable income. There's the average tax. If I make it a decimal, home tab, number, percentifying it, let's add a decimal to it. So about number group decimal, about 17.2. So in other words, if I went back to my software and saw this in tax formula in a tax summary, that's my average or effective tax. So it's not the actual tax because it's progressive. The highest rate was actually 24%, but you could see how we can at least double check that in our software when we're doing our data input. So then if I was to increase, notice that if I was to increase, for example, the income, I can't really rely on this average tax because the marginal tax is what's gonna be the impact on the next dollar change increase or decrease. But the average tax is something I can kind of double check there. So then we're down to this line item. If I look at my summary on this sheet, we've got the itemized standard deduction, we've got the tax before credits. So now I'm gonna say, okay, then we've got, after that, we've got the other half of this whole calculation, which is going to be tax credits, credits. Now the credits are a little tricky. We'll talk more about them later, but there's above the line, there's like credits that are non-refundable and refundable credits. We have to break those out because of the nature of the refundable credits. So we'll talk more about that when we get to credits. For now, I'm gonna put a zero here. And then we've got other taxes, other taxes, which could include things like self-implement tax. And so we'll talk about, we'll talk more about other taxes later. I'll put that at zero for now. And when we do, we'll add schedules to kind of tie into those as well. And then that's gonna give us the total tax. So that's gonna be the total tax, which is gonna be equal to the tax before credits and other taxes. And then I'm gonna subtract out the credits and I'm gonna add the other taxes. So that's gonna give us our total tax. And then we gotta put in together and they lump these two together because they have the same kind of impact and that's gonna be the refundable credits, things like the child tax credit has a refundable portion and possibly the earned income tax credit could have a refundable portion. For example, we'll talk more about that later, but we have the tax payments, payments, which is like withholdings included in that and refundable credits, refundable credits. So we'll talk more about those later and that'll give us finally down to the tax due or refund, tax due or refund. So and the tax due or refund is going to be the tax before credits and I'm sorry, it's gonna be the total taxes here, total taxes minus the payments and refunds. So here we have the tax and then we had the payments that we made, which would be like your W2 payments or withholdings that would be included here to finally get to the amount due or refund. We would also include those other refundable credits if applicable here, usually for lower income tax returns such as an earned income tax credit and a child tax credit possibly. Now we could make this, we got this table down a little further, we can move it up a little bit. We'll go into more formatting of it though in future presentations. So this is the kind of rough draft we put together. We'll go back into it. We'll add some more underlines to it. We'll make it look a little fancy. We'll probably need to do a spell check on it at least. And then we'll start to add sub reports which will be similar to the 1040 branching out to other things that feed into it like the schedule one, schedule two, the schedule A, B, C, D, so on that we'll add in and we'll see how this whole thing kind of fits together as we think about each of these line items. And we'll start to go through these in the presentations like line item by line item, what's included in income, what is income? How do you, what's that impact on the finance? What's included in the adjustments? What's included in itemized deductions? Standard deductions, who qualifies for what? When do those go up? And we'll start looking at these basically line by line or try to, that's the general outline that we can kind of think about progressing forward.