Sometimes, a dental provider may refuse to bill all charges to the insurance company, either because they are not in network, or because they claim that the insurance does not cover the procedure. In such cases, the patient may have to pay the full amount out of pocket, or submit a claim to the insurance company themselves.
One way to handle this situation is to use an Excel formula to calculate how much the insurance company should reimburse the patient, based on the itemized bill and the insurance policy benefits. This can help the patient to verify the accuracy of the provider’s charges, and to determine the amount they need to pay or claim.
Procedures
To calculate the insurance reimbursement using an Excel formula, the following steps are needed:
- Obtain an itemized bill from the dental provider, showing the charges for each procedure and the billing codes.
- Obtain the insurance policy benefits, showing the percentage of coverage for each procedure and the billing codes.
- Create an Excel table with the following columns: Procedure, Billing Code, Charge, Coverage, Reimbursement, and Balance.
- Enter the data from the itemized bill and the insurance policy benefits into the table, matching the billing codes.
- In the Reimbursement column, enter the formula
=C2*D2, where C2 is the charge and D2 is the coverage. This will calculate the amount that the insurance company should pay for each procedure. - In the Balance column, enter the formula
=C2-E2, where C2 is the charge and E2 is the reimbursement. This will calculate the amount that the patient should pay or claim for each procedure. - Sum up the Reimbursement and Balance columns to get the total amounts.
Example
Suppose a patient had a root canal treatment by an oral surgeon who is in network with their insurance. The office told the patient that their insurance does not cover the procedure, and charged them $1500. The patient asked for an itemized bill and found that the billing codes were D3310 for the root canal, D2950 for the core buildup, and D2740 for the crown. The patient checked their insurance policy benefits and found that their insurance covers 50% for D3310, 80% for D2950, and 50% for D2740. The patient wants to calculate how much the insurance company should reimburse them, and how much they should pay or claim.
The Excel table for this scenario would look like this:
| Procedure | Billing Code | Charge | Coverage | Reimbursement | Balance |
|---|---|---|---|---|---|
| Root Canal | D3310 | $500 | 50% | $250 | $250 |
| Core Buildup | D2950 | $300 | 80% | $240 | $60 |
| Crown | D2740 | $700 | 50% | $350 | $350 |
| Total | $1500 | $840 | $660 |
The formula for the Reimbursement column is =C2*D2, and the formula for the Balance column is =C2-E2. The total reimbursement is $840, and the total balance is $660. This means that the patient should pay or claim $660, and the insurance company should pay $840. The patient can use this information to dispute the provider’s charges, or to submit a claim to the insurance company.
