Create a health insurance reimbursement simulator in Excel
Solved
Le_Maniouk
Posted messages
349
Status
Member
-
isa47 -
isa47 -
Hello,
I'm looking for an Excel file (or to create one) that can calculate what my mutual insurance will reimburse based on the social security reimbursement.
For example: I'm going to a doctor who charges €30 for the consultation (compared to €23 for the conventional rate). Of the €30 paid, social security reimburses 70% of the conventional rate (of €23), which is €16.10. So if I do €30 - €16.10 = €13.90 remains my out-of-pocket expense (excluding reimbursement from my mutual insurance). Therefore, from the perspective that I am reimbursed at 100% of the social security reimbursement, my mutual insurance will reimburse me a MAXIMUM of €16.10 but since I have only €13.90 left to pay, it will not exceed that amount.
What I'm looking for is to create a table where I can enter the social security reimbursement (which will remain fixed) and the amount I pay. And that it gives me the reimbursement of my mutual insurance based on my coverage.
I'm not sure if I explained myself well, but I count on you to help me. I know there are plenty of simulators online, but when I go to an appointment, I don't always have internet access. Otherwise, I have to make a request to the company, but they don't respond within 2 days.
Thank you very much
--
- Tell me about yourself instead.
- Odile! I'm Odile! Pluto is Mickey's friend!
- Oh no! Pluto is Mickey's dog! Mickey's friend is Goofy!
I'm looking for an Excel file (or to create one) that can calculate what my mutual insurance will reimburse based on the social security reimbursement.
For example: I'm going to a doctor who charges €30 for the consultation (compared to €23 for the conventional rate). Of the €30 paid, social security reimburses 70% of the conventional rate (of €23), which is €16.10. So if I do €30 - €16.10 = €13.90 remains my out-of-pocket expense (excluding reimbursement from my mutual insurance). Therefore, from the perspective that I am reimbursed at 100% of the social security reimbursement, my mutual insurance will reimburse me a MAXIMUM of €16.10 but since I have only €13.90 left to pay, it will not exceed that amount.
What I'm looking for is to create a table where I can enter the social security reimbursement (which will remain fixed) and the amount I pay. And that it gives me the reimbursement of my mutual insurance based on my coverage.
I'm not sure if I explained myself well, but I count on you to help me. I know there are plenty of simulators online, but when I go to an appointment, I don't always have internet access. Otherwise, I have to make a request to the company, but they don't respond within 2 days.
Thank you very much
--
- Tell me about yourself instead.
- Odile! I'm Odile! Pluto is Mickey's friend!
- Oh no! Pluto is Mickey's dog! Mickey's friend is Goofy!
5 answers
-
Hello
You can build your file like this:
In A1 the cost of the procedure: 30
In B1 the security rate: 23
In C1 the security rate: 70%
In D1 the security reimbursement: =B1*C1
In E1 the mutual reimbursement rate: 100%
In F1 the mutual reimbursement: =IF(D1*E1>A1-D1;A1-D1;D1*E1)
In F1 remaining charge to me: =A1-D1-F1
Best regards
--
"Imagination is more important than knowledge." A. Einstein
