FIFO calculation

    Hi all


    I am a newbie here so please bear with me.


    I have a simple calc that I would like to do.


    I have a purchase history of various stock. It is simply listed as a purchase date and amount and shown as positive. The next cell down is the same concept. This continues until there is a sale which shows as a negative.


    Does anybody know of a way that excel can scroll up and down the spreadsheet and work out which original batch the purchase inventory was from ? This is an example of my list.


    transaction_datetime effective_datetime amount units
    16/02/05 12:22 11/02/05 0:00 48106.41 45452.41
    09/02/05 15:10 04/02/05 0:00 7176.44 6774.70
    02/02/05 8:10 28/01/05 0:00 9123.26 8670.65
    25/01/05 17:13 21/01/05 0:00 9627.53 9164.71
    24/01/05 16:17 24/01/05 0:00 124.16 118.09
    24/01/05 16:10 21/01/05 0:00 8857.29 8409.09
    21/01/05 15:31 17/01/05 0:00 2604.13 2466.03
    12/01/05 16:08 07/01/05 0:00 28295.23 26696.13
    05/01/05 16:48 31/12/04 0:00 54609.33 51605.87
    30/12/04 14:52 24/12/04 0:00 -2143.32 -2026.01
    20/12/04 14:28 16/12/04 0:00 110.21 104.57
    15/12/04 13:10 10/12/04 0:00 -8803.81 -8390.97



    For example if we bought 2000 units, then 4000 the next day and sold 3000 on the third day.


    How could excel work out that the sold units consisted of 2000 from the original batch and then another 1000 from the second batch. It also means that there is 3000 units from the second batch left on hand.


    Sorry it is long winded

    Re: FIFO calculation


    Hi Alan


    Each transaction has a unique transaction id. To keep it simple we log the transaction on the 15/12/04 as transaction id 1 and so on.


    If possible I need the report to identify each original purchase that has been completely redeemed and the amount of a residual. For example if we had a purchase of 5000, 6000 and 3000 and then we redeemed 12759 units I would like it to show.



    5000 (Units redeemed)
    6000 (Units redeemed)
    1759 (Units redeemed)
    1241 (Units Remaining)


    This would help greatly in determining original unit cost.


    Then for the next redemption I would like it to consider that there is 1241 Units remaining and move on and do the next calc.


    If this is not clear I can provide a spreadsheet which has my core data.


    Cheers
    Peter

    Re: FIFO calculation


    OK.


    You have a single list that could be broken into two lists.
    1. A list of transactions that are purchases
    2. A list of redemptions


    The difference should be your current holdings.


    How many records are there in each of these two lists?


    A sample of your core data would help.


    I think the solution is:
    Use advanced filter to create the two lists from the one list
    Go down through the list of redemptions and write out results to another sheet. For each redemption note:
    Start a the begining of the purchase list.
    If the purchase still has units not allocated then allocate as reqired and record on teh results sheet.
    If there are no units in the purchase unallocated then continue down the list.


    Should not be to hard.


    Is it possible that you will have redeeemed more than you purchased? Therefore redeeming before purchasing?


    Alan.

    Re: FIFO calculation


    Hi Alan


    I have attached an example sheet of the data that I need a FIFO calc for.


    All the redemptions are highlighted in red. If you look at the first redemption (i46) of 37352.22 units it will be coming out of the units purchased in cells i51 and then i50. I need the calc to show how many of the units in i50 remain from that purchase.


    Then the spreadsheet needs to look at the next redemption (cell i38) and work out how much of i50 or the next purchase lot is consumed.


    I really hope this is making sense and I hope I am not confusing you more.

    Re: FIFO calculation


    Peter,


    The attached file is a start. I have not gone through a detailed check but it appears to work!


    I have edited a few of the transactions to make them redemptions rather than purchases just for testing purposes.


    I have not checked that redemption and purchase dates are acceptable. For example I have not checked that the redemption date is after the purchase.


    Have a look and let me know if you would like any more work done and I will try to get the time.


    HTH,


    Alan.

    Re: FIFO calculation


    Hi Alan


    Thanks for your hard work on this one.


    I just cant seem to get the macro to run. I have read the VB behind it and can't debug it.


    I assume it was running perfectly well when you sent it so I am struggling to work out the issue.


    If you have the time perhaps you could look over it again.


    That would be much appreciated. Oh and thanks again for working on this one


    Peter

    Re: FIFO calculation


    The issue it seems is to do with the xlsortnormal in the code. I am running excel 2000 and it doesn't know how to read this.


    I am assuming you are running 2002.


    Umm (head scratch) not sure what to do now.....


    Peter

    Re: FIFO calculation


    Hi Alan


    I have the macro working now. It still doesnt seem to return the exact data that I would like. I have run the data for a different asset group just as a test.


    I have attached the file with another tab called "Results that should be shown". The macro as it stands is not calculating the items in red being the residual amount of units from that batch that are redeemed.


    If you note, column N now adds to column I.


    It could be asking a lot and I know I have bombarded you with e-mails but is it possible for the macro to be amended to do this ?


    Regards
    Peter

    Re: FIFO calculation


    Peter,


    The problem is that I have solved the same problem twice in the macro! The easiest solution is to comment out the penultimate line of the code.


    Code
    'If bolPurchaseAdded Then lngResultsLine = lngResultsLine - 1


    What was happening is that the first line of the 2nd red note was writing over the top of the last line of the 1st and so on, so the last red note was OK.


    HTH,


    Alan.
    PS
    Unless you are working very late you will have this for your morning. Hope that it is as beutiful as this morning here!
    A.

    Re: FIFO calculation


    Hi Alan


    That has worked a treat !


    Happy Days.


    BTW I am not working back here late at night. I am actually located in Sydney and we are averaging 30 degree days at the moment. Now that I have this sorted I can head off to the beach after work !


    Thanks again for all your effort on this one.


    Peter

    Re: FIFO calculation


    I realised you were in Sydney :)


    It's 30 degrees here to, just in Fahrenheit not celcius!


    I dream of setting of to the beach after work. I love to surf and spent many years designing beaches for amenity and coastal protection. Unfortunately the work does not pay that well!


    Glad you have what you need.


    Cheers,


    Alan.

    Re: FIFO calculation


    Hi Alan,
    Your solution is excellent.


    1) However I have a situation where I need to get the profit /Loss calcualtions for stocks of various companies and so the macro should be able to do the calculation company by company.


    2) also I need to add Rate and vlaue for the purchases as well and calculate the capital gain /loss against each transaction.
    Can u please suggest a modified solution for that
    Sample excel is given.

    Re: FIFO calculation


    Peter,


    I have just seen your new post, not sure when youposted it.


    Your attached file is quite different from the one that we used before. The FIFO Calc does not mention the company name, just works from the transaction IDs. I presumed that all of the transactions on a sheet were for the same company.


    If you actually have a list that includes the companies and the transaction IDs that is first filtered to produce a list of transactions by company and then passed through the FIFO Calc then it is easy to add this in. However I do not see the link at the moment.


    Also I think you should consider how you do the calculation and whether there is 3rd party software that may be better and provide more of the analysis that you require. I am not in the stock industry so do not know the software available.


    HTH,


    Alan.

    Re: FIFO calculation


    Alan,
    Let me first apolozgize for not being clear. Let me introduce myself. I am Ramesh from India and I saw your solution to Peter and was interested in using it as I am currently doing my personal capital gain tax calculation manually on FIFO basis.


    I find that most of the data in Peter's file is relevant to me also and hence I had a feeling that your macro can be used by me with modifications.


    I have attached a new file which gives a very small sample of data and the expected result. On the sheet "Desired Results" I have given some comments to make things clear to you



    To answer the points raised in your mail to me
    1) You said: Your attached file is quite different from the one that we used before. The FIFO Calc does not mention the company name, just works from the transaction IDs. I presumed that all of the transactions on a sheet were for the same company.


    My Response: This is not Peter's file. A new file is attached to make things clear to you.


    You said: If you actually have a list that includes the companies and the transaction IDs that is first filtered to produce a list of transactions by company and then passed through the FIFO Calc then it is easy to add this in. However I do not see the link at the moment.


    My Response: I want the results of porchases and redemptions one company (scrip) after another as shown in the attached. There is a transaction id for each transaction, similar to Peter's file.


    You said: Also I think you should consider how you do the calculation and whether there is 3rd party software that may be better and provide more of the analysis that you require. I am not in the stock industry so do not know the software available.


    MY Response: I would prefer a macro which will do the hob similra to Peter's case


    I.



    Notes for Alan (A9192 Shark)
    A. Scenarios from sample spreadsheet
    1) In case 1, (Biocon), Out of 20 all 20 allocated
    2) In case 2, ( Jaypee Hotels) Total bought was 50 and out of this only 40 were sold. There is 10 units remaining in your problem parlance which is left in stock.
    3) In case 3(Matrixlabs), 100 were bought at price x and 50 at price y and 150 were sold at price z. This gives two buys (purchases) and one sell (redemption).
    4) In case 4 (NDTV) out of 50 only 20 were sold and 30 are in stock


    B. I donot need the transaction id in the expected result list but if you can't avoid bringing it it is Ok.


    C. some fields such as "The brokerage rates" and "brokerages" and gains calculation can be manually inserted by me after running your macro if it is too much trouble for your to modify the macro to bring this data.


    D. Equivqlent fields compared to Peter's file
    Peter's File - My File
    asset_Transaction ID - Transaction Id
    associated_transaction_id- Not Reqd
    asset_id - Scrip (or Company)
    status - Not Reqd
    transaction_type_id - Not Reqd
    transaction_datetime - Date
    effective_datetime - Not Reqd

    Re: FIFO calculation


    Alan,
    This is Ramesh here. Thanks for the help in creeting the macros for the stock capital gain spreadsheet, which almost meets my requirements. I still need some more things to make the spreadsheet as automated as possible.


    Here are few things I tried doing-With my limited poor knowledge of VBA, I added a few more columns in the Purchase side of the results, in line with my desired results spreadsheet sent earlier.


    1) The Sell rows in the results to be broken up by qty bought (by scrip) so that I can apply Loss gain calculation across the last column. Is it asking too much (Please see desired results in new spreadsheet attached,
    This is explained in follg example


    Matrix labs bought 100,50,25


    When selling 175, I sold 100 from 100 lot, 50 from 50 lot and 15 from 25 lot and have 10 remaining. Can this be broken up that way.
    Next sell of Matrix labs will start with remaining lot of 10 for allocation.


    2) Can we get the formulae in the Core Data sheet intact in the results sheet? I am not sure
    3) Also Date column in the Purchases does not retain its format. Any clues why?

    Re: FIFO calculation


    Hi Ramesh,
    Did you manage to find the solution to your issue? I am also looking for a solution for a similar requirement.


    Thanks,
    Pradeep

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login