Spot an MG That's Already Fully Recouped in Your Contract Register

You paid a $500,000 minimum guarantee upfront to license “The Ember Lanes” for 5 years. Quarterly royalty statements come in — first $50k quarter, then $80k, then $120k. Somewhere in the stream, cumulative royalties crossed $500k and the MG was fully recouped. From that moment on, the licensor is entitled to ongoing royalty share on every subsequent dollar. If your contract register still shows MG Status = RECOUPING after that crossover, you’re underpaying — or over-retaining, depending on how you’re modeling it.

The fix: a SUMIF that accumulates reported gross × royalty %, compares it to the MG balance, and flags when crossover happened.

Setup

Your contract register needs these columns at minimum:

A: Title
B: Licensor
C: MG Amount
D: Royalty %
E: Recoupment Basis  (Gross, Net, or Net after fees)
F: MG Status         (RECOUPING | RECOUPED | N/A)
G: MG Recouped Date  (when the crossover happened, blank if not yet)

And a separate Reported sheet with the platform-statement stream, one row per period:

A: Title
B: Period (YYYY-Qn)
C: Gross USD
D: Net USD

The Recoupment Formula

In the contract register, for each title, compute:

Cumulative Royalty =
  SUMIF(Reported!A:A, A2, Reported!C:C) * D2

Where:

• Reported!A:A, A2 — match all reported rows for this title.
• Reported!C:C — sum of reported gross (or use Reported!D:D for net-basis contracts — check column E).
• * D2 — multiply by the royalty % per the contract.

Then flag status:

MG Status =
  IF(Cumulative Royalty >= C2, "RECOUPED", "RECOUPING")

MG Balance Remaining =
  MAX(0, C2 - Cumulative Royalty)

Conditional-format MG Status: green for RECOUPED, amber for RECOUPING. Sort by MG Balance Remaining ascending — the closest-to-recouped titles surface first.

Identify the Crossover Quarter

Once a title is recouped, when did it cross over? That quarter matters for the first post-recoupment payment calculation. Add on the Reported sheet:

Running Total (per title) =
  SUMIFS(Reported!C:C, Reported!A:A, A2, Reported!B:B, "<="&B2) * <royalty %>

Or, if the royalty % varies by title, VLOOKUP it from the register:

Running Total =
  SUMIFS(Reported!C:C, Reported!A:A, A2, Reported!B:B, "<="&B2)
    * VLOOKUP(A2, Register!A:D, 4, FALSE)

The first row where Running Total crosses the MG amount is the recoupment quarter. Flag it:

Recoupment Quarter =
  IF(AND(Running Total >= MG Amount, PREVIOUS Running Total < MG Amount), "CROSSOVER", "")

Pro-Rate the Crossover Quarter

The first recouped quarter isn’t a clean “50/50 pre/post recoupment” — part of that quarter’s royalty went to finishing off the MG, the rest is the licensor’s. Formula:

Pre-recoup amount (used to close MG) =
  MG Amount - Running Total at end of prior quarter

Post-recoup amount (licensor's share) =
  (Reported Gross of crossover quarter * Royalty %) - Pre-recoup amount

The post-recoup number is what the licensor’s first cash royalty payment actually is. Some contracts waive this pro-rate (pay the whole crossover quarter to the licensor as a “bonus”); most don’t. Read the contract.

Gotchas

• Net vs. gross basis. A 20% royalty on net is very different from 20% on gross. Check the contract’s recoupment basis per title. Column E in the register should pin this down; don’t assume.
• Cross-collateralization. Some deals let Licensor A’s MG recoup against Licensor A’s entire catalog, not per-title. In that case SUMIF by Licensor, not by Title. Check contract language.
• Allocation fees. Some platforms report “gross” after platform fee (e.g., Apple takes 30%). Your royalty % applies to the amount specified in the contract — whether that’s pre- or post-platform-fee. Reconcile the basis before the calculation.
• Amendments and MG adjustments. A subsequent amendment may reduce or increase the MG. Always compare against the current-effective MG, not the original.
• Adjustments and reversals. A quarter with a negative adjustment (correcting a prior quarter’s over-reporting) should reduce the running total. Make sure your SUMIF includes negative rows.

If You Need the Full Workflow

Audit Royalty Calculations Against Contract Terms covers the full reconciliation including MG recoupment as one of several audit checks. Validate Contract Metadata Before Filing covers catching missing MG fields at ingest — so this check actually has complete data to run against.

Download

The Contract Register with Quality Score template includes MG Status + Balance columns with the SUMIF formulas pre-wired. Pair with the Royalty Reconciliation Workbook for the full audit chain.