 Personal finance, Excel, practice, problem, insurance payments for medical claims, calculation. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the worksheet, that's okay because we'll basically build this from a blank sheet. If you do have access, there's three tabs down below. An example tab, a practice tab, and a blank tab. The example tab in essence being an answer key. Let's take a look at it now. Information on the left hand side related to health insurance and a claim. And then we're going to be calculating how much might be paid by us, how much by the insurance company. Practicing a little bit Excel formatting and formula formatting as we do so. The practice tab, second tab will have some pre-formatted cells. So you can work through the practice problem with less Excel formatting. The third tab, the blank tab will basically be a blank sheet with just this information on the left. If you don't have this, you could just add the information on the left. If you don't have this, I would start by selecting the entire sheet with the triangle. Right clicking on the sheet, formatting it in the way I would like my underlying formatting to be, which I usually go to currency brackets, negative numbers, and no dollar sign. And I remove the decibels as my starting point. I'm not going to say okay here, but just X out of it because I already have the data on the left. Then enter the data changing cells as necessary, like the percent here, making that a percent format. And then we can move forward from it. So our information says we got the health insurance pays percent of out of hospital expenses after the deductible. So remember the health insurance is a little bit more complicated in some ways than things like property insurance. Because the deductible is something that you have to pay to clear oftentimes. But then you might be paying a percentage after that deductible. Say after we clear the deductible at 70%, the deductible per person we're going to say is $950. And so a doctor and prescription drugs for one family member is 4,000. So now we're going to say the claim then for that one family member is 4,000. The deductible per person is 950. So we'll have to figure out how much the insurance company might pay, how much we might have to pay. So let's do this in a couple different ways. I'm going to start off by making this column D a little bit larger. I'm putting my cursor between D and E. I'm in dark mode by the way if it looks a little bit different because it's easier on my eyes. That's what they tell me. So here we go. So this is going to be the amount paid by insurance company company. That's going to be our calculation company. And if I misspell anything, then I apologize. I might do a spell check at the end. But here we go. We're going to go to the home tab. We're going to go to the font group. I'm going to put this black and white is my general custom. So make that black and white like that. And then I'm going to have a sub calculation. This is going to be the amount over the deductible deductible is going to be my sub calculation. Because I got to figure out how much is going to be over the deductible for the claim that we are making. So let's say that we have a claim amount amount. I'm going to put this on the inside. That's the $4,000. So we got to pay $4,000. That's the claim. And we're going to say that the deductible per person and this is for one person. So we're going to say that's going to be the 950 950 on the deductible putting an underline under that by going to the home tab font group. Let's put an underline underneath that one. And this is going to be the amount over deductible. We'll say if any. And I'm going to make this sell a little bit, a little bit. I'll keep it there. Then we're going to put our cursor in the outer cell. I'm going to bring this into the outer cell here and I'm doing my subtraction problem. So I'm going to say equals and go up, up over to the 4,000 minus the over and up 950. That gives us the 3,050. We'll do a little bit of the format in here. I'm going to format these three cells. I'm going to do some indentation by going to the home tab alignment. Let's do an indentation making it look a little nicer. Let's do an indentation here. Alignment and indent it again. All right. And then we're going to say this is the percent percent paid by insurance over the deductible, which is going to be 70%. I'm going to say equals and point to that 70%. That's going to be paid by the insurance. Let's do some formatting on that one, making it a percent percent to find at home tab number group percentification 70%. And so then that's going to be the amount paid by insurance company is going to be equal to the 3,050 times the 70%. And that's going to give us the 2,135. Let's put an underline here, underline here. And let's do some formatting. I'm going to make this one black and white on the end font group black and white there. And let's do our whole formatting with the blue and borders. I'm going to put my cursor on this whole thing and then go to the font group, make it border blue. Here's the borders. And here's the bucket for the blue. If you don't have that blue right there, you can go to the more and the standard. And then in this little wheel, I'm just using that blue right there. And okay. So there it is. So, so there we have that. Then we could make this a bit more fancy of a cell. So let's just think about this and say, well, what if this amount here that we're paying was below the deductible? What would be the amount paid by the insurance? It would be zero. Right. So how can I reflect that? Let's put the 4,000 down here just so I remember it. And let's say this amount was only like, like a 700. For example, notice my, my answer then doesn't really work because now this number really should be zero. So let's just do that in our Excel practice or Excel formatting and go, okay, well, how can I make this worksheet work? If I wanted to change my data on the left hand side and allow it to adjust automatically, I can use, I can use an if then function. I could say, well, if equals if brackets, if this number, the 750 minus this number is less than, which is shift, shift the little, the comma. And then if it's less than zero, then I don't want you to put a negative number. I'm going to say comma that goes to the next argument. What do I want you to do if it's zero? I want you to put just a zero there then not a negative number. But if not comma, meaning if it's greater than zero, then I want you to do the calculation, which I got to put in again, which is 700 minus the 950 or E3 minus E4 close up the brackets shift zero and enter. So you don't have to do that, but that makes it so it kind of works out and doesn't give you that, that false number just a little Excel practice. If I change this back up to 4000, then we should have the same calculation we had prior and then I can adjust it and be a little bit more adjustable. Now, let now I could then calculate. Let's put this down here, the amount paid out of pocket or the amount paid by us then let's say the out of pocket. And then I'm going to make this header black and white again. I'm going to go select the home tab thought group. Let's make this black and white. And then I'm going to say, okay, well, if I do the out of pocket, I could start out with the amount paid over the over the deductible, the amount paid over the deductible. If any, let's make this a little bit longer. I'll put this over here and make this black and white to put this in the outer column because I've got room over here. So we'll make that the 3050. And so and then the amount paid by the insurance company is this amount. So the amount over the deductible then paid by us, let's put an underline here. This is going to be the amount paid by us over the deductible. So the amount over deductible paid, let's say like out of pocket. I won't put that here. We'll say this is going to be equal to this number minus this number. So that's the 915. And then we also had to pay the deductible, which is that 950. So then we're going to say the deductible. So we'll say the deductible, if any, which is going to be so this is going to be actually it's going to be what is it going to be that up to the deductible claim. Up to deductible. And so I'm going to I'm going to pull in the deductible, which is going to be now if I'm going to show you a couple ways to do this if I pull in the 950. And then I go, okay, let's do an underline here. That means that the out of pocket out of pocket is a total of the sum of these two. And that would be the 1865. I can do my check figure figure, which would be adding the amount paid equals by the insurance company and the amount paid by us, which would equal the total amount of the of the bill. Right. And so I can under I could then let's do some blue and border here home tab font group borders and blue border blue. Let's do a border blue down here to border blue font group blue and the borders. Now again, if I put the 4000 down here and I say, well, what if I change this to like 700 again? Now notice that this amount right here isn't really working because I wouldn't be paying the full deductible. Then I would only be paying the claim up to the deductible. So what I want to do is take the smaller of these two numbers, the claim or the deductible so I can make this a little bit fancier with Excel. I can say this is going to be equal to men function, the men function of these two just take the smaller of the two of those two. So it's like the sum function, but with a men instead. And then I say enter. And so then I got just the 700 that would be paying that we would be paying because we didn't clear the deductible yet. So then if I bring it back up to 4000, we should we should be back to the same spot. So that looks good. Let's just calculate it another way. This is another way that you might see it. You might first think, well, I'm not going to, I'm going to think about how much I have to pay first. So I'm going to, I'm going to make a skinny column. I'm going to take this skinny seed and go to the home tab format painter format, paint it and make a skinny G out of it skinny G. And then this is going to be the amount paid, let's say paid out of pocket. So that's the first thing we'll think about. And so I'll make this a little bit larger, a little wider here. So there we have that. And then I'm going to make this a little smaller so we can have a bit more room. Maybe I can make these two smaller so I can save some space without making it too small. Okay, so then I'm going to select these three. We're going to go then to the home tab font group and we'll make this blue, no, black and white, black and white. That's our header because that is our custom. So then I'm going to do the same calculation to start off with. We would do the same thing up top, the amount over the deductible calculation. I'm just going to say equals to claim amount, same number, claim amount, same thing. And then we've got the deductible per person, which is the 950. Let's put an underline under that. And this is going to give us the amount over the deductible just like we started with before, which would equal the 4000 minus the 950. Let's do our indentation. I'm going to select these three home tab alignment indent this one alignment indent again. So then I want to take a look at the percent. I'm going to call this the percent out of pocket out of pocket. This is where things are switching up here because because now I'm not trying to calculate the amount the insurance company is going to pay, but how much we pay. So I'm going to take this is the amount that they're going to pay in percent. So if I take one minus that amount, that's the percent we're going to pay. Let's take one or one 100%. In other words, minus the amount that that comes that paid by the insurance company, which is the 70%. And let's make that a percent. I'm going to go to the whole the numbers group, percentify it. We could percentify this one 100%. But I usually don't. I usually just keep it like at one for some reason. And then I'll go to the font group and underline it. And then this is going to be the percent out of pocket. And so this will be equal to one or 100% minus 70%, which would of course equal 30%. Once we percentify the cell, which will do thusly home tab number group percentify percentify you better percentify. And then we're going to say and then recognize that you percent. Okay, we're going to select these three home tab alignment and indent and dent this one again. Home tab alignment and dent again. And so this is going to be the amount amount over deductible paid that we paid is what I'm trying to say. So now this is going to be equal. This is the amount over the deductible times the 30%. I'm just working in the outer column now times the 30%. There's the 915. There could be rounding involved here, but I'll keep it at that. So then we've got the deductible. So they got the deductible amount that we have to deal with. So I'm going to call it claim up to deductible or the deductible. I'll start just with the deductible same kind of situation we had down here. We'll adjust it a bit in a second. But if I take that 950, then we're going to say that the total the total out of pocket is going to equal the sum of those two. Sum it up. Sum it up. I'm going to go to the home tab font group and underline. I'm going to go to this one home tab font group and underline again. So there we have it. So that kind of matches this amount here. And then of course we can do kind of our check figure. Let's do our check figure again. Another check figure down here. And we'll check it out compared to to the amount paid by the insurance company. So I could say this is the total out of pocket is this number. And then I'll just pick up the amount paid by the insurance company paid by insurance was this number. We calculated over here. And that is that. And if I sum those two up, you get the total claim amount, which is our check figure. This is that 4000 once again. So we checked it out. Let's put some let's put some fancy blues and borders font group blue or border blue, border blue, border blue. And then this one let's do a border blue on that too. Border blue on that to font group border blue. And then we can do some fancy formatting over here. So let's do the same thing. I'm going to put my 4000 down here and let's pretend that this number was below the 950 like 600 this time. Notice this side's calculating properly here. So we would have to pay the 600 because we still have to pay the amount up to the deductible. The insurance didn't kick in until we clear the deductible. So I could do the same thing we did over here. I want to take the smaller of these two numbers. So or, you know, subtraction. I don't want to go below zero in other words. So I can do that by saying equals if this is one way you can do it. If this number minus this number is less than zero then comma just put a zero in. That's what this is. I'm on this. Just put a zero if that happens. Don't go negative. Don't go negative on me. Stay positive people. Stay positive. That's the theme of the presentation. So if not, then you do the calculation of this minus this and and then close up the brackets and boom. So now it's zero. But then down here you've got this claim up to the deductible. I took the whole deductible amount and it should be the smaller of the deductible or the claim if it's smaller than the deductible. So I want to take the smaller of these two amounts. So I can say, well, this is going to be equal to the men function. Let's use our trusty men function of those two, like the sum, but with a man same way to use it. So now it took the 600 instead of the 950. That looks good. And so that so that looks correct, I think. So let's go down here and then say then let's bring this back up to 4000 and we should be back. We should be back in process. So now you got something that we could do the calculation and you also got a worksheet over here. So that if you were to switch stuff up, say that this was different or you're comparing policies or you're comparing, you know, what would happen if and then we had a change to the deductible or the charges. If I made this 6000 or something, then you can check it out and that's how you should be setting up the worksheets. Let's bring it back down to 4000. There it is.