To track uncovered treatment costs, add a row for every bill where the insurer paid less than the amount billed, and give each row a reason code taken from the insurer's own letter: exclusion, limit, waiting period, non-payable item or co-payment. After a year, totalling by reason shows which kind of gap actually costs you most.
Why a reason code, not just a number
A single running total of "money the policy did not pay" tells you how much, but not why. And why is what you can act on. A gap caused by a waiting period behaves differently from one caused by a room limit, and both differ from a bill that was short of documents, which you may be able to fix. Reasons also help you ask better questions at renewal.
Which reason codes should I use?
Use short codes and take the wording from the settlement letter or claim response, not from guesswork. A workable set:
- EXCL: the insurer says the treatment or item is excluded
- LIMIT: paid up to a cap, such as a sub-limit, and you paid the rest
- WAIT: the claim fell inside a waiting period
- ITEM: individual items listed as non-payable on the settlement
- COPAY: your agreed share of the claim
- DOCS: rejected or reduced for missing papers, which may still be recoverable
- OTHER: anything the letter explains differently, with a note
If the letter does not state a reason, write "no reason given" in the note and ask the insurer for one in writing. Do not choose a code on their behalf.
The column layout
One row per claim or bill. Columns: A Date, B Patient, C What it was, D Amount billed, E Insurer paid, F Uncovered, G Reason code, H Letter or claim reference. In Excel or Google Sheets, put this formula in F2 and copy it down: =D2-E2.
If you pay a bill and have not yet heard from the insurer, leave E empty and the Reason blank, and mark it pending in the note. Come back and fill it in when the letter arrives.
A worked example for one year
Say these claims happened in the year. The amounts are hypothetical.
| What | Billed (Rs) | Insurer paid (Rs) | Uncovered (Rs) | Reason |
|---|---|---|---|---|
| Surgery with implant | 80,000 | 66,000 | 14,000 | LIMIT |
| Procedure soon after buying the policy | 22,000 | 0 | 22,000 | WAIT |
| Supplementary therapy | 6,500 | 0 | 6,500 | EXCL |
| Consumables listed as non-payable | 3,200 | 0 | 3,200 | ITEM |
| Hospital stay for a relative | 24,000 | 19,200 | 4,800 | COPAY |
| Total | 135,700 | 85,200 | 50,500 | Mixed |
The uncovered total is 14,000 + 22,000 + 6,500 + 3,200 + 4,800 = ₹50,500, which also equals 135,700 minus 85,200. The biggest single reason is the waiting period at ₹22,000, followed by the limit at ₹14,000. Knowing that, the questions for renewal are specific: how long are waiting periods, and what are the sub-limits?
How do I total by reason?
With reason codes in G and uncovered amounts in F, use one SUMIFS per code:
- =SUMIFS($F$2:$F$200, $G$2:$G$200, "WAIT")
- =SUMIFS($F$2:$F$200, $G$2:$G$200, "LIMIT")
- Grand total: =SUM($F$2:$F$200)
Type the codes exactly the same way every time. A dropdown on column G prevents "Wait" and "WAIT " being counted separately.
Is an app useful here?
The insurer's payment and the hospital's bill appear in different places, so a record is partly manual whichever tool you use. SpendixAI can bring in the card or bank statement rows for the hospital payments and keep scans of the settlement letters with the matching transactions, which saves hunting for them. The reason-code column is a note or sub-category you maintain yourself.
At renewal time
- Total the year's uncovered amount and the amount by reason.
- List the two reasons that cost most.
- Ask the insurer or your adviser how the current policy treats those two things.
- Compare any alternative on those same points, not just on premium.
This is a way to see your own history, not a recommendation of any policy.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

