A punch card feels generous, but what discount is it really giving? Once you express 'buy 9, get one free' as a percentage, you can compare it to a straight price cut.
Buy 9 and get the 10th free means 1 free in every 10 — a 10% effective discount.
The example
A card stamps nine paid drinks, then the tenth is free.
| A | B | |
|---|---|---|
| 1 | Label | Value |
| 2 | Paid drinks before free | 9 |
| 3 | Drink price | $5.00 |
| 4 | Effective discount | 10% |
The formula
One free drink spread across the full cycle:
How it works
The reasoning:
- A full cycle is the paid drinks plus the one free drink:
B2+1= 10 drinks. - Exactly one of those ten is free, so the customer pays for 9 and gets 10.
1/(B2+1)= 1/10 = 0.10, i.e. a 10% effective discount.- Format the cell as a percentage to display 10%.
Compare that to your margin: a 10% discount on a $5 drink is $0.50 of revenue per cycle.
Try it: interactive demo
Set how many paid drinks earn a free one — see the real discount.
Variations
Revenue given up per cycle
Multiply the free drink's price by how many cards complete.
Discount on the COGS basis
If the free drink only costs you its ingredients, use the cost instead of the price.
Pitfalls & errors
The free drink costs you its ingredients, not the full menu price — your real margin hit is smaller than the headline discount.
Breakage (cards never completed) lowers the true discount. Track completion rate and multiply it in.
Practice workbook
Frequently asked questions
Is buy-9-get-1 really only 10% off?
How is this cheaper than a 10% discount for me?
What about a buy-5-get-1 card?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class