 Income tax 2021-2022, child tax credit and other dependent credit Excel worksheet. Get ready to get refunds to the max, dive in into income tax 2021-2022. Lassert tax software. You don't need tax software to follow along, but you might wanna have access to the forms and schedules which you can find on the IRS website at irs.gov, irs.gov. We're gonna primarily be focused on the schedule 8812, credit for qualifying children and other dependents, and that's gonna feed into the form 1040. Let's look at the general scenario on the form 1040, single filer Adam Smith, living in Beverly Hills 90210. We've got the one dependent that we're gonna start out with, Sam Smith, son, qualifies for the child tax credit. We're gonna start with the 60,000 of the wages, and then we have the 12,550 standard deduction that gets us to the 47,450 taxable income. Let's mirror that over here on our Excel worksheet. We're gonna have W-2 income of the 60,000. That's gonna flow in here, 12,550. That gets us to the 47,450 taxable income, which we see here. No calculation based on the dependent yet because it's a credit, not a deduction. We'll be dealing with page two tax calculations starting at the 6193, 6193, which I'll plug in here, 6193, I believe it was. Is that right? That's correct. And so now we're looking at the credit, which is calculated down here on line 28. That's going to be the refundable child tax credit. It's coming from, of course, schedule 8812. So let's go back on over to 8812. Now you might just basically rely on the software to do the calculation, but reputing it together in Excel could give you kind of that double check and give a better understanding or grasp of what is happening. So we'll put in a more kind of simplified worksheet to get a general idea so the worksheet can do some calculations for us on it, just for the fun of it here. So we're gonna then go on over and say we've got then the credit that we're gonna be putting into place. I wanna put my new form in place here. Actually they already have one. This is the form 8812 child tax credit and other dependent credit. So I'm just gonna add a little bit of room here so that I have some place to work. I'm gonna delete the totals. I'm gonna put new totals if I need it. I'm gonna add some room under the child tax credit. Let's make it a little bit larger. And I'm just gonna add a whole bunch of cells in here so I got a lot of space and I'm just gonna insert. And I'm gonna do this fairly quickly just to get a general idea of what is going on. So if I go back on over to my worksheet then, and I'm gonna go to line 12, we've got then the basically the amount pulling in from our AGI here on line number one. So that's 60,000s pulling in from the 1040 down here with the adjusted gross income, the 60,000. And so that's where this number is coming from. And then we've got up top number of qualifying children under age 18. And then we've got the number of children included in 4A who were under six. Because remember, we have that two-tier threshold of children under 18, but under six is a higher amount of the credit than the other. And then we've got the main worksheet here. So this is the main worksheet that we can be drawing from. So it says here, we're gonna say, number one, multiply the 8812 line 4B by 3,600. 4B being the children that are under six, multiply times the greater amount. And then multiply schedule 8812 line 4C by 3,000. Those are the children that are six to 18. So if I was to mirror that here, I could say, okay, so I'm gonna say, let's say we've got number of qualifying children, let's say QC for qualifying children under age six. And then we're gonna multiply that times the amount per child, which is going to be 3,600. And then I'm gonna put another one. This is gonna be number of qualifying children age over, let's say over six to 18. And that's gonna be the number there that I'll put. And then I'll say the amount per child is gonna be 3,000 there. Now let's change our scenario a bit so that we have one of each of those. I've got one child that's under six. Let's add another one that is between six and 18. So I'm gonna say let's add another child that's between six and 18 here. So that's, I think that will do it. And so now if I go to the form 1040, you've got two children, both qualifying for the child tax credit, but for two different tiers of it, because one's under six and one's between six and 18. So actually no, they're both qualifying for the one. So one needs to be older. We need an older one. We gotta have an older one so one can babysit the other one while we go. Okay, so the one's older. So now we've got the two, and so now we've got these two different children that are included here that are of qualifying for the two different kinds of calculations. So if I mirror that on over here, I can say, okay, so now I can put my data input field. Now these ones, I'm gonna make the data input fields blue and everything else. So let's actually make this whole thing non blue first, and then I'll try to add the blue only to the data input fields that we're gonna put data in. So I'm gonna put data in to this field. Gonna make that blue and data into this field. So I'm gonna say one here, and I got one here. I'm gonna put the total in the outer column. So I'm gonna multiply this out one times 3600. This is gonna equal one times to 3000. So there's our full amount. So this would be the child tax credit. Child tax credit, if nothing else was going on, let's say before phase outs. So you would think that would be basically what we would have before phase outs and let's say and prepayments, prepayments. So we got phase out prepayments that we're gonna basically have to deal with then well let's just say, let's keep the prepayments out of it at this point. We're just gonna say before the phase outs for now and we'll put that in the outer column equals the sum of these two. So that's what it would be. So now we've got a bunch of other stuff that's gonna go on and let's put an underline here. Let's put an underline here and let's put an underline here. So now we're gonna have stuff that goes on related to basically a phase out based on the AGI level. So now we've got the add these together. There's the 6,600 and then we're gonna multiply schedule 8812 outline 4A by 2,000. And you can see 4A right here is has the number of children. So we're gonna take the number of children times 2,000 in essence. So I'll put that here. We're gonna say qualifying children. I'm gonna have to add these two boxes together equals I'm gonna say brackets of this box plus this box closing the brackets times 2,000. So there's our 4,000 that we have in our example here. And then we subtract line four from three to get to the 2,600. So I'll do that here. I'm gonna say this is gonna be this minus this to get to 2,600. I'll put an underline here to see that. So there we have that subtotal. And then if I go back on over, now we've got these amounts per the filing status. So we could use a tricky or fancy Excel worksheet to kind of pull over and match out the filing status. But I'm just gonna put these amounts in there. Married filing joint 12,005, qualified widow, 2005, head of household 4,375, all others 2,650. So I'm just gonna list those out here and say, okay, I'm gonna say, married filing joint, qualified widow. We've got head of household and we've got all others. I'm gonna indent those, selecting these items and indenting them a bit. And then I'm gonna put the amounts they gave us, which was 12,5, hopefully I get these right, two, five, four, three, seven, five, and the six, two, five, oh. And then I'm gonna pick the one that applies. We have a single filer for us. So I'm gonna be picking then this one. I'm gonna make these blue because I want to, let's actually make just one of them. I won't try to pick each one of them. I'll always put whichever one applies into this cell. So I'll make that one blue. So that one's gonna be this one. In that case, if it was married filing joint, then I would say it's this one, right? So we don't have multiple different cells that will mess up other formulas. And then what are we gonna do? It says we're gonna enter the smaller, we're gonna enter the smaller, enter the smaller of five and six, okay? So I'm gonna say here smaller of, smaller, smaller of. I have to use a logic function between these two to do that. I'll use an if function equals if brackets. If this cell is less than this cell, then, that's when you hit a comma, then what I want you to do is pick up that cell. If it's smaller, but if it's not smaller, comma, that's what the comma means. Then I want you to pick up that cell, close the brackets, there it is. So now if this one was smaller, like two, it would pick up that cell. So we've got our if then logical function happening. So then it says enter the amount shown below for your filing status. These are kind of like the thresholds or phase outs that you can look at. Now I'd like to see what my AGI is. So I'm actually gonna add my AGI in my worksheet right here. The AGI adjusted gross income. And I'm gonna pick that up from the first page of the form 1040, 1040 first page. Here's the AGI 60,000, where we are at this time. I'm gonna have these amounts from the table again, amounts per filing status. And so these are basically the phase out amounts that we have here, married filing joint, qualified widow, widower, 150,000, head of household, the 12,000, 500, all other 75,000. So I'm gonna go on over. Now we're filing single right now. So our phase out would be 75, we're at 60. So we shouldn't have those phase outs. I'm gonna use the same filing statuses we had up top. I'm gonna indent them. And then I'm gonna put those amounts in here, 150, married filing joint. Same for qualified widow, widower, one, one, two, 500, head of household, and 75,000 here. And I'm just gonna pick the one that applies, which is the single filer making that blue. So we're gonna have to pick that one out. We could use a fancy formula to let Excel do that for us, but I'm gonna pick the filing status and have to do the data input, which is a little less fancy, but it is what it is, okay? So that's, so then what do we do? We're gonna say then after that, we're gonna say that enter the amount shown below, subtract line eight from schedule 8812 from line three, if zero or less enter zero, if more than zero and not a multiple of 1,000, enter the next multiple of 1,000. And I think that means we're basically rounding up, I believe, to multiples of 1,000. So let's say that we're gonna say that this is a subtract, subtract, subtract, not below zero, zero round up 1,000 to the nearest thousand, is what I'm trying to say with all of that garbage that I just put right there. So what I'm gonna say, we could first say we're gonna take the lesser of these items, or we're gonna subtract these items, and if it goes below zero, we're gonna enter zero. Obviously this one's going below zero. So first I can say with an if logic function, I can say well equals if, so we're gonna say here equals if brackets, and then I'm gonna put another pair of brackets, we're gonna put the argument in this cell minus this cell, and then I'm gonna close up that argument, if that minus that is less than zero, then that's when I put a comma, then I want you to put zero there, but if it's not comma, that's the other comma, then I want you to do the subtraction, which I, let's put brackets around it again, which is this minus this, closing the brackets, and then closing the whole bracket thing again, and it puts a zero there. Now let's make it a little bit more interesting and make this 100,000 by going to the, by going to my income, let's say W2 income was 100,000, now we're over the phase out, that flows into the income here, and that would then flow into my worksheet here, so now we have a positive 25,000, let's match that in our Excel worksheet, just to check it out and say W2 income, now is at 100,000, to test this out, forms, let's go to here, we're gonna say okay, so now we're at the 100,000, and we're gonna go into here, so now we're at the 25,000, just to make it a little bit more tricky, because we wanted to round it now to the nearest thousand, so this one's too nice and rounded, let's say we went back on over here, and said it was a 100, 100,400, what's it gonna do with that, going back on over, and going to my forms, and take a look at it again, so now it rounded it, even though it was 400 up, it rounded it up to 26,000, so now I'm going over here and saying, oh man, what am I gonna do, what if this was 104, and I pull that over, and so now I pull that over, so now I wanna round it up, it needs to round up to 26,000, so I gotta round this up now, how am I gonna do, how in the world, let's go back, let's double click on this last argument, I've gotta say, I don't want you just to subtract it, I want you to round it up to the closest thousands, so I'm gonna add in here a round up formula, round up, round them up, and then I'm gonna say there it is, and then I'm gonna go to the end of this and say comma, so now it says number of digits, and negative three represents the thousands digits, so negative three, and then it's gonna close it up, I need another bracket, okay, add the bracket, so there it is, it rounded it up to 26,000, so there we have a bit of a fancy formula, so let's go back on over, and then it says after we do that fancieness, it says multiply line nine by 5%, so I'm gonna say times 5% times the rate, let's say of 0.05, this is gonna be part of the phase out calculation, let's make that a percent, and then I'm gonna say this is gonna be, I'll just call it a subtotal subtote, which is gonna be equal to this amount of the 26 times the 5%, okay, so let's see what else we've got there, so there's the 1,003, enter the smaller of line seven or 10, so seven or 10, so I think that is between this number and this number, so I'm gonna take the smaller of those two, so I'm gonna say okay, let's put that over here, smaller of, and this will basically be the phase out, phase out, then it's gonna be the smaller of these two numbers, so I'm gonna say equals if, there's another if thing, equals if brackets, if this number is smaller than this number, then that's when we do a comma, I want you to take this one, if it's smaller, if not comma, that's what that means when you say, if it's not then, I want you to take this one, so it's gonna take the smaller of that 1,300, and so that's gonna give us then a subtotal here, we've got the subtotal, subtotal, or this will basically be the child tax credit, credit, which is going to be equal to this number up here, before the phase out minus the phase out, so there we got the 5,300, so their bottom line, there's our 5,300, so we basically kind of recreated this calculation, and then we gotta take into consideration now number six, which is basically the other dependents that come into play here, so let's say we had another dependent, let's add another one, so now we've got this other person here, we're gonna go to page one, we've got Sam, Jane, children, one under six, one over six, but below 18, and then T Smith, which is an other dependent, which doesn't qualify for the child tax credit, but for the other credit. Okay, let's go back on over, and now let's say now we've got the other credit, which we have a one here, and we're gonna multiply that times the rate, which is gonna be 500, so I'm gonna go back on over and say okay, so now we've got then the other dependent credit, number of dependents, let's say one, and let's get rid of this whole blue thing for now, removing the blue, removing the blue for now, so we've got one there, and then we're gonna multiply that amount per dependent, which is 500, 500 per dependent, and so then I'm gonna put that, let's put that on the outside here, so it's lining up with this column, this equals this times this, so this is gonna be total other dependent credit before phase outs or anything like that, and so then we're gonna go back on over, so now we've got that 500, and so now we're gonna add those together, so that's gonna be the 5,800, so this is gonna be then, I'm gonna say okay, this is the total child tax credit and other dependent credit before phase outs, summing up, let's put this in the outer column here, equals the sum of all this stuff, so now we're at the 5,800, and then we could possibly have some phase out stuff here where it says the income's a little bit high, enter the amount below for filing status, married filing joint, all others, so 400 or 200,000, so okay, so we got, we're gonna say then we've got per filing status, and we've got married filing joint and all others, I'm gonna indent these two, a little indentation, and this is gonna be 400,000 and 200,000, and we're gonna pick one of those, so for us we have the 200,000, so I'm gonna pick up the 200,000, I'm gonna make this blue because we're gonna have to kind of figure out which one we should pick there between those two, and then it says subtract line nine from three, if zero or less enter zero, if more and not a multiple of 1,000, enter the next multiple of 1,000, for example, so we got the same kind of issue, obviously it's zero here because that's a fairly high threshold for a single filer, but let's go above it just so we can see that we could say, okay, well what if my income was above 200,000, like it was like two, one, oh, and then let's say oh, 300, so wait a second, that's too high, 200,300, so now it's a thousand, it's gonna round it up to 1,000 over that I believe, so I'm gonna say okay, let's see what that does, so now we're gonna say, see now it rounded, we're over the 200,000 by just $300, and it rounded it up to 1,000, so okay, so now we've got that weirdness happening again, so let's mirror that over here, let's say that we went over here and said that our income was 203, and that pulls into page one, and that's gonna pull in over here, so now we've gotta say that we gotta subtract these two out, which is gonna be my income, which is the 300,000 minus this, but round it to the 1,000, so it's gonna be, and if it's less than that, it's gonna be zero, so I'm gonna say okay, here it is, so it's gonna be if brackets, and I'm gonna say if this number up top, my 203 AGI minus this, and is less than, and I gotta put brackets around this argument because it's an addition and subtraction, if that's less than zero, then comma, I want you to put a zero there, but if it's not comma, then I want you to do the argument of brackets, this minus this, that's what we'll start off with, so there it is, there's the 300, so there's the 300, and so if this was something less than 100,000, it would put zero there, okay, so now I've gotta add, I've gotta make it, okay, now it's 300, I need to round it up to the next multiple of 1,000, so I'm gonna go back into here and add my rounding up to 1,000 before this last argument, round up brackets, and so this minus this, and then comma to the number of digits, which is negative three for 1,000s, so negative three, and enter, it's gonna close up the brackets, I need one more, so there is our 1,000 there, okay, and then it says multiply line 10 by 5%, so I go back on over and say, let's multiply it times 5% times a rate of 5%, 0.05, making that a percent, making that a percent, and so that's gonna give us our subtotal of another, so this is a subtotal, or this is gonna be a phase out, let's call it, I'm gonna put that into, should I put that into an outer column? This is gonna be a phase out, this is the, well let's put in the outer column, this is gonna be this times this, the $50 phase out, and this was the credit before the phase out, let's actually pull this whole thing, I'm gonna pull this whole thing over, so there we have it, so this is where we were before the phase out, then we have the phase out, which finally gets us to line 12, which is subtract line, this is gonna be the credit, so this is gonna be the credit amount, credit, this is gonna be CTC, and OCD, other DDC, credit, credit, so I'm gonna say this is gonna be equal to, let's put it in the outer column, this minus this, so there we have that, but then we also had the prepayments that we put into place, so if I go down here and say, okay, but we're gonna check line 13 and say, but then we made, we got prepayments from that, all right, and then it actually gets a little bit messy down here as well, but I'm not gonna go into a lot of the detail on this because we're getting kinda short on time, so I'm gonna say, okay, this is the amount that we got on the prepayment, which we're gonna say is the 1,800 from the letters 6419, so I'm gonna say, this is the advanced child tax credit payments that we got, which I said was 1,800, so 1,800, 1,800, so that's gonna give us the CTC and ODC available, available for, available at this point, let's say that, it's gonna be equal to the 4450 minus the 1,800, let's do that one more time, something went wrong, horribly, horribly wrong, and that's gonna be the 2,650, the 2,650, and then I'm just gonna break them out between the two credits, which is gonna be the O, ODC, which I'm gonna say is still equal to, and this is of course a simplification, is still equal to that 500, so the CTC is gonna be the difference between the two, this minus this, and it's important to break those out because this one up top is gonna be non-refundable, usually typically, and then this one down here is the refundable, so if I go back on over to my first page here, we've got the credits, these are gonna be the non-refundable, where I'm gonna put the ODC, this is gonna be equal to scrolling over this portion, the 500, and then down here, I'm gonna put the refundable portion, which is gonna be here, and I'm gonna pick up this refundable plus the payments that we're gonna have down here if there were any, and that's gonna give us the 2150, which kind of mirrors what you can see here, and I'm gonna go to the 8812, and so now we've got the totals down here that are being broken out, the 5000, the 2150 pulling into the 1040 page number two, here's the 500 for the non-refundable up top, and then here's the 2150, so if I kind of recalculate this or look at it, we've got then page one, the three dependents, two children, one under six, one between six and 18, other dependent, we had the 200,300, we've got the 12,550 standard deduction, there's the 87, 187,750, so I'm gonna go back on over and say okay, so there's just 187,750, the tax then calculated on page number two, 40,907, so I'm gonna say all right, there's the 40907, and then we've got this credit of the 500, so that mirrors here, the 500 that gets us to then the 40,407, so now we've got the 40,407, and then we put the other credit, we also have this other forms, where this is a Medicare thing because of the W-2 wages, but then we've got the 2150, the 2150 getting us to the 38994, we also have a penalty of the 987, so we've got the 987 penalty, 987, so that would get us to the 389,44, the 389,44, this penalty was 687, 687, 389,44, so somewhere, so there's the basic calculation. Let's go back on over, I'll try to clean it up a little bit, so just to see the data input, we have the data input here, no more data input, we've got data input here, and then we've got to put a data input here, and then the other items, we've got to put a data input point here, and then the 500,000, and then we've got to put a data input point there, and I think that's basically it, so it's not a perfect worksheet, and there are exceptions we'll talk about possibly in a future presentation, but doing a worksheet like this can give you kind of an idea of the phase outs and whatnot that you could see, and if you wanted to put a worksheet in to kind of get the general idea of what's happening with a return for a double check, it can be kind of useful to do as well, so there is that, let's spell check it, let's check the spelling, I'm sure there's got to be spelling errors, no, whatever, spell check must be broken, in any case,