Instructions for ACA Multiplan Calculator, release date 11/30/2013
Written by Paul Downs
Introduction and Disclaimer:
I wrote the ACA Multiplan Calculator in order to facilitate analysis of my own company’s health insurance options. It took me one day to complete it, and it is quite likely that there are errors built into it somewhere. I am not an Excel master, so I imagine that there are better ways to do the calculations and lay out the information.
I offer the spreadsheet to all users under the following conditions:
-Use it entirely at your own risk. I do not guarantee that the logic is correct (although I think that it is) or that the results will be honored by your insurance company. It replicates the logic of premium pricing, to the extent that I understand it. However, there may be other factors that I have not taken into account.
-It has not been reviewed by lawyers. I do not offer any assurance that the information it develops, and any course of actions that you decide to take on the basis of information you generate, are legal. You assume all risk of the decisions you make after using it.
-I claim no copyright or other rights over the intellectual property within the sheet. Use it and modify it any way you want. You are explicitly free to develop a business around the concept I have developed. I waive all rights to any future development of this spreadsheet. However, if you try to patent any of the processes I have written, please note that it will be easy to prove prior art, as I have published the sheet in the New York Times. If you develop your own calculator, put it up on the web, and sell it for billions of dollars, I congratulate you. I want nothing from you.
The Logic Behind the Calculator
I have incorporated the age/cost curves developed by the government for the ACA. Using these ratios, it is possible to generate a complete age/cost curve for non-tobacco and tobacco users from a single premium price at a given age. HHS does not require every state to use the federal age/cost curve, and I could not find a list of states that are not using it. It’s a safe assumption that every plan offered on the Federal Exchange (you can download them as an Excel sheet by clicking this link) will conform to this curve. However, the curve may be changed in the future.
In the course of analyzing my own quotes, I found that every premium and quote that I received, whether from my agent, directly from my insurance company, or from the Federal Exchange, conformed to the HHS curve. I looked at more than 30 plans, from 3 different companies, and they all worked the same way. Agent commissions seem to be baked into these numbers. I think that it’s reasonable to use the plan costs that the calculator generates to estimate overall company costs. But I don’t guarantee it – you will have to get confirmation on pricing from whomever you choose to provide your insurance.
The calculator assumes that you have multiple employees and that you want to consider multiple plan options for them. Without the calculator, it is difficult to model the effects of putting your employees in multiple plans. Asking agents for multiple quotes takes forever, and the information is likely to arrive in a confusing format. The Calculator allows a business owner to spend a reasonable amount of time modeling scenarios, so that you can ask your agent for the quote you really want – the one that lowers your costs.
What the Calculator does NOT do:
The calculator does NOT provide any information as to the details of each plan, i.e. whether the particular mix of costs and copays (which vary widely from plan to plan) are a good fit for you or your employees. It ONLY estimates premium costs, and shows the effects of two ways for an employer to share that cost with workers: by a percentage split, and by a defined contribution. It also allows for employer contributions to a Health Savings Account. You will need a decision as to what plans to include in the calculator, based on your own analysis of your needs.
The calculator also does not guarantee that any particular configuration of plans you choose, or any assignment of workers into those plans, is legal, or will be honored by an insurance agent. I think that it’s extremely unlikely that you could purchase plans from more than one company to offer to your employees, and I know for a fact that you cannot force employees to sign up for a given plan (unless you only offer one.) Even then they have the option to decline coverage and deal with the consequences on their own.
The calculator also does not guarantee that the plans you choose and the cost sharing schemes you invent will pass the affordability tests built into the ACA. It also does not predict whether you will qualify for a tax credit, or whether you might be responsible for penalty payments. It’s just for estimating premium costs. That’s it.
How the Calculator Works
I have developed the sheet to perform two kinds of analysis: of an individual health plan, and also of the aggregated cost of a group of plans. The Calculator contains 4 kinds of sheets:
Summary Sheet: There is one sheet, called “Cost Summary”, that presents a summary of costs of the plans that you place into a group.
Boundary Sheets: There are two of these. One is called “L-->”, the other is called “<--R”.
Any Plan Sheets that you drag between these two will be included in the aggregation on the Cost Summary Sheet. You can see the costs of different groups of plans by putting different configurations in a group. Just be sure that you don’t leave all of your employees in multiple plans in the group, or your premiums will be vastly overstated. You want the total number of enrollees in all of the sheets. Cells G4, H4, and I4 show the total number of employees in the sheets between between “L-->” and “<--R”.
Plan Sheets: There are ten of these, called “Plan_1, Plan_2,” etc. Plan _1 has been placed between the Boundary sheets, but has no information put into it, so the Summary will show zeroes when you open the file.
There are also two example sheets that I have filled out with data from my own company, and quotes that I received from local insurers, that you can take a look at to see how your sheets should look. Those are called “IBC_PPO_Bronze_HSA” and “Keystone_Gold_Premier_HMO”. You might want to try dragging these between “L-->” and “<--R”, just to see what happens to the Cost Summary.
Extra Plan Sheet: this is labeled “Blank_Plan_Page” and is just an extra that you can mess around with or make copies of for your own experiments.
What you need to get started:
You will need a complete roster of your employees and dependents. You need to know everyone’s age, and whether they smoke or not. ACA pricing is by individual. Family prices are composed of the individual prices of the members. Each child will be priced separately, although you will be charged only for the first 3 children in each family. (4th or more: free, don’t include them in the roster.)
You will also need a sample premium in order to populate the age/cost curve for each plan. You can get these in lots of places – many private insurance companies offer calculators that will give you a number for a person of a given age. Here’s an example from my own insurance company. You can also get sample premium costs directly from Healthcare.gov (you can download them as an Excel sheet by clicking this link). This sheet has thousands of plans on it, so use the filters to narrow down to those offered in your region. Then you will see pricing for individuals aged 27 and aged 50 – either of those can be used to generate the complete curve. Be sure to make a note of which plan you are taking the numbers from.
Start by Filling out Individual Plan Sheets
-Choose a sheet, could be any of the blank ones
-You will be adding information to some of the aquamarine colored cell(s). The forest green cells will autocalculate and have the numbers you want to pay attention to. The light sage green cells will autocalculate with the premium grid. I have put lots of notes and warnings to give you guidance as to what you need to enter in the aquamarine cells, and warnings if you mess with a cell that you shouldn’t edit. They can be annoying, so I apologize, but I don’t want you to accidentally modify a cell that has important coding in it. You can always click on and drag those little yellow boxes to a more convenient location.
-Look for the little red triangle in the corner of a cell, and hover your mouse over it for guidance. Also take a look at the two example sheets to see how I intended the sheets to work.
-Start by typing the plan name that you are considering into cell A2. Stick to the format shown – no spaces. You might want to rename the sheet to match the plan name, but it’s not required. If you do rename the sheet, it’s easier to set up scenarios by dragging groups of plan sheets between the boundary sheets.
-Next enter the percentage of the plan that the employer will pay for into cell B5. The employee percentage willautocalculate.
-If you want to investigate a defined contribution, enter a number PER ENROLLEE into cell B8. This would not necessarily be a number per employee, but a number for each person, spouses and dependents included. The calculator does not analyze your cost if you only give out a specific dollar amount per employee, without considering whether that person has dependents or not. That’s too complicated for me to program.
-You will need to figure out your state’s tobacco surcharge and enter it into cell B10. An insurance agent might be able to tell you what your surcharge is, but you can also figure it out by running a calculator for a person of the same age, both smoking and non smoking, and compare the two numbers. Surcharges vary by state; some have none.
-You can choose to subsidize the increased premium of your smokers or not. If you want to pay for that extra cost, put “yes” in the box. Otherwise you can leave it blank or put “no”.
-If the plan is HSA qualified, you might want to make an initial contribution for each employee. You will need to enter the number of employees (this will be not include spouses and dependents) in cell B13, and the amount you want to contribute per employee in B14. The calculator considers this to be a one-time, not monthly, cost.
-Next enter a single premium amount, generated from an agent’s quote, the Healtchare.gov calculator, or a company calculator, in column E. Place the quoted premium amount in the proper age column.
-Once this is done, the premium grids will populate.
-Now add the proper number of enrollees (including employees, spouses, and dependents) in the proper age rows for both tobacco and non-tobacco. You will see the total number of enrollees and the premium amount summed in row 70.
Once you have added all of that, look in the forest green areas in columns A and B for analysis of company and employee costs.
Using the Summary Sheet
The summary sheet is divided into two sections. The top portion, in green and yellow, summarizes the premium costs of the plans you drag between “L-->” and “<--R”. There are also light blue boxes where you can enter your existing plan costs to make comparison easier. Cells G4, H4, and I4 show how many people have been included in the summary calculations – I anticipate that it will be easy for people to double enter employees in multiple plans and end up with numbers that are too high. Watch those totals to make sure you are considering the proper number of enrollees.
The bottom section of the sheet is a list of all plan sheets, showing the configurations you have entered. This is a quick reference so that you can keep track of the different splits, etc. You can look here instead of clicking back and forth into different plan sheets.
The name you enter into boxes A18-A39 must match the sheet name EXACTLY, and cannot have spaces. I have pre-entered the sheets named “Plan_1” through “Plan_10” and the two example plans to show you how it works. Plans 1-10 have no data in them, so no values appear. As soon as you start entering details in those sheets, numbers will appear.
Employee Module
These sheets are designed to produce a printable report that you can give to your workers, comparing the PREMIUM cost of each plan to the previous year. I customized this to reflect the choices that we offered last year. I have included a sample showing my own family for your reference.
To use it:
-Make a copy of the Employee_Summary sheet and rename it Employee_EmployeeName (the actual name of your employee)
-You will be entering information into (some of) the bright turquoise squares.
-Start by entering the name of each member of the employee’s family, and their age.
-Scroll down and enter the number of people at each age, in the smoking or non-smoking section, just as in the other spreadsheet. Do not change the premium amount ($100).
-Enter the employer percentage of the premium cost in cell G2. Cell G3 will automatically calculate the employee percentage.
-In cell F3, enter the total premium amount that that covered this employee in 2013. The employer/employee split amounts will auto-calculate in cells G3 and H3.
-In cells A12 to A22, enter the exact sheet name of any plans you want to consider. These names cannot have spaces. As you enter them, you will see the cells adjacent auto-calculate. The bright green cells will show the comparison between this year’s premium and last year’s premium. The calculations will reflect the splits you entered into the plan sheets, and whether the company subsidizes tobacco or not.
-The Employee_Summary sheet does not include any dental costs. The Employee_PaulDowns sheet does – you can look at the coding to see how I did it. I’m not sure how dental is calculated in your own company, so I didn’t try to build it into the Employee_Summary sheet.
Now You Can Use the Calculator
I hope that these instructions give you enough to get started. I presume a familiarity with Excel – if you are a business owner and have never learned how to run a spreadsheet, you have more problems than this calculator can solve.
I probably don’t have the resources to answer questions about the calculator – I’m just one person, and I have a job already. But if you want to tell me whether you found it useful or not, or send me an improved version, I would be very happy to get email. Please don’t call me, just email me at:
Thanks! And I hope you save some money!