Insurance spreadsheet
Hi
I am trying to get the spreadsheet to give a sum of zero in the cell if the formula sums to a negative number or keep the positive number if it is greater than 0.
I am solving for how much the insurance company will pay.
Scenario-
If the Amt billed to insurance is <Deductible then the patient will pay
If the Amt billed to insurance is >Deductible then Insurance will pay the amount over the deductible-coinsurance.
After the deductible is paid by the patient, then they will owe the coinsurance % for the amount over the deductible and the insurance will pay the balance.
Example
$1357 billed to insurance
$500 Deductible 20% coinsurance
$1357-500 (pt amount of Ded)
$857 * 20% coinsurance =$171.40
Patient would pay $500 ded and $171.40 coinsurance for total of $671.40.
Insurance would pay $685.60
If the Deductible was $5000 then $1357 would be billed and it would all be patient deductible.
I cannot figure out how to make my spreadsheet work!
Insurance Covered ServicesUnits CostTotal Billed AmountCODEIns Units AETNATotal Contractual AmountRe-exams2 $65.00$130.0099212 $36.61$73.22Manipulations 1-2 reg0 $55.00$0.0098940 $24.56$0.00Manipulations 3-4 reg18 $65.00$1,170.0098941 $34.28$617.04Extra Spinal0 $38.00$0.0098943 $23.06$0.00Estim22 $30.00$0.0097014 $13.28$0.00Traction18 $33.00$594.0097012 $13.82$248.76 Rehab16 $30.00$480.0097110 $26.14$418.24 $2,374.00 $1,357.26Total Insurance Billed Amount $1,357.26Insurance Contractual $1,357.26Estimated Insurance Adjustment $1,016.74Noncovered Services Cash Manipulations3 $60.00$180.00 Cash Re-exam3 $50.00$150.00 Cash Rehab 0 $20.00$0.00 Cash Acupuncture/Dry Needling0 $60.00$0.00 Cash Decompression0 $60.00$0.00 SoftWave0 $ – $0.00 Total Noncovered Services $330.00 Total Insurance and Noncovered Services $1,687.26Deductible to be met: $500.00Services after deductible $0.00Coinsurance 20.00% $171.45Copays $0.00 $0.00Estimated insurance payment $685.81Estimated Patient Balance $1,001.45
HiI am trying to get the spreadsheet to give a sum of zero in the cell if the formula sums to a negative number or keep the positive number if it is greater than 0.I am solving for how much the insurance company will pay. Scenario-If the Amt billed to insurance is <Deductible then the patient will payIf the Amt billed to insurance is >Deductible then Insurance will pay the amount over the deductible-coinsurance. After the deductible is paid by the patient, then they will owe the coinsurance % for the amount over the deductible and the insurance will pay the balance.Example$1357 billed to insurance$500 Deductible 20% coinsurance$1357-500 (pt amount of Ded)$857 * 20% coinsurance =$171.40Patient would pay $500 ded and $171.40 coinsurance for total of $671.40. Insurance would pay $685.60 If the Deductible was $5000 then $1357 would be billed and it would all be patient deductible. I cannot figure out how to make my spreadsheet work! Insurance Covered ServicesUnits CostTotal Billed AmountCODEIns Units AETNATotal Contractual AmountRe-exams2 $65.00$130.0099212 $36.61$73.22Manipulations 1-2 reg0 $55.00$0.0098940 $24.56$0.00Manipulations 3-4 reg18 $65.00$1,170.0098941 $34.28$617.04Extra Spinal0 $38.00$0.0098943 $23.06$0.00Estim22 $30.00$0.0097014 $13.28$0.00Traction18 $33.00$594.0097012 $13.82$248.76 Rehab16 $30.00$480.0097110 $26.14$418.24 $2,374.00 $1,357.26Total Insurance Billed Amount $1,357.26Insurance Contractual $1,357.26Estimated Insurance Adjustment $1,016.74Noncovered Services Cash Manipulations3 $60.00$180.00 Cash Re-exam3 $50.00$150.00 Cash Rehab 0 $20.00$0.00 Cash Acupuncture/Dry Needling0 $60.00$0.00 Cash Decompression0 $60.00$0.00 SoftWave0 $ – $0.00 Total Noncovered Services $330.00 Total Insurance and Noncovered Services $1,687.26Deductible to be met: $500.00Services after deductible $0.00Coinsurance 20.00% $171.45Copays $0.00 $0.00Estimated insurance payment $685.81Estimated Patient Balance $1,001.45 Read More