Medical Premium Function/Formula Needed

Hello All,

I'm creating a workbook for a friend of mine who is trying to budget her family's medical expenses over the next 10 or more years depending on how many children she has once she is married.

It's easy to figure out her own medical premiums by simply multiplying her monthly premium by 12 to get the annual premium; very simple stuff. the problem arises when you begin to add people to the home, such as a husband and children. It would be easy if the premium just doubled or tripled depending on the amount of people you add to a plan, but it doesn't. Here's the figures:

1 adult = $185/mo

add a 2nd adult and the premium jumps to $534/mo, which is a $349 difference.

1st child jumps a married couple premium to $967. That 1st child costs $433/mo

Every child after that costs $292/mo each.

So,
First adult = $185/mo
2nd adult = $349/mo
1st child = $433/mo
2nd child = $292/mo
3rd child = $292/mo
4th child = $292/mo
etc.

What we would like to do is have monthly premium column (Column D) change every time an adult and or child is added to the plan. I only copied a small portion of the book because i can just drag, copy, and handle the simple math later; the function or formula is the most important thing right now.


medical premium photo.jpg


If anyone can lend a helping hand i would appreciate the advice. Thank you to all who offer their time and help!

Sincerely,
Endar

See attached file. I've set up your tables slightly differently.

Hope this helps.

Pete

that works! excellent job. Now if i wanted to add in a column for inflation percentage, how could i connect the two so that every cell from D3 could be affected by an inflation jump in costs?