Create a Medical Bill Format in Excel for Administration

Medical bill format in excel for Administration

hi thanks for watching this plan to the excel video in this video we're going to go over the basics of how your health insurance plan splits the bill between you and your health insurance to put the plan so I just pulled this off of a off of the exchanges the healthcare.gov website i just picked up kind of a more or less random plan and the key number that we see 44 to help us to understand what i want to hang on what my insurance without paying is this deductible this out of pocket maximum and this co-insurance right here and and so when you're looking at your what your health insurance plan is going to the paying these are the three numbers that you need you need to know your deductible your out-of-pocket maximum and your coinsurance rate so we're going to put this in in an excel format so we've got a ductile the stats are out of pocket maximum and this is your coinsurance rate so let's just pretend we have this the same plan that we have here so 5750 deductible 6550 so 5750 deductible 6550 out-of-pocket maximum and a co insurance that was thirty percent so now we need to figure out how much of my annual medical bills am i going to end up paying and how much is my health insurance canada pink so let's just put a number in here let's just suppose i accrue eight thousand dollars worth of medical bills in a given year so what is the way these numbers work is a deductible means that you up until you hit your deductible one hundred percent of all the medical bills that you accrue you have to pay and then after you hit your deductible you end up splitting your medical bills you pay thirty percent your employer insurance company pay seventy percent up until you end up paying a total out-of-pocket maximum of six thousand five hundred and fifty dollars after your out-of-pocket maximum your health insurance company pays 100 percent of your medical bills as long as you stay within network that's very important to know is is you have to stay with a network in order for you to get access to to all these bills so what am I going to end up paying so first I'm gonna do is I'm going to take if my annual medical bill is left and my deductible then how much of am I going to the pain well I'm just simply going to pay the entire medical bill but if my annual medical bills are larger than my deductible then I end up paying my deductible plus the the remaining amount of a difference between the remaining now I am required to pay thirty percent event so because I've got co-insurance so this part right here this says if my total medical bills are greater than my deductible then I pay my deductible plus whatever's beyond the deductible I split with my insurance company and I pay thirty percent so in this case if I fi eight thousand dollars worth of bills I would end up paying 6425 of that and my insurance company would end up paying the difference so 1575 now what happens now let's say what happens if if I have a twenty-five-thousand-dollar medical bill my out-of-pocket maximum is 6550 well this is saying IL 11 thousand dollars which is not true so then what we need to do is we need to put a min function that's going to give me the minimum of my out-of-pocket maximum and whatever the the bill amount is so any bill beyond basically beyond eight thousand dollars I'm going to hit my out-of-pocket maximum and my insurance is going to pay a hundred percent after that and so this formula right here where will calculate how my medical bills in a given year are going to be split between me and my health insurance company hope that was useful thanks for watching