Forum Discussion
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
4 Replies
I suspect that most users' understanding of the options is still constrained by the limitations of the conventional spreadsheet. Your work is interesting. You appear to have used recursion, whereas I used SCAN working over a stacked and sorted array of inputs and outputs.
The other difference is that you appear to have experience of FIFO and LIFO calculations whereas I had ideas for the solution but no experience of the problem! I provided an option for selecting the form of presentation by allowing the user to switch between crosstab and list formats but with no specific end use in mind. 😒
Given a function that performs the FIFO calculation, it should be possible to go further, say by using the function within GROUPBY to build an array of functions that would calculate the results for multiple products or distinct geographical regions. Either the entire set of results could be output as an array of arrays, or slicers could be used to down-select.
My feeling is that, this far, I have only scratched the surface of what is possible using modern excel.
Hi, Juan Miguel Arraztoa,
Can you share with us an excel file where your formula is applied? This way it would be easier to understand the formula and how it works.
Thnx.
What you've created isn't just a clever formula – it's compelling proof that inventory cost accounting has surpassed Excel's current functions.
Thank you for sharing it with us.
The LAMBDA formula itself is fully self-contained and demonstrates that FIFO costing can be implemented with native Excel functions, even if it requires multiple steps and careful state management. Seeing the formula in action, whether in a file or on paper, helps illustrate the complexity involved in replicating standard accounting logic in Excel.
On the comparison between recursion and a SCAN-based approach: both methods are valid and highlight different ways modern Excel can handle dynamic calculations. Using SCAN over a sorted and stacked array can make the logic more transparent and easier to follow in a spreadsheet, while recursion can closely mirror how FIFO engines work in ERP systems by explicitly tracking remaining inventory. Both approaches offer valuable insights into what is possible with Excel today😲.
I also appreciate the idea of extending this further — for example, embedding FIFO logic in GROUPBY to handle multiple products or regions. It shows the potential of modern Excel functions beyond single-item calculations. Overall, these examples reinforce the same point: Excel is flexible and powerful, but a native FIFO / LIFO / weighted-average inventory function would make these calculations simpler, more accessible, and easier to audit for a wider range of users.
Overall, I think the different approaches discussed here — recursive formulas, SCAN-based solutions, and potential extensions for multiple products or regions — reinforce the same point: Excel is powerful, but a native FIFO / LIFO / weighted-average inventory function would make these calculations more accessible, auditable, and easier to maintain for a wide range of users.
