Economic Order Quantity (EOQ) Formula in Excel

The EOQ formula in Excel is =SQRT(2*D*S/H): the square root of two times annual demand times the cost of placing one order, divided by the cost of holding one unit for a year. EOQ stands for economic order quantity, the order size that keeps the combined cost of ordering and storing stock as low as possible. This guide sets it up step by step with example figures, shows how to round it, and explains when the result can mislead you. It is a calculation guide, not advice for any real business.

What is economic order quantity?#

Economic order quantity is the number of units to order each time so that your total yearly ordering and holding costs are as small as possible.

Two costs pull in opposite directions:

• Ordering costs go down when you order in bigger batches, because you place fewer orders.
• Holding costs go up with bigger batches, because more stock sits on the shelf on average.

EOQ is the balance point between them. At the exact EOQ, the yearly ordering cost and the yearly holding cost are equal.

The formula is:

EOQ = √(2 × D × S ÷ H)

All values in this guide are made-up examples.

How to calculate EOQ in Excel#

Put each input in its own labelled cell so you can change it later. With the values in B2:B4:

1. Type the labels in A2:A4 and the values in B2:B4.

2. Click B6 and type:

   Excel formula

   =SQRT(2*B2*B3/B4)

3. Press Enter. The result is 632.46.

SQRT returns the square root of a number. You can also raise to the power of 0.5, which is the same thing: =(2*B2*B3/B4)^0.5 or =POWER(2*B2*B3/B4,0.5). The guide to square roots and powers in Excel explains both.

You don't need brackets around 2*B2*B3/B4 inside SQRT, because Excel works left to right through multiplication and division. Brackets are still a good habit if you rearrange it: =SQRT((2*B2*B3)/B4) is easier to read.

How to round EOQ to whole units#

You can't order 632.46 units, so round the result. Pick the rounding that matches how you buy:

ROUND's second argument is the number of decimal places, so 0 means whole numbers. The ROUND function guide and the guide to ROUND, ROUNDUP and ROUNDDOWN cover each one. Rounding changes the stored value, unlike number formatting, which only changes what you see.

Rounding barely affects cost here: the total yearly cost at 632, 633 or 636 units is within about 1 of the minimum.

Holding cost as a percentage of unit cost#

Holding cost is often given as a yearly percentage of the item's cost, not as an amount. It covers storage, insurance, damage and the money tied up in stock. Turn it into an amount per unit first:

H = holding rate × unit cost

If an item costs 15 and the holding rate is 20% a year, H is 0.2 × 15 = 3. In Excel, with unit cost in B7 and the rate in B8, the EOQ becomes =SQRT(2*B2*B3/(B8*B7)). Here the brackets around B8*B7 are essential. Without them, Excel divides by B8 and then multiplies by B7.

If you need to work out the unit cost first, the guide to cost per unit in Excel shows how.

Orders per year, total cost and reorder point#

Once you have the EOQ, a few more numbers help you plan. With the rounded EOQ of 632 in B6:

The holding cost uses B6/2 because, on average, half the order is on the shelf.

To see why EOQ is the best size, compare other order sizes with the same total cost formula. Ordering 300 at a time costs 2,000 + 450 = 2,450 a year. Ordering 1,000 at a time costs 600 + 1,500 = 2,100. Both are higher than 1,897.37.

The reorder point tells you when to order: daily demand × lead time in days. With a 7-day lead time, =ROUNDUP(B2/365*7,0) gives 231 units. Many businesses add safety stock on top. An inventory tracker can flag items that fall below their reorder point.

Limits of the EOQ formula#

EOQ is a simple model, and its answer is only as good as its assumptions. It assumes:

• Demand is steady and known for the whole year.
• The cost per order and holding cost don't change.
• The whole order arrives at once.
• There are no bulk discounts.
• You never run out of stock.

Real demand is seasonal, suppliers set minimum order sizes, and prices change with volume. Use EOQ as a starting point and check it against those real constraints. The guide to Excel skills for supply chain jobs shows where calculations like this fit in day-to-day work.

Common mistakes with EOQ in Excel#

1. Mixing time periods. Monthly demand with a yearly holding cost gives a wrong EOQ. Convert everything to a year.
2. Using a percentage as H. Typing 20% as the holding cost instead of 20% × unit cost gives a far larger, wrong result.
3. Missing brackets in the divisor. /B8*B7 is not the same as /(B8*B7).
4. Rounding the inputs. Round only the final EOQ.
5. Hard-coding numbers. =SQRT(2*12000*50/3) works, but cell references let you test other values instantly.

Practise it#

Try the exercise on this page to calculate the EOQ with SQRT and ROUND. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, and work through the department budget practice, which combines SUM, AVERAGE, ROUND and absolute references on a realistic sheet. Each lesson checks your formula instantly.