Income Tax 2021 2022 Excel worksheet, create a tax formula worksheet using Excel port number three, get ready to get refunds to the max dive in into income tax 2021 2022.
00:16
There we are in our Excel worksheet and prior presentations, we started putting together the income tax formula starting from a blank worksheet within Excel. If you missed those presentations, you might want to go back and take a look at them.
00:28
The idea being that we’re going to be putting together the income tax formula, and somewhat of a more simplistic type of form, helping us to visualize the formula, and also helping us possibly to kind of make a double check as we do data input into tax software. So we’re going to be continuing on here.
00:44
So we have the outline of the formula, we have, in essence, the income statement, the top half of it income, minus the adjustments to income, or above the line deduction, schedule one deductions,
00:55
you could call them, that gives us the AGI or just a gross income. And then we subtract the greater of the itemized or standard deductions, which we have some fancy formulas here to pick that stuff up with the if formulas and so on.
01:08
And then we’ve got the qualified business income deduction, which would only be applicable in certain instances, possibly, if, for example, having a Schedule C, that gets us our taxable income. And then we jump down to the bottom.
01:21
And we pick up the actual tax from the tax software, because we’re not going to calculate the tax in the system. But instead, we’re going to rely on software to do that.
01:31
That’s why it’s going to be blue here, because we’re going to be showing blue items as the items that we’re going to be populating in this form, basically manually put in that number into the system.
01:42
And then we’ve got the credits minus the credits, and other taxes that will be put in place that gives us the total tax, and then we’re going to be subtracting out the refundable credits and the payments. Now we want to add some more information to it, including the sub schedules.
01:57
And you’ll recall that this outline, in essence, you can think of it as basically the first page of the 1040. Although the 1040 is a little bit more expanded than that. And then other schedules feed into some line item in general, on the form 1040 have a kind of summary page. That’s in essence, what we have here.
02:16
So now, let’s just add some of the other items that we might expect in another tab, that will then feed in to the first page. So we can see how that will work. So the income line item we already we already added, for example, the income line item for the W two even though that was on the first page of the 1040,
02:36
some of that stuff will be here, like the W two income, we put it on a separate tab on our formula, because because I think that it’s easier to basically visualize, we also could have some other stuff that will feed in here, such as a Schedule B, for example.
02:51
So if I went down to the Schedule B, this is interest and or an ordinary dividends. And it would only be used if your interest in dividends were over a certain amount. But for example, that’s another area that you can see adding into this income line items.
03:07
So we might have multiple tabs, for example, that are going into the same line item, you could try to put some of those stuff on the same on the same page right here and say it’s all income items.
03:18
Or you might have another tab, it could be a little bit more clear to have more tabs. So I’m going to say let’s have another tab, I’m going to double click on it, this is going to be an income tab. And I’m going to call it in Sureste and dividends or an S Schedule B, Schedule B tab.
03:36
So we’re gonna say that, let’s go ahead and format it, I’m going to select the whole tab, like we have seen in the past, right click and format it format, older cells format in the cells to currency, I’m going to make the negative numbers, bracketed, get rid of $1 sign, get rid of the pennies,
03:55
and then I’m going to say okay, and then hold down Ctrl I’m going to scroll up a bit. And so there we have it. And I’m not going to put a lot of detail in here at this point in time, I’m just going to just to get an idea of these sub tabs.
04:08
And then when we get into these line items, as we go through the practice problems, we’ll build up this Excel worksheet as we go. But in general, you’re going to have in turist, and they might be coming from the 1099.
04:21
So I’m going to say interest, I’ll say I’m going to select the whole thing again, and make it bold. So we might have interest and then they could come from like 1099. So this would be bank, no 1099 interest, this one might be indented now.
04:36
And then we could have a few spaces. So I want to have we could have multiple places that we’re getting 1099 income, so I’m going to give it a little bit of space. And I’m going to make this whole thing blue to indicate this is where my data input will be.
04:49
So I’m going to put my bucket here and if you don’t have that blue, it’s going to be in the more colors and this standard here and then that blue right there. That’s the one that’s the Excel is On guy blue, and then font here hit the drop down, we’re going to go down to the all borders.
05:06
And so there we have it. So if we had interest of say, you know, $100, then I’m going to total that up at the bottom. So we’ll say, total interest. So in Tourette tourist, and I’ll sum it up on the outside with the trusty some formula, some of these items. And then I’m going to add dividends here too, because this form has has dividends in interest, typically,
05:34
so you got dividends down here. So I’m gonna have another category Part B, I might even sub categorize it as part A and part B. So I might say this is interest, or Part A,
05:46
Part A, or part one, was it part one, part I, part one, right? Isn’t that how they said Part One and Part B, so that I’m going to put dividends down here dividends, ports, to not be too we’re doing Roman numerals, not letters, Roman numerals.
06:06
So this could be bank, this would be a form 1099, whatever data input that we’re imagining, here, I’m going to indent it, I’m going to make this whole thing blue, we’ll give it a few spaces down here and give it enough room. So that looks good, I’m going to make it blue, put brackets around it, and this will be the total total.
06:30
The difference, put that on the outer column equals the sum of these two. And dividends can be a little tricky, because you might have like, did, I’ll put like $50 in here, you might have dividends that are that are, you know, taxed at different rates. So we’ll talk more about that later.
06:53
But just to give a general idea. And then if I was to take total, in tourist and dividends, equals the outer column here, the outer column, I’m going to sum up the voucher column. There it is. And that number is what I want to pull into the line item, the first line item on the 1040. Going back to the 1040.
07:17
So what I’m going to do is double click on this 100, go to the end of it and say plus, go back to this tab and pick out that 150. So that’s where we could see now we’re at the 101 50 in the income line item. And we can continue on with that with different income line items.
07:34
You can see here that this Schedule B feeds into the 1040 on line one, or on page one, into the into the interest in dividends that are going to be on page one.
07:49
So here we’ve got the dividends, dividends, we’ve got different areas have different ordinary dividends versus qualified, we’ll talk about those later and the interest.
07:57
And also you might not have Schedule B, if it’s below a certain threshold. We’ll talk about that later. But you can see how that kind of feeds into page one. So there is that so let’s let’s move away from income, there’s a lot of other things that will be fitting into the income line item.
08:11
So but that’s the general idea, we’ll just keep on adding tabs as we need them, feed them into the income other tabs might be Schedule C, Schedule D, you know, Schedule E and so on. And then adjustments, so adjustments, we could go over here and say,
08:25
Okay, how did that work on the tax return, if I entered and adjustment, that would be typically on schedule one, the and page two, this would be adjustments to income, like educator expenses, certain business expenses of reservists health insurance moving, so you might then say,
08:42
Okay, I’ll just make another tab here, that includes all this stuff, or at least a lot of this all a lot of this stuff in it in my worksheet.
08:50
So I’m going to say okay, let’s make another tab, I’m going to say let’s make another tab. And I’m going to put this, I’m going to drag it, grab it and drag it, grabbed it and dragged it get over here thing, dragged it over there.
09:05
And then I’m going to double click on this, and I’m going to copy that it’s going to be adjustments to income, adjustments to income schedule one. So I’ll double click on it, call it schedule one, adjustments to income. So there it is.
09:22
And this time I put the schedule first, maybe I should be doing that all the time, like Schedule B before this part, so maybe I should be seeing Schedule B cut and put that up front. Maybe Is that better Schedule B and then the income form 1040 income Oh, leave that the way it is because it’s the 1040 Okay.
09:45
So I’m going to go back out over here. I’m going to highlight the whole thing again, select the whole thing, or right click and let’s just format this thing. So I’m going to format the cells and I’m going to make it currency dollars brackets negative and get rid of that dollar sign, no pennies because it’s a tax return,
10:03
I’m going to increase the size, holding ctrl scrolling up to 190, for me select the whole thing, I’m going to make it bold. And I’m going to emboldened it, the whole sheet has been emboldened. And then I could add basically all of these inflates,
10:18
I’m not going to add them all right now. So if you want to do this at one time, you could, I’ll just add the IRA, for now, that’s one of the more common ones you’re gonna see. So let’s just add an IRA. So we’ll say IRA, I could name it up top, let’s
10:34
say, a just minutes to end income, it might not be in the same order, it won’t be, because I’m going to just add the IRA here, I wrote, I wrote, I’m going to indent now, you could have multiple different different components for the IRA,
10:51
because you could have the married couple, IRA for the husband and spouse, so you might want, you know, a couple spaces even within the IRA.
11:00
So you might say, IRA, and and then you might have a couple spaces down here, for the IRA. And then you could have the total Ira possibly. So let’s do that.
11:11
Let’s, let’s give it let’s give it a couple of spaces, like 1234, I’ll make that blue and bordered. And I’ll call this total IRA, that’s not the IRS, what are you doing IRS? Ira, Ira, Ira, not IRS.
11:32
And then I’ll say the totals on the outside, I’ll put this in the outer column equals the sum of these items and the outer column. And so if I had an IRA of like, $1,000, for example, there it is.
11:45
And let’s pick up just one more just for another example. student loan interest, okay, let’s say okay, student loan interest, maybe I should skip a line student, loan, and, and tourist. And you might even make these headers like black and white, maybe.
12:05
So maybe I’d make these black and white, depends on your preference. Just a note that this is like a new section, maybe black and white student loan interest. And again, you could have multiple people that have like student loans,
12:21
depending on you know, husband, wife, you know, that are in so that are coming on different forms. So you could have a few of those, actually. So let’s give it a little bit of space here and give it brackets and we’ll make it then blue.
12:37
So there it is, and say, let’s say we had one of these forms, and let’s say it was a big $200 student loan interest. And so then that would give us the total, not the total total student loan in turist, tap it out TAB TAB, or one tab, and then I’ll sum it up,
12:59
summing it up. And so there we have that. And so this is just a rough draft, we’ll keep on adding these as we go. But you can see kind of a pattern here that we have.
13:11
And then we might have the total down below, which is going to be the total just meant to income. And then we’ll sum up the outer column, summing up the outer column, the outer column, there it is, and then I can bring that over to the first page.
13:31
So this first page, I’m not just going to have a zero there, I’m going to say this equals whatever I come up to in my sub schedule, which is right here 1200 that will match in essence,
13:43
what we do on the tax return, when we populate when we populate items into this schedule A and then it will feed into stage one. So for example, if I went into the IRA, here, say IRA, let’s go into the IRA. Sorry, I got a hiccup. And then I’m going to say this is $1,000.
14:05
And I’m going to bring that on over. Now schedule one is populated, as you can see, and we’re on page two. So there it is, and then it sums up down here and it pulls it over to page one, back to page one. And so now it pulls it into to page one on that line item that’s pulling from schedule one, that’s kind of how it’s built in a similar fashion as it is with our our formula here. Let’s go on to simplify it back.
14:32
Let’s bring it back to our starting point. Let’s let’s get rid of the other income, no dividends, let’s get rid of the dividends. Let’s get a get rid of the interest and let’s get rid of these items, too.
14:45
And I left a couple spaces up here on accident. I’m going to delete a couple couple rows from 123 highlighting from one to three, right click and delete those back to the first time.
14:57
Okay, so then we have the greater of the itemized or the state deductions. So the itemized deductions are going to be on what we typically call a Schedule A, the standard deduction is going to be something that I’m going to draw from down here.
15:09
So this is something I’m going to say is a blue item that I’m going to data, input it right into the first page. So I’m gonna make that blue. This one, however, is going to draw from a sub schedule, which will be equivalent or similar to me recalculating a Schedule A,
15:25
so it’s called the Schedule A itemized deductions. So I’m going to say, Okay, let’s make that one. I’m going to make a new tab, pull it to the right, grab it, left click on it and drag it get over here, go right there thing, dragged it over.
15:40
And then you could have just asked it right, if you asked it nicely, you wouldn’t have had to drag it like anyway,
15:45
I’m going to double click on it, this is going to be a Schedule A itemized deductions if I when I spell anything wrong, because I will, it’s not an IF thing, then bear with me, I’m going to count and I’m going to, I’m going to select the whole worksheet and right click on it, and format this thing.
16:05
Let’s format it currency, put some brackets and some red on the negatives drop down, none on the sign and take down the decimals. Let’s make the whole thing Bolden and Bolden the whole thing, hold down Control, scroll up a bit. And this will be the Schedule A, again, we’ll get into more detail on the schedule A because it’s a pretty in depth schedule.
16:29
But you could go line by line. So okay, I got the medical, the medical has some components to it, that we’ll have to talk about it taxes, we can add the different tax line items. So this one’s going to get a little bit more detail.
16:41
So let’s just but let’s just add like a summary for now. medical and dental expenses, I’m going to say all right, medical, we’ll call this scared, schedule, schedule a itemized deductions. And then I’m going to say medical and dental to all medical and dental.
17:09
Okay, and hit the let’s make this black and white, maybe make these black and white. And then there’s going to be some other kind of sub calculations we’ll have in here. So I’ll get into that later. There’s like an AGI limitation, possibly that you can see as they apply 7.5. And so we’ll talk more about that later will be a little bit more complex, but not right now.
17:29
Because we’re that’s not where we’re at. Right now. I’m just gonna say let’s make it all blue. Put some brackets, and let’s just, let’s just imagine, let’s just leave it at that for now.
17:39
And then the next one, the next one’s going to be taxes you pay. So taxes. So it’s in this will be total total, I can put the total total medic met tau, tau, Medic cow, and then tau. And then taxes taxes, you pay. You pay? Is that how they called it? Is that what they call it? Taxes you paid past tense.
18:12
Okay, let’s make that black and white, black and white. And this could this includes different kinds of taxes. So you could have like state tax, local local tax, property tax.
18:28
So we’ll get into more of this in a future presentation. But let’s just add something here for now. And we’ll say let’s say this is like 500. On this one, I’ll add a couple more lines just to give it some space brackets around it, let’s put some blue here. And this is going to be total taxes you paid,
18:50
bringing that on out to the outside, summing it up. So there’s one, let’s do another one, I’m going to say okay, what else we got interest you paid, let’s stop it at that one for now, interest you paid.
19:03
So I’m gonna say interest interest you paid. And so let’s make that one black and white, black and white. We’ve got mortgage interest, mortgage, and trust. And there could be like multiple different different mortgage interests.
19:23
So we might even want to get a little bit more detailed on that because you could have multiple different forms that from different banks, even if you have one home. So you might say okay,
19:31
maybe I need a different a couple different line items here, maybe four just to be just in case I’ve got someone who has, you know, a few different ones here. And I’m gonna say and then if we if we start to populate this, let’s start to populate this one at like, at like 6000, let’s say, so they could have a 6000 and a second, let’s say of of 1000.
19:55
So there we have that and then I’m going to that I’m going to sum this up to the outside total, total mortgage tow, tell more Gadge in tourist, and we’ll bring that out to the outside equals the sum.
20:14
Actually, now let’s sum it up. Let’s sum it up right here, I can sum that up right here equals the sum of these items. And then we could have other interest rate other in tourist.
20:29
And we might apply that out, we might have some other interests that we would deal with. And that would give us then the total, total,
20:38
and tourist you paid, which I’m going to say now it’s going to be equal to the sum of these two, not summing these up, because I already got a total down here. So there it is. Now, this is just a basic format, we’ll go into more of this later.
20:53
But I’m going to go ahead and and say this is stop this for now, this is going to be the total total itemized deductions. Now we get we’ll get more detail into it as we start making making stuff. But I’m going to sum this up for now outside just so we could see how this is getting built.
21:12
So we’ll just sum this out. And then when I go over to the first page, first page, let’s make this a little smaller. On the form 10, four, zero, this is not going to be hard coded as zero, but it’s going to equal what is on my itemized deductions.
21:28
So equals, in this case, that seven, five. So there’s the seven, five. And in that case, I can kind of go through what happens on the tax return, tie it out to what we had, as we recreate the Schedule A,
21:40
and basically see, okay, does that make sense to me? Does it make sense? Can I build it myself in Excel, if I can, at least I’m more likely to get make get some idea of what it’s doing. So there it is.
21:50
Now notice that due to our formula, the if then formulas, because this is lower than that, we’re not going to have itemized we’re going to take the standard, if we were to increase something on the itemized, and this is usually going to be like the mortgage interest.
22:04
That’s why That’s why, you know, most people that itemize are mostly more well off, possibly not just having a home right now these days, but having an expensive home with a large mortgage on it most likely living in a high cost of living area.
22:17
So we’re gonna say like, what if this was 15,000, then we’re gonna say, okay, then that’s usually the kind of thing that’ll push you over. And we’ll talk more about that later. But now you’re taking the itemize.
22:27
So if you had, like a home, and you’re and that would increase the property taxes, that you’d be paying on a more expensive home and a high cost of living area, then that could significantly increase your itemized deductions and push you into an itemizing kind of capacity.
22:43
So now you’re itemizing instead of taking the standard, so there is that so I’m going to go back on over go back to the simplified one, I’m just going to delete this for now. Just get an idea of this.
22:54
And so that’s back down to zero. And you can see how you go back into these every time you do a new tax return, clean it back out and then work it again. So that you could basically reconstruct what is happening for whatever tax return you’re working on.
23:06
Now we’re to the standard deductions, the standard deduction we’re pulling from down below, I might want to, I might want to pull this worksheet up a little bit, so I can see it.
23:17
So I’m going to put my cursor on 18 and drag down till I get to 27. And right click and delete. So I pull it up a bit, maybe maybe like one more row, I’m going to put my cursor on 18 and insert. So it’s not right next to it, maybe insert so it’s down a little bit. So there it is, it’s down there.
23:39
You could also put it maybe on this side, but I kind of like reconstructing when I have to do calculations over here. If there’s anything I want to, you know, recalculate based on this information, so I don’t like having it on the left or on the right. That’s right, that’s on the right.
23:53
So this is pulling in from the standard deduction, which means it’s not like you’re, it’s not like you’re hard coding it directly, but you’re pulling it in, you’re gonna have to adjust that from this sheet by basically, you know, doing some data input, not hard coding or typing in the number, but you’re doing activity, that’s why I’m going to make that blue.
24:10
And then we’ve got the this one we’ll talk about later, the qualified business income deduction, possibly, we would want another worksheet for that as well. But we’ll get into that later. That’s going to give us our taxable income.
24:23
So that’s our taxable income. This is obviously a formula I’ll keep it white, because I’m not I’m not going to do anything to that formula, it should calculate properly if everything set up, right.
24:32
And then this one, I’m not going to do anything to either that’s going to give us the tax before credits.
24:38
Now this one is something that we would pull from the actual tax return. That’s why it’s blue. Now next time, we’ll get into the bottom half, which is talking about the the credits,
24:49
and the other taxes and so on, and we’ll we’ll add a couple more schedules that will show you how we’re going to tie these out in a similar way to other to other schedules.