Forum Discussion
Solved
Calculate Payout using Individual and Family Deductibles
I am consulting for a company whose services are similar to an health insurance company, where each person has a deductible before the company begins paying on their medical bills. However, the company has an individual deductible (per person), but then also have a family deductible, which is typically 2x the individual deductible. Once one of the deductibles is met, the company will begin paying out a certain percentage of medical bills, say 90%. I want to be able to manipulate the numbers for individual deductible, family deductible, and percent of payout to show projected costs based on a certain timeframe of allowed medical bills.
For example, the individual deductible is $1,000 and the family deductible is $2,000.
- So in one family, if person #1 has medical bills over the individual deductible of $1,000, the company will pay 90% on their losses after the deductible. If person #2 has medical bills over their individual deductible of $1,000, the company will begin paying 90% on their medical bills after the deductible, and 90% on all the other family members' medical bills as well, because the family deductible of $2,000 was met.
- This is also aggregate, so if 4 of the family members each have medical bills of $500, this would equal the $2,000 family deductible, so the company would begin paying 90% on all family members.
Each person has a "family" number and a "person" number. So the formula needs to look at how many people are in the family based on the duplication of the "family" number:
- If there's just one person, it will return the amount of medical bills, less individual deductible ($1,000), multiplied by percentage of payout (90%).
- If there's two or more people, it will:
- look to see if the total of the family's combined medical bills is greater than the family deductible ($2,000);
- If so, it will return the total medical bills of all family members, less family deductible, multiplied by percentage payout (90%).
- If not, then it will see if any one person in the family has medical bills over the individual deductible ($1,000);
- If so, it will return that person's medical bills, less individual deductible, multiplied by percent of payout (90%).
- If not, it will return 0.
- look to see if the total of the family's combined medical bills is greater than the family deductible ($2,000);
Is this possible?? I've attached a file with some sample info. Any help you can give is greatly appreciated!!
bryson8420 OK didn't catch that. So is that maximum any individual can count toward the family deductible = to the individual deductible or does the 10% they pay on services over that amount also count? assuming the former I think this will work:
=CompPerc*MAX( MIN(SUMIFS($C$1:$C2,$A$1:$A2,$A2,$B$1:$B2,$B2)-Individual,$C2), MIN(MAX(REDUCE(0,UNIQUE(FILTER($B$1:$B1,$A$1:$A1=$A2,0)), LAMBDA(p,q,p+MIN(1000,SUMIFS($C$1:$C1,$A$1:$A1,$A2,$B$1:$B1,q)))) +$C2-Family,0),$C2), 0)EDIT: I got to wondering how I would have done this 'old school' before LAMBDA and figured I would probably add a couple helper columns. These helper columns actually help see what is happening so you might consider this option. I created a column for amount of $ going towards Individual deductible, amount of $ going towards Family deductible, and then resulting payout based on that.
$ to IND = IF(C2,MIN(Individual-SUMIFS($F$1:$F1,$A$1:$A1,$A2,$B$1:$B1,$B2),$C2),"") $ to FAM = IF(C2,MAX(MIN(Family-SUMIFS($F$1:$F1,$A$1:$A1,$A2),$F2),0),"") Payout = IF(C2,(C2-MIN(F2:G2))*CompPerc,"")See attached.
Yes, it is possible to calculate the payout based on individual and family deductibles using the described logic. Here's an example formula (without having opened your file, for your own security reasons) that you can use in Excel to achieve this calculation:
=IF(COUNTIFS(A:A,A2)>1,IF(SUM(B:B)>family_deductible,SUM(B:B)-family_deductible,0),IF(B2>individual_deductible,B2-individual_deductible,0))*payout_percentage
Here's how you can use this formula:
- Assume that column A contains the "family" number and column B contains the medical bills for each person.
- Replace family_deductible with the value of the family deductible (e.g., $2,000).
- Replace individual_deductible with the value of the individual deductible (e.g., $1,000).
- Replace payout_percentage with the percentage of payout (e.g., 90%).
- Enter the formula in the desired cell (e.g., in column C next to each person's medical bills).
- Copy the formula down for all the individuals in the family.
The formula will check the number of occurrences of the "family" number in column A.
If there is more than one occurrence, it will calculate the payout based on the family deductible.
If the total medical bills for the family are greater than the family deductible, it will subtract the deductible from the total.
If there is only one occurrence of the "family" number, it will calculate the payout based on the individual deductible.
If the individual's medical bills are greater than the individual deductible, it will subtract the deductible from the individual's medical bills.
The calculated payout amount will be displayed in column C for each individual.
Remember to adjust the cell references and values according to your specific data and requirements.
I hope this helps!
NikolinoDE I copied your formula, and I think I replaced all the cell/value references correctly, and it is not quite right yet:
The families of only one person (row 11 and 20) are correct, but if there are >1 member in the family, it is calculating the entire B column less 2,000 times .9.
- Thanks for your quick response! The file I uploaded is fake data with no private information, so you are free to open it! I will try to apply this formula and let you know! Thanks for now!
bryson8420 alternatively
=MAX(0,SUMIFS($C$1:$C2,$A$1:$A2,$A2)-Family,SUMIFS($C$1:$C2,$A$1:$A2,$A2,$B$1:$B2,$B2)-Individual)*CompPercI'm assuming the list is in date order (i.e. first claim, second claim, ....) and hence payout must be a cumulative summing and hence the SUMIFS use $1:x format to sum down to this line.
The formula takes MAX of Family sum - Family deductible OR Individual sum - Individual deductible OR 0 (i.e. both of the others are < than deductible)
