 Income Tax 2021-2022 Excel Worksheet. Create a tax formula worksheet using Excel part number three. Get ready to get refunds to the max. Diving into Income Tax 2021-2022. We are in our Excel worksheet in prior presentations. We started putting together the income tax formula starting from a blank worksheet within Excel. If you missed those presentations, you might wanna go back and take a look at them. The idea being that we're gonna be putting together the income tax formula in 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 gonna be continuing on here. 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 deductions, schedule one deductions you could call them. That gives us the AGI or adjusted 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. 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 gives us our taxable income. And then we jump down to the bottom and we pick up the actual tax from the tax software because we're not gonna calculate the tax into the system, but instead we're gonna rely on software to do that. That's why it's gonna be blue here because we're gonna be showing blue items as the items that we're gonna be populating in this form, basically manually putting that number into the system. 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 gonna be subtracting out the refundable credits and the payments. Now we wanna add some more information to it including the sub schedules. 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 all other schedules feed into some line item in general on the form 1040, the kind of summary page. That's in essence what we have here. 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 added for example, the income line item for the W-2, even though that was on the first page of the 1040, some of that stuff will be here like the W-2 income. We put it on a separate tab on our formula 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. So if I went down to the schedule B, this is interest and ordinary dividends. And it would only be used if your interest and dividends were over a certain amount. But for example, that's another area that you can see adding into this income line item. 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 page right here and say it's all income items or you might have another tab. It could be a little bit more clear to have more tabs. So I'm gonna say let's have another tab. I'm gonna double click on it. This is gonna be an income tab and I'm gonna call it interest and dividends or an S schedule B, schedule B tab. So we're gonna say that. Let's go ahead and format it. I'm gonna select the whole tab. Like we have seen in the past, right click and format it. Format all the cells, formatting the cells to currency. I'm gonna make the negative numbers bracketed, get rid of the dollar sign, get rid of the pennies and then I'm gonna say okay and then hold down control. I'm gonna scroll up a bit and so there we have it. And I'm not gonna put a lot of detail in here at this point in time. I'm just gonna just to get an idea of these sub tabs 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 gonna have interest and they might be coming from the 1099. So I'm gonna say interest. I'm gonna 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 big, 1099 interest. This one might be indented now and then we could have a few spaces. So I wanna have, we could have multiple places that we're getting 1099 income. So I'm gonna give it a little bit of space and I'm gonna make this whole thing blue to indicate this is where my data input will be. So I'm gonna put my bucket here and if you don't have that blue it's gonna be in the more colors and the standard here and then that blue right there that's the one, that's the Excel is fun guy blue. And then font here hit the dropdown we're gonna go down to the all borders. And so there we have it. So if we had interest of say, you know, $100 then I'm gonna total that up at the bottom. So we'll say total interest, total interest and I'll sum it up on the outside with the trustee sum formula, sum of these items. And then I'm gonna add dividends here too because this form has dividends in interest typically. 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, part A or part one, was it? Part one, part I, part one, right? Isn't that how they say it? Part one and part B. So then I'm gonna put dividends down here, dividends. Part two, not B, two. We're doing Roman numerals, not letters, Roman numerals. So this could be bank, this would be a form 1099 whatever data input that we're imagining here. I'm gonna indent it. I'm gonna make this whole thing blue. We'll give it a few spaces down here to give it enough room. So that looks good. I'm gonna make it blue, put brackets around it and this will be the total, total dividends, put that on the outer column equals the sum of these two. And dividends can be a little tricky because you might have, I'll put like $50 in here, you might have dividends that are taxed at different rates. So we'll talk more about that later, but just to give a general idea. And then if I was to take total interest and dividends equals the outer column here, the outer column, I'm gonna sum up the outer column. There it is. That number is what I wanna pull into the line item, the first line item on the 1040. Going back to the 1040. So what I'm gonna 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 can see now we're at the 100 and 150 in the income line item. And we can continue on with that with different income line items. You can see here that this schedule B feeds into the 1040 on line one or on page one into the interest and dividends that are gonna be on page one. So here we've got the dividends, dividends, we've got different areas of ordinary dividends versus qualified, we'll talk about those later and the interest. 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 move away from income. There's a lot of other things that will be fitting into the income line item. 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, schedule E and so on. And then adjustments. So adjustments we could go over here and say, okay, how does that work on the tax return? If I entered an adjustment that would be typically on schedule one 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, okay, I'll just make another tab here that includes all this stuff or at least a lot of this stuff in it in my worksheet. So I'm gonna say, okay, let's make another tab. I'm gonna say, let's make another tab. And I'm gonna put this, I'm gonna drag it, grab it and drag it, grabbed it and dragged it. Get over here thing, drag it did over there. And then I'm gonna double click on this and I'm gonna copy that. It's gonna be adjustments to income. Adjustments to income schedule one. So double click on it, call it schedule one adjustments to income. So there it is. 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 saying schedule B cut and put that up front, maybe. Is that better schedule B? And then the income form 1040 income. I'll leave that the way it is because it's the 1040. Okay, so I'm gonna go back on over here. I'm gonna highlight the whole thing again, select the whole thing or right click and let's just format this thing. So I'm gonna format the sales and I'm gonna make it currency, dollars, brackets, negative and get rid of the dollar sign. No pennies because it's a tax return. I'm gonna increase the size, holding control scrolling up to 190 for me. Select the whole thing. I'm gonna make it boldened. I'm gonna emboldened it. The whole sheet has been emboldened. And then I could add basically all of these in place. I'm not gonna add them all right now. So if you wanna 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 say adjustments to income. It might not be in the same order. It won't be because I'm gonna just add the IRA here. IRA, IRA, I'm gonna indent. Now you could have multiple different components for the IRA because you could have the married couple IRA for the husband and spouse. So you might want a couple of spaces even within the IRA. So you might say IRA and then you might have a couple of spaces down here for the IRA. And then you could have the total IRA possibly. So let's do that. Let's give it a couple of spaces. Like one, two, three, four. 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. And then I'll say the totals on the outside. I'll put this in the outer column equals the sum of these items in the outer column. And so if I had an IRA of like $1,000 for example, there it is. 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 interest. And you might even make these headers like black and white maybe. So maybe I'd make these black and white. Depends on your preference. Just to 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 depending on husband, wife, 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. 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 teetle, total student loan interest. Tap it out, tab, tab, or one tab and then I'll sum it up, 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 the pattern here that we have. And then we might have the total down below, which is gonna be the total adjustments to income. And then we'll sum up the outer column, the outer column, there it is. And then I can bring that over to the first page. So this first page, I'm not just gonna have a zero there. I'm gonna say this equals whatever I come up to in my subschedule, which is right here, 1200. That will match in essence what we do on the tax return when we populate items into this schedule A and then it will feed into page one. So for example, if I went into the IRA here, say IRA, let's go into the IRA, sorry, I got hiccup. And then I'm gonna say this is $1,000 and I'm gonna 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 two page one. Now it pulls it into two 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 formula here. Let's go and simplify it back. Let's bring it back to our starting point. Let's get rid of the other income, no dividends. Let's get rid of the dividends. Let's get rid of the interest and let's get rid of these items too. And I left a couple of spaces up here on accident. I'm gonna delete a couple rows from one to three, highlighting from one to three, right click and delete those back to the first tab. Okay, so then we have the greater of the itemized or the standard deduction. So the itemized deductions are gonna be on what we typically call a schedule A. The standard deduction is gonna be something that I'm gonna draw from down here. So this is something I'm gonna say is a blue item that I'm gonna data input it right into the first page. So I'm gonna make that blue. This one, however, is gonna draw from a sub-schedule, which will be equivalent or similar to me recalculating a schedule A. So it's called the schedule A itemized deductions. So I'm gonna say, okay, let's make that one. I'm gonna 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. And then you could have just asked it, right? If you asked it nicely, you wouldn't have had to drag it. Anyway, I'm gonna double click on it. This is gonna 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 gonna count. I'm gonna select the whole worksheet and right click on it and format this thing. 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 it 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 but you could go line by line and say, 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 gonna get a little bit more detail. So let's just, but let's just add like a summary for now. Medical and dental expenses. I'm gonna say, all right, medical. I'll call this a schedule A, itemized deductions. And then I'm gonna say medical and dental. Medical and dental. Okay, let's make this black and white, maybe make these black and white. And then there's gonna be some other kind of subcalculations 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. It'll be a little bit more complex, but not right now, because 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 imagine, let's just leave it at that for now. And then the next one, the next one's gonna be taxes you pay. So taxes, so it's, and this will be total, total. I can put the total, total, medic, total, medical and dental. And then taxes, taxes you pay, you pay, you pay, is that how they called it? Is that what they called it? Taxes you paid past tense. Okay, let's make that black and white, black and white. And this includes different kinds of taxes. So you could have like state tax, local, local tax, property tax. 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 gonna be total taxes you paid. Bringing that on out to the outside, summing it up. So there's one. Let's do another one. I'm gonna say, okay. What else we got? Interest you paid. Let's stop it at that one for now. Interest you paid. 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 interest. And there could be like multiple different, different mortgage interests. So we might even wanna 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. Maybe I need a couple different line items here. Maybe four just to be, just in case I've got someone who has a few different ones here. And I'm gonna say, and then if we start to populate this, let's start to populate this one at like 6,000, let's say. So they could have a 6,000 and a second, let's say, of 1,000. So there we have that. And then I'm gonna sum this up to the outside. Total mortgage, total, mortgage, interest. And we'll bring that out to the outside equals the sum. 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, right? Other interest. And we might apply that out. We might have some other interest that we would deal with. And that would give us then the total, total interest you paid, which I'm gonna say now it's gonna 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, but I'm gonna go ahead and say this is, stop this for now. This is gonna be the total, total itemized deductions. Now we'll get more detail into it as we start making stuff, but I'm gonna sum this up for now outside just so we can see how this is getting built. So we'll just sum this up. And then when I go over to the first page, first page, let's make this a little smaller, on the form 1040, this is not gonna be hard-coded as zero, but it's gonna equal what is on my itemized deductions. So it equals, in this case, that's 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 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 some idea of what it's doing. So there it is. Now notice that due to our formula, the if then formulas, because this is lower than that, we're not gonna have itemized, we're gonna take the standard. If we were to increase something on the itemized, and this is usually gonna be like the mortgage interest, that's why 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. 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 itemized. So if you had like a home, 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. So now you're itemizing instead of taking the standard. So there is that. So I'm gonna go back on over, go back to the simplified one. I'm just gonna delete this for now. Just get an idea of this. 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. Now we're to the standard deductions. The standard deduction we're pulling from down below. I might wanna pull this worksheet up a little bit so I can see it. So I'm gonna 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 I'm like one more row. I'm gonna 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. You could also put it maybe on this side but I kinda like reconstructing when I have to do calculations over here. If there's anything I wanna 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. So this is pulling in from the standard deduction which means 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 doing some data input. Not hard coding or typing in the number but you're doing activity. That's why I'm gonna make that blue. 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 gonna give us our taxable income. So that's our taxable income. This is obviously a formula. I'll keep it white because I'm not gonna do anything to that formula. It should calculate properly if everything's set up right. And then this one I'm not gonna do anything to either. That's gonna give us the tax before our credits. 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 credits and the other taxes and so on. And we'll add a couple more schedules that we'll show you how we're gonna tie these out in a similar way to other schedules.