Average price problem with multiple purchases and sales

  • Hello,


    I have recently started trading which is irrelevant but i have created an excel spreadsheet to track my portfolio and that is very relevant to the problem.


    The problem is as follows:

    for simplicity let's say i bought 100 of 'A' at a total price of 1,000 and an average price of 10.


    Then, i sell all 100 of A (the price at which i am selling does not matter because i am only trying to establish the average purchase price)


    Then, i buy another 100 of A at a total price of 500 and an average cost of 5.


    I calculate my average price by dividing total cost by total amount

    The issue i have is that my spreadsheet returns an average price of 7.5 not 5.


    The reason for this is because it does not recognise that i have sold out of my position so it gives me the total average price of all purchases no matter how many times i have sold. For instance, after the above example i would be left with 100 of A. But, my spreadsheet thinks i've got 200 because it doesn't know i have sold my original 100.


    This problem is the final error i need to deal with in my spreadsheet (i hope). All other problems i have been able to resolve and i really don't want to have to buy a portfolio tracker when i have got this far in making my own!

    I have attached a document that gives an example of how my 'trades' tab works on my spreadsheet.


    Any advice would be much appreciated.

    Honestly, at this point i am desperate for a solution. I have combed through youtube and google but cant seem to phrase my problem in a way that gets any results.

    If i haven't explained my problem well enough i am more than happy to try explaining again.

    This is my first time using this sort of forum for a solution so i apologies in advance if the way i have constructed this issue is problematic.

    Files

    • Problem.xlsx

      (11.04 kB, downloaded 37 times, last: )
  • Hi Roy,


    Thank you so much for your response. Unfortunately that has not solved the issue so i will try give a better explanation.

    In the document you provided it does give the average price as 5, but this coded to take sell price into account.


    This is problematic because if you change the sell price (say, to 15) in then returns an average price of 0 for the 100 'A' (continuing with the example above)

    In your code it gives the correct average purchase price because the selling price is the same as the original buying price.

    In case i haven't explained that correctly, on the document you so kindly offered if you were to change the sell order to an average price of 20 it would then give a negative valuation of the stock - for instance saying i have 100 A but at a cost of -500 (this represents profit but not the average purchase price).


    This is one of the early problems i had with my spreadsheet and i resolved it by separating purchases and sales of stocks (as can be seen in the document i uploaded with my original question).


    By separating purchases and sales it allows me to calculate the average purchase price by only taking into account the amount purchased and total cost.

    Example:

    Purchase 100 A, total cost 500 (average price 5)

    Purchase 200 A, total cost 700 (average price 3.5)

    Average price of all 'A' = 4


    The price I sell at isn't taken into consideration when calculating the average purchase price because it is a sell order and not a buy order. I only want to know the average price i have bought at.


    However, lets say i then sell all of my A - so selling 300 A (and while its not important, for explanatory purpose lets say i sell for a total of 1500 at an average price of 5)


    Then, i am left with 0 A, obviously. (and some profit but that is not relevant to the problem or the solution)


    But, if i am to then buy back into A, lets say I buy 200 A at a total cost of 2000 (average price 10)

    The true average price of the amount of A that i own in this example would be 10, because i sold all of the A i originally had and bought back in


    My spreadsheet would return the average price of 6.4, because it does not recognise that i have sold out of my position and so, takes the total amount of A that i have purchased and the total cost of those purchases into consideration.


    The solution I am so desperately after is one that allows me to keep a record of my transactions but also allows me to have an accurate average price of the stocks i own. In other words, i don't want to have to delete my purchase history after i have sold out of that position because it performs the function of a transaction log, which is helpful to have.


    My understanding of a solution would be one that takes the total amount of A that i have sold ( in this example 300) and then subtracts that from the total purchased (which would give the accurate amount owned, in this example total amount purchased 500 - total amount sold 300 = 200)

    However, in terms of costs, the solution needs to reduce the total cost by the cost originally purchased at not the amount sold at.


    Continuing the example: (1,2, 3 and 4 represent the order i made transactions at: so two purchases followed by a sell order and then another buy order).


    (1) Purchase 100 A, total cost 500 (average price 5) (3) Sell 300 A, total return 1500 (av. 5)

    (2) Purchase 200 A, total cost 700 (average price 3.5)

    (4) Purchase 200 A, total cost 2000 (av. 10)


    My spreadsheet calculates the average price by taking all purchases and total cost into consideration: result being 6.4


    However, in this example, the correct answer would be 10 because of the 200 A that i am left with at the end of this example the average cost i paid for them was 10, and not 6.4 which would be the average of all purchases.


    I have added an edited version of the original document, updated to fit the example in this response.


    I am sorry for how long this response has been but i feel it was necessary in order to properly explain where i am at and the problem i am facing.

    Thank you for taking the time to help me.

    Files