Excel guide · 5 min read
How to do FIFO inventory valuation in Excel
=MIN(units, MAX(0, counted on hand - units bought after this layer)). Multiply units left by unit cost and add them up for the ending inventory value. COGS is the total cost of everything you had minus that ending value.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 | Date | Source | Units | Unit cost | Cost | Units left | Value left | Sold from layer | Counted on hand | Ending value | COGS | |
| 2 | Jul 1, 2026 | Opening stock | 120 | 41 | 4920 | 0 | 0 | 120 | 260 | 11954 | 26932 | |
| 3 | Jul 14, 2026 | PO-3301 | 200 | 42.5 | 8500 | 0 | 0 | 200 | ||||
| 4 | Aug 5, 2026 | PO-3318 | 150 | 44 | 6600 | 0 | 0 | 150 | ||||
| 5 | Aug 28, 2026 | PO-3342 | 180 | 43.2 | 7776 | 20 | 864 | 160 | ||||
| 6 | Sep 12, 2026 | PO-3360 | 100 | 45.8 | 4580 | 100 | 4580 | 0 | ||||
| 7 | Sep 26, 2026 | PO-3377 | 140 | 46.5 | 6510 | 140 | 6510 | 0 |
Download this sheet as an Excel practice file (.xlsx)
How FIFO works, and the layout
FIFO assumes you sell the oldest stock first. You don't have to track which drill left the shelf: at the end of a period you count what's left, and FIFO says those units come from your most recent purchases. Everything older was sold.
The sample is one product, a cordless drill, for July to September. A:D lists the opening stock and each purchase order oldest first, with the unit cost on each. E is the cost of each layer (=C2*D2). J2 holds the stock count on 30 September: 260 drills. Columns F to H work out which layers those 260 come from.
Keep one table per product (or add a Product column and the same logic per product). The method only works if the layers are sorted by date, oldest at the top.
- Type or paste the purchases in A:D, oldest first. Start with the opening stock as its first layer, at the cost it was valued at last period.
- Put the counted units in J2.
- Add the formulas from the next sections in E2:H2 and fill them down.
Units left in each layer: MIN and MAX
For each row, ask: how many of the counted units are already covered by newer purchases? Whatever's left over, up to the size of this layer, belongs to this layer. Newer units are the total units minus the running total down to this row: SUM($C$2:$C$7)-SUM($C$2:C2).
MAX(0, ...) stops older layers going negative once the count is used up, and MIN(C2, ...) stops a layer holding more units than were bought in it. Fill it down and the counted 260 lands on the newest rows: all 140 from PO-3377, all 100 from PO-3360, and the last 20 from PO-3342.
Why a running total and not SUM(C3:$C$7) for "rows below"? On the last row that range would become C8:$C$7, which Excel rewrites as $C$7:C8, so the row counts itself. The running-total version works on every row.
Result: 20
20 of the 180 drills from PO-3342 are still on hand. In the table the formula is =MIN(C5,MAX(0,$J$2-(SUM($C$2:$C$7)-SUM($C$2:C5)))) so it fills down; the three older layers show 0.
How many units were sold from each layer?
Units bought minus units left: =C5-F5 in column H. For PO-3342 that's 160 of 180. The column adds up to the units sold for the period, which is a useful check.
Result: 630
630 drills sold: 890 available minus the 260 counted.
Ending inventory value and COGS
Value each layer at its own unit cost (G2: =F2*D2) and add the column. Or skip the helper column and use SUMPRODUCT of units left and unit cost.
Cost of goods sold is everything you had available (opening stock plus purchases, the sum of column E) minus what's still on the shelf. It's the same beginning + purchases - ending formula as in calculate cost of goods sold, with the ending value coming from FIFO instead of a typed number.
Result: 11954
11,954 of ending inventory: 140 x 46.50 + 100 x 45.80 + 20 x 43.20. COGS in L2 is 38,886 - 11,954 = 26,932 for 630 drills, about 42.75 each.
FIFO vs weighted average cost on the same data
Weighted average cost values every unit at the same price: total cost of goods available divided by total units, 38,886 / 890 = about 43.69 a drill. Ending stock is then 260 x 43.69.
Prices rose over the quarter, from 41.00 to 46.50. FIFO leaves the newest, dearest units on the shelf, so its ending value is higher (11,954 vs about 11,360) and its COGS lower (26,932 vs about 27,526). When prices fall, it's the other way round. The difference changes reported profit, which is why the method is an accounting policy: pick it with your accountant, apply it every period, and don't switch to make a quarter look better.
LIFO (last in, first out) is the mirror image: it leaves the oldest layers on the shelf. It isn't permitted under IFRS, so check with your accountant before using it.
Result: 11359.955056
11,359.96: the same 260 drills at the weighted average cost of 43.69. See weighted average in Excel for why you divide total cost by total units rather than averaging the unit costs.
Rolling to the next period, and checks
Next quarter, the layers with units left become the opening layers: PO-3342 with 20 units at 43.20, PO-3360 with 100 at 45.80 and PO-3377 with 140 at 46.50. Paste them as values at the top of a new table, then add the new purchases underneath.
Add a check that the count isn't more than you had available. If it is, a purchase is missing from the list or the count is wrong, and every layer would show full.
Result: OK
OK: 260 counted against 890 available.
What about returns to the supplier?
Reduce the units on the layer the goods came from so the layers still match what you actually kept.
Can I do FIFO sale by sale?
This page values stock at the end of a period from a count. Costing every sale as it happens (perpetual FIFO) needs a running ledger of layers per product, which inventory software handles better than a worksheet.
Common mistakes
Purchases not sorted oldest first
The formula hands the count to the bottom rows. Sort by date (Home > Sort & Filter > Sort Oldest to Newest) before filling it down.
Using SUM of the rows below
On the last row the range flips and includes the row itself. Use total minus running total for newer units.
Leaving out the opening stock
Last period's remaining layers are this period's oldest units. Put them in as the first rows at their original costs.
Averaging the unit costs for comparison
A plain AVERAGE gives each purchase the same weight. Weighted average cost is total cost divided by total units.
FAQ
What is the FIFO formula in Excel?
For each purchase layer, units left = MIN(units, MAX(0, units on hand - units bought later)). Ending value is SUMPRODUCT of units left and unit cost, and COGS is total cost available minus ending value.
How do I calculate COGS using FIFO in Excel?
Add opening stock and purchases at cost, then subtract the FIFO value of the counted ending stock. Or multiply the units sold from each layer by that layer's cost and add them up; both give the same answer.
Does FIFO give higher profit?
When purchase prices are rising, FIFO usually shows a lower COGS and so a higher gross profit than weighted average. When prices fall, the opposite happens.
Is there a FIFO function in Excel?
No, Excel has no built-in FIFO function. The MIN and MAX layer formula above, filled down a sorted purchases list, does the job in any version.
Functions used
MINSmallest numberMAXLargest numberSUMAdds numbersSUMPRODUCTMultiply then addIFPicks a result from a test
Related guides
- How to calculate cost of goods sold in Excel
- How to calculate inventory turnover in Excel
- How to calculate a weighted average in Excel
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate sales commission in Excel
- Tiered commission in Excel: sliding-scale formulas that match your plan
Updated