How to Track Medical Expenses in a Spreadsheet

You have a folder — physical or in your inbox — with bills, Explanation of Benefits statements, and a couple of things you’re not sure are bills at all. Somewhere in there is the answer to three questions you’d like to be able to answer quickly: how much have we actually spent on healthcare this year, have we met the deductible yet, and is any of this worth claiming on our taxes.

The reason it’s hard isn’t that there are a lot of pieces of paper. It’s that healthcare produces three different numbers for every single visit, and most tracking systems only record one of them.

The three-number problem

Every medical encounter generates:

1. The billed amount — what the provider charges. Largely fictional if you have insurance.
2. The allowed amount — the negotiated rate your plan and the provider agreed on. This is the real price.
3. Your responsibility — the slice of the allowed amount that falls on you, determined by whether you’ve met your deductible, your copay, and your coinsurance rate.

A typical office visit might be billed at $340, allowed at $186, and leave you owing $186 (pre-deductible), $37.20 (20% coinsurance), or $30 (a flat copay) depending on where you are in your plan year.

If your tracker has one column called “amount,” you have thrown away the two numbers that answer your questions. Every good medical expense tracker starts by keeping these apart.

The columns you actually need

Here’s a copy-ready structure. Build this in Excel or Google Sheets and you can answer every question below without touching the paperwork again.

Fifteen columns sounds like a lot until you notice that ten of them are copied straight off the EOB in about ninety seconds, and that they’re the only reason you can later answer “wait, why did we owe that?”

Two of those columns do more work than the rest.

“Applies to deductible?” exists because not everything counts. Premiums never count toward a deductible. Flat copays sometimes don’t, depending on plan design. Out-of-network care may count toward a completely separate out-of-network deductible. If your running total quietly includes premiums, you will think you’re closer to your deductible than you are — and you’ll be surprised in November.

“Payment source” exists because paying with HSA dollars, FSA dollars, or a credit card leads to three different tax outcomes for the same expense. Get this wrong and you can accidentally take a Schedule A deduction for something you already paid with pre-tax HSA money, which isn’t allowed.

A worked year

Here’s a whole plan year for an illustrative household — two adults on a family HDHP. Every figure below is an assumption for the example, not a national average. Put your own plan’s numbers in.

Plan assumptions: family deductible $3,400, family out-of-pocket maximum $9,000, coinsurance 20% after deductible, monthly premium $520 (which counts toward neither).

Three things fall out of this table that a shoebox of receipts will never tell you:

The deductible broke in June. Everything from that point cost 20% instead of 100%. The October physical therapy course had an allowed amount of $1,240 but cost the household $248. If that same PT course had happened in March, it would have cost $1,240. That’s the single most actionable fact medical tracking produces: once your deductible is met, elective care gets dramatically cheaper for the rest of the plan year — and the deductible resets on the plan’s renewal date, not necessarily 1 January.

Nobody hit the out-of-pocket maximum. Total responsibility was $4,436 against a $9,000 OOP max. Worth knowing, because if a household is at $8,400 in October, the correct move is often to schedule the deferred procedure this year rather than next.

The real annual cost was $10,676, not $4,436. Premiums were $520 × 12 = $6,240. Premiums don’t count toward the deductible or the OOP max — but they absolutely count when you’re deciding whether next year’s plan is a good deal. A tracker that ignores premiums makes a high-deductible plan look cheaper than it is, and a low-deductible plan look more expensive than it is.

Answering the four questions

Once the log exists, the four questions become one formula each.

“How much have we spent?” Sum the amount paid column, then add premiums separately. Report both numbers — out-of-pocket care and total cost of coverage — because they answer different questions.

“Have we met the deductible?” Sum your responsibility where applies to deductible is yes and date of service falls in the current plan year, then subtract from the plan deductible. If you have individual and family deductibles, do it per person and for the household. This is fiddly enough by hand that most people simply don’t know the answer, which is exactly why it’s worth automating once.

“What’s still unpaid?” Filter on status. The useful version of this filter isn’t “what do I owe” — it’s “what’s been billed but has no matching EOB,” because that’s where errors live.

“Is any of it tax-deductible?” Sum amount paid by date paid within the calendar year, exclude anything paid from an HSA or FSA, and compare against 7.5% of your adjusted gross income. Only the amount above that floor is potentially deductible, and only if you itemize.

Where medical tracking usually goes wrong

Logging the bill instead of the EOB. The bill is a request. The EOB is the ruling. If you enter bills, your totals will overstate what you owe and you’ll pay balance-billed amounts you weren’t required to pay.

One date column. A December visit paid in January is a prior-plan-year deductible event and a current-tax-year deduction. One date makes one of those answers wrong.

Counting premiums toward the deductible. They never count. This is the most common reason people believe they’ve met a deductible they haven’t.

Not tracking per person. Most family plans have both an individual deductible and a family deductible, and one family member can meet theirs long before the household meets the family total. Track per person or you’ll miss it.

Dropping HSA receipts. More on this below — but the short version is that an HSA receipt you throw away is money you can’t withdraw tax-free later.

Starting in July. Partial-year tracking is nearly useless for the deductible question, because the running total is the whole point. If you’re starting mid-year, backfill from your insurer’s claims portal, which typically holds the plan year’s EOBs.

Stop rebuilding this every January

Everything above is buildable by hand — the columns are listed, the logic is described, and if you want to construct it yourself, you now have the spec.

The Medical & Healthcare Expense Tracker is that system already assembled, with 393 formulas doing the arithmetic. You enter your plan details once on the Insurance Overview tab — premium, individual and family deductible, out-of-pocket maximum, copay amounts and coinsurance rate — and every other tab reads from them. The Medical Expense Log holds 100 rows with the billed/insurance-paid/your-responsibility split kept properly separate. The Deductible Tracker turns that log into a live answer to “how much is left before insurance takes over.” The EOB Log gives you somewhere to reconcile statements against bills, the Tax Deductions tab applies the 7.5% AGI floor for you, and the Family Expenses tab rolls up to six household members into one household total.

Go deeper on each piece

Each part of the system has its own set of gotchas, covered in detail:

• The deductible ladder — how to work out exactly where you sit between your deductible, coinsurance and out-of-pocket max, and why the answer changes what care you schedule: how to know if you’ve met your deductible
• HSA receipts — why the receipts you’re throwing away are withdrawable cash, and the log that makes them usable years later: how to track HSA receipts for reimbursement later
• The tax question — the 7.5% AGI floor with a full worked calculation, and what does and doesn’t count as a qualifying expense: are medical expenses tax deductible?
• Catching billing errors — the seven discrepancies to look for when a bill and its EOB disagree: how to check a medical bill against your EOB

The bottom line

Medical expense tracking fails when it treats a healthcare cost like a grocery receipt — one date, one amount, one category. It works when you record the three numbers healthcare actually produces: what you were billed, what your plan allowed, and what you genuinely owe. Keep date of service and date paid in separate columns, mark which expenses count toward the deductible, note what paid for each one, and reconcile every bill against its EOB before money moves.

Do that and the folder of paperwork becomes four answers you can read off a dashboard: what you’ve spent, where you sit against your deductible and out-of-pocket max, what’s still unresolved, and whether the year clears the 7.5% tax floor. Those four answers are worth considerably more than the hour it takes to set up.

This article is general information about tracking your own healthcare spending, not tax or medical advice. Plan rules vary — check your own Summary of Benefits and Coverage, and see IRS Publications 502 and 969 or a tax professional for how the deduction and account rules apply to your situation.