5 Replies Latest reply on Dec 2, 2008 2:03 PM by TSGal

    Need Calculations for Investment Portfolio



      Need Calculations for Investment Portfolio


      Kindly request help in formulating calculations for investment portfolio.  I have two tables:  EQUITIES and TRANSACTIONS.  

      The EQUITIES table is a database of all equities including the following fields:  _UniqueID (auto enter ser.#); Equity (text); Quantity, sum(TRANSACTIONS::Units traded); Market Pice (number); Market Value (Quantity x Market Price); Total Cost Basis, Sum(TRANSACTIONS::Cost Basis); Unrealized Gain (Loss), (Market Value - Total Cost Basis); and Holding Days.  

      The TRANSACTIONS table is a database of all transactions including the following fields:  _UniqueID (auto enter ser. #); _EquityID(auto enter ser. #); Equity (text); Units Traded (number); Pur. Price (number); Commission (number); Trade Date; Cost Basis (Units Traded x Price) + Commission; and Cost Per Share (Cost Basis/Units Purchased).

      Kindly request calculation and instructions in setting up a table that records sales.  For example, if you only sell a part of an equity, IRS rules suggest three ways to account for capital gains/losses:  First in first out (FIFO) of all Units Trades; Last in first out (LIFO); or average cost of all Units Traded.  

      There are three issues in developing these calculations:  First issue concern the length of time for holding the security.  This is represented in the last field of the EQUITIES table.  What is the calculation to determine holding days (TRANSACTIONS::Trade Date to present) to determine whether this is a short-term or long-term held asset having tax implications, and how does one set up the conditional formatting if the period < 365 is short-term, and period => 365 is long-term asset;  Second issue is to allow trader the opportunity to determine the cost basis of the sell transaction:  FIFO; LIFO; or average cost of Units Traded.  Kindly help me formulate these calculations.  Also, is an EQUITIES SOLD Table needed and how would it be set up? or can one use the TRANSACTIONS Table for sales as well as purchases?



        • 1. Re: Need Calculations for Investment Portfolio



          Thank you for your post.


          The Get (CurrentDate) function returns today's date.  You can use with the Trade Date field to get the number of days.  For example,


          If (Get (CurrentDate) - Trade Date < 365, "Short Term"; "Long Term" )


          That is, if the days between today's date and Trade Date is less than 365 days, then return "Short Term".  Otherwise, return "Long Term".


          To determine the average cost of Units Traded, you would use the Average () function across the found set of assets.  FIFO would be a bit different, as you would have to sort the portal by date, and then use the cost basis per share for the first "x" shares, and then the cost basis for the next "y" shares.  It can get complicated if you have "x+y+z+z1+z2..." shares.  This will take some time.  The LIFO would be similar, except you would sort descending by date.


          You could set up an EQUITIES SOLD table, but since it is a transaction, you would probably need to add a couple of fields to your TRANSACTIONS table.  That being:


          Type (Text ) with the values "BUY" and "SOLD"

          PriceValue (Calculation: Number) = If (Type = "Sold"; Transaction Amount; -1 * Transaction Amount)


          This way, you can see how much is paid, and how much is returned.  You can then use a Summary field to add up these amounts to get the net profit (or loss).


          Let me know if you need clarification for any of the above step.s



          FileMaker, Inc.

          • 2. Re: Need Calculations for Investment Portfolio

            Thanks TSGal for responding so quickly and providing me the calculations I needed.  Although a novice with Filemaker Pro, I understand your answers with most of the calculations with the exceptions of understanding your response for a calculation to FIFO and LIFO in your fourth paragraph.  Could you explain in more novice terms and provide the appropriate calculations.


            Also, do not understand the calculation of PriveValue (calculation: Number) = If (Type = "sold"; Transaction Amount; -1 * Transaction Amount).  Could you explain each of these elements in the calculation you propose.




            • 3. Re: Need Calculations for Investment Portfolio



              Sorry for the late reply.


              FIFO and LIFO are "First In First Out" and "Last In First Out", respectively.  That is, if you want the oldest transactions listed first, you want to sort the records by Date.  That will list the oldest transactions first.  For LIFO, you sort descending by date, leaving the most recent transactions listed first.


              In order to add up your net costs, you want to add up the Transaction Amount field for the stocks you purchased, and subtract that from the Transaction Amount when sold.  This gives you the net amount.  Therefore, PriceValue is created to give a positive Transaction Amount to the "sold" transactions, and a negative Transaction Amount to the "purchase" transactions.  Summarize this field, and you get the "Unrealized Gain (Loss)".


              Back to FIFO and LIFO, you would need a script to determine how many transactions are needed to offset the sale.  For example, if you purchased 100 shares of "ABC" in 2005, 100 shares of "ABC" in 2006, and 100 shares of "ABC" in 2007, that would give you a total of 300 shares.  If you sold 250 shares, how do you want this reported?  You would need to evaluate all three "Purchase" Transactions, and although the first two are long-term gains, you would have to determine if the last 50 shares are short-term.  This can be done by a script.  Since you call yourself a "novice", I'm a little nervous writing out a script that performs this.  You may want to instead create a table that takes each of the stocks and summarizes the shares into long-term and short-term values.  Then again, I'm throwing you another curve ball.  Let me know if you want help and how you want to proceed.



              FileMaker, Inc.

              • 4. Re: Need Calculations for Investment Portfolio

                TSGal:  Thanks, some of the transactions for many securities exceed 100.  Therefore, a script would seem to me to be the most efficient.  I understand the purpose of scripts and have used some in simple databases, but formulating them for complicated calculations is beyond my comprehension at this point and I was hoping that a brief explanation of each of the elements could be explained.  As I pointed out, I wanted the options for selecting FIFO, LIFO, and average price so three scripts would be required.  


                On a simpler basis, you are correct when I could just subtract short term sales from net proceeds to obtain both "long-term" as well as "short-term" net proceeds, but the scripts process is more accurate and performs the calculations the way a database should be run, one reason I'm attempting to get away from using an excel spreadsheet.  Specifically, the scripts process is what I am hoping to understand and learn. I have read a number of books about scripts but still lack the understanding on how to formulate them when involving calculations like these.


                Your assistance is greatly appreciated.



                • 5. Re: Need Calculations for Investment Portfolio



                  No problem with the explanation.  I just didn't want to overwhelm you with too much information.


                  Pull down the Scripts menu and select "ScriptMaker".  This is where scripts are created.  Click the bottom left icon (New), and you will be prompted to enter the name of the script.  At this point, leave it as the default "New Script".


                  On the left side of the screen are the actions that can be performed.  I won't go through all of them individually, but you should be able to figure most of them out on your own.


                  Let's use the example that we had from above.  That is, 250 shares of ABC sold.  We are going to need to track a number of things, so we will use a few variables:



                  (used for the name of the company)



                  (used for shares leftover after current record)



                  (used to compare to earlier records for long term and short term)



                  (amount applied to long term)



                  (amount applied to short term)


                  A sample script may look like the following:



                  Set Variable [$stockname; <Stock Name field> ]

                  Set Variable [$available; <Amount sold field> ] 

                  Set Variable [$datesold; <Date sold field> ] 

                  Set Variable [$longterm; 0]

                  Set Variable [$shortterm; 0] 

                  Go to Layout ["Transaction" ]

                  Enter Find Mode []

                  Set Field [<Stock Name field>; $stockname]

                  Set Field [Status; "Buy" ]

                  Perform Find [] 


                  (That is, we take the Stock Name field and put it into the variable $stockname, amount of shares sold into $available, and the date of selling into $datesold, and initialize $longterm and $shortterm to zero.  We then switch to the Transaction layout and enter Find mode. We are looking for all records of that Stock name and where the status was set to "Buy" )


                  If [Kind = "LIFO"]

                     Sort Records [No dialog; Restore - Date Ascending ]


                     Sort Records [No dialog; Restore - Date Descending ]

                  End If 


                  (depending if this is FIFO or LIFO, the records are sorted accordingly)


                  Go to Record/Request/Page [First]


                  (go to the first record)



                     If [$available <= NumStock ]

                        Set Variable [$temp; $available]

                        Set Variable [$available; 0 ]


                        Set Variable [$temp; NumStock ]

                        Set Variable [$available; $available - NumStock ]

                     End If

                     If [$datesold - Date acquired > 365]

                        Set Variable [$longterm = $longterm + $temp]


                        Set Variable [$shortterm = $longterm + $temp]

                     End If

                     Exit Loop If [$available = 0]

                     Go to Record/Request/Page [Next]

                  End Loop



                  This loop checks to see if the amount in stock is greater than what is available.  If so, we put the amount from $available into $temp and set $available to 0.  Otherwise, we put the amount from NumStock into $temp and reduce $available by the amount in NumStock.


                  Then, we check to see if the date acquired subtracted from the datesold is more than 365 days.  If so, we put the number of shares in $longterm.  Otherwise, put it in $shorterm.


                  We then see if $available is zero.  If so, we exit the loop.  Otherwise, we go to the next record in sequence (whether ascending or descending) and repeat the loop until $available is zero.


                  At this point, we can display the number of shares long term and short term.  I'll leave you with what you want to do at this point.


                  I hope this gives you a good starting point.  Let me know if you have any questions or need clarification.



                  FileMaker, Inc.