Calculate open stock position book value using LIFO

  • Hello everyone,


    I have a personal investment tracker, where I am recording all my stock transactions. For my open positions I want to calculate the book value and the average share price purchased of my open positions using the LIFO method.

    My current implementation of calculating book value is incorrect, as it simply calculates the weighted average of all previous transactions, including the ones that have been closed. Here's an example of how I want things to be calculated in the statistics tab:

    The current book value of QQQ should be: 3 * 181.55 (remaining stocks from Sep 10 transaction) + 10 * 231.7899 (Remaining stocks from Feb 13 transaction) = 2862.55

    The average share price for QQQ should be 2862.55 / 13 = 220.196

    I want the statistics tab to update automatically if I add more entries to the transactions tab. I think it would make much more sense if you take a look at the attached spreadsheet.

    Any help would be greatly appreciated.

    Thanks,

    Max

  • The specific identification method is simple. Just calculate COGS for each sale based on the cost of the inventory sold in each transaction (e.g. sale on march 17th comes from inventory purchased on march 9th, so use the cost per unit on that purchase to calculate COGS on the sale). Add up COGS for each sale and then multiply remaining inventory batches by their respective purchase cost to get ending inventory. video downloader , stream videos