TEMPLATES & BUSINESS WORKFLOWS

FIFO Inventory in Excel: Cost of Goods Sold Formula Without VBA (Free Template)

FIFO (first in, first out) assumes the oldest stock is sold first, so your cost of goods sold uses the oldest purchase prices and your ending inventory uses the newest. Doing it by hand means peeling units off one purchase layer after another. In this guide you will build it in Excel with ordinary formulas, no VBA and no macros: a layer table that works out how many units come from each purchase, the FIFO cost of goods sold and ending inventory, the FIFO cost of every single sale, and a comparison with weighted average cost. The free template uses a real month of purchases and sales, and every number is checked.

FIFO inventory in Excel: cost layers bought Jan 8 at 19.20, Jan 15 at 19.80 and Jan 22 at 20.40 with FIFO costs of 3,840.00, 2,970.00 and 2,040.00, and FIFO cost of goods sold of 11,070.00.
Each purchase layer, its FIFO cost, and the total cost of goods sold.

How FIFO works, in one example

Brightline Trading sells A4 copy paper by the box. In January 2026 it started with 120 boxes and bought four more lots at rising prices. It sold 570 boxes in the month.

LayerBoxesUnit costUnder FIFO
Opening, Jan 112018.50All 120 sold
PO-1041, Jan 820019.20All 200 sold
PO-1047, Jan 1515019.80All 150 sold
PO-1052, Jan 2218020.40100 sold, 80 left
PO-1058, Jan 2910021.00All 100 left

The 570 boxes sold are the oldest 570: 120 + 200 + 150 + 100. The 180 boxes left are the newest: 80 from Jan 22 and 100 from Jan 29. The template does exactly this with formulas.

Free download: Get the FIFO inventory template (.xlsx). It has a Layers sheet with the FIFO calculation, checks and a weighted average comparison, a Sales sheet with the FIFO cost of every sale, and a How It Works sheet. No macros. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

Step 1: List the purchase layers, oldest first

On the Layers sheet, enter one row per purchase in date order: date, reference, quantity and unit cost. The opening inventory is the first layer, at its cost from last month. Column E is the layer cost, =C5*D5, and column F is a running total of units bought:

=IF(C5="",0,SUM($C$5:C5))

The mixed reference $C$5:C5 grows as you fill down, like any running total in Excel. Empty rows return 0 so the Sales sheet formula in Step 5 can use the whole column.

Excel FIFO purchase layers for January 2026: opening inventory of 120 boxes at 18.50 and four purchase orders, 750 units available at a total cost of 14,802.00.
Each purchase is a layer with its own unit cost.
Checkpoint: 750 boxes available in January at a total cost of 14,802.00.

Step 2: Take the units sold from the oldest layers

C18 holds the units sold, 570, taken from the Sales sheet. For each layer, the units sold are whatever is still needed after the older layers, but never more than the layer holds and never less than zero. In G5, filled down:

=IF(C5="","",MIN(C5,MAX(0,$C$18-(F5-C5))))

F5-C5 is the number of units in the older layers. $C$18 minus that is what is still needed. MAX(0, …) stops it going negative once the sales are covered, and MIN(C5, …) caps it at the layer quantity. For the Jan 22 layer: 570 – (650 – 180) = 100, and MIN(180, 100) = 100.

LayerOlder layersStill neededUnits sold
Opening0570120
Jan 8120450200
Jan 15320250150
Jan 22470100100
Jan 296500 (MAX gives 0)0

Column H, units left, is simply =C5-G5.

Excel FIFO units sold per layer using MIN and MAX: 570 units sold use up the first three layers and 100 of the 180 boxes bought on Jan 22.
MIN and MAX hand out the units sold, oldest layer first.

Step 3: Value cost of goods sold and ending inventory

Multiply each layer’s units by its unit cost. Cost of goods sold in I5 is =G5*D5 and ending inventory in J5 is =H5*D5 (both wrapped in IF so empty rows stay blank). Row 15 totals them.

CalculationAmount
Cost of goods sold (FIFO)120 x 18.50 + 200 x 19.20 + 150 x 19.80 + 100 x 20.4011,070.00
Ending inventory (FIFO)80 x 20.40 + 100 x 21.003,732.00

The cost of goods sold goes to your profit and loss statement and the ending inventory to the balance sheet. With sales prices you can also work out the margin per box; see gross margin vs markup in Excel.

Excel FIFO cost of goods sold of 11,070.00 and ending inventory of 3,732.00, valued layer by layer.
Units x layer cost, for what was sold and what is left.

Step 4: Check the result

Three checks in C22:C24 protect the calculation:

CheckFormulaResult
COGS + ending = cost of goods available=IF(ROUND(C20+C21-E15,2)=0,"Ties","Check")Ties
Enough stock for the units sold=IF(C18<=C19,"OK","Short by "&TEXT(C18-C19,"#,##0"))OK
COGS per Sales sheet=IF(ROUND(C20-Sales!H15,2)=0,"Ties","Check")Ties

The first check is the inventory equation: what you had available is either sold or still on the shelf. The second stops you from selling stock you never bought, which usually means a missing purchase or a sale entered twice.

Excel FIFO summary checks: cost of goods sold 11,070.00 plus ending inventory 3,732.00 equals 14,802.00, with Ties, OK and Ties.
Available = sold + left, and enough stock for every sale.
Try it: On the Sales sheet change the Jan 30 quantity (C9) from 60 to 120. COGS rises to 12,294.00, ending inventory falls to 2,508.00 and both Ties checks still pass. Then try 260: the stock check on that sale shows Short and the summary shows Short by 20. Change it back to 60.

Step 5: FIFO cost for every sale

Often you need the cost of each sale, not just the month. The Sales sheet lists the sales in date order with a running total of units sold in column D. Column G then values all units sold to date at FIFO in one formula:

=SUMPRODUCT(Layers!$D$5:$D$14,(D5>=Layers!$F$5:$F$14)*Layers!$C$5:$C$14+(D5<Layers!$F$5:$F$14)*(D5>Layers!$F$5:$F$14-Layers!$C$5:$C$14)*(D5-Layers!$F$5:$F$14+Layers!$C$5:$C$14))

It looks long, but it is the same idea as Step 2 written for every layer at once. If the units sold to date cover a whole layer, take the whole layer. If they end inside a layer, take only the part that is needed. Otherwise take nothing. SUMPRODUCT multiplies those units by the layer costs and adds them up. The template wraps it in IF(C5="","",...) for empty rows.

The cost of each sale is the change from the previous row:

=IF(C5="","",G5-IF(ISNUMBER(G4),G4,0))

Column E adds up the purchases made on or before the sale date with SUMIFS, and column F flags any sale bigger than the stock bought so far. To keep stock levels visible day to day, pair this with an inventory reorder tracker.

Excel sales sheet with FIFO cost for each sale: the Jan 19 sale of 130 boxes costs 2,514.00 and the five sales total 11,070.00.
Each sale costed at FIFO, adding up to the same 11,070.00.
Checkpoint: The Jan 19 sale of 130 boxes: 6,654.00 – 4,140.00 = 2,514.00 (100 boxes at 19.20 and 30 at 19.80). The five sales total 11,070.00, the same as the Layers sheet.

Step 6: Compare with weighted average cost

Weighted average cost spreads the total cost evenly over all units available: 14,802.00 / 750 = 19.7360 per box. The summary in C27:C30 compares it with FIFO. For the SUMPRODUCT version of a weighted average, see calculating a weighted average in Excel.

MethodCost of goods soldEnding inventory
FIFO11,070.003,732.00
Weighted average11,249.523,552.48
Difference(179.52)179.52

When prices are rising, FIFO gives a lower cost of goods sold and a higher profit, because the cheaper old stock is expensed first. When prices fall, the opposite happens. Pick the method your accounting framework and tax rules allow, and use it consistently.

Excel comparison of FIFO and weighted average cost: average cost 19.7360, weighted average cost of goods sold 11,249.52 against FIFO 11,070.00.
FIFO vs weighted average in a month of rising prices.
FIFO is per product: Each product needs its own layers. For several products, keep one Layers and Sales pair per product, or add a Product column to both sheets and add it as an extra condition in the formulas.

Frequently asked questions

How do I calculate FIFO in Excel without VBA?

List the purchase layers oldest first with a running total of units. For each layer, units sold = MIN(layer quantity, MAX(0, total sold – units in older layers)). Multiply by the layer cost and add up for the cost of goods sold.

What is the FIFO formula for cost of goods sold?

Cost of goods sold = the cost of the oldest units, layer by layer, until the units sold are covered. In the template that is the sum of units sold from each layer times its unit cost.

How do I calculate ending inventory with FIFO?

Value the units left in each layer at that layer’s cost. They are always the newest units. Check that cost of goods sold plus ending inventory equals the total cost of goods available.

Does FIFO give a higher or lower profit than weighted average?

When purchase prices are rising, FIFO gives a lower cost of goods sold and a higher profit. When prices are falling, it gives a lower profit.

Can I use FIFO for several products?

Yes. Use one set of layers per product, or add a product column and include it as a condition, so units are only taken from layers of the same product.

Final checklist

  • Layers are in date order, with the opening inventory first.
  • Sales are in date order, and every Stock check shows OK.
  • COGS + ending = cost of goods available shows Ties.
  • COGS per Sales sheet shows Ties.
  • Ending inventory agrees with the stock count, valued at the newest costs.
Practice file: Download the FIFO inventory template. Replace the yellow purchases and sales with your own; up to 10 layers and 10 sales are ready, and you can insert more rows inside the ranges.

Keep your stock and margins under control

Browse the ready-to-use Excel templates, visit the OneXcel Tutorial Hub for step-by-step guides, or turn your inventory data into a dashboard with the OneXcel Dashboard Builder.