A free discount break-even calculator in Excel, at the margin you actually earn
The four profit levers, the volume a price cut needs and a price rise can lose, the rebate cliff solved by bisection, the rate hidden in payment terms and a discount authority grid, built on 458 account lines: eleven sheets, no macros.
The file answers one question for a fictional manufacturer: how much more volume does a discount need to pay for itself? At its pocket contribution margin of 27.0%, a 5% price cut needs 22.8% more volume just to stand still, while a 5% price rise can lose 15.6% of volume before it costs anything. One point of price is worth about 3.6 points of volume. The same file shows that 2/10 net 30 is borrowing at 37.2% a year, and where a retroactive rebate tier makes the last units before it almost free.
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 129 free models.
Discount_Break_Even_Calculator.xlsx (257 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 file is one worked case from Pricing Strategy. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 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
Eleven sheets. Blue cells are inputs; the company margin is built from 458 account lines, not typed.
Read Me and Assumptions
The three product families, list prices and costs, the published discount rules and the pressure factors.
Accounts and Lines
180 customers and their 458 account-family lines, from list price to contribution.
Levers
Price, volume, variable cost and fixed cost, one per cent each, in operating profit, and the right pocket margin to use.
Break-Even
d/(m-d) and d/(m+d), with a table from 2% to 8%.
Rebate Cliff and Solve
An account just below a retroactive tier, and the units that make the tier pay, solved by bisection.
Payment Terms, Authority and Checks
The annual rate in a cash discount, a discount authority grid, and the checks.
What the worked case returns
Measure
Value
Pocket contribution margin
27.0%
5% cut: volume needed
+22.8%
5% rise: volume that can be lost
-15.6%
2% cut: volume needed
+8.0%
8% cut: volume needed
+42.2%
Annual rate in 2/10 net 30
37.2%
The two are not mirror images. A cut needs d/(m-d) more volume and a rise can lose d/(m+d); at a thin margin the first explodes and the second stays small. That asymmetry is the whole case for defending price rather than buying volume with it.
How to use it on your own business
Enter your list prices, costs and discount rules on Assumptions, or type your pocket margin directly on Break-Even.
Read the volume a cut needs and a rise can lose.
Check any retroactive rebate on Rebate Cliff.
Price your payment terms before offering a cash discount.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. The rebate cliff is solved by a bisection laid out across cells.
What is the break-even formula for a discount?
A price cut of d at a contribution margin m needs volume to rise by d/(m-d) to leave contribution unchanged. A price rise of d can lose d/(m+d) of volume. Use the margin after every discount and rebate, not the list margin.
Can I use it for my own prices?
Yes, by replacing the inputs. The company and its customers are fictional.
The rest of the files
This template is one of the four companion workbooks for Pricing Strategy: the others build the price waterfall, bridge price, volume and mix, and give a blank calculator for your own book of business. Free, like this one.
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.
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 template stays free either way.