What the Clauses Actually Cost, and How to Price Them Before You Sign
Julian R. Sterling
These are the companion files of Contract Management by Julian R. Sterling.The book on Amazon →
These are the four Excel workbooks that go with the book. Larchmere Industrial ran a
competitive tender, received two bids, and signed the cheaper one: 104.00
a unit against 109.50, a gap of 5.50 worth
1,228,920.00 over three years on the business case volumes. The price did
hold. What the approval paper never priced was the paper the price came attached to.
The eight clauses that differ between the two agreements cost
1,565,906.48, the realised price saving was
87,304.04, and the net position against the bid that was rejected is
−1,478,602.44. That is a swing of 2,707,522.44
against the approved case, and 94.68 per cent of it was knowable on the
day of signature. The signed price path crosses the rejected bid’s in
month nineteen, which is inside the term. Every figure the book prints is
reproduced here by a live formula rather than a typed constant, and each workbook ends with
a Checks sheet setting the printed figure beside the computed one:
108 checks in all, every one passing. Revised on 24 September 2026: the
counterfactual sheet the first version announced and did not contain is built, and it
corrects the book on one point (three clauses reach +21,103.79, not
+31,230.95). Around the workbooks: the same four with the inputs emptied, thirteen working
documents to print, forty questions that mark themselves, and three cases the book names and
never takes to a number.
Free to download. No sign-up, no email address, nothing to fill in.
Twelve workbooks, the printable documents and the read-me. Each group below can also be
downloaded on its own. Last revised 24 September 2026.
Get an email if these files are corrected
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
These carry the book’s own figures, so that every number it prints can be traced to a live
formula. Each closes on a checks sheet that ends ALL OK.
Download these four103 KB
Chapters 1 to 17
The contract priced
The whole Larchmere case, built from its inputs rather than asserted. The two price paths
are struck quarter by quarter, the signed one firm for four quarters and then revised
every quarter at 8.40 and 5.10 per cent a year, the
rejected one firm for eight and then capped at 3.00. They cross in
quarter seven, which is month nineteen, and after that
the cheaper bid is the dearer contract. The annual prices of 104.0000,
109.4133 and 116.3174 fall out of the quarterly table
rather than being typed, and carry 159,700 units and
17,501,681.88 of spend with them.
Then the eight clauses, each priced as what was signed minus what the rejected bid offered,
because both contracts were genuinely on the table on the same day: the volume band at
514,263.68, currency at 455,844.71, warranty at
196,996.34, change control at 188,340.00, force majeure
at 83,309.93, liquidated damages at 81,120.00 and
payment terms at 46,031.82. Indexation, worth
892,881.88 over the term, is shown and deliberately not added, because
it already sits inside the price gap and counting it twice is the most common way this
arithmetic goes wrong. Both bridges then run on the face of the sheet: the
1,228,920.00 the business case promised falling to the
87,304.04 the price actually returned, and that
87,304.04 meeting the 1,565,906.48 clause package to
land on −1,478,602.44. The counterfactual sheet is the one worth
an argument: no single clause turns the deal positive, and the smallest
package that does is three — indexation, the volume band and the
currency — reaching +21,103.79, with all eight worth
+625,613.21. Revised: the sheet was announced and missing, and the book
held the force majeure premium at the uncapped price when the index is capped; a lower
contract price makes every spot unit dearer against it. The best single fix leaves
−901,798.46, not −891,671.30, and the book’s +31,230.95 becomes
+21,103.79. A Forecast Risk sheet runs Chapter 16: 114.34 a unit at the low case,
107.77 at the high, and a rebate of 122,105.24 at twenty per cent above forecast,
where the book says nothing changes.
Nine standalone sheets, each pricing one clause without the case around it, so that a
single calculator can be taken to a single meeting. Payment terms turn fifteen conceded
days into 46,031.82 and test any settlement discount against your cost
of funds, the offered 0.75 per cent for fifteen days annualising at
18.39 per cent. Indexation prints the full twelve-quarter table and
finds the crossover for itself. The volume band shows the cost of the last unit below the
threshold at 70.298 and the factor of 14.2778 between
the two ways of drafting the same clause.
Liquidated damages set the 639,400.00 the stoppages actually cost beside
the 5 per cent cap that recovers 12.69 per cent of it.
The warranty sheet works the exposure window of 29.2 months that nobody
covers, and the 370,114.33 of uncovered repairs the signed twelve months
leaves behind. Change control, currency and the exit cost complete the set, the last
pricing the 2,009,092.93 it would take to leave the signed agreement
against 978,830.47 under the notice the other bid offered. Each sheet
carries the sensitivity table printed in its chapter. Revised: the warranty sheet had the
part price typed inside a formula; it is an input now, and the volume band sheet shows year
two 6.55 per cent below the threshold, not the 5.81 the book prints.
The procedure in Chapter 19 as a working file, and the one to use on a contract that is
still a draft. Enter your volumes, your two bids and the six operational rates —
cost of funds, cost of an hour of downtime, defect rate and repair cost, change count and
average value, the low case volume, and the currency movement — and it returns the
clause cost, the cost per unit, the equivalence price, the three columns kept apart, the
ranked ledger and the smallest package of clauses that clears your own threshold.
It ships loaded with the Larchmere figures, so the Checks sheet is a real test rather than
a decoration: 25 checks confirm that this general calculator, driven only
from its input cells, reproduces the 17,501,681.88 of spend, all seven
clause costs, the 9.8053 a unit, the 94.1947
equivalence price and the month nineteen crossover exactly as the book
prints them. Overwrite the inputs with your own and the checks will move, which is the
point: they exist so that you can tell your own changes apart from a mistake in the file.
The Three Columns sheet displays the sum of incurred, exposure and barrier once, labelled
as meaningless, so that nobody quotes it by accident. Revised: the cell labelled low case
(55,000) was used as the band threshold, so a buyer who typed in a real low case moved the
threshold. They are two inputs now, and the low case is tested on its own:
247,164.64 of surcharge at 50,200.
The one-page brief, which is the only one of the four meant to be printed. The equivalence
price of 94.1947 at the top with the sentence that goes with it, the
three must-haves in the middle with their per-unit values, the five tradables at the
bottom marked as available, and the walk-away at the foot. Every ask is converted to a
value per unit, because a negotiator cannot hold 514,263.68 in their head
and can hold 3.2202 a unit very easily, and per-unit values are in the
same currency as the price.
Ranked, the three at the top are worth 9.4454 a unit between them and the
two at the bottom 0.7962, a ratio of 11.8631 to one
pointing the wrong way — which is the order Larchmere’s negotiators spent their
credit in, working hard and in good faith. The Trade Planner sheet lets you tick
concessions and asks and tells you whether the asks close the gap: the four concessions the
supplier had already asked for are worth 3.2090 a unit against the
9.4454 of the three asks nobody made, and those three clear the
9.2586 a unit by which the paper trailed the alternative. Revised: the
first verdict compared a price that rose with every ask won against the equivalence price,
and so always said “keep asking”.
The four workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 1 to 19
Your bids, your clauses, your ledger
The contract priced, the nine clause calculators, the draft-contract calculator and the negotiation ledger, with the same formulas and every blue cell emptied. The sensitivity steps stay: days, thresholds, caps, margins and warranty lengths.
The book’s inputs are listed in a text file inside the archive. Type them back in and the book’s figures come back with them: 1,565,906.48 of clauses, 94.1947 as the equivalence price, month nineteen.
Thirteen pages to print and use. A4, with margins wide enough for US Letter.
Chapters 2 to 21 · Appendices B and C
Thirteen working documents
The two positions, clause by clause. The six operational rates and who holds them. One line of arithmetic per clause, down to the equivalence price. The three columns that are never added together. The one-page brief and the ledger with the smallest sufficient package. The liability test. Volume and who carries the forecast. Delivery, warranty and change control. Continuity and exit. The first ninety days. And the forty questions before signature, on two pages.
A workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Forty questions on the book
Seven on the case and the method, seven on price, volume and the rebate, six on payment, currency and delivery, seven from liability to force majeure, seven on exit, the ledger and the procedure, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part.
New material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it.
Chapters 4, 5 and 16 · Case one
Five ways to draft the volume band
The band as signed, on the shortfall, capped, as a take-or-pay (ADDED terms) and in both directions, run through the three scenarios the plan had already published. Only the low case triggers anything: 247,164.64 as signed against 23,633.27 on the shortfall. Expected across the three, 82,388.21 as signed; a two-way band is neutral only at a discount of 3.30 per cent above 68,000, not the 2.00 the chapter suggests.
Chapters 14 and 15 · Case two
The benchmark at month thirteen
The book says a benchmark at the end of year one is worth more than every other protection combined. On the signed price path, with the market at 100 and a 3 per cent band (ADDED), a single benchmark is worth 336,158.61, less than the 501,929.13 six months’ notice takes off the cost of leaving. An annual one, at months 13 and 25, is worth 931,789.96, in cash.
Chapter 10 · Case three
The trigger, the remedy and the duration
On Larchmere’s own field curve, twelve months from commissioning is worth 85,365.08, 43 per cent of the 24-month concession. If the remedy is the part only, the 24 months are worth 23,019.80, not 196,996.34, and moving the remedy to the repair is worth 200,336.62: more than the duration the chapter prices.
an assumption that decides the answer rather than merely feeding it: the six operational rates, the low case volume that prices every threshold in the agreement, and the annual cap on the alternative. In the third workbook every input cell carries the fill, because on a contract not yet signed every one of them is an assumption somebody is making
Black text
a formula: do not overtype these
Grey text
a note
Green text
on the Checks sheet, the published figure being checked against
Checks sheet
the printed figure beside the computed one, the difference, and a PASS or a FAIL; the foot of the sheet reads ALL OK
ADDED
a figure the book does not give, used in the cases and marked wherever it appears
There are no macros, no external links, no protection and no circular references anywhere, and
nothing is locked or watermarked. The files behave identically in Excel, LibreOffice and Google
Sheets.
Why the checks matter more than the models
A workbook that agrees with a book proves nothing on its own, since the author wrote both. What
the Checks sheets do is different: they force the model to reproduce a number that was printed
before the model existed, from a formula rather than from the number itself.
108 checks across the four files, being 39, 22, 25 and 22, all passing.
On this book the discipline earns its keep twice. The first time is on rounding. The model
rounds at each step, as any contract administrator would, and a workbook that compounds at full
precision produces figures that are defensible, different, and wrong against the printed page.
Fourteen checks failed on the first build for that reason alone, and the fix was to make every
formula round where the model rounds rather than to widen the tolerance.
The second time is on double counting. Indexation is worth 892,881.88 over the
term and it appears nowhere in the 1,565,906.48 clause package, because its
effect is already inside the price gap the bridge starts from. Adding it would produce a larger,
more impressive and entirely false number. The workbooks display it, label it, and refuse to
sum it, and a check holds the clause package to the seven lines that belong in it.
The third time is on interaction. Cap the index and the contract price falls, so every
clause that multiplies the price moves with it: the band, the currency, the payment terms and
the premium paid for spot units during the force majeure. The book recomputed the first three
and held the fourth, and overstated the cap by 10,127.16. The counterfactual
sheet recomputes all four, and a check says so.
The assumptions most worth arguing with are the two index paths and the low case volume. Move
the annual cap on the alternative from nothing to 3.00 per cent and the cost of
the indexation clause falls from 892,881.88 to
354,572.71, a saving of 538,309.17 which is
3.3708 a unit and the single most valuable thing nobody asked for. Move the
volume band threshold from 55,000 down below your own low case and
514,263.68 of surcharge disappears. Type your own figures instead and watch
which answers move and which do not.
Go beyond the companion files
Contract Management develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF
prints on A4 or US Letter. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks
to update links on opening, decline, because there are none.
What changed, and when
24 Sep 2026
The four workbooks corrected; 108 checks. The counterfactual sheet of Chapter 17 built: three clauses reach +21,103.79, not +31,230.95, and the best single fix leaves −901,798.46, not −891,671.30. Chapter 16’s scenarios and twenty per cent test added. The draft-contract calculator no longer uses the low case as the band threshold; the trade planner’s verdict corrected. Added: a blank set, thirteen working documents, forty questions that mark themselves, and three cases.
How do you calculate service credits under an SLA?Shortfall in points times the credit rate times monthly charges, up to the cap: 2 per cent a point on 480,000 pays 191,040 a year against 338,960 of loss.
How do you calculate contract value leakage?Audit each term against what was received: 241,840 of leakage on 5,450,000 of spend, 4.44%, of which 178,700 can still be claimed from the supplier.
Closing the DealTwo defensible bridges 20.70 million apart, a peg worth 7.00 million, and the six choices that remove 6.60 of a 12.00 earn-out.
CMBS and CRE CLOsWhere the loss actually lands, from appraisal reduction to realised severity, and what the B-piece is really being paid for.
How to Read a Commercial LeaseThe three refinements chapter 19 names and never performs, and the renewal rate below which the mark-to-market is worth nothing.
How to Read a Credit AgreementWhere the default actually comes from, the cure that costs 5.5 times the other, and the capacity nobody adds up.
How to Read a Real Estate Loan AgreementThe cure ratio in closed form, the four-point window in which the cheap cure works, and the cure sized to the wrong threshold.
Office Real EstateA six per cent yield that returns 3.2 per cent once the re-letting cycle is paid for, and the headline-to-net-effective rent arithmetic.
Private Equity Real EstateBoth worked waterfalls to the dollar, the two capital stacks, and the arithmetic of the promote made changeable.
Private Markets PerformanceThirty-one of the thirty-three figures chapter 19 publishes reproduce exactly — and the two that do not are named rather than quietly adopted.
Raising a Real Estate FundThe chapter 17 funnel run on a calendar — when the first close actually lands, and why more travel does not help.
Real Estate FinanceFour people look at one building and reach four numbers; the lender is whole only above 105,109,489, twelve per cent below today’s value rather than forty.
Real Estate Financial ModelingProperty, development and fund models built line by line, and the modelling test worked end to end.
Real Estate Fund ManagementThe waterfall of 6.11, the build-to-core of 8.7 and the proceeds gap, reproduced as live formulas rather than asserted.
REIT Analysis and ValuationFFO of 532.0, AFFO of 381.0, net asset value and dividend safety — every figure a formula you can change.
Retail Real EstateThe occupancy cost of every unit in a centre, the sixteen per cent of the rent roll no tenant can sustain, and the right-size-convert-or-hold decision priced.
Sale and LeasebackA €179.5 million transaction end to end, with rent cover measured on the entity that actually signs the lease.
Self-Storage Real EstateThe cohort engine behind a 590-unit store, and the rate increase on existing customers priced against the move-outs it causes.
The Fund Finance ProfessionalChapter 8 builds the reported-to-eligible NAV bridge; chapter 9 computes every ratio without it. Two points at every state — and what a subscription line does to the IRR.
The Growth Equity InvestorWhat a pro rata cheque really costs, and the band where defending your ownership loses money.
The Private Credit InvestorThe two coverage ratios are not measured on the same thing: the erosion is 47.7 per cent, not the 28.7 the headline implies.
The Private Equity Fund Controller PlaybookThe book defines IRR, DPI, RVPI and TVPI, tells you to update them at the exit, and prints not one value. Computed: a 1.833× deal inside a fund at 0.892 TVPI.
The Venture Capital AssociateWhat defending a position costs, and how many companies a reserve pool actually defends.
Financial Risk ManagementA fund inside every limit that cannot meet a redemption — and the number that decides it is the one with no currency attached.
Business ValuationThree advisers land 26.8 per cent apart on one company, and the whole gap turns out to be 1.96 points of perpetual growth.
Quantitative FinanceThree models agree to a quarter of one per cent about a number that one unobservable input moves a hundred and three times as much.
Asset ManagementFour people quote four returns for one mandate, all correct and 2.7017 points apart — forty-eight times the manager’s net skill.
Alternative InvestmentsA manager reports 13.29 per cent and the endowment earns 6.26 — both correct, and only a third of the advertised advantage arrives.
Venture CapitalOne company out of twenty-eight returns 56.7 per cent of the fund, and half the capital goes in after the decision — at half the return.
Machine Learning for FinanceFive people quote the accuracy of one credit model, all five are right, and the number that decides how much money it makes is none of them.
Commercial Real Estate InvestingThe equity earned 8.6647 per cent and the investor received exactly 8.0000 — the preferred return, and nothing above it.
Mergers and AcquisitionsThe board paper says the deal creates 13,436,667 of value. The arithmetic says it destroys 17,530,855. Nobody is lying.
DerivativesThe treasury report says the hedge cost 1,233,698. That is the interest differential, not a cost.
Treasury ManagementFive cash balances for one company, all correct and 145,600,000 apart — and the revolver that is two-thirds of the liquidity leaves at a revenue fall of 8.4127 per cent.
Financial Planning and AnalysisRevenue 3.0190 per cent above budget and operating profit 16.3209 per cent below it, in the same quarter, with every figure correctly stated.
Energy TradingA position report that is 91.7031 per cent hedged and correctly computed, on a book that is short 2,542,000 MWh — and a margin call of 198,400,000 the next morning.
Construction Cost ControlA contract sum of 26,301,102 became a final account of 29,153,363 on the building that was drawn — and 85.8 per cent of what was lost was knowable on the day it was signed.
Trade FinanceSix routes to payment on one 4,200,000 export order cost between 178,040 and 223,268, a spread worth 14.8 per cent of the margin, and a day of buyer credit costs 1,031.76.
Cost AccountingOne factory costed twice on the same 13,440,000 of overhead, and 4,053,091 moves between four product families.
Pricing StrategyA list price of 148.00, a pocket price of 112.51, and the nine deductions in between — with what one point of price is actually worth.
Capital MarketsFour defensible readings of one bond, the cost of an issue under each convention, and the placement discount computed at a constant discount.
Financial RegulationThree ratios pass and the fourth is in breach by 1,956.0 million: the output floor, the liquidity buffer after encumbrance, and the loan split.
Bank ManagementFour defensible readings of one balance sheet, the deposit beta measured two ways, and the liquidity that lasts 3.9 days.
The Real Estate WorkoutFour ways out of one broken loan, discounted to today, and the 58.7 cents at which a payoff beats enforcement.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.