You could use the DATEDIF function (see e.g. http://www.cpearson.com/excel/datedif.aspx). Let's assume that your purchase date is in cell A2 and you sell date in cell B2. The formula =DATEDIF(A2,B2,"d") returns the number of days between the two dates.
One way to use this function for your scenario could be to enter the formula for each of the three transaction types into additional cells on the same row (including the tax considerations), e.g.: - cell H2: long-term gain - cell I2: short-term gain - cell K2: speculative gain You could now enter the following formula in cell L2: =IF(DATEDIF(A2,B2,"d")=0,K2,IF(DATEDIF(A2,B2,"d")<365,I2,H2)) If this does what you were hoping for, you could now copy cells H2, I2, K2 and L2 down to the other rows in your table and hopefully be done. Hope this helped,Rolf On Oct 8, 1:31 am, chinmay kamat <kamatchin...@gmail.com> wrote: > Hello friends, > > I'm interested in using excel to do calculation for gains/losses in > shares. > > As per law, there are 3 kinds of transactions which can be entered > into in shares. The types are determined on the basis of the period of > holding of each share by using FIFO method. FIFO method assumes that > out of several shares purchased of multiple dates, the earliest shares > are sold first. Thus for 10 shares sold on a day at the same price, > the gains might be completely different as 5 shares might have been > purchased first at some price and the rest might have been purchased > on a later date. thus the date of purchase decides the category of > transaction and the price decides the gain/loss. > > If the period of holding is 365 days or more, it is long term. if it > is more that 1 day but less than 365 days it is short term. and if it > is sold on d same day than the transaction is teated as a speculative > transaction. > > The tax treatment for each type is different. > > I have data in the following format for purchase and sale separately. > > Date, quantity and total amount > > Is there any way that the FIFO algorithm can be implemented in excel?? > > Thanks in advance --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
