need help developing an excel formula. specifically the total hospital cost of a single staff member

need help developing an excel formula. specifically the total hospital cost of a single staff member on a high-deductible plan. if the initial hospital bill is 5000, the total cost to a single/staff only member should be 4321.16.

Background: You just landed you first full-time job and you reviewing the health plans available from your employer. Your 22 and single and you have no clue which plan would be the best for you so you decide to make a budget in excel to determine which plan would be least costly.    High Deductible Staff Only $29.00 Staff & spouse / domestic partner 72.5 Staff & Child(ren) 61.5 Staff & Family 105 Semi-Monthly HSA Company Contribution: Individual / Other Coverage Levels $29.16/$58.33 Plan Coverage Limits: High Deductible Annual Deductible: Individual $3,250 Family $6,450 Annual Coinsurance Maximum (excludes deductible) Individual $2,000 Family $4,000 Annual Out-of-pocket Maximum Individual $5,250 Family $10,450 Doctors’ Office Visits Deductible, then 10% coinsurance

You also note that all the plans cover 100% of preventative and pre-natal care, so all the plans will treate hospital bills the same. There is also additional cost for using an out of network doctor so you call your doctor and make sure they accept your company’s health insurance provider.

Based on your research you also find out that you can only have a health savings account (HSA) if you select the high or middle deductible. The company will only contribute to your HSA if you select the high deductible plan as indicated in the first table above. You can make additional contributions from your pay check pre-tax up to $200 semi-monthly.