What the Clauses Actually Cost, and How to Price Them Before You Sign
Julian R. Sterling
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:
82 checks in all, every one passing on delivery. If a check ever reads
FAIL, the workbook is wrong, not the book.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
The four workbooks
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 +31,230.95, with all eight worth
+625,613.21.
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.
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: 21 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.
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 trade is in your favour: 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.
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
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.
82 checks across the four files, being 24, 20, 21 and 17, all passing on
delivery.
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 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.
Opening the files
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. 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.
Also by Julian R. Sterling
The other books with companion files. The full list of titles is on the
author page.
Credit AnalysisOne borrower, four defensible EBITDAs, and the add-back argument worth fifty times the covenant headroom.
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.
Contract ManagementA bid 5.50 a unit cheaper, signed on paper worth 9.81 a unit more — and 94.68 per cent of it was knowable on the day of signature.