Income Tax 2021 2020 to create a tax formula worksheet using Excel, get ready to get refunds to the max diving into 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 they’re related schedules, including schedules and forms tie into an essence of a 1040.
00:33
However, I know this is not an Excel course, I’m going to 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 will provide you with the Excel template as well.
00:46
So I’m in Excel, first thing I’m going to do, I’m going to, I’m going to increase the size of the sheet a little bit down here, we’re going to increase the size a bit, bring it up,
00:54
let’s bring it I’m going to bring it up to 160, then I’ll typically format the whole sheet and the general format that I would like to see it in. And I do that by saying Ctrl A, or I’m going to put my cursor on the little triangle up top selecting the whole sheet.
01:08
And then I’m going to right click on the sheet and I’m going to go into the format have in the cells, we want to format the cells, I’m going to I usually go to currency, I put some brackets around it and make them red if they’re negative.
01:22
And then actually, I’m just going to 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 going to hit the drop down and say I don’t want the dollar sign, I’m going to put it to none.
01:36
And then I’m going to start out removing the decimals and only add the decimals if I want to pour the rationale is that taxes is somewhat of an imperfect science, I’m going to take those off. And in so rounding to the dollar is oftentimes good enough.
01:52
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 going to be a rounding thing that will be involved. Also, if you look at tax software, generally though rounded to the dollar.
02:04
For example, if I jumped 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 going to think about is in essence, the 1040. However, we’re going to 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.
02:26
Oftentimes many tax all tours have this not just with cert, I’m looking at a cert here. But here’s the tax summary, which basically breaks this down into a formula type of basis.
02:36
And so you could use something like that to build your worksheet. At the end result of this, we’re going to build something that looks something like this, we’re majorly focused on this first page, just the formula.
02:49
And then after that, we’ll start to add these other worksheets, which can 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.
03:03
So that’s going to be our general idea. So let’s go back on over to our to our worksheet over here, I’m going to say okay, we’ve got everything formatted.
03:12
I’m going to make cell a a little bit longer. And I’m just going to list out our formula, I’m going to put my cursor in between A and B, so it looks like that. Not like that.
03:19
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 going to put my heading, which is a tax form you love 2021. I’m going to keep it at that I’m going to format this one up top.
03:36
So I can see that it is a heading type of thing. And so I’m going to go up top and I usually make this in the home tab Font Group bucket, make it black and make it white.
03:46
That’s what I’ll usually do to just show that it is part of a heading, I’m not going to center it or anything right now we’ll get into that later. Once we add the rest of the data. I’m also going to 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.
04:01
And I’m at the 205. I’m quite squirreled in at this point. So the first line item is going to be income. You might call it tax income, for example, this is going to be the income line item similar to what we see here in Excel.
04:14
Now I’m not going to list out the different kinds of income underneath it, like w two income, interest income, and so on and so forth. Because I’m going to put that in another schedule. I’m going to put all types of income somewhere else.
04:26
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, schedule one. So why don’t you list it all out?
04:40
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, you know, a couple years ago, adding that schedule one and so on,
04:54
which I think was probably you know, makes sense to me. But in any case, I’m going to Put all the other income line items on another thing, including the W two income.
05:04
And so I’m going to start with just the income. And 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.
05:15
And then I’ll add, I’ll add the income later. And let’s say that’s our only our only line, we’ll add the the other forms later after this. And then we’re going to have the adjustments,
05:25
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 decimals or adjustments to income. So adjustments to income, and so I’m going to put a zero here.
05:45
And again, we’ll add this to another form another sheet, that will add later, but for now, I’m just going to put a zero there. And then that’s going to give us our adjusted gross income, adjusted gross income.
06:00
It’s not really gross, like nasty, gross. It’s gross, like kind of stats, that name is supposed to indicate that’s like our top line of the income statement, because we’re building an income statement.
06:11
So there it is, just like we have here. So we’ve got the adjustments to income gives us the adjusted gross income.
06:19
So there we go, I’m going to say, well, it’s put an underline here by going to the homepage, Font Group and underline, and then we’ll subtract it out, I’m going to subtract it out with a formula equals, we’re going to take the 100 minus the zero,
06:35
nothing’s in it right now. But if there was something like 1000, then of course, it would be a decrease. So we’re gonna see that, okay.
06:43
Now, here’s where it gets messy. This is the messy time. Now we’ve got the greater of either I’m going to put a colon, the itemized deductions, or the standard de duct, Shawn.
06:59
So that’s where that’s where it gets messy. So I can I’m going to put a little asterix here to say, Where are the standard deductions, which I’m going to put down below, I’m going to try to put them down below somewhere,
07:10
I want to give myself plenty of room. But I’m going to 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.
07:21
And we could go down and say, okay, the standard deduction for single filers, it’s 12,500, Married 25, Head of Household 18, eight. So let’s put let’s put a little table down here and say,
07:33
Okay, I can put a table down here, and say that this is going to be the standard, the deductions shunts, and put a little colon, and I could say, single, and then married, filing, married filing joint, so say, married filing joint, which, and so and that will usually put as married filing joint, something like that. And then we’ve got the head of house, house hold,
08:08
which sometimes abbreviated 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.
08:30
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.
08:42
So single or married, filing separate is going to be the 12 550. And married filing joint is going to be twice that generally this times two, which kind of makes sense, right?
08:53
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.
09:06
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 if you’re born before January 2 1957, or blind,
09:21
then you can go through this worksheet basically, and goes down here and you could see that then the chart on the last page of form 1040 SSR. So that would then qualify you for 1040 ASR.
09:33
And so if I was to check the form 1040 as our and look at the last page, page four, you can see that you have some more variants on basically the 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.
09:56
So if it’s 14 to five oh, minus to 12 550 then it would be a difference of 17. Let’s do that again, it was at one to 550 minus 14 to five oh, that’s going to be a change of 1700. If either over the 65, or blind, and if both, then of course, you would increase it by the 170,
10:18
you know, twice times two, if they were single, in other words, it would go up to 50 950 minus two 145. Oh, so we have incremental increases based on age and whether or not blind of 1700.
10:32
So I’ll put that in if married, the old married, if they weren’t, if none of those conditions were met, was that 20 510. And then it goes up to the 26 450,
10:44
that would be a 1003 50. Now, there could be four circumstances here, because now you got age and blind and two people. So you could have one person over 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.
11:01
So you can see the different variants there. And then, so if I jump back over to the worksheet, I’m going to try to put just something a simplified version of that, I’m going to say a married filing joint.
11:11
And then we’ve got this single and head of household, Head of Household variance, I’m going to center that, I’m going to center that I’ll make it black and white for a header.
11:24
And then I’ll say that they’re going to be possibly incremental increases, if married filing joint of 1350. And then I could multiply at times two, depending on how many factors are going to be applicable.
11:36
And then here, we’re going to say that it’s going to be 170 that we’ll have here that will be incremental changes dependent on those conditions age and whether or not blind or not. Now we could get more fancy than that. But we’ll keep that we’ll keep it at that for now.
11:51
So I’m going to scroll back up and say, okay, that means that the standard deduction is going to be equal to let’s start it off at the single I’m always going to say equals, and I’m going to pull it down to the single and I’m going to change this number based on the filing status.
12:07
And then those other two conditions, I’m going to do it manually at first, we could put like a drop down, 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 to work it. But
12:20
in any case, there’s that I’m going to end it these two to say that it’s an indentation because we got we got these two conditions, which are the greater of, I’m going to do that by going to the Home tab, alignment and indent.
12:31
And then the itemized deductions, I’ll put zero for now they’re going to pull in from a worksheet. So we’ll take a look at that, we’ll take a look at that in the worksheet.
12:41
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 want to pull the one that I’m actually going to use to the outside.
12:53
So I’d like to actually see the one I’m going to be using over here. So I’m going to put my I could use I could do that with like an if then formula. So for example, I’m going to I’m going to make this one zero, let’s say this one, let’s say this one was 200. To make my formula easier,
13:09
I want to use this one, if it’s greater than this number. If it’s not greater than this than this number, I want to put a zero here, that’s going to be like an if then logic function formula, which looks something like this, it’s equals,
13:23
if I’m going to do my condition, I’m going to double click on the if function, I’m going to say the logic test will be if this condition is met, if this cell right there,
13:32
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 so so I want you to put that 200 in place. In other words, if not, comma, that’s what that comma means.
13:52
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 going to close it up. I’m going 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.
14:10
We want you to pull in this cell V six or the 200. If not, that’s what the next comment is. 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.
14:29
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.
14:40
I don’t want to delete the formula. So there it is. I want to make this back down to zero. Let’s do it here. This is going to 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,
15:02
I want you to put a zero in there. So on this one, I’m going to do the same kind of thing, it’s going to 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,000 550. Or what’s in cell B seven, if not, comma,
15:21
I would like you to put a zero there. So if I hit enter, now it’s going to pull in that number. Now it’s a negative number. So I’m going to go back in there, I put a negative sign in front of it.
15:30
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, you know, what is actually happening on these two cells.
15:45
And I could still compare and contrast myself what’s happening over here. So so you could format that different ways. For example, you could just pull over, you know, the total down here, whichever one is higher, and have one if then formula. But in any case, that’s how I’m going to put it, I’m going to put a bracket here.
16:04
So we’ll put a bracket there or an underline there. And then we’ve got we also have this, this thing we’ll talk about a bit later, which is a qualified, this, this this income deduction.
16:19
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 going to underline this one on underline,
16:29
I’ll put the underline there, that’s where I want to underline. And this one is a relatively new thing, that other line item I put there, so you can see it on the 1040. It’s going to be here, you’ve got you’ve got then the qualified business income deduction here.
16:45
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 going to, I’m going to keep it at this, this format for now. So we’re going to go back on over and say,
17:00
Okay, well, we’ll take a look at that later, that’ll give us then the taxable income, taxable, 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,
17:19
I’m going to take out the standard deduction, or the greater of the standard or the itemized, I’m going to subtract both of these out minus this, minus this, and minus minus this. And then I’m also going to subtract out the qualified business income deduction.
17:37
And so that’s going to be our taxable income, or you can write it this way, equals this number, minus the sum of these three numbers.
17:47
And notice I’m taking either the itemized or the standard deduction, because only one of these are going to be zero, given the if then formula that we put in place.
17:58
So I’ll put a bracket around that. So that’s going to be our taxable income. So now we’re on the bottom half of the tax return, we’re going to apply the actual tax now, which I can’t actually get,
18:09
because it’s the progressive tax. So this is something that I typically get from the computer, it’s gonna be the tax the app. So this will be the average tax rate. So this is going to be the average tax rate or from tax tables, right? We’re going to count in other words, I’m trying to say that we’re calculating,
18:28
I’ll just delete, 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 do approximate it in Excel and might might be worthwhile.
18:42
But I’m going to rely on the software, I’m going to check this number rely on the software to calculate the actual tax, and then I’ll put in the average tax here to give to give me at least that double check. So then we’re going to have the tax that’s going to give us then the tax before before credits, and other taxes.
19:06
So in other words, if I was to look at this example, for example, if I go back on over here, and I say, if I had 100,000 12,000 558, that would give us the 87 587 450, which is going to be which is going to be this 87 450.
19:24
I’m not going to put the rate here, what I’m going to do is get the tax now from the software. So this is something I’d have to go back to the software each time it’s okay, they calculated it at 1515.
19:36
So I’m going to say all right, the tax is actually 15015. Now I can go back in here in 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,
19:51
but I can 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 As the average tax, if I make it a decimal, Home tab, number percent define it, let’s add a decimal to it. So about number group decimal about 17.2.
20:11
So in other words, if I went back to my software install this and tax formula. In a tax summary, that’s my average or effective tax. So it’s not the actual task, because it’s progressive, the highest rate was actually 24%. But you could see how we can at least double check that, you know, in our software, when we’re when we’re doing our data input,
20:34
so 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 going to be the impact on the next dollar change, increase or decrease.
20:48
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.
21:04
So now I’m going to say okay, then we’ve got after that, we’ve got the other half of this, this whole calculation, which is going to be tax credits. Credits. Now the credits are a little tricky.
21:16
We’ll talk more about them later. But there’s above the line, there’s like credits that are 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
21:29
we get to credits for now I’m going to put a zero here. And then we’ve got other taxes, other taxes, which could include things like self employment tax, and and so we’ll talk about impossible, 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.
21:51
And then that’s going to give us the the total tax. So that’s going to be the total tax, which is going to be equal to the tax before credits and other taxes. And then I’m going to subtract out the credits, and I’m going to add the other taxes.
22:09
So that’s going to give us our total tax. And then we got we got to put it together and they lump these two together, because they have the same kind of impact.
22:18
And that’s going to be the refundable credits. Things like the Child Tax Credit hasn’t refundable portion, and possibly the earned income tax credit could have a refundable portion, for example, we’ll talk more about that later.
22:30
But we have to tax payments. payments, which is which is like withholdings included in that and refundable credits, refundable credits. So we’ll talk more about those later.
22:45
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 the tax before credits. And I’m sorry, it’s going to be the total taxes here. Total taxes minus the payments and refunds. So here we had the tax.
23:10
And then we had the payments that we made, which would be like your W two 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,
23:21
if applicable here, usually for lower income tax returns, such as an earned income tax credit, and 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.
23:34
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 we’ll add some more underlines to it, we’ll make it look a little fancy, I’ll probably need to do a spellcheck on it at least. And then and then we’ll start to add sub reports, which will be similar to the 1040,
23:56
branching out to other things that feed into it like the schedule one schedule to the Schedule A, B, C, D, so on, that will add that will 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?
24:17
How what you know, how do you what would would 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.