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.

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.
| Layer | Boxes | Unit cost | Under FIFO |
|---|---|---|---|
| Opening, Jan 1 | 120 | 18.50 | All 120 sold |
| PO-1041, Jan 8 | 200 | 19.20 | All 200 sold |
| PO-1047, Jan 15 | 150 | 19.80 | All 150 sold |
| PO-1052, Jan 22 | 180 | 20.40 | 100 sold, 80 left |
| PO-1058, Jan 29 | 100 | 21.00 | All 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.
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.

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.
| Layer | Older layers | Still needed | Units sold |
|---|---|---|---|
| Opening | 0 | 570 | 120 |
| Jan 8 | 120 | 450 | 200 |
| Jan 15 | 320 | 250 | 150 |
| Jan 22 | 470 | 100 | 100 |
| Jan 29 | 650 | 0 (MAX gives 0) | 0 |
Column H, units left, is simply =C5-G5.

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.
| Calculation | Amount | |
|---|---|---|
| Cost of goods sold (FIFO) | 120 x 18.50 + 200 x 19.20 + 150 x 19.80 + 100 x 20.40 | 11,070.00 |
| Ending inventory (FIFO) | 80 x 20.40 + 100 x 21.00 | 3,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.

Step 4: Check the result
Three checks in C22:C24 protect the calculation:
| Check | Formula | Result |
|---|---|---|
| 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.

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.

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.
| Method | Cost of goods sold | Ending inventory |
|---|---|---|
| FIFO | 11,070.00 | 3,732.00 |
| Weighted average | 11,249.52 | 3,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.

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.
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.
