Excel guide · 5 min read
How to calculate economic order quantity (EOQ) in Excel
=SQRT(2*B2*C2/G2). Holding cost is usually unit cost x a carrying rate, such as =D2*$O$1. Round the result up to the supplier's case pack with =CEILING(H2,E2) before you order.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Annual demand | Cost per order | Unit cost | Case pack | Min order | Holding cost/unit/yr | EOQ | Order qty | Orders/yr | Days between | Ordering cost/yr |
| 2 | Filter cartridge | 4800 | 45 | 12 | 24 | 0 | 3 | 379.47331922 | 384 | 12.5 | 29.2 | 562.5 |
| 3 | Hose reel | 1200 | 60 | 40 | 12 | 0 | 10 | 120 | 120 | 10 | 36.5 | 600 |
| 4 | Washer pack | 15000 | 30 | 2.5 | 100 | 2000 | 0.625 | 1200 | 2000 | 7.5 | 48.666666667 | 225 |
| 5 | ||||||||||||
| 6 | ||||||||||||
| 7 | Hose reel price breaks | Min qty | Unit price | EOQ at price | Order qty | Total cost/yr | ||||||
| 8 | Break 1 | 1 | 40 | 120 | 120 | 49200 | ||||||
| 9 | Break 2 | 300 | 38.5 | 122.31532595 | 300 | 47883.75 | ||||||
| 10 | Break 3 | 600 | 37 | 124.77005878 | 600 | 47295 |
Download this sheet as an Excel practice file (.xlsx)
What EOQ balances, and the inputs you need
Every order costs money to place (purchase order admin, receiving, a delivery charge), so ordering rarely is cheaper. But big orders sit on the shelf, and stock costs money to hold (cash tied up, space, insurance, damage). EOQ is the order size where those two yearly costs add up to the least.
You need four inputs per item: annual demand in units, the cost of placing one order, the unit cost, and a carrying rate, the yearly cost of holding stock as a share of its value. The sample has three plumbing SKUs in rows 2 to 4, with the carrying rate in O1 (25%) and days per year in O2. The carrying rate is your number to estimate, from your cost of capital, storage, insurance and losses; keep it in one cell so you can test other values.
Step 1: holding cost per unit per year
Holding cost = unit cost x carrying rate. A 12.00 filter cartridge at a 25% carrying rate costs 3.00 a year to keep on the shelf. Lock the rate cell with dollar signs so the formula fills down.
Result: 3
3: each filter cartridge costs 3.00 a year to hold.
Step 2: the EOQ formula
The filter sells 4,800 a year and each order costs 45 to place. EOQ = SQRT(2 x 4,800 x 45 / 3) = SQRT(144,000) = about 379 units.
Keep demand and holding cost in the same period. If you have monthly demand, either multiply it by 12 or use a monthly holding cost; mixing a monthly demand with a yearly holding cost gives an EOQ that's far too small.
Result: 379.47331922 shows as 379.5 with Number format
379.47: the cheapest order size for the filter cartridge, before rounding to what the supplier ships.
How sensitive is EOQ to the carrying rate I guessed?
Less than you'd think, because of the square root. Raising the rate from 25% to 30% (a fifth higher) lowers the filter's EOQ by only about 9%.
Result: 346.41016151
346.41 units at a 30% carrying rate, against 379.47 at 25%.
Step 3: round to the case pack and respect minimum orders
Suppliers ship in cases and some have a minimum order. CEILING rounds up to the next multiple of the case pack, and MAX lifts the result to the minimum when the EOQ is below it. The filter ships in cases of 24, so 379.47 becomes 384. The washer pack's EOQ is 1,200, but its supplier's minimum is 2,000, so you order 2,000.
Rounding costs almost nothing. The yearly ordering plus holding cost at the exact EOQ of the filter is 1,138.42; at 384 it's 1,138.50. The total cost curve is flat near the bottom, which is why EOQ is useful even when your inputs are estimates. A minimum far above the EOQ is a different story: the washer pack costs 850 a year at 2,000 against 750 at its EOQ.
Result: 384
384: the EOQ rounded up to whole cases of 24. For items with no minimum, F is 0 and MAX keeps the rounded EOQ.
Step 4: orders per year, days between orders and the check
Orders per year = annual demand / order quantity, and days between orders = 365 / orders per year. The filter is ordered 12.5 times a year, about every 29 days.
At the exact EOQ, the yearly ordering cost equals the yearly holding cost. That makes a quick check of your formulas: the hose reel's EOQ is exactly 120, and both costs come to 600. The filter's are close (562.50 and 576) because 384 is slightly above its EOQ. If the two are far apart and you haven't hit a minimum order, check the units of your inputs.
Result: 0
0: for the hose reel, ordering cost per year (10 orders x 60) equals holding cost per year (120 / 2 x 10).
Price breaks: when ordering more than the EOQ pays
The basic formula assumes one unit price. When a supplier offers a lower price for bigger orders, compare the total yearly cost, including the purchase cost, at each price. For each break: work out the EOQ at that price, lift it to the break's minimum quantity, then total purchase + ordering + holding cost. Rows 8 to 10 do this for the hose reel.
At 40.00 the EOQ of 120 costs 49,200 a year in total. At 38.50 you must order 300, which costs 47,883.75. At 37.00 for 600 or more, the total is 47,295, the lowest, even though holding cost goes up. XLOOKUP returns the order quantity with the lowest total.
Result: 600
600: the quantity at the 37.00 price break gives the lowest total yearly cost.
When EOQ doesn't fit
EOQ assumes steady demand through the year, a fixed order cost and no stock-outs. It works well for steady sellers and poorly for seasonal or lumpy items, new products and anything with a short shelf life. Check whether you have the space and the cash for the order size it suggests.
EOQ tells you how much to order; the reorder point tells you when. Pair it with safety stock and reorder points, and track the effect on inventory turnover. To decide which items deserve this level of attention, start with an ABC analysis. This page shows how to calculate EOQ; it isn't purchasing advice.
Common mistakes
Mixing monthly demand with a yearly holding cost
Use annual demand with annual holding cost, or convert both to the same period.
Using the unit price as the holding cost
Holding cost per unit per year is unit cost x carrying rate, not the unit cost itself.
Ordering a fraction of a case
Round up with CEILING(EOQ, case pack) and apply any minimum order with MAX.
Ignoring price breaks
Compare total yearly cost, purchase cost included, at each break quantity.
FAQ
What is the EOQ formula in Excel?
=SQRT(2*annual demand*cost per order/holding cost per unit per year), for example =SQRT(2*B2*C2/G2). Holding cost is usually unit cost times a carrying rate.
How do I round EOQ to a case quantity in Excel?
Use =CEILING(EOQ,case size), which rounds up to the next multiple. =ROUNDUP(EOQ/case size,0)*case size does the same thing.
How do I calculate how often to order?
Orders per year = annual demand / order quantity, and days between orders = 365 / orders per year.
What carrying rate should I use?
It depends on your cost of capital, storage, insurance and losses, so estimate it from your own figures and keep it in an input cell. Because of the square root, a rough estimate still gives a usable EOQ.
Functions used
SQRTSquare rootCEILINGRounds up to a multipleMAXLargest numberXLOOKUPModern lookup in any directionMINSmallest number
Related guides
- How to calculate inventory carrying cost in Excel
- How to calculate safety stock and reorder point in Excel
- How to calculate inventory turnover in Excel
- ABC inventory analysis in Excel
- Excel formulas for inventory: stock balance, reorder and value
- #NUM! error in Excel: causes and fixes
- Excel not responding: how to fix it, without closing it first
Updated