A free early payment discount calculator in Excel, with the working capital around it
The cash conversion cycle in days and in money, the annual rate of an early payment discount and what taking it is worth, supply chain finance from both sides, and a cash pool: six sheets, no macros.
The file works through the working capital of a fictional manufacturer with revenue of 1.4 billion. Its cash conversion cycle is 80 days and ties up 281.5 million. A supplier offers 2/10 net 60 on 40% of purchases: not taking it is borrowing at 14.9% a year. Taking it captures 7.8 million of discount, costs 2.4 million of revolver interest at 4.5%, and nets 5.5 million a year, at the price of a little more leverage. A supply chain finance programme on 90-day terms saves the suppliers 5.5 million against funding the same days themselves.
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.
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 Treasury Management. 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: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Blue figures are inputs; yellow cells are the assumptions that decide the answer.
Inputs
Revenue, cost of goods sold, EBITDA, DSO, DIO and DPO, net debt and the covenant, the revolver, the discount terms, the supply chain finance programme and the pool balances.
Cycle
Receivables, inventory and payables in money, the cash conversion cycle, and what five days off DSO release.
Discount
The annual cost of not taking the discount, the discount captured, the cost of funding early payment, the net gain and the effect on leverage.
SupplyChainFinance
Cash released, the bank's exposure, the supplier's cost on the programme against its own funding, and the stock-versus-flow error to avoid.
Pool and Checks
Notional pooling of a eurozone cash position, and the checks.
What the worked case returns
Measure
Value
Cash conversion cycle
80 days
Net working capital
281.5m (20.1% of revenue)
Five days off DSO release
19.2m
Terms
2/10 net 60
Annual rate of not taking the discount
14.9%
Discount captured a year
7.84m
Revolver cost of paying early
2.37m
Net gain a year
5.47m
Saving from pooling
1.19m a year
The annual rate is d/(1-d) times 365 divided by the days bought: 2/98 times 365/50, or 14.9%. Any source of cash cheaper than that pays for taking the discount; at 2/10 net 30 the same discount costs 37.2% a year.
How to use it on your own company
Enter revenue, cost of goods and your DSO, DIO and DPO on Inputs.
Enter the discount terms and your revolver rate.
Read the net gain and the leverage effect on Discount before deciding.
If you run supply chain finance, check both sides on SupplyChainFinance.
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.
How do you calculate the annual cost of an early payment discount?
Divide the discount by one minus the discount, and multiply by 365 over the number of extra days you get by not taking it. For 2/10 net 60 that is 0.02/0.98 times 365/50, or 14.9%.
Can I use it for my own company?
Yes, by replacing the inputs. The company and its figures are fictional.
The rest of the files
This template is one of the companion workbooks for Treasury Management: the others reconcile five readings of cash, test liquidity, and cover hedging. 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.