I work for a rural community health center where I both help consumers choose health insurance plans (at no cost to them) and support other community assistance workers in doing the same. I am trying to create a tool that will help consumers understand how the insurance plans they are considering will operate under different scenarios of health care services.

After many hours of youtube, I have created a sheet that gets me frustratingly close. I have all my data and I can set up the comparisons but I have to calculate the costs for services manually with a calculator because I can't figure out the formulas. Ideally, I would choose 3 plans to compare, select the services for the scenario and see an estimate of how much each plan would cost the consumer.

All of the required data is there and next year when the plans change, it will be easy to update. Here is the description of what I need it to do:

For a specific plan at a specific level of cost sharing reduction (CSR) find the consumers share of the full price for the selected service based on whether or not the deductible has been met and when the deductible amount reaches zero apply the after deductible cost sharing to the balance of the service cost.

On the sheet called "High Cost Scenario" I need to calculate "Your Share" from the information in the columns labeled "Before Deductible Copay," "Before Deductible Coinsurance," "After Deductible Copay," and "After Deductible Coinsurance." It needs to factor in overall "Medical Deductible" limit, "out of pocket maximum" limit and any prescription deductible limits.

I have attached the spread sheet because this it is much easier to see than to explain.

The end result will be shared freely with other community assistance workers who help consumers. The people who will get this tool, will not ever charge for the service. It will be used to help provide "benefits counseling" and to help with plan selection. Community assistance workers remain impartial as to plan selection but need help explaining how insurance plans work to consumers.

My project (Rural Action Enrollment Network or RAEN) is part of an effort to increase the health literacy of consumers purchasing health insurance in the ACA health insurance marketplaces. We also hope to improve the enrollment process so that consumers are choosing plans that truly meet their individual or family health needs.

One the complexities of this is to show consumers that often plans with the lowest premiums have cost-sharing structures that often cost more in the long run than plans with more expensive premiums.

Thanks for considering this request!
David Stewart
david@raen.org