Calculating Stock Profit/Loss in Excel

I'm trying to create simple spreadsheet to calculate profit/loss after every complete transaction I perform on the stock market. This way I'll be able to track my portfolio performance at any moment and will have historical values saved for analytic purposes.

In the table below, columns A through C, and cell D2, are inputs; I want a formula to compute column D ("assets total") from D3 on down. The value should stay the same when action (column B) is "buy". If action is "sell", the value should update to reflect profit/loss.

Can anyone help?

 A B C D E F G  transaction initial 1 stock action value assets total investment 2 apple buy 1000 512 512 3 apple sell 1001 513 4 google buy 7000 513 5 google sell 7004 517 6 twitter buy 20016 517 7 netflix buy 14000 517 8 twitter sell 20000 501 9 sony buy 19000 501
10 sony sell 19256 757
11 netflix sell 14064 821

Explanation/rationale: each stock has a nominal value (V) of A×1000, where A is the numeric value of the first letter in the stock’s name (apple=1000, google=7000, twitter=20000, netflix=14000, and sony=19000).  The transaction (buy and sell) values are all V and V+4ⁿ,not necessarily in that order, where n is a one-up number (apple=1, google=2, twitter=3, netflix=4, and sony=5).  This way, transactions are transparent and decodable, rather than mysterious.  For example, the increase from 512 to 513 (between rows 2 and 3) can only be the profit made by selling apple for 1001 after buying it for 1000.  The decrease from 517 to 501 (between rows 7 and 8) can only be the loss from selling twitter for 20000 after buying it for 20016.

821 = 512+1+4-16+64+256

1 Answer

I presume that you’ve already set D2 to =G2.  Set D3 to

=IF(B3="buy", D2, D2+C3-INDEX($A$1:$C$99, MAX(ROW(A$2:A2)*(A$2:A2=A3)), 3))

replacing the 99 with an upper bound on your last row number.  Type Ctrl+Shift+Enterto make it an array formula.  Drag/fill down.

From the inside out:

• ROW(A2)*(A2=A3) is a tricky way of saying IF(A2=A3, ROW(A2), 0), because TRUE is 1 and FALSE is 0.
• ROW(A$2:A2)*(A$2:A2=A3) is a virtual array, running from the first row of data ($2) to the row above the current one (2).  As indicated above, the value is the row number, if the stock (An) on that line is equal to the stock on the current row (A3), and 0 otherwise.
• MAX(ROW(A$2:A2)*(A$2:A2=A3)) is the largest value in the above virtual array; i.e., the number of the last row (highest row number) above the current one where the stock is equal to the stock on the current row.
• INDEX($A$1:$C$99, (the above), 3) gets the value (column C; i.e., the 3rd column) for the last row above the current one where the stock is equal to the stock on the current row.

• OK, if the current transaction (column B) is a “buy”, the assets total is the same as it was on the previous row; otherwise, it is the previous assets total plus this sell price (C3) minus the buy price for this stock (the INDEX(…) formula).

            

As per @fixer1234’s warning, this will not handle overlapping transactions, like

apple buy
apple buy
apple sell
apple sell

and, while it will get the correct bottom line for partial transactions like

apple buy 16000
apple sell 8005
apple buy 1000
apple sell 9012

the intermediate values will not be what you want.

For completeness, here are your original numbers:

For testing purposes, copyable TSV data are in the source of this answer.