The basic Economic Order Quantity is calculated in Excel with the Wilson square-root formula Q* = sqrt(2DS/H), where D is annual demand in units per year, S is the fixed cost of placing one order in your currency, and H is the annual holding cost per unit in the same currency. The result is the order size that minimizes the sum of annual ordering and holding costs for one inventory item, and the formula sits inside an Excel worksheet as a single cell expression that you can copy down a column of demand scenarios. Setting it up is fast once you label three input cells with consistent annual units, because Excel's SQRT function evaluates the square root and every other figure — orders per year, average cycle length, ordering cost, holding cost, and total relevant cost — follows from plain arithmetic on the same row. The trick is keeping D, S, and H on one annual basis, treating EOQ as a continuous optimum you may have to round to a feasible pack size, and remembering that the formula answers how much to order rather than when.
The EOQ formula and what each variable means
The EOQ formula comes from minimizing the annual relevant cost TC(Q) = DS/Q + HQ/2. The first term, DS/Q, is annual ordering cost, which falls as Q grows because fewer orders are needed to cover the same annual demand. The second term, HQ/2, is annual holding cost on average cycle stock, which rises with Q because more inventory sits on the shelf at any time. The minimum of that sum is the basic EOQ. University operations-management materials describe the same square-root formula and the same cost components, so the calculation you build in Excel is the one taught in coursework (SUNY New Paltz, Operations Management).
Three inputs feed the formula, and each must be stated on the same annual basis:
- D — annual demand. Units consumed or sold in a year. If your demand data is weekly, multiply by 52; if monthly, multiply by 12.
- S — fixed cost per order. The cost to place one purchase order, including administration, transport setup, receiving, and any fixed supplier fees. Currency per order.
- H — annual holding cost per unit. The annual cost of carrying one unit in stock for a year, including warehouse, insurance, shrinkage, and the opportunity cost of capital. Currency per unit per year.
If your holding cost is given as a percentage of unit price, convert it to currency first: H = holding-cost percentage × unit price, before you type it into Excel. The formula stays the same; only the number changes.
Setting up an EOQ worksheet in Excel
Keep one row per scenario or one sheet per item, and reserve the first column for labels. Three cells hold the inputs, one cell holds the formula, and four more cells hold the derived figures. The whole layout fits in a single block eight rows tall.
| Cell label | Variable | What to enter | Unit basis |
|---|---|---|---|
| Annual demand | D | Units consumed or sold per year | units / year |
| Order cost | S | Fixed cost per purchase order | currency / order |
| Holding cost | H | Annual cost to carry one unit | currency / unit / year |
| EOQ | Q* | Formula result | units (continuous) |
| Orders per year | D/Q | Annual order count | orders / year |
| Cycle days | 365Q/D | Average days between orders | days |
| Ordering cost | DS/Q | Annual ordering spend | currency / year |
| Holding cost | HQ/2 | Annual carrying spend | currency / year |
Put each input in its own cell, and use cell references — not hard-coded numbers — inside the formulas. That lets you change D, S, or H in one place and see the EOQ, orders per year, cycle days, and both cost components update together. If you want to compare scenarios, copy the whole block to additional rows and overwrite the inputs; the formulas follow.
How to calculate EOQ in Excel, step by step
Use this eight-step build for any item. The cell references assume D lives in B2, S in B3, H in B4, and that your results start in B5.
- Type the labels in column A from row 1: Annual demand (D), Order cost (S), Holding cost (H), EOQ, Orders per year, Cycle days, Ordering cost, Holding cost, Total relevant cost.
- Enter D in B2 as units per year (for example 1200).
- Enter S in B3 as currency per order (for example 5).
- Enter H in B4 as currency per unit per year (for example 1.2). If H is a percentage, multiply it by the unit price in a separate cell first.
- Calculate EOQ in B5 with the formula =SQRT(2*B2*B3/B4). This is the Wilson square root.
- Calculate orders per year in B6 with =B2/B5 and cycle days in B7 with =365*B5/B2.
- Calculate ordering cost in B8 with =B2*B3/B5 and holding cost in B9 with =B4*B5/2. The two figures should be equal at the optimum.
- Total the costs in B10 with =B8+B9. Use this as the relevant-cost figure for comparison, not for purchase price.
Copying B5 down through B10 to extra rows automatically evaluates each scenario. If you want to test pack-size rounding, copy the entire row, replace the EOQ formula in B5 with a fixed quantity, and read the new total in B10 — the difference shows the cost penalty of rounding away from the continuous optimum.
A worked example using textbook values
The university example D = 1200 units per year, S = 5 currency per order, H = 1.2 currency per unit per year produces clean numbers. Inside Excel:
- B5 = SQRT(2 × 1200 × 5 / 1.2) = SQRT(10000) = 100 units
- B6 = 1200 / 100 = 12 orders per year
- B7 = 365 × 100 / 1200 ≈ 30.42 days between orders
- B8 = 1200 × 5 / 100 = 60 currency per year in ordering cost
- B9 = 1.2 × 100 / 2 = 60 currency per year in holding cost
- B10 = 60 + 60 = 120 currency per year of total relevant cost
Note the symmetry: ordering cost and holding cost both round to 60, confirming the cost-balance invariant at the continuous optimum. If your two cells do not match, the EOQ has been rounded or the inputs have been entered on inconsistent bases. A real order of 100 units a month, twelve times a year, matches the formula's recommendation here, so no pack-size correction is needed.
Why ordering cost equals holding cost at the EOQ
Annual ordering cost DS/Q falls as Q grows. Annual holding cost HQ/2 rises as Q grows. The two cost curves cross exactly once, and that crossing is the EOQ. The reason is straightforward calculus: the derivative of DS/Q is -DS/Q², the derivative of HQ/2 is H/2, and setting the derivative of the sum to zero gives -DS/Q² + H/2 = 0, which rearranges to Q = sqrt(2DS/H). At that point DS/Q = HQ/2, so the two cells in your Excel sheet match.
This is also a useful sanity check in the spreadsheet: if B8 and B9 disagree, one of three things has gone wrong. Either the inputs are not on a single annual basis, the formula has been rounded before the check, or the EOQ cell is being compared to a manually overridden quantity. The step-by-step total cost guide walks through the same balance in more detail if you want to extend the sheet.
What basic EOQ does not cover in Excel
The Wilson formula is a deliberately small model. Basic EOQ assumes constant known demand, constant known ordering and holding costs, instantaneous complete replenishment, no shortages, no safety stock, no quantity discounts, and no capacity or cash constraint. It also assumes unit purchase price does not change with Q, so purchase cost is omitted from the relevant-cost comparison. Violating any of these assumptions can shift the best quantity materially, so an Excel sheet built on this formula is a starting point rather than a final answer.
Lead time is not an EOQ input. EOQ answers how much to order, not when; reorder timing needs a separate model that combines demand during lead time plus any justified safety stock. Seasonal demand, uncertain lead times, perishability, obsolescence, production replenishment, quantity discounts, or service-level targets each require a different model or an extension of the basic sheet.
Excel sheet versus a browser EOQ calculator
Both reach the same Wilson formula. The trade-off is control and iteration versus speed and assumptions.
| Aspect | Excel worksheet | EOQ Calculator |
|---|---|---|
| Best for | Scenario tables, sensitivity grids, embedding in larger models | A single quick baseline, sensitivity exploration, or a sanity check |
| Inputs | You define and label every cell | Three labelled inputs, validated for positivity |
| Outputs | Whatever formulas you build | EOQ, orders per year, cycle days, ordering cost, holding cost, total relevant cost |
| Pack-size rounding | Manual — you copy the row, overwrite the EOQ, read the new total | You compare feasible whole or pack-multiple quantities using the same total-cost equation |
| Data location | Your local file | Browser-only; metrics can be copied, nothing is uploaded |
| Caveats shown | Whatever comments you add | The basic-model assumptions and the cost-balance invariant are exposed next to the result |
The Excel sheet shines when you want a sensitivity grid — paste a column of demand forecasts or unit prices and watch the EOQ column update. The EOQ Calculator is the faster option when you only need a single number, want the assumptions and cost-balance invariant displayed alongside the answer, or need a quick cross-check on a figure your spreadsheet is producing.
Either way, treat the number as a baseline, not an automatic purchasing commitment. Validate demand forecasts, carrying-cost composition, supplier pack constraints, cash, shelf life, lead-time risk, service levels, and feasible pack sizes with responsible operations staff before ordering. Recalculate when forecasts, administration, warehouse cost, insurance, shrinkage, or capital cost change — D, S, and H are estimates, not timeless facts.
Related reading: How to Convert Time to Hours: Formulas and a Free Tool.