 Income tax 2022-2023. Create a tax formula worksheet using Excel part number one. Let's do some wealth preservation with some tax preparation. Here we are in our example form 1040. We're using Lassert tax salt to populate it. You don't need tax salt to follow along, but if you have access to it, it's a great tool to run scenarios with. You can also get access to form 1040 related schedules and forms at the IRS website, irs.gov, irs.gov. We're going to be using this information to construct an Excel worksheet and put our income tax formula within the Excel worksheet. So note, if I look at the form 1040, the part up top is basically informational. The items related to the filing status and related to dependence will have an impact on some of the calculations down below. We're going to be using a single filer at this point with no dependence. So we'll start with a fairly basic scenario. Then we have in essence our equation down below the income line items. And then we've got our basically the support accounting instruction in 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. The deductions, the above the line deductions and the item eyes deductions and standard deductions or the higher of the two to get down to our taxable income. And then online to we have the actual tax calculation. Then we have other taxes and credits that are involved to finally get down to the amount that is refunded or the amount that is due. So you can see the actual 1040 is quite a long document. And if you were to actually read through it all, it would be referring to other documents and so on. And it would kind of walk you through each line. We can look at basically each line in the instructions to get further detail of it. But obviously when we're thinking abstractly, that's way too large of a thing to be keeping in our mind. Also note that if you were to construct this form 1040 from scratch, as if you're just going to build it today, then you would probably construct it more like an Excel worksheet, in which case you would put the bare bones on the form 1040 and have everything else on some type of schedule. And they've done that to some degree here. As we can see fairly recently, they broke out the schedules one, two and three that give more information, which we'll talk about shortly. But we also see that like this income line has a whole bunch of stuff on the face of the form 1040. You might want to ask, like, why? Why would that be? Why don't they just put that on another schedule? Many people don't have all this stuff involved. A simple tax return is just going to have maybe W2 income. The tax return would be a lot smaller if you just had like one income line and everything else coming from somewhere else. And one of the reasons it's built the way it is is because, of course, it's been changing throughout history. Before we had electronic filing, it was thought that you want to have just one page of a tax return would be the easiest tax return for most people. Therefore, they try to put everything on the one page and not have any other schedules. And then we had different forms for people that had more complex tax returns. Now that we're online and people file online, other schedules are not a big issue. And it makes a lot more sense to try to streamline the form 1040 to a summary page and then have everything else somewhere else. But obviously, we're not there at this point in time because we're building based on what has happened in the past. But conceptually, that's how we would basically visualize it. Most tax software has a tax formula kind of in it. So if I go down to here to the tax formula, this gives us the tax return and basically a tax formula outline. So this is basically what we're going to reconstruct in Excel. You might ask, why would I reconstruct it in Excel? There's a couple of different reasons. One, I think just building this in Excel helps us to visualize what we are doing. It's something that you can use as a double check to the software. The software as you do data input into it doesn't have the thing that I kind of like to go into school with to give me some assurance because sometimes I had a little bit of dyslexia, like I switched numbers around sometimes. And so the double entry accounting system, I really liked it because it gave me a double check that when I messed something up, I had to double check. That when I messed something up, when I turned a number around, I can see it. I can find it. I can fix it. We don't have that with the taxes because we're just looking at, in essence, an income statement. We don't have the balance sheet. There's nothing balancing. Therefore, how do you double check that you didn't do a data entry error, which I used to be just terrified of because I do them all the time? Well, then one way you can try to stop that is you could basically make an Excel worksheet and do the data input fairly quickly in an Excel worksheet. It also helps you to kind of see what the tax software is doing so that you can explain what the tax software is doing to a client. Because remember, if you're doing taxes as a profession, you don't really want to say, well, that's why the tax software spit that out. That's why it happened. What you're supposed to do is you want to use the tax software, be honest about using the tax software, but also be able to explain the rationale of what the tax software is doing generally. Not just, well, that's the answer because the answer key said that that's the answer. We want to add a little bit more detail in that. So those things are helpful as well. So let's just kind of reconstruct this. What I'll do is I'll build this in Excel and then I'll add to it as we go through the different components of our practice problem going through basically the accounting equation from income to deductions and so on. And we'll add more schedules to it each time. So right now I'm just going to add like the bare bones outline what I'm doing blank Excel sheet. I'm just zooming in. So I'm going to zoom into it a bit down here. I'm also in dark mode, by the way, so your ribbon might look a little bit different. But in dark mode, if you look, if you work on a lot of tax returns is easier. It's the Microsoft office has a dark mode, so I kind of like it. So I recommend it better on your eyes, they say. All right, so I'm going to scroll in. What I'm going to do is I'm going to format the entire thing in the format that I wanted to be in in general. So I'm going to put my cursor on the triangle. You can also hit control A. I'm going to right click on all the cells and format them. And I usually format them to be I like to go to currency. And then oftentimes I make the negative numbers red and bracketed. But right now I'm just going to make them black and bracketed because I might do some stuff with the colors. And I don't want the red numbers to kind of mess me up if I want to make the text color a little bit different. And then I'm going to go to the dollar sign. I don't want the dollar signs on everything. I'm going to remove it. I'm also going to remove the pennies because when we talk about taxes, we're usually rounding. So I'm just going to take it to the closest dollar because that's what the tax software does as well. That's what the tax return generally does. So that's what we will do. And then I'm just going to basically the top part of our formula is just kind of an income statement. So I'm going to the way you make these a little bit wider is you put your cursor between column A and B. So it looks like that, not like that, but like that. And left click and I'm going to drag it to the right to give us a little bit more space. And actually I might, well, let's just put my formulas first. So it starts off with income. So income is the top line. We're basically making an income statement here. And I might want to put signs in here as well just to indicate the direction of something. So I'm going to put another column before column A. The way to do that is I can select the entire column A by putting my cursor on that little drop down, right click and insert. And that should insert always to the left. So I'm inserting. I'm going to make this a little bit smaller, dragging this on down. And the next line I'm going to say is going to be minus what you might call above the line deductions or adjustments to income. Adjustments to income. And so I'll make this a little bit larger so you can see it. So if I look at the forms here, income is all of this stuff that's going to be summarized down here as total income. All of this stuff up top, as well as some of the other stuff on schedule one and whatnot. I'm just going to put all of that on a different tab. And then we're going to have the adjustments to income. And those are going to be the items on schedule one. Those are like the above the line deductions. So I'm going to put those here. I'll just put that on one line item. I'm going to make the whole thing bold because I think that might help you to see it. I'm going to select the whole thing, make it bold. You might not need to do that yourself, but I think that might help to see it. And then I'm going to say this is going to be equal then to a subtotal of adjusted, adjusted gross income. Why is it gross? Because it smells smelly. I don't know. It's not really gross. It's just a gross income. I'm going to make this a little bit wider. You can double click on it to make it a little bit wider if you want. And then over here, so there's the AGI. So adjusted gross income. And then you've got the standard deduction or the itemized deduction. So I'm going to indicate that clearly in my formula by saying, okay, then after I get to the subtotal, I'm going to subtract. I'm going to subtract the greater of brackets, the itemized deductions or the standard deduction. And then the standard deduction, I'll put a little thing down here to indicate that I'm going to show those standard deductions on down below. So I'll put a table after I'm done with my formula of the standard deductions where I will pull from. You can see kind of a summary of the table right here. Those are the standard deductions, which will be dependent upon the filing statuses, for example. So then I'll go back to that in a second later, but that's going to give us the greater of those two. And then I'm going to add in this added thing, which we'll talk about later when we get to the business stuff. Schedule C and other business income, qualified business income deduction. You can see they kind of dumped this one in here. So sometimes we don't talk about it with the standard formula, but if you have a schedule C, they kind of squeezed this line 13 into the formula. That was kind of a mess when they tried to simplify the code. They did a good job for a lot of things, but then they kind of plugged up some holes with this adjustment here, which is a little bit, which is quite messy. You think there'd be a better way to fix that, but that's what we have right now. And then we've got minus, so that's the qualified business deductions, and then that's going to equal then the taxable income. And so that means that's going to get us down to here, the taxable income. And then if we're on page two, you would think we're done, but we actually got a ways to go from this point in time. So I'm going to go back on over and say, all right, what happens next? We were going to say multiply. You could say I'm going to say like as if it's a multiply, but it really comes from the tables times the average tax rate. Now, we don't really multiply times the average tax rate. We multiply by the tables. So in practice, what will happen is I will double check this number reconstructing it in that's being the taxable income in Excel to double check that I didn't miss key anything. And then I'll be more dependent. I'm not going to recalculate oftentimes the taxes because I'm going to rely on the software to do that due to the double entry account. I mean, due to the progressive tax system being quite complicated and having other things involved like different tax rates for passive income or different tax rates for capital gains and different tax rates for dividends and this kind of stuff that could kind of throw everything off. So but then you think we'd be you think we'd be done. That'll give us that'll equal then the tax before credits and other taxes. So now we've got the tax before credits, but we're also going to have other taxes that might be involved. So now I'm going to say minus other other other minus other credits credits. So credits are going to reduce the amount of tax and then plus other taxes other than the income tax social the self employment tax being like a big one. And that's finally going to get us to the total tax. And then we're going to compare that to the payments that we made payments and the refundable fundable credits, which will finally get us to the tax due or refund. Okay, so let's populate this with this basic scenario that we have right on our tax return thus far. So this is our tax return. Let's see if we can kind of reconstruct this single filer and no dependence 100,000 W2 income. So that's what we will build. So I'm going to go back on over here and say, okay, on the income line item, there's going to be 100,000. But instead of me putting it right here at 100,000, I'm going to make another tab. So this is going to be the the I'm going to double click down here to rename the tab double click on the tab. I'm going to call it this is the tax form you love it's like the form 1040. And then I'm going to add another tab and this is going to be my income. So I'm going to call it income income. But this is on the form 1040 the income that's basically on the first page of the 1040. So I'm going to make another tab for this whole section of income in essence. So if I go back on over, I'm going to say, all right, let's format this cell. It's just like we did before. I'm going to hold control zoom in. I'm at 250 on the zoom in put my cursor on the triangle or control a right click. And I'm going to format and I'm going to say this is going to be currency. I'll make the negative numbers read this time and bracket it. I probably will not have negative numbers. Get rid of the dollar sign, get rid of the decimals. And there we have it. I'm going to say OK. And the major income categories here I could start to add from here. So the first one is the amounts from the W to income. So I'm going to say, all right, W to W to income. And then I'm just going to leave some space. I'm going to make this larger and then I might have multiple W to. So I'm just going to make a data input space fairly long that I can pick up the W to maybe down to here. I'll make it like blue. I'm going to go to this blue and I go to the standard color. I usually make this blue for my data input to indicate I'm going to put data input into here. And then I'm going to go to the home tab font group brackets and I'm going to put borders around it. So this will be my W to stuff. And then I'm going to say this is like W to for the employer number one or something like that, which I'm going to say is 100,000. And so there we have it. There's the data input and this is going to total up down below. So I'll put the total down here, which will calculate automatically. I'll put that in the outer column. This equals the sum of all of this stuff. So now I can put this many W to is in place and just put the dollar amount and it'll sum up automatically to 100,000. I'm going to make this whole thing. Let's put the whole thing making it border and blue. So that's going to be my total, let's say W to income. And so then I'm down. And so now I might add other kinds of income later, but for now this is my total income, my total income for form 1040 on the first page of the 1040. So if I sum this up, I just have the 100,000. That's it because that's the only thing I have. And I'm going to pull this page then into the first page of the form 1040. So if I go back to the form 1040 and I'm going to put my cursor in D1, I'm going to pull D1 from the second page, kind of like on the tax return, pulling from a separate schedule. So I'm going to say this is equal to the prior page, that number, not this one, but that one because I'm going to add more to it later. And there's our 100,000. And then I'm going to say the adjustments to income, adjustments to income are currently, I have zero for it. We'll talk about adjustments later. I am going to tie this to another place, another page later, as they do over here on our actual tax return, where we have the adjustments being tied to schedule one. So we'll do that later. And then I'm going to underline it, font group underline. And then this is going to be equal to then do a formula, the 100,000 minus the adjustments to income, which are zero right now. So the 100,000. Then I have the greater of these two. I'm going to indent these two to show that they are subordinate to this colon here. So I'm going to go to do that. I go to the home tab alignment and let's increase the indenting with this one increase so that gives it a little indent. And then I'm going to say I'm going to pull these to the inside and say I either have itemized deductions. I'm going to show these itemized deductions coming from another tab later. But for now, I'm just going to put zero or the standard deduction. Now the standard deductions, I'm going to put down in a little table down here so we can see them. I'll put them a little bit low, a little bit down here. And I'm going to call these the standard deductions. And I'm going to pull this from basically this information. If you're single 12,950, married filing joint 25,9, and so on. So I'm going to say, all right, the way I would remember this is single, single or married, married filing. Separate, that's going to be, which is married filing at separate like that is going to be, then let's put it right here. 12,950. And then if married filing joint, if I misspell anything, I'll check the spelling shortly, which is shorthand for MFJ. Usually it would be equal to this times two, you would expect, which it is because that makes sense. So there's the 25,9. And then if they were head of household HOH, then it's 19,4. And then if there's other kind of added components to it, if they're over 65 or blind, then you could have an added 1,400,1750 will get into those details later. This is for married filing joint and single, single head of household. So I'll make these two, I'll make, I'll make these two like black and white home tab font group, making it black and then and then white and I'll center it. And then let's make this thing table. I'll put a table around this whole thing. Not a table, but font group borders. Let's put some borders around it. And so that's what I'm going to use. I'll make this a little bit wider to see that one. And maybe I could indent these going to the alignment and indent and then make this one a little bit wider. That might be messing stuff above, but there it is. So then if I go up, I can say I'm going to pull this information then from my table down below. So I'm going to say that the standard deduction is for single, which I spelled wrong right there. I'll check the spelling later. That's really bad though. Single. That's not how you spell single. Single. Oh my God, your fingers. My fingers are wrong. Single. Okay. Okay. So there it is. Then I'm going to put an underline here, home tab font group and underline. So there we have that. And then I'll pull the larger of the two over onto this side. Now, right now I'll just pick the larger of the two, which is this one later. I'll put a formula in, which will be an if formula or take the, take the larger of the two. So we'll talk about a formula to do that later. And then we'll get more into this one later. So that's going to be applicable if you have like a schedule C business oftentimes. I'm just going to put a zero for now on that one. And then that's going to be the, the taxable income taxable income is going to be equal to the 100,000 minus the greater of the standard or itemized deduction minus the qualified business income deduction. Boom. So 8750. That's the taxable income 8750. So we've recalculated this number. I'm going to go back on over and say, all right. And then the average tax, I'm not going to calculate the average tax, but rather use the software to calculate the tax based on the tables, which comes out to 14774. So I'm going to say, okay, this is 14774. I'm going to pull from the table and then I can check the average tax by just doing this is going to be equal to the tax before credits and taxes divided by the taxable income. And that gives us, if I make this a percent home tab number percentify 17%. If I go back to my tax formula, you could see the average tax 17%. So that allows us to like back into the average tax. And then we've got, if I go back on over, we've got other credits. We'll talk about that later, making new tabs to add those credits. We've got other taxes such as the self-employment tax, which we will add when we get into more complex scenarios on the like self-employment tax schedule sees, for example, total tax. Let's put an underlying home tab font underline. This is going to be equal to then the tax before other taxes and then other credits are going to be lessening it. Other taxes are going to be increasing it. So here's the tax what it was before minus the credits plus the other taxes that still gets us to the 14774. And then we had payments and other refundable credits, which we'll add another page for later. We don't have anything now, which finally gets us to the tax due or refund. This is going to be equal to the total tax minus the payment. Okay. And so at the bottom of the day or the bottom line, we got to the, to the, we have a penalty here, but basically before the penalty 14774. So we got the 14774. Let's do a quick spell check on it. I'm going to review and edit it and income is wrong. You started it off wrong on the first. Okay, but then you did pretty good. All right. So, so later we're going to make this a little bit more fancy. So notice we kind of just populated whatever we took the income from another cell, but then we just populated the standard. We took that from another cell, but we just populated some of these other items where as we get more complex returns, we will add more tabs, which will reflect a similar process as adding schedules. We'll also make it a little bit more fancy to try to indicate which items are going to be data input items, which items are going to be formulas. We'll put some colors into it as well. And we'll do a little bit more complex of a formula over here, possibly to pick the greater of these two items. I'll make this a capital and so that's what we'll continue with next time.