A free lost profits damages calculator in Excel, from lost sales to damages at the trial date
Lost sales by period, the incremental margin built from the cost lines, the costs the breach saved and added, the profit earned by mitigating, the cover premium on the substitute input, then simple prejudgment interest from each period's midpoint and the discounting of the future loss: one sheet of inputs, live formulas and six checks.
The worked case, a fictional supply dispute, turns lost sales into lost profits at an incremental margin of 44.0%: $4,709,245 before other items. After the outage items and the cover premium, the items before the trial date come to $5,774,316; simple interest adds $479,848, the future item is worth $626,472 today, and damages at the trial date are $6,880,636.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 147 free models.
Lost_Profits_Damages_Calculator.xlsx (12 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This model goes with The Lost Profits Damages Handbook, which explains the method behind it, chapter by chapter.
The Financial Model Library: all 148 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
3 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.
Inputs
Lost sales by period; eight cost lines with their share of sales and whether each moves with volume; the costs the outage saved, the extra costs, the mitigation profit; contract and substitute prices and the quantities bought; years from each midpoint to the measurement date, the interest rate, the discount rate.
Profits
The incremental margin, lost profits by period, the cover cost, the outage net, the items before and after the measurement date, interest, the discount factor, the present value and the damages.
Checks
Six tests on the cost shares, the 1 or 0 flags, total costs, the cover price, the outage net and the rates, and an All checks cell.
What the worked case returns
Measure
Value
Costs that move with volume (share of sales)
56.0%
Incremental margin
44.0%
Lost profits before other items
$4,709,245
Cover premium a pound
$0.75
Outage period net of saved costs, extra costs and mitigation
$2,588,416
Items before the measurement date
$5,774,316
Item after the measurement date, before discounting
$647,429
Prejudgment interest (simple)
$479,848
Discount factor
0.96763
Present value of the future item
$626,472
Damages at the measurement date
$6,880,636
Every figure is a teaching assumption of the book's fictional case, not a market norm or a rule of law. Whether prejudgment interest is available, at what rate, simple or compound, and whether and at what rate future losses are discounted, depend on the governing law: take them from counsel. Not legal advice.
How to use it on your own case
Type the lost sales of each period from your but-for analysis.
Enter each cost line's share of sales and a 1 if it moves with volume, a 0 if it does not, and read the incremental margin.
Enter the costs the breach saved, the extra costs it caused, the profit earned by mitigating, and the contract and substitute prices with the quantities bought.
Enter the years from each midpoint to the measurement date and the rates counsel gives, and read the damages and the checks.
Questions people ask about it
What margin should be used for lost profits?
The incremental margin: the share of each lost sales dollar left after every cost that would have moved with that sale. Not the gross margin, which mixes in fixed costs and leaves out freight and commissions, and not the net margin, which charges lost sales with fixed costs paid anyway.
Are avoided costs deducted from lost profits?
Yes. Costs the plaintiff did not incur because of the breach, such as wages saved by a furlough, are deducted, and so is profit earned only because the breach freed capacity.
Is prejudgment interest simple or compound?
It depends on the governing law: many states set a simple statutory rate for contract claims, some leave it to the court. The calculator applies a simple rate from each period's midpoint; take the rule from counsel.
Is this template really free?
Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Go further
The free workbooks of The Lost Profits Damages Handbook go further: the full companion set, blank models and the worked cases of the book.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.