 Income tax 2022-2023. Create a tax formula worksheet using Excel part number three. Let's do some wealth preservation with some tax preparation. Here we are in our example form 1040. We're using LASERT tax software to- Objective. Nice software. Populate it. You don't need tax software to follow along. But if you have access to tax software it's a great tool to run scenarios with. You can also get access to the form 1040 related schedules, related forms at the IRS website irs.gov irs.gov. So we've been looking at our standard scenario here single-filer for Mr. Anderson. No dependence. And we've been mirroring what's on the form 1040 over here on our Excel worksheet. You don't need worksheets. Putting together the Excel worksheet so that we can use it to kind of double check the data input that we're putting into the tax system. Get a handle on how the tax forms are populated. Be able to help us visualize what is happening in a formula kind of basis. So now we're going to continue on with this process and note that support accounting instruction by clicking the link below, giving you a free month membership to all of the content on our website, broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files, and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. On the actual tax software, oftentimes they have a formula component over here to help us kind of visualize the short compact version of what is happening. But again, it's useful to do in Excel so that you can see how the other numbers, the other forms are feeding into a formula kind of scenario and so that you can kind of double check with any kind of data input stuff. So we're going to go back to the first page of the form 1040 and I'm going to now add a little bit more detail on some of these sub schedules. So we saw before, for example, that this income line up top, I'm not going to be populating directly into the income line because I'm going to have it feed in from other schedules that we're going to be setting up. So that would be similar to the first page of the 1040 being all this stuff up top, all these other kind of income places that we can get paid from that are feeding in from other schedules. Now some of these income line items are already on other schedules as well, such as like taxable income and dividend income might be on like a schedule B if it's below a certain threshold. So again, you've got some redundancies on the tax code, which isn't exactly efficient, you would think, but you can see from the past of how the tax code has been constructed, what has happened with it. They used to have it on all on one page and now it makes more sense to have these other schedules and so they're kind of modifying things as we go. So I'm not going to add like every different line item. I'm going to go to the income page that is on the tax return here. You could do that. You could go in here and just add everything that's on kind of page one, but I'll continue to build on this as we do the data input as we experiment or as we interact with other incomes in our practice problem, we'll construct our Excel sheet a little bit more detailed. So what I will do here is I'm going to make this my header column. I'm going to select these two rows, home tab, font group. Let's make this black and white and then everything else looks good. I'm going to pull this so that will be pulling in to here. Now then we've got the adjustments to income. So let's just add like a basic adjustment over here and then I'll make a sub ledger worksheet so I can pull from that worksheet. So the adjustments to income are going to be down here. So there's the adjustments to income. If I right click and jump to the adjustments using our jump feature here, it won't let me it won't let me do it, but the main one if I go back over here is going to be let's say that we have a ira situation. So I'm just going to add an ira and so now you've got that 6000. So that 6000 is being pulled from schedule one line 26. Okay, so now I can see that by going to the schedule one here and I could say okay line 26. Notice that the schedule one part one is the additional income. So these are other income line items and then down here we've got the adjustments to income and there's the adjustment for the ira. So let's go. Okay, I'm going to go back on over and call this then schedule one adjustments to income or something like that. So I'm going to say let's make another tab. I'm going to pull this to the right. I'm going to double click on the name. I'm going to call it adg to income schedule one and then I'm going to do my formatting of it. I'm going to hold control scroll up a bit so I can zoom in on it. I'm going to go up to the top of the sheet this time. Select the entire thing with a triangle or control a right click and format it format the cells. I'm going to make it first tab number currency bracketed numbers no dollar sign getting rid of the decimals and then okay and so then I'm going to call this adjustments to income. Let's just say that up here adjustments to well actually I don't really even need the header because I have it well let's call it that adjustments to income. So that'll be our header line. I'm going to make that bold let's do that and then we can list our adjustments. We're going to make this a little bit larger and the main adjustment here is going to be the one we see often is an ira deduction. Now you could make it more detailed you can actually put in the actual numbers and whatnot so it ties into the form depends how much detail you want to put on your Excel worksheet. I could add these one by one having each of them but I'm just going to kind of add them as I go and build them as I go instead of reconstructing the whole thing from here it depends on what you want to do. I'm going to call this an ira and this is going to be 6000 for the ira. Now again we have another choice here when we look at that 6000 we could have said do I want if that's the maximum could I reconstruct the maximum or calculate it or do I want to just do the data input of the 6000 so you've got these different choices you can go into here but I'm just going to put that here and I'm going to put the total down here total adjustments to income. I'm going to put that in the outer column equals the sum of the let's do it here let's do the sum of these items. All right and then I'll add more as we go so this is just going to be kind of my starting point and let's make this centered across here maybe I'm going to make it well let's just make it black and white home tab font group I'm going to make it black and white so there is that let's make the whole thing home tab font group I'm going to bold the whole thing and so that looks good I'm going to make this blue because this is going to be a data input format so I'm going to go to the home tab font group bucket drop down if you don't have that blue it's in the more colors standard there's the blue okay you don't need to use that blue you can use whatever you want but that's what I'm going to use and so there we have it and then this total is what's going to be pulled over so this is just the bare bones of it right now so we'll just do in the bare bones so that means that this zero shouldn't be populated directly as a zero it's going to be equal to what's being pulled in from this other schedule which is going to be that 6000 right here and enter so there we have it so like I say we'll get into more of these adjustments as we start to read through this and we'll we'll build up our excel worksheet more or you could add all these line items in there if you wanted to from the start but we'll take we'll just keep constructing it so I'm going to go back to the first tab so there's our adjustment so if I go back on over to the excel worksheet to page one we're now at the adjusted gross income of 94000 94000 94000 thousand then we've got the standard or itemized deductions now the itemized deductions are on schedule a so I could say okay if I had larger income higher income individuals I'd have a schedule a so I'm going to hit the plus button over here pull this to the right and I'm going to double click on the name I'm going to call it a schedule a and then let's hold down control scroll in so I'm zoomed in a bit quite a lot and then I'm going to select the triangle up top right click the whole thing format like we always do and I'm going to say this is going to be currency bracketed dollar sign and get rid of the decimals and then uh let's say okay and then I can go look at my schedule a over here I can jump to it opening up the tabs schedule a now we could add everything on the schedule a we'll get into more detail with each of these categories maybe it'd be useful just to add the different categories and then the main ones right now which are usually the things that push people over are the interest paid and oftentimes the taxes that are paid those are like the big ones that might pull people over from a standard to using an itemized deduction so let's just add the names of all of them now I'm going to say I'm just going to say let's say let's make this like this and I can just say okay this is going to be let's say uh schedule let's call it uh schedule a schedule a and then down here I'm going to say this is the medic medical and dental expenses and then I won't actually add those right now I'll just leave some space and then say this is going to be the taxes uh you paid we'll get into that later and then we've got the in the interest paid this is usually the big one because it includes mortgage interest interest you paid and then we've got the gifts to charity and then we've got the casualty and theft losses and then we've got other itemized deductions and that gives us the total itemized deductions and so that gives us our total and then so that looks good so then if I go back on over I could say all right then making this large we can let's make this a little bit wider and let's take the whole thing I'm going to embold it home tab font group bolding it I'm going to make this header black and whites let's pull it across to here and make this whole thing black and white and then maybe these headers I make black and white as well black and white black and white and then black and white and then black and white and so on to these items and then so let's just put something in like interest right now so for the interest you paid so uh let's say that we have a mortgage interest so let's say say that it's going to be mortgage interest we'll get into more detail on this later I'm going to add a few lines the way to add rows is to put my cursor on column 14 I'm going to select a few a few rows and then right click and insert it'll always insert above so there we have it and then mortgage interest so we might have multiple mortgage I'm going to say uh mortgage number one let's say let's say that's the only one we have I'm going to say this is going to be indent and then let's say this is 15 000 which will be enough to pull me over uh the standard deduction already so 15 000 on the mortgage interest and so there we have that and then I might have multiple items here so I'm going to say this is the total down below let's say this is the total total interest interest you paid which is going to be the sum of these items so I might get a little bit more into detail on on this later but I'm just going to add one item here so I can have this information pull in to the first page and then once we start doing tax returns we'll continue constructing this so this is going to be let's make this blue so I'm going to go up top and say this is going to be let's say this whole thing right here could be bordered and blue because that's where my data input is going to go there's the 15 000 let's go back on over here and mirror that so in my interest here I've got the mortgage interest let's see if I can jump to it uh mortgage interest and so I'm on the interest for the schedule a let's say 15 000 and pull that back on over boom now if I had 15 000 there I would also typically have uh taxes that are going to be applicable and oftentimes I have state taxes here that are being pulled in as well so we'll get into the state taxes too but oftentimes you have to kind of check this and double check it if you're doing itemized deductions because it'll change every time everything else changes because the state chat taxes will be adjusted oftentimes as you as you do other things so I'm just going to say we'll properly have on your taxes property taxes property taxes and then we'll probably possibly have like state uh taxes and you might have multiple state taxes but I'll just put one line on up here and this is where I'm going to get that 9 63 9 63 and let's make this blue and bordered and then I'll put here total taxes you paid outside equals the sum of these items so now we've got these sub categories that are going to be adding up I'm going to put the total down below this is going to be the total itemized deductions which I'll put down here we could have an adjustment possibly happening to them depending on the circumstance but we'll talk about that later this will be the total itemized deduction which I already have a line item here for I don't think it should be a emboldened like that let's make that I'm going to un-embolden it put that in the outer column little column a little column b this is going to be in d 32 equals the sum of everything that's in this column the sub categories and so we've basically constructed it there's the 15 9 63 tying out to the 15 9 63 so we're basically reconstructing the schedule a and kind of that means we can kind of double check the categories in here which can get quite complex because obviously these categories could have phaseouts and whatnot which we'll talk about later and the putting it into excel can help us to double check to some degree some of those calculations so we can make sense of it for ourselves make sure the data input is right and also so we can communicate what is going on to a client so now I'm going to take this one this is going to be equal to notice it's not blue because I'm not going to do the data input here it's going to be pulled from the schedule a that line so boom so now this one is picking the greater of the two the maximum now or the greater of the two now being the itemized deductions if the itemized deductions were less it would be picking the standard deduction as we saw before so if this was like 10 000 it would be picking the standard the standard deduction okay so then that's that's that now this one we'll talk more about later that gets us to our taxable income so now if I pull this back on over and I say okay if that if that happens let's just check out what we've built here 6000 that goes to the 94 000 there's the there's the 94 000 okay and then we've got these the itemized are now 15 963 so that's 15 963 which gets to the taxable income 7 8 37 is that what we have here we've got the 7 8 37 and then on page 2 I'm going to depend on the tax software to calculate the tax 12 7 83 so I'm going to say all right this one is going to be now 12 7 83 that means the average tax if I divide this out is now 16 percent so the average tax not the not the tax being applied because it's a progressive tax system being applied but if I just look at the average it's 16.4 let's add a decimal to this so we can see that home tab number will add a decimal 16.4 so that's good and then and then down below we're going to have a couple other things that we can basically break out possibly for the other credits the non-refundable credits the other taxes like self-employment taxes and then total taxes and then we'll take a look at the payments which I'll make another schedule for and the refundable portion of the credits so let's take we'll take a look at that in a future presentation rounding them out making other schedules but just the bare bones of the schedules as we did up top so that then when we encounter different items within these sub schedules we will add to the sub schedules as we concentrate and looking on on each of these items basically trying to go through the income tax equation line by line income adjustments to income itemized deduction stint and so on although because these are interrelated items we can't do that perfectly but that's the that's the general strategy that we will try to take here