 Income tax 2022-2023. Create a tax formula worksheet using Excel part number four. Let's do some wealth preservation with some tax preparation. Here's our example Form 1040. We've been populated with LASERT tax software. You don't need tax software 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 the Form 1040 related forms and schedules irs website irs.gov irs.gov 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. We've been looking at the Form 1040 and constructing an excel worksheet that we can use to double check the data input with and better understand a formula format for the income tax forms so we've been entering the information starting with the income statement basically reflected on the first page which is income and then the deductions and then we get our taxable income kind of like the bottom line of the income statement and then on page number two we're going to have the tax calculation other taxes credits and payments so that's where we are in our formula down here so the top half we've been looking at which is going to be the income line and then we had the adjustments to income and that gives us our adjusted gross income then we have the greater of the itemized or standard deductions and then we're not going to dive into the qualified business income that's if you had a business a schedule c we'll talk more about that later and that gets us basically to our taxable income so this one actually kind of makes intuitive sense just from an accounting standpoint we're basically doing an income statement although the format of the income statement is quite complex because it's quite weird because it's quite manipulated from the tax code not just including those deductions you would expect to be there in a normal income tax system which would be one or deductions which were used in order to generate the income that's what you would expect to be kind of like the natural deductions but we have all these other kind of weird standard deductions and above the line deductions for whatever other incentive or tax purposes that they wanted to put those in place for so but in any case that's kind of normal we can reconstruct that the tax calculation as we saw is complex because it's going to be based on the tax tables so we'll actually pull that from the tax return oftentimes and then the bottom half you would think we're almost there once we get to the tax calculation but no because there could be a significant amount of credits which we have to break out between refundable credits and non-refundable credits and then we have other taxes that might be in place such as self-employment tax for example that we have to deal with and we've got the payments and refundable credits that we have to deal with so let's go back up and let's kind of make it a simplified scenario again and then we're going to basically make our sub schedules with relation to the to the other credits and other taxes to have these numbers pulling in from somewhere and then we'll build on to them in future presentations as we do our practice problems let's go back to just basically a basic scenario on the top half of the income statement to start with so we've got income and so i'm going to get rid of this adjustment to income here so let's get rid of this one i put that over here on the adjustments i'm going to remove that i'm going to put a little border on this let's put a border here and then i'm going to remove that so now we're at 100 000 let's get rid of the schedule a stuff too so i'm going to go to the schedule a and we have that up here so the taxes i'm going to remove these for now bring that back to zero so nothing's on the schedule a if i pull that into the first page now we're taking the 12950 which is the standard deduction so if i go back on over 12950 that gets us to the 87050 so if i go we're at the 87050 and then the tax i'm going to depend on the software to calculate on page two would then be the 14774 let's start there uh 14774 and then we've got the other credits so let's just add another tab i'm going to add another tab now it gets a little bit messy with the other credits because you might have multiple different forms so if you're constructing your tabs in excel you might make like another tab for each form or you might just make a general tab that's going to be comprising most of these other type kind of credits over here also note it gets complex with the credits because we have the non-refundable credits and the refundable credits and we might have uh credits many credits that are refundable meaning they can take the tax liability below zero so you're actually getting like a refund or benefit program even though you didn't owe in paying a tax uh many credits that have that component have a non-refundable and refundable component to them which makes the calculation more complex because then we got to think about well okay if that brings the tax down to zero then it's not eating into the to the refundable component and different tax credits are structured a little bit differently so that muddies up the water as well with our calculations so let's just take a look at one over here so a common credit would be the child tax credit so let's just add that one now note again that's on schedule 8812 so let's just add that to do that i'm going to add a dependent and so i'm going to say let's add a dependent and just see what that looks like so i'll put a sam anderson sam anderson and we'll say this is relation i'm going to say so there it is that should do it so if i go back on over page one then so now we have single now note that if we have a dependent that might move the filing status up to a head of household but i'm not really focused on the filing status right now i'm really just focused on the the dependent status let's actually move that up let's just play with that we'll say okay that means that we're going to go from single possibly to head of household and let's go back on over so there we have that and so let's just see what that does to the page one so now we've got sam anderson it's going to be have a credit we'll talk more about the credit later but just to get our formula down we still have the hundred thousand and then we've had the standard deduction is now within 19 four and that's because we're going to say that it was boosted up to head of household so we'll talk more about this the those later but head of household boom and so that gets us then to the eighty thousand six hundred eighty thousand six hundred page two then has the eleven eight fifty five as the tax so let's put that down here eleven eight sixty five was it eleven eight fifty five eleven eight fifty five okay and then on the credit side of things we've got the two thousand for the child uh credit so you could see kind of a worksheet on how the calculation would be if there's a phase out and whatnot so you have a choice then on your side do i want to add it as another schedule like the form eight eight one two here's your kind of calculation for it but it's a fairly standard kind of credit so the other option is that i i add it as just basically a general credit worksheet in my excel worksheet and then do i want to kind of recalculate the phase outs in my excel worksheet or just basically populate it at you know two thousand for the general number and then adjust it uh as needed basically depending on the software so i'm going to do it kind of generic here i'm going to go back on over and say this is going to be another tab and i'm going to pull this all the way to the right and i'm just going to double click on it and just call it other credits and so i'm just going to make a generic one i'm not i'm not calling it the name of the form because i might put more than one kind of credit here i'm holding control zooming in and i'm going to put the title up top i'll just say other credits i'm going to i'm going to select the triangle for the whole thing right click and format this as we do every time go to currency bracketed numbers no dollar sign i'll get rid of the decimals okay home tab font group make it bold and there we have it let's make this black and white as the header so i'll go up top and say this needs to be let's make it black and white for the header and then i'm just going to include the the child tax credits let's just say here child tax credit credit now you could you know base this like i could calculate the number of dependents and then try to figure out what the credit would be and and work in phase outs i could make a quite complex worksheet we might dive into that a little bit more when we get into the credits or i can just say hey look i can i'm just going to realize that there is a credit and i'm going to be able to start to assume that's going to happen if there is a dependent and so i can imagine what's going to happen and then i know it's going to be like 2000 unless they go over an age limit the general rules and if there's a phase out i might be be then dependent on the tax return to some degree to help calculate the phase out and then go in and try to understand it to double check that it's doing what i think should be done properly and so that i can explain the phase out to a client right what it's actually happening so i'm just going to do it that i'm going to make this blue for our data input so if you don't have that blue it's down here standard i'm going to make it that blue and then border it and then i'm just going to say total other credits now i might put more credits in here but now i've got my worksheet where i'm just going to have my first thought process of where i'm going to add the credits which is going to tie into my first line item in here so this this credit line i'm going to say this equals other credits that number this number might change as i add other types of credits but now i can just see how everything's being fed in to this one form so if i go back on over there's the 2000 that brings the tax to the 9855 so if i go back on over we could say okay the 9855 that looks correct okay so now then let's pull this over here so then we've got these other other taxes so one of the big other taxes will be like a self-employment tax so i'm going to make a change that's going to have a big effect on a lot of different things i'll put in some income into a business income a schedule c and that'll shake things up a bit so we'll go over here and i'm going to go let's go income and i'm going to say we have a schedule c income and i'm just going to make up a schedule c income amount see if i can just dump in uh let's put let's put 60 000 here and then down here i'll just put uh 20 000 as an expense just to see the calculation on a schedule c so if i go over to the schedule c which we'll dive into later now uh obviously i didn't add everything to it but i'm just kind of showing what's going to be populated here an income statement on the schedule c 60 000 minus the 20 000 gets us to the 40 000 now look at everything that happens once i do that that 40 000 is going to be pulling into the first page of the form 10 40 uh but here it is and it pulls into schedule one as well which we'll take a look at shortly but just note that that 40 000 is is not um the the gross income it's the net income so remember when we look at our formula here i just want to point out that we've got income which includes a bunch of different kinds of income and then we've got the above the line deductions or adjustments to income and then the itemized or other standard deductions but this income line if i'm pulling in some items there might already be expenses kind of kind of applied to the schedule in other words like when i pull in the net income in essence from a schedule c i already got all the deductions which are the business deductions on the schedule c the same would be true for like a rental property uh similar kind of thing for for when i sell stuff for capital gains like stocks because the cost of the stock is kind of like a deduction you can think of it kind of like so so so that's something i just want to kind of point out here so i'm going to add another form over here and let's add it to next to the income line so i'll make another schedule for a schedule c to add that so i'm going to add another uh form i'm going to pull it next to the income for the 10 40 double click on it i'm going to call it schc and i'm going to scroll in i'm going to highlight the whole thing and say right click and format the sales and i'm going to say it's currency bracketed no dollar signs no decimals okay and then this will basically be kind of like our income statement let me just check did i put sometimes i didn't i put this over here on other credits on line seven i'm going to remove these cells above it or the rows above it because i do that sometimes when i zoom in too fast so i'm going to put my cursor on one to six right click and just delete and that should pull it up to the top let's do that over here this time i'll start on the top and i'll just make a mock income statement now if you were to populate your business schedule c income we'll talk about it later how would you get that data you can get it from like a quick books or something like that you might not repopulate the whole income statement on your worksheet but possibly just summarize it here on the worksheet so i'll just do a quick kind of summary of it so i'm going to say we have income i'm going to make this whole thing bold and then we've got expenses expenses so the income line i said was 60 000 we might have and then the expenses and then ah man i'm over here on column d really i'm going to delete these columns what in the world expenses and then i think i just put something in advertising advertising okay so let's pull the income out here actually i'll pull the income out to the outside actually let's put it in there and then i'll have total income in in case i have multiple income line items it's probably only a couple i'll say total income and then i'll sum that up in the outer column like so so now i've got this is my data input right here i'll make that blue and then it'll sum up in the outside and then my expenses will be down here i just got advertising and then i'll have total expenses total expenses and this will be the sum of this and then i'll say that was 20 000 and we'll get more into this an income statement a schedule see later but just to put something in here so we can pull this in and this will be net income which is the taxable line item which is the 60 000 minus the 20 000 so that 40 000 then is pulling in to the first page in this income line so now i've got two cells here two sheets that should be pulling into that one income line so i'm going to double click on this one go to the end of it say plus and then point to this schedule see and the net income and i'm going to pull that in so now i've got 140 in my first line which ties out to this number and now i've got the adjustments to income so let's take a look at some of the other things that are affected by this whole schedule see which will dive into later so i'll just do a quick overview of it because it it adds a lot of complexity to things so and i'm not want to dive into it a lot of detail i just kind of want to mirror what the system has put in place right now so the 40 000 uh is here and that pulled into the income line item it's also pulled into schedule one right there and it sums up on the schedule one here and then it pulls into the first page of the form 1040 as we saw right there which is included in the total income so we also have the self-employment the the tax so this is self-employment tax similar to payroll tax but for the business where you have to pay the employee and employee or portion in essence of the self-employment tax so that comes out in essence if we calculate this whole thing out i will i will get into the calculations more but it comes out to 5652 that goes to the second page of the form 1040 that's kind of where our focus was which is right here right there so that's our added tax so let's first do that and then we'll look at the next consequence so i'm going to go back on over and say okay this other taxes i'm going to make another line for that now again i i could make multiple lines i could call it like a schedule s e self-employment tax or or i can just call it generically you know other taxes and possibly put other things in there other than just self-employment so i'm going to make a fairly generic one just call it other taxes for now i'm just going to say plus pull this all the way to the right and right there double click on it i'm going to call it other taxes and then i'm going to select i'm going to scroll in and then i'm on a one this time and i'm going to say we have other times say other taxes i'm going to select the whole sheet right click format the cell currency bracketed no dollar sign no decimals okay bolden the the whole thing make this black and white home tab font group black and white and then i'll put the taxes now i could i could pull this calculation in from the schedule c for example and calculate the the taxes i can recalculate basically this whole uh this whole kind of calculation in excel but i don't want to get too detailed into it right now we might do that later and see the pros and cons of that exercise but right now i just want to recognize that if i have a schedule c what's it going to do this helps me to just see what the the software is doing applying the tax code i'm just going to call this self uh employment employment tax i'll put that in this outer column over here because i'm not going to do any sub calculation to it and it comes out to two it comes out to the to the five six five two that's social securing medicare we'll talk more about it later five six five two and five six five two and so then the total i'm going to say down here the total total other tax i'm going to put in the outer column equals the sum of this and that's the only thing we have in there right now because that's the big one i'll make this i'll make this blue and bordered bordered and blue and so there we have that now this i'm going to pull this into our first page of the 1040 which is going to go right here other taxes so other taxes instead of having zero i'm going to think that pulls in from this other taxes page which only has the self-employment tax i might add to it later so there is that so that pulls in and that should mirror what we have now on page two right there so that makes sense but that five thousand uh six fifty two we also get to deduct half of it on page on on page one in essence as part of of an adjustment to income which is right here now why do you get to do that it's kind of complex because it's basically payroll taxes social security and medicare which they're trying to mirror what happens on a sole proprietorship to what happens on like a corporation but they're trying to treat a sole proprietorship on the schedule c as if like you're an employee of your own sole proprietorship in other words they want to take your 40 000 income here and charge you both the employer and employee portion of social security uh and medicare because on the corporate side uh if you had an employee so they're treating you like an employee of your own business like so if you were on the corporate side of things then then the the they withhold the employee half of social security and medicare and then they have to pay their half of social security and medicare but they get a deduction for their half of social security and medicare so we should get a deduction if we're self-employed and they're treating us as an employee of ourselves and whatnot but we can't put the deduction on the schedule c because we use the schedule c to calculate the tax and we end up with a circle of reference so they have to put it on the schedule one see this is all we'll get more into this later but that's just a quick uh recap of it so that means that on page two of schedule one there's that two eight uh six two eight six two six two eight two six so that's going to be right here so we talked about it being right adjustments adjustments to income here and so that pulled in from this tab where we had an IRA now i'm going to say half of self-employment tax what let's get the terminology correct here so they call it they call it uh deductible part of self-employment so let's say part of basically half let's say half half self-employment tax so this one i could say this is going to equal then the full tax which is on other taxes that number self-employment tax divided by two so it should be that two eight two six two eight two six and that number then is i'm going to make this blue and bordered should pull into page one of the 10 40 and there we have it okay so let's see what we've got now we've got the 140 000 does this make sense we've got 140 that's right we've got the two eight six two six the two eight two six for the 137 174 then we have the standard deduction 19 four 19 four gets us to the taxable income 117 777 but now we've got this other uh the the the qualified business income deduction now i'm not going to dive into this in detail right now this is kind of a weird when they changed the law a few years ago they they kind of plugged the a hole in the law that they were trying to do some stuff i won't get into it now but i'm just going to depend on the software for now we might dive into this more when we get into the schedule c stuff obviously it's a huge component uh if you have a business schedule c but i don't want to get into detail on it now 7 4 3 5 i'm just going to plug it in right here 7 4 3 5 obviously we would want to have a good understanding of that number it's material it's significant i will dive into it on the schedule c a bit more and then that gets us to the 26 8 35 and the and the 110 339 so the 110 339 there we have that i'm going to depend on the software to calculate the tax on page 2 18 8 17 so let's say okay this is going to be now 18 8 17 18 8 17 okay and then we've got the 2000 from other from the child credit boom and then we've got the uh five six five two from the self-employment so five six five two boom that gets us a total tax of the 22 469 22 469 then down below you've got your payments and withholdings so let's just add a withholding now so we could see what that looks like so i'm going to say all right let's say that that on my w2 i withheld i withheld for federal taxes whatever 15 000 and then we'll pull that over that shows up on page 2 here so then if i pull that in my formula i've got my payments and refundable credits we'll get into the refundable credits later let's just add the payments so i'm gonna i'm gonna go and add another tab pull this all the way to the right and i'm gonna say let's scroll in go up to the top so i'm on the a1 select the whole sheet right click format the cells and i'm going to make this currency bracketed numbers no dollar sign no decimals and then make it boldened and this is going to be payments so this is going to be let's double click down here let's say payments and credits should be like refundable credits refundable refundable credits so i'll say payments and refundable credits that's what we had on line one right so payments and refundable credits yes so i'm gonna make this larger let's make it black and white up top and boom boom and let's say the first category is going to be withholdings withholding so this is going to be showing on like w2 let's make it w2 withholdings w2 withholdings and so i'll make this i'll make this black and white and then i might leave a few spaces down below because i might have more than one w2 so i'll leave a couple spaces and then i put this first w2 i'm gonna say for whatever was 15 000 and so i'll sum this up down below total w2 withholding withholding summing out to the side right and there we have it now we also might have withholdings for for other things like like retired individuals will have uh withholdings on distributions possibly and you could also have payments if you have the schedule c businesses for example and you didn't have enough of the withholdings to pay for that uh you might have withholdings there we'll get into that later but for now i'm just gonna i'm gonna say that this is what we have and this is gonna be the total the total uh payments payments and let's say let's make it capital payments and refund refundable credits and i'll put this to right there equals the sum all we have is that 15 for now we'll add to it when we get to more complex problems later but the bottom line is this tab is going to pull into the first tab which is here this is going to be equal to the payments and the refundable credits so that's that's the general idea and then if i go back on over now we've got uh the payments and that comes out to the uh seven seven six seven eight now it act it added another 209 for for like penalties so let's say penalties and interest was the the software's adding another 209 209 so after after penalties and interest we're going to say this is going to be this plus this gets us to the seven seven six so the seven seven six seven eight yeah seven six seven eight so that's the general that's the general layout so we just kind of constructed it we'll use this worksheet and expand on it as we go so i'm gonna i'm gonna do a review and say does it all spell checky it i'll do spell checks for each of these spell check spell check let's do some spell checkies and look at this i'm a spelling master i feel wish i i wish i was this good when i was doing those spelling bees back in grade school man i wouldn't medical oh mortgage i hated those spelling bees i hate them i don't want to stand up there and spell whatever you spell it you spell it not the same i've spelled check on a computer now all right so there we go okay so then so then notice that we don't have really any data input on the first page here except for a couple items this one's pulling from a table down below this one is coming is is basically a data input right now but it would only be there if we had like a schedule c and we'll talk more about it later they kind of shove that one in a few a few years ago and like we talked about and then we've got the actual tax calculation which we're going to depend on the software to help us to calculate so the general strategy we're using these other worksheets to pull into the first page of the 1040 in a similar fashion as the actual 1040 uses other schedules to pull in for the income line item and then the adjustments which are going to decrease it the above the line to get us to that agi number then we're taking the larger of the itemized or standard deduction the standard deductions being pulled from this table down below so that we can we're still kind of populating it but it's basically pulled in from the information down below taking the larger of the two which is a formula the max of the two the qualified business income we're pulling from the software for now and that gets us to our taxable income with a formula the average tax we're not actually entering but rather pulling this data in which is the tax which is calculated using the progressive tax structure from the software to back into the average tax and then we've got our other credits which are now being pulled from our other tables over here we only have one other credit right now the child tax credit will add more later but we'll just add them to that sheet so they'll pull in over here and then we got the other taxes the only other one we have at this point being the self-employment tax but we might add more there later that gets us to the total tax after the credits notice it's a little confusing to call it you know tax after we've applied you know credits to it and we have the tax up there up top so we have the difference between credits and taxes and then we could have other credits even down here but all we're doing is comparing it this time to the payments payments we usually make with withholdings or payments that would be coming from uh from like schedule C payments for example to get us to the tax due or refund and then if we had to add a little bit added item down here for the penalties and interest that are applied by the software then we can add a little added calculation to uh to add the penalties and interest so that we can tie in exactly so that's the general structure and that's the general layout and you can see hopefully how this kind of this kind of layout can help you to better structure in your mind what's going on better be able to visualize it and then double check your numbers by entering the same data into two separate items one in your excel worksheet one into the actual tax formula and the excel worksheet can help you to kind of explain it uh in your mind double check some calculations and be able to explain what's happening happening to yourself and to a client if necessary