Build an ASC 606 Contract Modification Engine That Auto-Classifies and Calculates Catch-Up Adjustments

A customer extends a contract by six months and agrees to pay another $42,000. Nothing about that sentence sounds like an accounting problem. But depending on two questions nobody in the room is likely to ask, that modification means recognising $5,000 a month, then $7,000, or a flat $5,500 a month, or $5,400 a month plus a $2,400 adjustment posted the day it was signed.

Three treatments. Three different revenue curves. Same contract, same customer, same money — and only one of them is right.

The bit that makes this genuinely dangerous is that all three produce a schedule that looks finished. Columns tie. Cumulative revenue climbs. The total across the life of the contract is $162,000 either way, so even a smart reviewer checking the total finds nothing wrong. What moves is when the revenue lands, and by the time anyone notices, it has landed in a quarter that has already been reported.

Grab a coffee. We’ll build the thing properly: the classic version that opens in any Excel back to 2007, then the 365 version that computes all three treatments at once so you can argue about which applies using numbers instead of adjectives.

Who this quietly bites

You, if you’re in revenue accounting, technical accounting, controllership or FP&A anywhere that contracts change after they’re signed. Which is to say: SaaS, professional services, construction, licensing, media — anywhere a statement of work gets amended.

The pattern is always the same. A contract is set up beautifully at inception. Someone builds a lovely ratable schedule. Six months later the customer wants more, sales agrees a number, and the amendment lands in the revenue team’s inbox as a PDF. Somebody copies the original tab, edits the remaining months, hardcodes the new price, and moves on.

That rebuild breaks in three specific ways, and each one is invisible:

1. No classification trail. The two-part test — are the added goods distinct, and is the price commensurate with their standalone selling price — lives in an email thread rather than in the workbook. Six months later nobody can say why the schedule changed shape.
2. No catch-up calculation. Where a true-up is required, the difference between what was recognised and what should have been recognised gets worked out in a separate file, if at all.
3. No formula link. The original schedule, the modified schedule and the journal entry are three unconnected tabs, so changing an input updates exactly one of them.

Why the usual formula gets it wrong

The classification test you’ll see written most often looks like this:

=IF(Mod_Type="Upgrade","Separate Contract","Prospective")

That formula has a very specific failing: it only ever tells you what you already typed into it. Somebody decided “Upgrade” in a dropdown, and the workbook agrees with them, forever, with no evidence attached.

ASC 606-10-25-12 doesn’t ask what you called it. It asks whether both of two things are true: the additional goods or services are distinct, and the price of the contract increases by an amount that reflects the standalone selling price of those additional goods. Only then is it a separate contract. Fail either test and you’re in 25-13, which has two branches of its own.

That second test is a number, not an opinion — and it’s the one nobody puts in the workbook.

Here’s what it’s worth. Same contract, same modification, priced under each of the three treatments:

Look at the right-hand column. Every treatment recognises exactly $162,000, which is the whole reason this error survives review: a modification changes the pattern and the timing of revenue, never the total. Anyone reconciling revenue to the contract value finds a perfect match.

Now look at July 2024. Under one treatment it’s a $5,000 month, under another $5,500, under the third $7,800. That’s a 56% spread on a single month’s revenue, decided by a ratio most workbooks never calculate.

The fix, two ways

The scenario, in full. A $120,000 contract, delivered ratably over 24 months from 1 January 2024. One performance obligation, satisfied over time — $5,000 a month.

On 1 July 2024, after six months and $30,000 of recognised revenue, the customer extends by six months and agrees to pay another $42,000. The standalone selling price of those six extra months is $30,000 — six months at the $5,000 list rate.

First, two questions no formula can answer for you

1. Are the additional goods or services distinct? Can the customer benefit from them on their own or with resources already available to them, and is the promise separately identifiable from the rest of the contract? For six more months of the same service, the honest answer is usually yes.

2. Is the price commensurate with standalone selling price? This one is a formula, and that’s the point. $42,000 of additional consideration against a $30,000 standalone price is a ratio of 1.40 — the customer is paying a 40% premium for the extension. That is not the standalone selling price of the extension.

Set a tolerance band — 20% either side of 1.00 is a common policy choice, and like every policy choice it needs writing down — and the classification falls out of the arithmetic:

=IF(AND(Distinct="Yes",Commensurate="Yes"),"Separate contract",
   IF(Distinct="Yes","Prospective","Cumulative catch-up"))

Distinct, but not commensurate. Prospective, then.

Option 1 — Classic (backward-compatible functions, step by step)

Works in Excel 2007 onwards.

Step 1 — Put the inputs somewhere real and name them.

On an Inputs sheet: Start_Date (1 Jan 2024), Term_1 (24), Price_1 (120000), then Mod_Date (1 Jul 2024), Add_Months (6), Add_Consid (42000), Add_SSP (30000), SSP_Tol (20%), and Distinct as a Yes/No dropdown. Formulas → Define Name, one each.

Step 2 — Run the two tests for real.

B19  =Add_Consid/Add_SSP
B20  =IF(ABS(SSP_Ratio-1)<=SSP_Tol,"Yes","No")
B23  =IF(AND(Distinct="Yes",Commensurate="Yes"),"Separate contract",IF(Distinct="Yes","Prospective","Cumulative catch-up"))

Ratio 1.40, commensurate No, treatment Prospective. Everything downstream keys off B23, so answering the distinct test differently switches the whole workbook onto the catch-up branch without a cell being retyped.

Step 3 — Derive the nine numbers everything else needs.

B26  =DATEDIF(Start_Date,Mod_Date,"M")
B27  =Price_1/Term_1
B28  =Elapsed*Base_Rev
B29  =Term_1+Add_Months
B30  =Term_2-Elapsed
B31  =Price_1+Add_Consid
B32  =Total_Consid/Term_2
B33  =(Price_1-Rec_To_Date+Add_Consid)/Remaining
B34  =Elapsed*Blend_Rev-Rec_To_Date

Which gives you: 6 months elapsed, $5,000 a month originally, $30,000 recognised to date, a revised term of 30 months with 24 remaining, total consideration of $162,000, a blended rate of $5,400, a prospective rate of $5,500, and a cumulative catch-up of $2,400.

DATEDIF counts whole elapsed months, which is exactly the number of months already recognised. Two of those three rates are always wrong for any given contract — which one is right is what Step 2 decided.

Step 4 — Build the 30-row schedule.

Headers in row 8, data from row 9: Period, Month, Ratable revenue, Catch-up adjustment, Total revenue, Cumulative.

A9  =1
B9  =EDATE(Start_Date,A9-1)
C9  =IF(A9<=Elapsed,Base_Rev,IF(Mod_Test="Separate contract",IF(A9<=Term_1,Base_Rev,Add_Consid/Add_Months),IF(Mod_Test="Prospective",Pros_Rev,Blend_Rev)))
D9  =IF(AND(A9=Elapsed+1,Mod_Test="Cumulative catch-up"),Catch_Up,0)
E9  =C9+D9
F9  =SUM($E$9:E9)

Row 10 differs in exactly one cell — A10 =A9+1 — so the period counts up. Everything else copies straight down to row 38. Two of those rows deserve a second look:

C9 is the whole engine in one nested IF. Months at or before the modification always keep the original rate, because no treatment reaches backwards through the ratable column. After that, the treatment decides.

D9 puts the catch-up in its own column, and that is not cosmetic.

Step 5 — Post the entry.

Under the prospective treatment there is nothing to post on the modification date — that is the defining feature of the branch. Under cumulative catch-up:

A contract asset rather than a receivable, because the revenue has been earned under the reallocated rate but nothing new has been billed. If the reallocation ran the other way — a modification that reduces total consideration — the same entry reverses and revenue is debited.

Option 2 — Excel 365 spill (dynamic-array equivalent)

Same maths, same answers to the cent. But instead of picking one treatment and building it, the 365 version computes all three at once, which turns out to be the more useful workbook.

Step 1 — The period and date columns.

A9  =SEQUENCE(Term_2)
B9  =EDATE(Start_Date,SEQUENCE(Term_2)-1)

Two spills, 30 rows each. Change Add_Months on the Inputs sheet and they redraw themselves.

Step 2 — Every treatment, from one cell.

=LET(
   n,    Term_2,
   el,   Elapsed,
   base, Base_Rev,
   per,  SEQUENCE(n),
   pre,  per<=el,
   sep,  IF(per<=Term_1, base, Add_Consid/Add_Months),
   pro,  IF(pre, base, Pros_Rev),
   bl,   Blend_Rev,
   cat,  IF(pre, base, bl) + IF(per=el+1, Catch_Up, 0),
   sel,  IF(Mod_Test="Separate contract", sep, IF(Mod_Test="Prospective", pro, cat)),
   HSTACK(sep, pro, cat, sel, SCAN(0, sel, LAMBDA(a,b, a+b)))
)

Type that in C9 and five columns spill across to G and down to row 38. Reading it top to bottom:

• pre is an array of TRUE/FALSE — one per month — marking everything up to and including the modification. It gets reused three times, which is exactly what LET is for.
• sep, pro and cat are the three treatments, each a full 30-row column. None of them is conditional on which one you picked.
• cat places the catch-up with IF(per=el+1, Catch_Up, 0) — an array test that is true on exactly one row. No lookup, no hardcoded row number, and it moves on its own if the modification date changes.
• sel picks the column the tests chose.
• SCAN turns that column into a running total in the same pass.

Step 3 — The journal entry, as an array.

=LET(adj, IF(Mod_Test="Cumulative catch-up", Catch_Up, 0),
  VSTACK(
    HSTACK("Account","Debit","Credit"),
    HSTACK("Contract asset", MAX(0,adj), MAX(0,-adj)),
    HSTACK("Revenue",        MAX(0,-adj), MAX(0,adj))
  ))

VSTACK of HSTACKs gives an actual three-column entry rather than a strip of text, and the MAX pair puts the amount on the correct side automatically — so a reallocation that reduces cumulative revenue posts as a debit to revenue without you touching it. Under the other two treatments adj is zero and the entry is correctly empty.

Where else this pattern pays for itself

The engine — classify from the numbers, reallocate, true up the past, respread the future — is the same shape for most of what changes mid-contract:

• SaaS upgrades and seat expansions, where the added seats are distinct but almost never priced at list.
• Construction change orders, which usually fail the distinct test outright because the work is integrated into a single combined output — the cumulative catch-up branch is the default there, not the exception.
• Professional services SOW amendments, where the scope changes and the fee is renegotiated in one conversation.
• Licensing and royalty renegotiations that change the rate retrospectively.
• Variable consideration re-estimates, which are not modifications at all but produce the identical arithmetic: reallocate over the whole term, true up what has already been recognised.

Download the workbook

Everything above, live, with the classic and 365 builds side by side and a Check sheet that proves they agree.

Seven sheets: Inputs (all yellow, all yours) carries both tests, with the standalone-price ratio calculated rather than answered. Classic Original is the pre-modification schedule; Classic Modified runs all 30 rows under whichever treatment the tests selected, with the catch-up on its own column. 365 Engine spills every treatment at once from a single LET, so the two you didn’t pick are still on the sheet. Journal Entries carries the catch-up entry, a typical month either side of the modification, what the other two treatments would have posted, and the same entry rebuilt as one spilled VSTACK/HSTACK. Check proves every treatment recognises total consideration exactly once, ties the classic and 365 numbers to the cent, rebuilds the catch-up from first principles, and prices four wrong answers so you can watch them move.

Nothing in the file is hardcoded. Change the additional consideration to $30,000 and the ratio hits 1.00, the classification flips to Separate contract, and all 30 rows redraw. Answer the distinct test No and the catch-up column comes alive.

Download the ASC 606 Contract Modification Engine (.xlsx)

The 365 Engine sheet needs Microsoft 365 or Excel 2021+. The classic sheets open in anything back to 2007, which is usually what your auditor is running.

Takeaways

• One modification, three possible treatments, and they are not interchangeable: separate contract, prospective, or cumulative catch-up. ASC 606-10-25-12 and 25-13 decide which, not the word on the amendment.
• The commensurate test is a ratio, not a dropdown. =Add_Consid/Add_SSP against a documented tolerance band is a control. A cell someone types “Yes” into is a decoration.
• Total revenue is the same under every treatment. $162,000 either way — which is precisely why reconciling revenue to contract value never catches this. What moves is the timing, into quarters you have already reported.
• The catch-up is elapsed months × the reallocated rate, minus what was actually recognised. Reallocate without truing up and $2,400 of revenue is never recognised at all; the schedule still ends tidily.
• Give the catch-up its own column. Folded into July’s revenue it becomes invisible, and the reconciliation that proves it was posted once is gone.
• On 365, LET + SEQUENCE + SCAN compute all three treatments side by side from one cell, so the argument about which one applies can be had with numbers.
• Your move: open your current revenue workbook and find a modification. If the classification lives in a dropdown someone filled in, calculate the standalone-price ratio for that deal and see whether it agrees. It takes ten minutes, and it is a genuinely uncomfortable ten minutes.