 Income tax 2023-2024. Create a tax formula worksheet using Excel part number one. Get ready and some coffee because you're supporting an entire generation with income tax preparation. 2023-2024. First, a word from our sponsor. Actually, we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But that's okay, whatever. Because our merchandise is better than their stupid stuff anyways. Like our crunching numbers is my cardio product line. Now, I'm not saying that subscribing to this channel, crunching numbers with us, will make you thin, fit, and healthy or anything. However, it does seem like it works for her. Just saying. So, subscribe, hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com Here we are in Excel. If you don't have access to this workbook, that's okay because we'll basically build this from a blank worksheet. If you do have access to this workbook though, there's currently three tabs down below. We've got two green tabs with an E in front of it. That's going to be our example. In essence, the end result. And then the blue tab with a P in front. That's where we're going to be doing our practice problem, building our Excel worksheet. Let's take a look at one of these example tabs to get an idea of where we will be going. In essence, constructing the income tax formula in as clean and streamlined format as possible, this allowing us a better understanding of the income tax formula conceptually and practically gives us another source that we can do data input, having a double check to the data input on the tax return, which is quite useful even in practice, given the fact that normal individual income taxes don't use the double entry accounting system. So we don't have that internal control. Having an external worksheet that we can basically punch the numbers into can help us better understand what the software is doing and double check our data input into the software. Alright, so we're just going to basically create our income tax formula and then we'll color code it and make it a little bit fancy. And then we'll make other tabs. This will be a work in progress as we go through our practice problems so that we can have tabs that will feed into each of these line items. Let's go back to the first tab, our practice tab, and let's just look at a tax software. Now if you don't have access to the tax software, that's okay. You can look at the same form in the form at the IRS website, irs.gov, irs.gov. But if you do have software, it's a great tool to run scenarios with. So I'm going to look at a baseline tax return to just build our basic formula. So we have Adam Taxman, who's just trying to avoid the dang Taxman. And then where's the single filer we'll start off with, earning $100,000. We're just going to start with a nice even number for the W-2 income. Single filer will just take the standard deduction to start off with, and that gives us our taxable income, the bottom of the income statement, basically income statement, part of the tax return. And then we've got the taxes and credits on the second page. Now in LASERT, they give us a nice little tax summary over here, which is basically kind of like our worksheet that we're putting together in a formula basis. So we're going to mirror, in essence, this kind of format in Excel. All right, let's go on over to Excel. Now what I like to do is basically format the whole thing first. So first I'm zoomed in, I hold down CTRL, I zoomed in on the scroll wheel, currently at 295 on the zoom-in. I'm going to select the triangle up top, right-click on the selected area, and we're going to format the cells. And then I typically like to make it currency, negative numbers, bracketed and red. Don't want the dollar signs, because they just cloud things up. We don't really need the pennies, the decimals, so I usually remove them because we round things typically for taxes. Let's go ahead and say OK. I'm also in the home tab up top in the font group. I'm going to make it bold. You might not need to do that yourself, but I like to be bold because my producer is going to be bold when I'm on camera here because that's how you get views on the social platforms and what not. You've got to be bold, man. So that's as bold as we can do it. So then I'm going to make this column A skinny, and let's make it column A skinny. And I'm also going to type up here it's going to be the tax formula, let's say 2023. Tax formula 2023 and then I'm going to select from A on over to D. I'm not going to worry that it's hanging over the cell right now, but I do want to show that it's a header. I usually do that by going to the home tab, font group dropping down the bucket black and then on the lettering white. So that's going to show that it's a header for us. I'll center it later. I'll deal with that later. I'm going to put my formulas over here. I made this a skinny so I can put plus or minus. And then I'm going to say that my tax formula starts here. We start with income. Now income, there's a lot of sources of income, W2 income, interest income, dividend income, capital gain income, schedule C income. But all of those types of income we're going to feed into this line item. Now in our practice problem, we had one kind of income we've added thus far that being the W2 income. So let's mirror what we would typically do if we were going to make this in Excel putting another tab over here for that income line item. So I'm going to hit a plus button and I'm going to say this is going to be the P I'll say for income. I'm going to make that tab blue right clicking on it, selecting and if you don't have that blue right there colors, standard color wheel, I'm going to make it blue. That's the one I use just I've just gotten used to that so you can use a different color if you want but I like that color. So there it is. So we're going to say this I'm just going to type in a generic worksheet W2 data input. I'm going to make that a header. Let's format this entire worksheet like we always do. I'm going to select a whole thing again, right click format the cells, currency, negative numbers blacketed in red, no dollar sign get rid of the decimals okay home tab, font group borders, all right holding down control scrolling in so we're zooming into it and then I'm going to say let's make these two my headers. So I'm going to go home tab, font group border or black and white I'll make a a little bit longer putting my cursor between the A and the B making that a bit longer so that people can type in the W2 income right so this would be job one 100,000 we're going to say now they might have multiple W2's if they're married they could have each worked like three jobs right so let's say 1, 2, 3, 4, 5, 6, 7, 8 let's just leave like that much for W2 and then say total W 2 income income and so I'm just going to sum this up then I'm going to say all right then the total W2 let's put the total on the outside equals the sum of all of this okay and then I'm this is my data input range I like to make my data input like blue so I'm going to right click and say let's make this format the cells and make it let's do it this way home tab font group bucket blue and then home tab font group I'm going to put borders around it so now we've got that income has flown through and then I'm just going to put my total income total income down here there's only one thing in it I'm just going to sum up what's above it that 100,000 that's all we have in it thus far but we're going to have more income line items like dividend interest and so on later so this is a work in progress the point is that I'm making a different tab that's going to pull into my parent tab over here so this income line item I'm going to say is equal to what's on this tab the total of all my income so later on that will include possibly multiple different things on this worksheet and possibly other worksheets like a schedule C schedule E which I will put on their own worksheet and what not or I'll put dividend income and what not possibly on this worksheet so it might not tie exactly into the tax return system that's in essence if you look at the tax return that's kind of what they're doing in that the income line item all of this stuff is on the face of the statement which is kind of funny but they kind of put the interest on a separate tab if it's over a certain dollar amount that's going to be schedule B that flows into the formula that's right and then you've got the schedule C which is a separate worksheet that flows into the schedule one and then to the form 1040 so we're not going to mirror like the schedule one the schedule C to the schedule one we're just kind of but we're going to make a similar format in that we'll say that the 1040 is is the parent or the summary and then we'll make a separate worksheet to break out the detail and that'll again that'll be we'll try to make it a combination between being simple for the data input so I can double data input and but also complex enough that it helps us to give us that double check of the data input right which is a tricky kind of balancing act okay so then we're going to say that's the income line item the next line item is going to say I'm going to say minus in my cells it's going to be the adjustments to income and so I'll make this one a little bit larger be a little bit larger and then this is going to pull from another worksheet which I'll say is adjustments to income but I don't want to add that worksheet just yet so I'm just going to put zero here because in our example problem there were no adjustments to income so so I don't have anything in that line item so as we do we will build another worksheet and pull it in like we did with this one but for now I'll leave it as is home tab font group underline that's going to give us equal to I'm going to say equal it's going to try to say a formula but I'm just going to say tab so it doesn't do a formula that's going to give us the adjusted gross income otherwise known as AGI I'll make column B a little bit larger and so it's going to be here this will be equal to the 100,000 minus the adjustments so there we have that and then we're going to subtract out the greater of greater of boom so by the way where we add in our here we're right here so now we have the adjusted gross income we have the adjustments to income up top that I name it the same adjustments to income or the above the line deductions you might call them sometimes schedule one deductions now and then and so now you have the greater of the itemized or standard deductions okay so the greater of colon subtotal either the itemized deductions I'm going to put those two on an internal bit right here a sub category that's what the colon means it means there's going to be I'm going to pull these inside and then have a total outside which will take the greater of the two in essence so itemized deductions or the standard deduction and then the standard deduction I'll put a little asterisk because it's going to be coming from a worksheet now the itemized deductions let's put it let's indent this as well home tab and then alignment and indent it because it's going to be one of one or the other of these two the itemized deductions come from the schedule a so we will create another worksheet for that but we don't have anything in there right now so I'm just going to put zero we will continue to build this later the standard deductions is going to come basically from a worksheet now you can get this in essence from this table right here here the standard deductions for single married head of household so these deductions tie into the filing status so I can put a little worksheet down here and then again you can get more or less sophisticated with this but I'm going to put it down here on let's put it just for now on 21 I'll put it down here and say these are the standard deductions colon and I'm going to say if they were single or married filing separate which is NFS that's the shorthand for married filing separate then let's put that could I make this a little bit wider let's put it like over here 13850 that comes from our table so 13850 alright and then this is going to be head or let's say married filing joint and that's often abbreviated as MFJ and that's going to be you could think of it as this hold on a second this is not right 850 and then this is going to be that times 2 27700 that helps you to kind of memorize it if you can memorize this number then it's doubled for married and then we can say head of household often abbreviated HOH is going to be in between at 20800 so that's the 20800 now if you're filing a 1040 SR they have these added brackets down here for single married so if certain conditions are met which we'll talk about later but I'll try to put a little thing in here so if they say over 65 or blind for I'll put this up top married filing joint single or head of household then you have another 1850 and 1500 per person that's over 65 if married filing joint or blind we'll get into that basically later but that's our little worksheet so I'm going to put this let's make this black and white home tab font group I'll make this black white I'll center it and then I'm just going to put tables around this home tab font group let's put borders around that let's make this black and white home tab font group black white there we have it so that means that that when I look at the standard deduction I'm going to change this based on the table now we can try to automate this you can say well if it's single it'll populate automatically but I kind of like the physical necessity of saying equals and then going down here and saying okay what's going on here it's the single status that helps you to determine that's helping me determine okay it's a single file or that's why it's at the 13 850 and that ties out into our worksheet here where it's the 13 850 so then that when we say okay well which one is it going to take between these two we want to take the larger of the itemized or this one so this will be a data input in here and this will be a formula this is a formula we can use called max meaning take the biggest one that's what that formula means pretty basic formula but sometimes it's hard to remember because it's not used as often but take the max of those two and so it takes the 13 the 13 850 in this case I should probably pull that down here and then say this is going to be so then and I could just say this is the greater of let's say just a greater of and then I'll indent that two times home tab alignment indent that two times just so we have a subtotal pulled it inside we pulled basically the result down here which is taking the greater of these two so that's going to give us then our that's going to be equal to then our man it did a formula enter and then that's going to give us our taxable income so taxable income which is going to be equal to the 100,000 minus the greater of the itemizer standard deductions so that's the 86 150 which ties into the 86 150 here so that's the bottom of like the income statement which we usually do our data input and are focused on with our data input side of things the second half of the calculation is the tax so we go back on over and say okay let's say we're going to take that times in essence I'm going to say times with an X times the the tax rate let's call this the average tax rate now notice we don't actually multiply times an average tax rate what we do is we we apply those tax brackets so but I'm so I'm going to back into this number I'm going to let the software now do the calculation so I'm going to say this equals the tax before credits and other taxes so that I'm going to let the software do that calculation let's look at the the formula this time so we're down here total credits this is standard deduction larger taxable income tax before credits so that's going to be the 14266 so I'm going to say alright I'll let it do the calculation 14266 now once I have that I can back into the average tax rate so I'm going to say okay what's the average tax rate well that's the 14266 divided by the taxable income making that a percent home tab number group percentify to recognize and if I pull back on over here that's going to give us the 16.6 let's add a decimal so we can see it add a decimal home tab 16.6 right and then after that we've got I'm going to say minus other well that's not right other credits so now we've got basically the below the line credits I'm just going to put a zero in here for now we'll talk about the credits more credits later so we'll add more worksheets to feed into that line item later and then we've got other I'm not going to underline at home tab underline plus this should be tax before credits and other taxes and then minus other credits plus other taxes I've kind of mixed up my capitals and non capitals here other taxes and that would be like self employment tax we'll talk more about that later and then I'm going to put an underline here and say that's going to give us equal to enter our total tax so the total tax is going to be equal to this tax that's the tax that we owe and then credits will reduce that so credits are good because it's going to reduce the tax that we owe and then if there are other taxes I'm going to say plus like self employment tax for example we'd have to add that which would be bad in this case and so that's going to be the 14 14 to 66 again and then we're going to say minus the payments and refundable credits so now we have credits that are those refundable ones which means your liability could go below zero and the payments that you make so let's in our example problem over here let's for example say that we withheld on our taxes we already have 12,000 so 12,000 so if I go into the first page, second page here there's the withholdings so I could add another worksheet called payments right so we could say ok let's go over here and say we see that in our worksheet so I'll add another one and this is going to be we're pulling that to the right our payments and credits I'm going to make that one blue right click formatting it to make it blue and then I'll select the whole thing again right click format we're going to make it currency negative numbers bracketed and red no dollar sign no decimals and then ok font group bold hold down control scroll in so I'm going to label this one up top I'm just going to give it a name payments and refundable credits which is going to feed into that line item so I'm going to make A a little bit larger and then I'll select from A to C make that a header by going to the home tab font group black and white and then I'm going to put the most basic kind of payment which is a W2 W2 with withholding and so I'm going to make that black and white home tab font group black white now we could have multiple W2s again so you could have a married couple one they could have three jobs each right 1, 2, 3, 4, 5, 6, 7, 8 right so I'll put the total down here total W W2 with withholding and say this was Job 1 12,000 12,000 and I'm going to sum that up over here equals the sum of this I'll make this my data input area home tab font group I'll make it borders drop down on the bucket make it blue and so there it is and that's going to give us our I'll put it right down here total pay payments and credits and I'll just sum up the outer column there's only one number in it right now but we'll add more to it later back to my first tab and I'm going to say this is going to feed in from this number right here so there's our there's our 12,000 and so then if I go back on over to my tax formula that's going to finally give us equal to the tax do or I'm going to put brackets around it refund refund because a negative number would be a refund it's going to be a positive number in this case so we still owe taxes now note you could also have penalties so we'll talk about penalties the software might try to calculate later but that's the general idea so we get to here there's the 12 there's the 2308 and then there's a $43 penalty so if I put the penalty of 43 of penalty penalty T and then say okay now it's at this plus that 2309 so 2309 okay and let's check the spelling because I think I misspelled that penalty and single I misspelled it okay alright so that's the general outline so we're running kind of long so I'll stop it here next time we'll make it a little bit more fancy adding some colors to it but that's the and then of course as we have more complexity in the return we will add more schedules to the right that will feed into this income tax formula