Native FIFO Inventory Costing Function for Excel (Proof of Concept with LAMBDA)

Excel currently lacks a native function to calculate FIFO (First-In, First-Out) inventory costs when products are purchased at different unit prices and later issued in partial quantities.

FIFO costing is a standard accounting requirement under IFRS and is widely used in inventory management, retail, manufacturing, and financial reporting. Today, Excel users must rely on VBA, Power Query, or complex multi-step formulas, which are error-prone and difficult to maintain.

As a proof of concept, I created a native Excel implementation using modern functions such as LAMBDA, LET, SCAN, MAP, and dynamic arrays. The function calculates the FIFO unit cost of the latest outgoing inventory movement, given:

an array of incoming quantities
• an array of unit costs for each incoming batch
• an array of outgoing quantities

While this demonstrates that FIFO costing is feasible using native Excel functionality, the level of complexity required highlights a clear feature gap. A built-in FIFO inventory costing function would significantly improve usability, readability, performance, and accessibility for non-technical users.

I believe a native function such as FIFO.COST() or INVENTORY.FIFO() would be a valuable addition to Excel, alongside potential support for LIFO and weighted average costing methods.

I’m sharing this example to illustrate both the feasibility and the need for native inventory costing functions in Excel.

=LAMBDA(
    InQty,
    UnitCost,
    OutQty,
    LET(
        PrevOutQty,
            TAKE(OutQty, ROWS(OutQty)-1),

        LastOutQty,
            INDEX(OutQty, SEQUENCE(1,1,ROWS(OutQty))),

        OutIndex,
            ROWS(OutQty),

        RemainingFromPrevious,
            LET(
                InMatrix, InQty,
                OutMatrix, PrevOutQty,

                ApplyFIFO,
                    LAMBDA(InMatrix, OutMatrix,
                        LET(
                            n, ROWS(OutMatrix),

                            RecursiveFIFO,
                                LAMBDA(self, Remaining, i,
                                    IF(
                                        i > n,
                                        Remaining,
                                        self(
                                            self,
                                            LET(
                                                OutAmount, INDEX(OutMatrix, i),
                                                CurrentStock, Remaining,

                                                Consumed,
                                                    VSTACK(
                                                        OutAmount,
                                                        TAKE(
                                                            SCAN(
                                                                OutAmount,
                                                                CurrentStock,
                                                                LAMBDA(acc, qty,
                                                                    IF(qty > acc, 0, acc - qty)
                                                                )
                                                            ),
                                                            ROWS(CurrentStock)-1
                                                        )
                                                    ),

                                                IF(CurrentStock > Consumed, CurrentStock - Consumed, 0)
                                            ),
                                            i + 1
                                        )
                                    )
                                ),

                            RecursiveFIFO(RecursiveFIFO, InMatrix, 1)
                        )
                    ),

                ApplyFIFO(InMatrix, OutMatrix)
            ),

        FirstRemaining,
            SCAN(
                LastOutQty,
                IF(OutIndex = 1, InQty, RemainingFromPrevious),
                LAMBDA(stock, qty,
                    IF(qty >= stock, 0, stock - qty)
                )
            ),

        SecondRemaining,
            VSTACK(
                LastOutQty,
                TAKE(FirstRemaining, ROWS(FirstRemaining)-1)
            ),

        FIFOQuantities,
            MAP(
                IF(OutIndex = 1, InQty, RemainingFromPrevious),
                SecondRemaining,
                LAMBDA(stock, qty, MIN(stock, qty))
            ),

        FIFOUnitCost,
            SUMPRODUCT(FIFOQuantities, UnitCost) / LastOutQty,

        IF(ROWS(InQty) = 1, UnitCost, FIFOUnitCost)
    )
)

Have a nice day.
Juan Miguel Arraztoa