FIFO & Weighted-Average Stock Valuation in Excel

Last updated: 27 June 2026

FIFO (First-In First-Out) issues the oldest stock first, so issues are valued at your oldest purchase cost and closing stock is valued at your most recent purchase cost; weighted average spreads cost evenly across all units on hand. Both methods are permitted under Indian tax and accounting standards (s.145A, ICDS II, AS-2) — only LIFO is disallowed — and both are straightforward to implement in Excel: FIFO with a layer-by-layer table, weighted average with a running cost formula updated at each receipt.

Key takeaways

• FIFO is strongly preferred (not legally mandated) for perishables, pharma and items with expiry dates; it gives a higher closing stock value when prices are rising.
• Weighted Average is simpler for general trading and manufacturing; it smooths price fluctuations and is easier to maintain in Excel.
• Both methods must be applied consistently within a financial year under AS-2 (Inventories).
• For FIFO in Excel, build a "layers" table: each purchase is a separate row; issues consume the oldest layer first.
• For weighted average, recalculate cost after each purchase using (Previous Value + New Value) ÷ (Previous Qty + New Qty).
• Closing stock value flows into your balance sheet (current assets) and affects taxable profit via the P&L.

Fact box. AS-2 (Inventories), issued by the Institute of Chartered Accountants of India, requires that the cost formula (FIFO or weighted average) be applied consistently for all inventories of a similar nature. You cannot use FIFO for one product category and weighted average for another similar category within the same business.

What is FIFO inventory valuation?

FIFO assumes the oldest stock is sold first. When you issue or sell goods, you consume the earliest purchase batch at its original cost. What remains in stock carries the cost of your most recent purchases.

Why FIFO matters for Indian SMBs

• Pharma and FMCG: goods with expiry dates must physically rotate on a FIFO basis, so the accounting method matches reality.
• Rising prices: FIFO shows a higher closing stock value, which increases reported profit — and therefore tax. Plan accordingly.
• GST ITC reconciliation: FIFO helps trace which purchase batch an issued item came from, useful when tracing ITC eligibility.

How do I implement FIFO in Excel?

FIFO in Excel uses a "layers" approach. Each purchase creates a new row (layer). Issues consume layers from the top (oldest) downward.

Step 1 — Build the FIFO layers table

Create a sheet called FIFO_Layers with these columns:

Enter one row per purchase batch. "Available Qty" = Purchase Qty on entry.

Step 2 — Record an issue

When you issue stock, consume from the oldest layer first:

1. Find the first layer where Remaining Qty > 0.
2. Deduct the issued quantity from that layer. If the layer is exhausted, move to the next.
3. The cost of the issue = sum of (units consumed from each layer × that layer's unit cost).

In Excel, this is most cleanly done with a helper column using cumulative quantities.

Cumulative Qty column (running total of purchases):

=SUM($C$2:C2)

Issued from this layer (for a total issue of, say, 50 units — enter total in a named cell Issue_Qty):

=MAX(0, MIN([@[Purchase Qty]], Issue_Qty - SUM of consumed from earlier layers))

This logic is easiest to implement as a structured formula table where each row calculates how many units were consumed from that layer for a specific sales transaction.

Fact box. A simpler FIFO implementation for small businesses: maintain a "Stock Card" per SKU with columns Date | Transaction | Qty In | Cost In | Qty Out | Balance Qty | Balance Value. Update balance value manually after each transaction using FIFO logic. For fewer than 20 SKUs, a manual stock card is faster than formula gymnastics.

Step 3 — Calculate closing stock value

=SUMPRODUCT(Remaining_Qty_Range, Unit_Cost_Range)

This multiplies each remaining layer's quantity by its unit cost and sums the result.

How do I implement weighted-average cost in Excel?

Weighted average (also called moving weighted average or perpetual weighted average) recalculates the average cost after every purchase.

Formula

After each new purchase:

New Avg Cost = (Previous Stock Value + New Purchase Value) ÷ (Previous Stock Qty + New Purchase Qty)

Issues are then recorded at the current average cost.

Step-by-step Excel table

Create a transactions sheet with these columns:

Avg Cost column formula (row 3, assuming row 2 is opening stock):

=IF(C3>0,
  (H2 + C3*E3)/(G2 + C3),
  F2)

Explanation:

• If Qty In > 0 (it's a purchase): recalculate average = (old value + new value) ÷ (old qty + new qty).
• If it's an issue (Qty Out > 0): average cost stays the same — only the quantity decreases.

Running Qty:

=G2 + C3 - D3

Running Value:

=IF(C3>0, H2 + C3*E3, H2 - D3*F3)

Fill these formulas down for every transaction row. Your closing stock value is always the last row's Running Value.

FIFO vs weighted average — which should I choose?

For most Indian SMBs in general trading or manufacturing, weighted average is the practical choice. Use FIFO if you deal in perishables or pharma, or if your auditor or lender requires it.

Free FIFO & weighted-average template

The free Excel template includes:

• FIFO layers sheet with formulas for issues and closing stock value.
• Weighted-average transactions sheet with running cost and value.
• Summary dashboard showing closing stock qty and value under both methods.
• Sample data pre-filled for three SKUs.

Free to download, no sign-up.

Download the FIFO & Weighted-Average Excel Template »

How Ankeshan helps: Ankeshan maintains a weighted-average cost ledger inside Excel for every SKU automatically, updating the cost after each GRN entry so you always have a current closing stock value without manual recalculation. It's launching soon; join the waitlist.

Frequently asked questions

Can I switch from weighted average to FIFO mid-year? Under AS-2, a change in cost formula is a change in accounting policy and must be disclosed in the financial statements with the effect on profit. Avoid switching mid-year; make changes at the start of a new financial year with proper disclosure.

Is FIFO required by Indian tax law? No. Indian tax law (Section 145A read with ICDS II) and AS-2 permit both FIFO and weighted average — only LIFO is disallowed. Section 145A requires inventory to be valued at the lower of cost or net realisable value (NRV), with cost computed under a permitted formula. For pharma, FMCG and food, physical FIFO rotation is a regulatory and practical requirement, so FIFO is strongly preferred there — but no statute mandates the FIFO cost formula specifically.

How do I handle purchase returns in the weighted-average model? Treat a purchase return as a negative Qty In at the original purchase cost. Recalculate the weighted average after the return using the same formula.

Does GST change the cost I use for inventory valuation? For GST-registered businesses, purchases eligible for Input Tax Credit (ITC) are valued excluding GST (since you recover the tax). Purchases where ITC is blocked (Section 17(5)) or you are unregistered should include the GST as part of cost. Always check ITC eligibility before entering the unit cost.

What if two batches arrive on the same day at different prices? In FIFO, record them as two separate layers with the same date. In weighted average, process each receipt in sequence and recalculate average after each.

Sources

• AS-2 Inventories — Institute of Chartered Accountants of India (icai.org).
• CBIC GST rate notifications effective 22 September 2025 (56th GST Council).
• Income Tax Act 1961, Section 145A — Method of accounting for purposes of tax.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Inventory Management pillar » · Stock Register / Inventory Template » · Stock Register with GST in Excel » · Batch & Expiry Tracking in Excel »