Free Excel templates

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.

Early_Payment_Discount_and_Working_Capital.xlsx (18 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 Treasury Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
Cash conversion cycle80 days
Net working capital281.5m (20.1% of revenue)
Five days off DSO release19.2m
Terms2/10 net 60
Annual rate of not taking the discount14.9%
Discount captured a year7.84m
Revolver cost of paying early2.37m
Net gain a year5.47m
Saving from pooling1.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

  1. Enter revenue, cost of goods and your DSO, DIO and DPO on Inputs.
  2. Enter the discount terms and your revolver rate.
  3. Read the net gain and the leverage effect on Discount before deciding.
  4. 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.

Open the companion files →

Other free templates

This template comes from Treasury Management. The book is on Amazon.

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.