I have a widget that I buy in variable-sized lots at various prices, and sell in variable-sized lots of various prices. I know how to compute the overall costs, receipts, and profit/loss. But I need to know (for tax purposes ☹️) the gain/loss of each sale, computed by the FIFO inventory method. How do you do this in Excel?
Here’s an example where I put in the “FIFO Gain/Loss” column by hand (with an “Explanation” column). It shows the idea, but doesn’t exhibit all the edge cases. (It’s an image: I didn’t think an uploaded spreadsheet would be allowed.)
I’m looking for a relatively straightforward solution – e.g., columns that I could add and formulas that I could fill the columns with. Or an add-in that I could use.
- I tried myself but the “recursive” nature of computing the remaining units of each purchase at the time of each sale defeated me
- I looked around the web, couldn’t really find anything. Except this question on this site which uses
LETand (nested)LAMBDAandSCANand so on and is way beyond my Excel capabilities to understand much less modify to my particular case. - I didn’t invent this FIFO inventory rule. It’s standard in accounting. So why doesn’t Excel support it natively? 🤔 (or does it?)
Please don’t do anything heroic (like in those answers to the linked question) unless this problem really strikes you and you’re going to have fun. If, as I suspect, there is no reasonably straightforward way to do this in Excel just say so, and then I’ll export everything as CSVs and do it in Python.
