 Personal finance practice problem using one note. Insurance payments for medical claims calculation. Prepare to get financially fit by practicing personal finance. Information's on the left-hand side. We are imagining that we have health insurance, health insurance that will pay out an amount for claims after the deductible at 70%, the deductible per person at the 950. We are imagining that we have doctor and prescription medication for one family member at $4,000. We're gonna calculate the amount paid by the insurance company and the amount paid out of pocket for that claim of the $4,000, getting an idea of what does it mean to have a deductible in this case and what is that kind of 70% over the deductible mean noting it's a little bit different, a little bit more complex on the medical insurance, this deductible component or idea or concept. Then other types of insurance like property insurance for example, so in property insurance, we often think of the deductible as the amount that we would have to clear that we would have to pay and then the insurance company would pay like 100% up to a certain amount, for example, possibly. With the health insurance, it's often a little bit different. We've got the deductible, the amount that we might have to pay and then over and above that, we would then have a percent that we would still pay a percent that the insurance company would pay and then possibly have a maximum above which the insurance company would pay for example. So we wanna get an idea of that. We wanna be practicing the building of our table. These are great tools to practice in Excel and then get an idea or concept how you might put the table together so that you can adjust data on the left-hand side and have your table kind of populate as you would like to if you were to put this in something like Excel. You could start to kind of visualize what it would need to do in order to make that work. Okay, and if you wanna do this in Excel, we do these in Excel too. So then we're gonna say, okay, we got the amount over the deductible that we're gonna calculate. Notice that I'm gonna put a colon here and tell myself what we're going to do first. And then I'm gonna indent when I actually do the calculation, pull that to the enter comment column. That's the claim amount of 4,000. That's the costs that we have. The deductible that we have to pay before the insurance company kicks in is gonna be 950. So if we subtract those two out, the 4,000 minus the 950, the amount over the deductible, I said what we were gonna do, we did the sub-calculation. Now we're summing it up in the outer column is the 3,050 over the deductible. And then we're saying that 70% is gonna be paid by the insurance company. So we're gonna say 3,050 times 0.7. That means that they're gonna be paying then the 2,135 would be the idea here. Now note that if you set this up in Excel, you would like to be pulling the data from your data set over here so that I can change these if I had different sets of circumstances and basically have this populate automatically over here. One concept you might be saying to say, well, what if this amount was below the deductible? What if it was below then the 950? What if it was only like 840 or something like that? Well, then you'd be paying the whole thing because you hadn't cleared the deductible and you would have to put basically an if kind of calculation over here to pull in the proper amount. This is actually down here, right? So then in this cell, we'd have to say, well, what if this amount was only 800 or something like that? Well, then the amount over the deductible, if any, would have to basically stop at zero. We wouldn't want it to be negative because that would kind of mess up our calculation. So if we can practice this logic function in Excel, you could just get an idea of what you would kind of have to do. You'd have to say, well, if it goes below zero, then I don't want it to go below zero and you could practice in Excel if you wanna work this in Excel using an if or logic functions one way that you can make that work. So we got the amount paid out of pocket. How much are we gonna pay then? Well, the amount over the deductible was the 3050 and then we've got the amount paid by the insurance company, 2135. That means that we're gonna be paying for the amount over the deductible of the 915 and the claim up to the deductible, meaning the deductible, we're gonna have to pay the deductible plus 30% over the deductible, which is that 915. So if I add those two together, that's gonna be the out of pocket, the 1,865. The 1,865. To just kind of prove this or check it to myself, I'm gonna say, well, the insurance company is gonna pay the 3135 here and we're gonna be paying the 1,865. That should be adding up to the 4,000 on down below for the check figure at the 4,000. Now again, in Excel, you might be doing this calculation and say, well, down here, the claim, what if I didn't get up to the deductible? What if this is less than the deductible? Then I might wanna use my formulas in Excel to take the lesser of these two numbers. It should be I'm paying either the deductible or the smaller amount if it's less than the deductible so that we can then change our data on the left-hand side and have a worksheet that will populate with us. Now you might visualize this another different way. There's many different ways we populate our table. Another way we might look at this, we might say the amount paid out of pocket calculation would be something like this. Amount over the deductible. Once again, I'm gonna put a colon sub-calculation. The claim is 4,000, same starting point. The deductible at the 950, the difference between the two, the 3,050. But then we might go right to the percent out of pocket. So I don't wanna calculate the percent the insurance company is gonna pay. I wanna calculate what we're gonna pay. If the insurance company is gonna be paying 70%, and we're taking 100% or one minus the 70%, which obviously you could put in the calculator, one minus 0.7. In other words, you could put this one in a percentage format, 0.3 or 30%. If I move the decimal over, that's gonna give us then the 30%. So if they're paying 70%, we're paying 30% over the deductible. So 30% of the 3,050, 3,050 times 0.3 is gonna give us the 915. And then we're gonna claim or add the claim up to the deductible. We're also gonna be paying the deductibles. We're gonna pay the deductible plus 30% of what was over the deductible. So the 915 plus the 950 is gonna give us that 1,865. Again, same concepts with an Excel. You might say, well, what if the claim amount was less than the deductible? Well, then this number here should basically get to zero and we'd just be having the deductible. So I could do that with an if calculation on the left-hand side and use a logic function and we could practice that in Excel. Same thing down here with this 950. We're gonna be paying the 950, but we're only gonna be paying that if we clear the deductible. If we had some amount below the deductible, we would take the lesser of the claim, which I could take here, or the deductible using, say, a mend function. And if you use those functions, you could practice in Excel with these nice little practice practical problems to use our data set so we can adjust the data set and see how the calculations would populate to see if we could get it to populate properly based on different scenarios such as having an amount over the deductible, having an amount below the deductible.