Build a Buyback and Expiry Return Simulator in Excel

A buyback or expiry claim values returned stock according to how close it is to expiry, or how long the partner has held it. The model bands each returned line by age, looks up the rate for that band, and multiplies. Batch and expiry data is what makes it work — without it, the banding is guesswork.

One disambiguation up front: buyback here means a channel stock buyback — a supplier taking back unsold or near-expiry stock from a trading partner. It is not a share buyback, where a company repurchases its own shares. Everything below is the distribution sense, as covered commercially in the buyback process and supplier buybacks.

This article builds the model; the workbook structure it assumes is in how to build a claim and scheme simulator in Excel.

The worked example

Four illustrative return lines, all returned on the same date, with a unit value of ₹250. All figures illustrative.

Return line	Batch	Expiry date	Days to expiry at return	Age band	Rate (%)	Quantity	Value (₹)	Claim value (₹)
R-1	B-101	31 Aug 2027	401	365+	100%	120	30,000	30,000
R-2	B-102	15 Dec 2026	142	90–179	60%	80	20,000	12,000
R-3	B-103	10 Sep 2026	46	0–89	40%	60	15,000	6,000
R-4	B-104	30 Jun 2026	−26	ineligible	—	40	10,000	0

R-4 is the important row. The stock was already 26 days past expiry when it came back, so it falls outside the accepted return window and earns nothing — regardless of what any rate band would have paid. A model that runs the rate lookup first and asks about eligibility second will happily pay for it.

Why age decides the rate

The commercial logic is straightforward. Stock returned well before expiry may still be resaleable, so the supplier recovers most of its value; stock returned close to expiry has little remaining selling life; stock returned past expiry usually cannot be sold at all and must be destroyed. Buyback terms reflect that with banded rates rather than a single percentage.

Which is why the return policy — not the model — has to define two things: the bands and the eligible window. A policy that says "near-expiry stock will be bought back at an appropriate rate" produces a claim nobody can compute and everybody can dispute. Bands, thresholds and the window belong in the written terms alongside the rest of the agreement, because they are the only things that make the number reproducible.

This shape recurs across categories with dated stock: pharma expiry and breakage returns, pharma buybacks, FMCG buybacks and agrochemical season-end returns. The bands differ; the model does not — and each one feeds the same channel claims process.

Calculating the age band

Days to expiry at the return date:

=D2 - C2

where C2 is the return document date and D2 is the batch expiry date — both real Excel dates, not text.

Age band rate lookup, with band thresholds in Terms!$B$2:$B$5 sorted ascending and rates in Terms!$C$2:$C$5:

=INDEX(Terms!$C$2:$C$5, MATCH(E2, Terms!$B$2:$B$5, 1))

where E2 holds days to expiry.

Claim value:

=G2 * H2 * F2

where G2 is quantity, H2 is unit value and F2 is the banded rate.

Two warnings, both of which cost real money.

MATCH with 1 requires the thresholds sorted ascending. Approximate match walks the list assuming ascending order and returns the last threshold not greater than the lookup value. Unsorted thresholds do not raise an error — they return the wrong band, which pays the wrong rate on every line that hits it.

Expiry dates arriving as text is the single most common failure in these models. A text date makes the subtraction fail or produce nonsense, and the error is easy to miss because some rows still calculate — the ones where the export happened to produce a real date. A workbook that is half right looks like a workbook that is working. Check the column alignment before trusting anything: real dates align right by default, text aligns left.

Eligibility before rate

Most return policies define a window — returns accepted only within a stated period before or after expiry — and conditions: unopened, saleable packaging, the original invoice traceable. The model must test eligibility first, and only then apply a rate.

The pattern in words: add an eligibility column that returns Yes or No by testing the line against the window and the conditions, then make the claim value depend on that test, so an ineligible line resolves to zero rather than to a banded rate. Build and test that gate against a worked example — including a line you know should be rejected, like R-4 above — before running it over a real return file.

The reason to sequence it this way is not tidiness. An ineligible line that reaches the rate lookup gets a plausible rate and a plausible value, and plausible wrong numbers are the ones that get paid.

Batch data is the dependency

Banding by age requires batch and expiry detail at line level. Everything above assumes it exists.

When the return document lacks batch detail, the honest answer is that there is no way to recover it inside the workbook. The line either gets settled at the least favourable band — which the partner will contest, reasonably — or gets queried back, which delays the settlement and consumes more handling time than the line is usually worth. Neither is a good outcome, and no formula improves either.

That is why batch capture at dispatch matters more than any spreadsheet technique in this claim type. The data has to exist before the return happens; the model can only read what was recorded. Where returns are routine — as they are across expired and damaged goods — the capture discipline upstream is the whole game.

What the model cannot decide

The model computes a commercial value. It does not decide the tax treatment, and it should not try.

The GST position on returned stock — and particularly on stock that is destroyed rather than resold — is a separate question with real consequences, and it depends on the facts and the documentation. This article asserts no position on it. Route those questions to the existing coverage: credit notes for expired, damaged and returned goods and the GST treatment of pharma claims and returns, and confirm the treatment for your own returns with a qualified professional.

<!-- TODO CA REVIEW: confirm this routing paragraph asserts no tax position. -->

Where this model stops coping

High return volumes, where the exception list outgrows the claim. Batch-level reconciliation against dispatch records, which a workbook cannot do without the dispatch data beside it. Multiple concurrent return policies with different bands running at once. And linking a return back to the original settled claim, so the earlier payout can be clawed back — which a spreadsheet has no way to remember, because each workbook is a fresh calculation with no knowledge of the last one.

At that point the answer is moving the work off spreadsheets rather than a better formula. ClaimDS bands returns by batch age against the policy on record and links each return to the claim it affects, so a return arriving after settlement reconciles against the original payout instead of being handled separately. <!-- TODO: confirm capability wording with founder -->

This article is general information about commercial and spreadsheet practice, not tax, legal or accounting advice. It describes commercial practice only and makes no statement about what any return policy must contain as a legal or regulatory matter. Confirm tax treatment and policy requirements with a qualified professional.

Read next

• How to build a claim and scheme simulator in Excel — the workbook structure this model assumes.
• The buyback process — the five stages, end to end.
• Credit notes for expired, damaged and returned goods — the settlement side.
• Returns, reversals and cancellations in channel claims — what happens after a claim is settled.
• Settlement factsheets for rebate claims — showing the working on a banded claim.

Book a demo to see how ClaimDS bands returns by batch age against your own return policy.