How do I calculate FIFO in Excel?

List purchases oldest-first with units and cost per unit, then allocate units sold to batches with a cumulative formula: Units_from_batch = MAX(0, MIN(batch_units, total_sold − units_already_allocated)). Multiply allocated units by each batch's cost and sum for FIFO COGS; the unallocated remainder values ending inventory. Our free template has these formulas pre-built.

Is this FIFO Excel template really free?

Yes — direct download, no email address or signup required, free to use and share. It works in Microsoft Excel, Google Sheets, and LibreOffice Calc.

What are the limitations of calculating FIFO in a spreadsheet?

Spreadsheet FIFO works well for one product with purchases in date order and a single units-sold total. It breaks down with interleaved buys and sells, multiple SKUs, returns, or when you need LIFO and Weighted Average comparisons — research shows 94% of businesses using Excel for inventory have material errors. That's the point to move to dedicated software.

Free FIFO Excel Template

Enter your purchases and units sold — the built-in formulas allocate costs first-in-first-out and calculate COGS and ending inventory automatically.

Download Free Template (.xlsx)

No email required · Works in Excel, Google Sheets & LibreOffice

What's Inside

20-Batch Purchase Table

Date, units, and cost per unit for up to 20 purchase batches — pre-loaded with a worked example you can overwrite.

Live FIFO Allocation

Cumulative MIN/MAX formulas consume oldest batches first and recalculate COGS and ending inventory as you type.

Built-In Sanity Checks

An oversell warning fires if units sold exceeds units purchased, plus a full instructions tab explaining every formula.

How FIFO Works in Excel

FIFO assumes the oldest units are sold first. The spreadsheet challenge is allocation: deciding how many of the units you sold came from each purchase batch. The template solves it with one cumulative formula per row:

Units_from_batch = MAX(0, MIN(batch_units, total_units_sold − units_allocated_to_earlier_batches))

Each batch gives up as many units as are still unallocated, capped at what the batch holds. Multiply by that batch's cost and sum the column: that's your FIFO COGS. Whatever wasn't allocated stays in inventory at its original batch cost — your ending inventory value.

The template ships with the standard worked example (100 @ $10, 200 @ $12, 150 @ $14, selling 320 units → COGS $3,680, ending inventory $1,820). Overwrite it with your own numbers and everything recalculates. Want to check a result instantly without the spreadsheet? Use our free ending inventory calculator or the FIFO method guide.

When a Spreadsheet Stops Being Enough

The template is honest about its limits — they're printed on the instructions tab:

One product per sheet. Ten SKUs means ten copies to maintain.
Purchases must stay in date order, and sales are one period total — interleaved buy/sell sequences need a perpetual system.
FIFO only. Comparing against LIFO or Weighted Average means building two more allocation engines.
Error-prone at scale. Industry research finds 94% of businesses using Excel for inventory carry material calculation errors.

When you hit those walls, the upgrade path is one CSV upload: same data, every method calculated, exportable results, and a saved history. Free for up to 500 transactions.

Frequently Asked Questions

Is it really free? What's the catch?

Really free — direct download above, no email gate. The "catch" is that we think when your inventory outgrows a spreadsheet, you'll remember who gave you the template.

Does it work in Google Sheets?

Yes. Upload the .xlsx to Google Drive and open with Sheets — MIN, MAX, SUM, and IF behave identically. LibreOffice Calc works too.

Can I use it for LIFO or Weighted Average?

Not directly — the allocation direction is FIFO-specific. For instant LIFO and Weighted Average results use our free ending inventory calculator, or upload a CSV to compare all methods on real data.

Can I share it with my team or clients?

Yes — it's free to use and share, including for client work. Accountants and bookkeepers are welcome to redistribute it.

Ending Inventory Calculator

No spreadsheet needed — instant results online.

COGS Calculator

Cost of goods sold by formula or by method.

FIFO vs LIFO Guide

Which method saves you more money?

Skip the Spreadsheet Maintenance

Upload the same purchase data as a CSV and get FIFO, LIFO, and Weighted Average results side by side — no formulas to break.

Start Free — 500 Transactions Included