Health Insurance Cost Estimator

A calculator to compare costs and risks across various health insurance plans

This project is licensed under the terms of the MIT license.

Developed by Michael Lines

Please use Github Issues and Discussions to report problems or ask any questions. Also, if you would like to be notified of new releases or updates, use Github Watch button at the top of this page.

Overview:

This spreadsheet is used to evaluate various health insurance plans based on both costs and risks, allowing you to compare them based on your risk tolerance. Low and high costs for insurance are easy - best case is just paying the premiums without using the insurance, worst case is premiums plus the out of pocket max. By using some simple scenarios, this spreadsheet allows you to guesstimate your costs in between these two extremes and pick your best option.

Sample data included is used to illustrate the use of the calculator, and bears no relation to actual plans or vendors. Replace with real plan data provided by your employer or exchange.

Instructions:

1. Download the plans you are interested in as provided by your employer (or Health Care exchange).
2. Go to the plan websites to be sure your doctors or providers are in-network, if this is important to you.
3. Enter the cost and limits from the plan documents as appropriate into the Plans tab - one plan per column up to a max of 12. Use Family limits/costs for any option more than yourself (you and spouse, you and child or multiple)
4. Enter the standard examples for hospital, long term care and ER from the plan document. These are used for your high cost estimate. If you are just modeling individual plans you can enter $0 as you feel appropriate to your situation.
5. Adjust the usage data in the Usage tab according to your estimates of regular, specialist and urgent care visits for your situation. Adjust the visit costs up or down as you feel appropriate to your situation or area.
6. Enjoy figuring out the best insurance option for your situation!

Assumptions:

1. This spreadsheet is designed for ballparking costs and risks, there is no such thing as perfect prediction.
2. When it comes to predicting the future, less is more. The more variables you try to account for, the less accurate your prediction is likely to be past some point. It's a bell curve.
3. Make sure to enter and consider the Out of Area limits and the Provider network scope. These model the risks associated with each plan.
4. Risk comes from costs associated with out of network charges. These can occur in emergency situations where you have no choice of the provider being used (air ambulance and ER are notorious examples)
5. The low cost estimate is based on premiums and just the usage you estimated. High cost is premiums, usage and the plan examples costs.
6. This model assumes usage costs are all in network. It does not attempt to estimate out of network costs.
7. This model does not attempt to model individual costs/limits within a family plan, but instead uses the family limits (assuming you entered these from the plan). A conservative cost estimate in other words.
8. Where costs of usage and major expenses exceeds the OOP max, the max cost is used instead.
9. This model does not take into account employer HSA contributions for HDHP plans. You can manually add these to applicable plans as a credit to annual costs when available.
10. Assumes a semi monthly payroll (2 paychecks a month) in order to calculate monthly and annual costs. If this is not your situation you need to adjust the cost per period or calculation in excel.

Revisions

• 4/25/21 - Version 1.0 - First Release