 Personal finance practice problem using Excel. PPO payout calculation. Prepare to get financially fit by practicing personal finance. Here we are in Excel. If you don't have access to Excel, that's okay because we'll basically work 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 and answer key. Let's take a look at it now. Information on the left-hand side basically laying out the PPO and basically an expense that we have here. Then we're gonna do our calculations on the right. And the second tab, the practice tab, we got some pre-formatted worksheets on the left-hand side so you could work through the practice problem with less Excel formatting. The third tab, the blank tab, we're gonna be doing the Excel formatting as we go. If you don't have access to this, you won't have the information on the left-hand side. You could open up a blank sheet. I would suggest selecting the entire sheet, thusly, right-clicking on it and then go into the format cells, putting your underlined formatting, which I usually use currency and then brackets for the negative numbers, no dollar signs and no decimals. That's where I start. I'm not gonna hit okay, but I'm just gonna X out. That's what I would do if this was a blank sheet starting from scratch. Then put your data on the left-hand side like so, adjusting cells as necessary, such as that percent here and then make a skinny C column and we're ready to roll. So what we have on the left, the PPO plan, emergency room care at a network hospital coverage percent after the member has met an annual deductible is 75%. So we're gonna say there's a deductible of 500 and then after they clear the deductible, they're gonna be paying the 75%. And we're gonna assume that an accident has happened that costs $2,100. So what's gonna be the amount that's paid out of pocket would then be the question. So let's do, we're gonna do this a couple different ways. I'm gonna make the column D a little bit larger. Let's put my cursor between D and E and make it a little bit bigger here. And so obviously we're imagining we got an accident. We broke every bone in our body and our first thought is like, oh my gosh, well, how much is this gonna cost me for the medical bill? So as we're agonizing over that, we're gonna do the calculation here. So we're gonna call it the amount out of pocket. Let's say out of pocket. So we're looking at how much it's gonna be costing us here. So let's make this a header format, selecting these two cells up top. I'm gonna go to the home tab font group, make it black and white with the bucket drop down, make that black. And then I'm gonna go to the letter and drop down and make it white. That's our header style that we've been using. So let's say the claim, we're gonna start with the claim. And so that's the hospital saying that it's costing us 2,100 to fix every bone in our body. That's not bad, actually. So let's say then the deductible. So we'll take the deductible. We gotta clear the deductible of this amount. I'm gonna say equals that amount, pulling it in from the data whenever I can. That's best Excel practices. That's how we practice here with the best practice of Excel. Equals the 500. I'm gonna put an underline for formatting sake. Going to the home tab, we're gonna go to the font group and underline. The line underneath is called, we refer to it as an underline. Just came up with that name for no apparent reason. That's just what we call it. Gotta remember it. There's no logic behind it. Deductible. So this is the amount after the deductible. It's gonna be equal to the 2,100 minus the deductible. So E2 minus E3. That's gonna be the 1,600. And so then we're gonna say that the percentage covered by the company, so let's say covered coverage percent after deductible. Now I'm gonna need to make this cell a little bit larger. I'm gonna put my cursor between the D and the E and fatten it up a bit. It's gotta fatten up that cell. It looks better. It looks better when it's fatter. So this is gonna be equal to the 75. This is gonna be equal to the 75. I need to percentify that cell though, because it looks like a one, but it's not a one, it's 75. So let's go to the home tab. We're gonna go to the number group and percentify it. Percentified. You best percentify and recognize the percentify. This is in the font group. Let's gonna underline it here. And then this is gonna be the amount paid by insurance, which is gonna be equal to, let's say this is gonna be equal to the 1,600 times the 0.75 or 75% E4 times E5, 1,200. And so that's the amount paid. So the amount over the deductible paid out of pocket then that's the amount paid by the insurance company. So amount over the deductible out of pocket I'm gonna make this cell even wider. It needs to be thicker. The cell needs to be wider. I'm gonna put my cursor between the D and the E and widen that thing up. There we go. And so this is gonna be equal to, this is gonna be equal to the 1,600 minus the 1,200. So that's how much we're going to pay. Plus we had to pay the deductible. So I'm gonna say this is gonna be the claim up to deductible. And so the deductible is gonna be equal to this 500 up top. So that means we're gonna pay that 400 and the 500 if this is how it's working. So the amount out of pocket on the total basis is gonna be equal to the S to the U to the M. Otherwise no one is the sum. And then I'm gonna just select E, these two, the 400 and the 500 and enter. Let's put an underline under the 500. We call it an underline because we put a line underneath it with the little U that U stands for underline. And so then let's make this, let's put some blue and border around it, fancifying it, make it fancy. We're gonna go to the home tab, font group, bucket blue. If you don't have that blue right there, it's in the more colors right down here. Standard right there, blue. That's the one. You're the one Wyatt font group. We're gonna hit the drop down here and say this is gonna go, that's from Wyatt or Tombstone. You're the one Wyatt, he's the brother, Wyatt, one of the brothers, any case. Now let's adjust this a little bit and let's pretend this claim was below the deductible so we can do a little bit, practice some fancy formatting. Let's say this was 400 below the deductible. So now then that would mean that this shouldn't go negative. I don't want that to go negative, don't go negative. So what we're gonna do is stop it at zero with an if function, that's one way we could do it. Let's say equals if brackets. If this cell, the 400 minus that cell is less than zero, then when I say then I'm gonna put a comma, then I want you to put a zero there. That's what the logic is. But if it's not meaning it's greater than zero, then I want you to do the calculation which once again was this cell 400 minus the 500 close up the brackets and so now it'll put a zero there. And then this cell down here also needs to be, notice we kind of characterized it in the name. If my amount of the claim was less than the deductible, then I'm only gonna pay the lesser of. So I'm gonna say I want the lesser of these two numbers with this trustee men function. So I'm gonna say equals men brackets. Take the smaller of the minimal one of these two, the four and the five close up the brackets. And so there it gives us the 400. So we would only be paying the 400 because that's what it costs and it's under the deductible. Let's bring it back on up to what it was before. Why did I put 4,000? What was it before? 200. Okay, so there that is, that looks good. So that, we can also say, okay, how much is paid by the insurance company? So let's say that the total claim, so let's say amount paid by insurance company. And let's do our border or black and white here. Home tab font grew, let's make this black and white. And let's say that we've got, okay, if the total bill, the claim, equals the 2,100. And we paid out of pocket for this whole thing, the 900, the difference is gonna be equal to the 2,100 minus the 900 or that 1,200, which we already had up top, but just to calculate it here again. So the amount paid by insurance company, let's put some border blues around that. Gonna go up here and say this is gonna be border, font group, border it, border it please, and put some blue on the background. Let's put an underline under the 900, underline under the nine, font underline. So there we have it. Now I'm just gonna calculate this again, just using a little bit different format of the calculation, just to practice putting together our tables here. So let's say if we just look at it a different way, you can imagine these things, best to kind of work them different ways for your own, what works best for you, and also because you wanna see what other people are doing and be able to kind of visualize things multiple different ways, practicing these calculations. So I'm gonna format paint the skinny because I want a skinny F here. So I'm gonna put my cursor on the skinny C, home tab, clipboard, format paint it, and then skinny F, gonna skinerize it, skinerize that one. And so I'm gonna put my cursor from C to E, let's go from C to E and hide these cells. So we right click on it then and don't delete it, but hide it. We're gonna hide it so that we have now, we have column F still here. We've got this hidden cells between B and F. I'm gonna make G a little bit wider now. We're gonna say this needs to be got a fat enough G here. G's way too skinny, way too skinny. And so this is gonna be the amount out, let's say amount out of pocket. Here, I'm gonna make three cells of border or our header format from G1 to I1. Go to the home tab, we're gonna go to the font group, make that black and the lettering white. So then I'm gonna start in the same way. I'm gonna say, but this time I'm gonna do another kind of sub calculation for it. So I'm gonna call it amount after deductible colon and then we're gonna have a sub calculation underneath it, which will be the claim amount, which is gonna be, I'm gonna pull that to the inside now. That'll be equal to the 2,100 for us breaking all of our bones. And now we're in about the insurance about it. And then the deductible, let's say this is gonna be equal to the deductible equals the 500. This is the same starting point, but we put it under this kind of subcategory. Let's underline it, home tab, font group, underline. Then I'm gonna copy this one up top and this is gonna be the total down here, but I'm gonna get rid of the colon by double clicking on it, going to the end to get rid of that colon. And then I'm gonna bring this to the outside because this is the total equals then the 2,100 H3 minus the 500 H4 equals the 1,600. Let's do some formatting. I'm gonna take these three cells because this is a subcategory calculation. We're gonna go to the home tab, alignment and indent. And then one more time, pour five or home tab, alignment, indent again. So there we have that. And then the next one's gonna be, I'm gonna call this the percent. This is where things kind of change a little bit. Percent paid out of pocket after deductible. In other words, I wanna see how much percent we're gonna pay instead of calculating the amount the insurance company pays first, meaning if the insurance company is paying 75%, we're gonna be paying 25%. But let's do a quick calculation to get that. So this is gonna be, let's call this one or 100%, which is gonna be one. And then we're gonna subtract out the amount coverage percent after deductible by the company. I'm gonna make column G a little bit fatter here. We're gonna fatify our Gs, a little too skinny still. Gotta get a little bit more calories in that one. And then this one is gonna be equal to 75, 75. And then let's percentify. Best percentify, because that's not one home tab number. There you gotta percentify in order to recognize. And then we got the one, that one could be 102 because you could go to percentify that one if you want. And then the difference between the two will be the 25% home tab font group. Let's underline. I'm gonna copy this title, copy it and put it down here and get rid of the colon. I'm gonna go to the double click on it, go to the end, remove the colon, and then put this in the outer column. This equals the 100% minus the 75%. And now we've gotta percentify if we're gonna recognize home tab line numbers percentify it. There we have it. Let's do some indentation by selecting these three because we did some subcategory calculations. Home tab, alignment indent. And then this one needs to be indented again. Home tab, alignment indent again. Let's fatten up column G again. It's still, you're still too skinny. Column G, you're still too skinny. Let's put it, that's too fat. We don't need it to be that fat. Okay, so then we're gonna go down here. Let's put an underline here. We're gonna go to the home tab font group and underline. Okay, so then this is gonna be the amount over the deductible. So the amount over the deductible out of pocket. So that means that we're gonna be paying 25% of this amount over the deductible. So this equals the 1,600 times the 25%. So we didn't calculate how much the insurance companies paying, we went straight to the percent that were paying. And then we've got the claim up to deductible. So that meaning the deductible amount, or if the claim was less than the deductible, it would be the amount less than the deductible. I'm just gonna put the deductible for now. We'll do our logic test calculations in a second. And that's gonna give us the amount out of pocket, which is the sum of these two, the four and the five equals the S to the U to the N, sum of the fourth at 100 and the 500 and enter the 900. Let's put an underline under the five. Let's go to the home tab to do it, font group and put a line underneath, often known as the underline. Let's border blue this whole thing. I'm selecting this whole thing and let's go to the home tab, font group, borderize it and blueonize it. So there is that. Okay, so then notice we didn't calculate the amount paid by the insurance company now. So we could do that down here. We could say amount paid by company insurance, let's say. And let's just make this our header, home tab, font group and make this black and white. And so I'm just gonna pick up my claim amount. So if the full claim amount is that 2,100 equals the 2,100 and we paid out of pocket equals out of pocket this amount, 900 equals the 900. Then the difference is the amount paid by insurance, which equals the 2,100 minus the 900 is the 1,200. Let's do the border blue brackets or select these items, home tab, font group blue or borders and then blue, put an underline under the nine. The nine needs an underline underneath it. Okay, and then we'll do our little formatting up top here. So let's do our formatting up top and let's imagine that this was down to 300. So I'm gonna put the 1,200, 2,100 down here. Let's imagine it was something below the deductible like 300, our calculation wouldn't work because this shouldn't go negative. So let's practice our if logic function to make that not go negative stick at zero equals if brackets, if this number 300 minus the 500 is less than zero then comma, we go to the next test. What do you wanna do if it's less than zero? Well, don't go negative. I don't care what you do. I ain't going negative. We're gonna stay positive around here. And then comma and then the value, if it's false, then what do you do? Well, then you just subtract it like normal, the 300 minus the 500, close it up and enter so it stops at zero. And then down here, if this amount is below the deductible we want the amount below the deductible not the deductible. So we're gonna do the smaller of these two numbers with the good old min function equals the min function. The min function of these two close up that one. So that looks good. Mucho may hoard nothing paid by the insurance company because it's under the deductible. Let's put this back up to 2,100, see if it messes anything up, it doesn't. I think we're good. Let's unhide the cells that we hid between B and F. There's hidden cells, you can tell because that's not the way the alphabet goes. A, B, F, what, that's not right. So I'm gonna put my cursor from B and go on over to G, right click the selected area and unhide, that's how it goes, A, B, C, D, E, F. So now I've got these two, they look good. So here, this calculation, notice we went right to the percent we paid instead of calculating the amount the insurance company paid first. And so that's kind of the difference between the two. Let's do a spell check on it, review it. Spell checky coverage, coverage. Okay, all right, that's not too bad.