 Personal finance practice problem using Excel health insurance out-of-pocket maximum or stop loss cap prepare to get financially fit by practicing personal finance here we are in our excel worksheet if you don't have access to the excel 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 we have the information on the left-hand side we're going to do a calculation for in essence a health insurance claim to think about what the personal costs will be focusing in on the stop loss cap or the max out-of-pocket so we can get an idea of what that is once we have the data set up we can change the numbers on the left-hand side and run different scenarios with it the second tab the practice tab has a pre-formatted worksheet so you can work through the practice problem with less excel formatting the third tab basically a blank tab just has the information on the left-hand side if you don't have this worksheet you can just add the information on the left-hand side i would start off by selecting the whole sheet right clicking on the sheet formatting all the cells i usually start off with currency brackets and red and then no dollar sign and no decimals i'm not going to say okay but just x out of it for now and then add the data changing the cells as you need for example making this 80 percent a percent type of cell skinifying or making skinnies column c and there we go we're ready to go so then i'm going to start over here we're going to say that our data will be here in column d we've got health insurance the deductible is going to be five hundred dollars pays percent of amount above the deductible so once we clear the deductible it's going to pay 80 percent and then we're going to say that there's a sickness causing medical expenses of ten thousand dollars if that's the case uh what if the then what's going to be the cost to us is going to be our calculation and then we're going to add in this stop cap what if the policy had a stop loss feature that capped out of pocket expenses at four thousand two hundred meaning kind of like the max out of pocket uh cap that you might see in a medical insurance remember when you start thinking about the medical insurance gets a little bit more confusing than other kinds of insurance because you've got this deductible but once you clear the deductible you might still have to pay a percentage above that amount and then you could have an out of pocket basically maximum which the insurance company would then pay out if you hit that out of pocket maximum that out of pocket maximum is something that you might think of that's your goal possibly to try to self insure against some kind of big problem happening in other words if there was some big injury it would be nice if you had the money to be able to cover the out of pocket maximum so that you can kind of self insure against that that could be kind of like a personal target as you kind of do your whole risk management strategy but in any case let's think about this we're gonna we're gonna make column D a little bit wider and see if we get a better understanding of this we're gonna make this a little bit wider here this is i'm just gonna call this the personal costs and we can say okay what would happen then let's let's make i'm going to select these three cells and do our header formatting as we normally do by going to the home tab font group hit the bucket drop down we'll make that black and white black and white and then we're going to say that the claim will start off with the claim for the insurance for the amount of medical expenses and once we have this set up if you're trying to think about and budget into the future you might just make up a claim number and say well what would happen if these were my costs and that were my costs and worst case and best case scenarios if you set it up in excel then of course you can run worst case and best case scenarios by just changing the data on the left hand side we'll take a look at that in a second so we're going to go after we're done here so we're going to say the claim is going to be that ten thousand dollars so then we're going to say the deductible equals the deductible is going to be five hundred dollars i'm going to say this equals five hundred dollars and so that means that the amount over the deductible i'm going to say if any because i'll change this later just to make sure that it doesn't get us a zero but right now let's just think about if it's over the deductible this equals the ten thousand minus the five hundred and so i'm going to say that's the nine thousand five hundred and let's go to the home tab font group and underline so that means we pay the five hundred but it doesn't just stop there because then we got to pay eighty percent of the amounts over that so now i'm going to say okay this is going to be the percent percent that's a dollar sign percent uh cut percent over deductible percent we pay over let's say out of pocket out of pocket over deductible something like that and so for that if the insurance company is going to be paying eighty percent we're going to pay one or one hundred percent minus eighty percent or twenty percent so i'll do a quick calculation of that it'd be one i'm going to say one or one hundred percent would be more accurate thing to say maybe one or one hundred percent equals i'm just going to put a one there and then we're going to have the percent percent covered by the company by let's say insurance company and that's going to be the eighty percent so this is going to be equal to i'm going to scroll over and point to the eighty percent and then i'm going to make that a percentified cell by going to the home tab number group percentifying it and remember that this one obviously i mean it could be a hundred percent right you can make that into a percent if you wanted to one hundred percent i can then underline here home tab font group and underline so this is going to be i'm going to take this down here and just going to paste it right there and get rid of the colon percent out of pocket over the deductible let's put this in the outer column now this is going to be equal to the one hundred percent minus the eighty percent one hundred minus eighty it's a zero because we need to format the cell so i'll go back on that cell we're going to go to the home tab number group and and percentifies it it's been percentified you best percentify and recognize and then we're going to go you got to recognize that it needs to be percent so this is going to be eighty let's put an underline here home tab and then font group and underline like so and let's do some indentation by selecting these three and do some indentation home tab alignment indent and then i'll select this one down below and double indent double indenting so there we have it i'll make this a little bit larger so we can see the e right there okay so then let's put an underline under that one we're going to go font group and underline and then this is going to be the i'll call it the personal cost not include the deductible personal cost not including the deductible i'm going to make column d a little bit larger a little bit longer a little bit fatter whatever you want to call it you know what i'm talking about and then we're going to say this is going to be the nine thousand five hundred times the twenty this equals the nine thousand five hundred times the twenty percent that we're going to be paying and then we also had to pay the deductible so the deductible and that equaled i'm going to say the five hundred up top so the amount out of pocket personal costs equals the sum of the one thousand nine hundred and the five hundred brackets enter two thousand four hundred dollars let's say that there was a stop loss cap here or something of that four thousand two hundred dollars obviously we're below that so it wouldn't have an impact but then we'll change the numbers and see what it looks like so we're going to say this is a stop loss cap or or out of pop well let's say or out of pocket max something like that lingo on it it's going to be the four thousand that equals the four thousand two hundred so let's put an underline here we're going to go to the hometown font group and underline it so let's call this this is the personal cost let's call this before before the cap and this is personal costs after cap and and what we want to do then is take the lesser of these two numbers so it would be the two thousand four hundred if it was capped then it would take it would take the amount it would cap it at that amount so let's do a men would just do a men calculation it's like the sum but it's an m men equals the m e n m i n brackets of these two numbers so it looks like a sum kind of calculation but with a men and enter so then it takes the two thousand four hundred let's test it if this number came out to be higher like five thousand then it would cap it at the four thousand two hundred that's the point looks like it's working let's undo that and then we'll run a couple scenarios let's make it let make it look nice with a border blue around it i'm going to select the whole thing and make it border blue border blue people home tab font group border and blue if you don't have that blue by the way you could go right here more colors and then standard and you go to this one right there there it is border and the blue so that looks good that looks good so now just a couple things we could we can kind of test this out and say okay well what if this one thing i might wonder about it's like well what if this amount was less than the deductible then this number might get messed up it'll go into negative so let's test that out just to see it i'm going to make this one yellow to note that i'm going to mess with that data input number we started at ten thousand and so let's what if i made this like three hundred and that's all the that's all the costs that we had so now this number has gone negative and we don't want that number to go negative because we don't have this whole calculation down here we'd like it to stop at zero so that's the amount over deductible that's why we added the if any over here so let's do it if logic function so i'm going to delete this and practice our logic function this is going to be equals the if brackets and i want to take the 300 the 300 minus the 500 if that is less than zero then we don't want you to go negative don't go negative comma that means what are you going to do in that case then we don't go negative we just stop at zero and then comma but if it's above zero then what are we going to do we're going to do the calculation we're going to take the 300 minus the 500 which is negative this time so it's going to do zero but if it wasn't then it would be the other way so enter so that's going to be the zero that's what we wanted now if i bring that back up to ten thousand so that look and now it took now it took the greater the the positive number so let's bring it back to 300 the other thing i want to do down here and we have the deductible down here of 500 if it was below the deductible the amount we paid it would be the costs up to deductible so i'm going to say these are going to be the costs costs up to deductible so in other words i want to take the smaller of the 300 or 500 here which would be equal to the men so we're going to do a trustee men function of these two like a sum but with an min instead of the sum and there's the 300 so we could do a calculation like that so we could see if it was below a certain threshold and and now we could start to kind of look at this we could say okay well i can i can have a pretty large amount of of sickness here if i was at 10 000 then i would still be well under the stop loss cap but medical costs can get quite expensive right even in a year you know so if i went like if i went to 15 000 on the medical costs if i went up to 20 000 now i'm over at 20 000 you know if i had a huge bill one year for whatever reason 30 000 then it'd be nice for me to be able to say okay now i could see that the insurance company will cap it out even if i have some kind of catastrophe that cost me like you know over over 30 000 and you might then say okay i'd like to know how much cost i would need here in order to for the stop loss cap to kind of kick in with the medical bills that i might need for it to kick in and to me it's kind of like uh obviously the medical bills if you had a big accident or something like that or a big sickness or something you know you could get quite ridiculous right because the medical whole thing is kind of kind of crazy and how on certain things can be quite expensive so it'd be nice to have some kind of stop loss cap and then you can kind of shoot to be able to possibly be able to uh ensure you know at least on a yearly basis self ensure so that you can cover that max out of pocket and that would be kind of one way you might think about the risk mitigation but you might say okay how much just to just to check it out how high does this number have to be so that so that that cap is going to be hit so in other words i want to know when will this number in essence be at the 4200 so i could use my goal seek to do that so i could i'm going to put my cursor off the cell i'm going to go to the data tab over here and uh data tab that's the one and go to the what if analysis what if what if this happened i'm going to seek a goal we're going to say let's say let's say that we're going to set this cell right there i want you to make that cell excel to be 4200 4200 by changing this cell in other words make this cell whatever it needs to be just do your trial and error until you find the number here that makes that number 4200 okay do it do it excel and so it's at the 19,000 so i've had 19,000 costs above 19,000 medical costs according to this calculation right that's when that stop loss cap would kind of kick in so that's kind of nice to know but again to in my mind it's nice to have this stop loss cap just in case i have some ridiculous medical bill for some crazy reason that was unforeseen and and then try to self insure you know up to that and then try to budget the rest if possible obviously this would you know that would be nice if you're able to do that but that's how you might want to start thinking how you can kind of start thinking about these insurance stuff possibly but if you set up a worksheet like this then of course you could change things like the deductible and let's say well what what if the deductible is higher or lower and uh and so if i had a $1,000 deductible obviously you could take a look at the differences and the calculations related to that if we had a different rate that was being paid by the insurance company over the deductible up to the cap then we can change that data input and do some different comparisons and of course we can have the the stop loss cap changed down here and then run different scenarios based on that data so if you set up your excel worksheets thusly it can give you a lot better understanding and of running the different scenarios just good practice to do so bringing the data back to where it was let's do a quick spell check check on the spelling including that's not how you spell it okay that's that's good