I have a worksheet that records all buy and sell trades executed for multiple different stocks (e.g. MSFT, APPL).
I'm struggling to work out a single formula I can use that will calculate an accurate running Book Cost figure for each stock, one that updates for each buy or sell transaction
Whereas a "Total Cost of Investment" calculation that simply sums to total cost of acquiring all shares in a stock to day, including changes, a stock's Book Cost figure also takes into account any sale of shares in the stock, reducing in value by an amount equal to what the sale share quantity is as a proportion of the total share quantity currently held in that stock.
For example, assuming I only made buy trades for APPL shares up to a particular date, purchasing a total of 12 shares for a total cost of $1596, my running Book Cost for that stock would be $1596. If I then sold 3 of my APPL shares, I would be selling 25% of my holdings (3/12) and the book cost should be reduced by that amount. The book cost being calculated as:
STOCK'S CURRENT BOOK COST - (STOCK'S CURRENT BOOK COST * 0.25) = $1596 - ($1596 * 0.25) = $1197
If I then bought another 2 shares of APPL at $145 per share (i.e. total consideration of $290), the new Book Cost figure would be:
STOCK'S PREVIOUS BOOK COST + THIS TRADE'S CONSIDERATION = £1197 + $290 = $1487
I've attached a screenshot showing an example list of trades and what calculations would be involved in keeping an running Book Cost total beside each. I'm leaving out trade fees to keep things simple.
The following formula works fine for keeping a running total of the quantity of shares currently held in each stock (excluding the current trade's QTY being bought or sold):
=SUMIFS([QTY],[Date-Time],"<"&[@[Date-Time]],[Type],"BUY",[Stock],[@Stock])
-SUMIFS([QTY],[Date-Time],"<"&[@[Date-Time]],[Type],"SELL",[Stock],[@Stock])
If I used a separate worksheet to record the buy and sell trades for each stock, the below formula works fine to calculate the running book cost, referencing the book cost up to date from the previous row:
=IF([@Type],"BUY",SUM(OFFSET([@[Book Cost]],-1,0),[@[Consideration ($)]]),
IF([@Type],"SELL",SUM(OFFSET([@[Book Cost]],-1,0),-(([@QTY]/[@[Current QTY]]) * OFFSET([@[Book Cost]],-1,0))),0))
However, having a separate worksheet for each stock becomes unwieldy with a large portfolio of stocks.
I'd prefer to stick with a single worksheet that records the buy and sell transactions for all stocks and have one column on it that can keep a running Book Cost total for all of them - i.e. the value shown in the column for each buy or sell transaction reflects the Book Cost total for that stock at that point in time (up to and including that trade).
On each row that represents a sale of stock, the book cost formula needs to pick up what the last Book Cost figure was for the stock in question and the amount of shares currently held in that stock, then subtract an amount from the Book Cost equal in proportion to the quantity of shares being sold against the total share quantity currently held in that particular stock. Simply summing up the total cost of all purchases of the stock and then reducing that value by a percentage equal to the total quantity of shares sold across all sell trades of that stock wouldn't work (the order of the individual buy and sell trades of each stock matters in calculating an accurate running Book Cost figure).
At its most basic, I think the Book Cost formula would take the below form, I just need help with the main bits in the middle:
=IF([Type],"BUY",………………,IF([Type],"SELL",…………………,"")
The list of trades are stored in an Excel table, so ideally need the Book Cost formula to use structured references. I'm using Excel 365 (latest version) on a Windows machine.
I'm struggling to work out a single formula I can use that will calculate an accurate running Book Cost figure for each stock, one that updates for each buy or sell transaction
Whereas a "Total Cost of Investment" calculation that simply sums to total cost of acquiring all shares in a stock to day, including changes, a stock's Book Cost figure also takes into account any sale of shares in the stock, reducing in value by an amount equal to what the sale share quantity is as a proportion of the total share quantity currently held in that stock.
For example, assuming I only made buy trades for APPL shares up to a particular date, purchasing a total of 12 shares for a total cost of $1596, my running Book Cost for that stock would be $1596. If I then sold 3 of my APPL shares, I would be selling 25% of my holdings (3/12) and the book cost should be reduced by that amount. The book cost being calculated as:
STOCK'S CURRENT BOOK COST - (STOCK'S CURRENT BOOK COST * 0.25) = $1596 - ($1596 * 0.25) = $1197
If I then bought another 2 shares of APPL at $145 per share (i.e. total consideration of $290), the new Book Cost figure would be:
STOCK'S PREVIOUS BOOK COST + THIS TRADE'S CONSIDERATION = £1197 + $290 = $1487
I've attached a screenshot showing an example list of trades and what calculations would be involved in keeping an running Book Cost total beside each. I'm leaving out trade fees to keep things simple.
The following formula works fine for keeping a running total of the quantity of shares currently held in each stock (excluding the current trade's QTY being bought or sold):
=SUMIFS([QTY],[Date-Time],"<"&[@[Date-Time]],[Type],"BUY",[Stock],[@Stock])
-SUMIFS([QTY],[Date-Time],"<"&[@[Date-Time]],[Type],"SELL",[Stock],[@Stock])
If I used a separate worksheet to record the buy and sell trades for each stock, the below formula works fine to calculate the running book cost, referencing the book cost up to date from the previous row:
=IF([@Type],"BUY",SUM(OFFSET([@[Book Cost]],-1,0),[@[Consideration ($)]]),
IF([@Type],"SELL",SUM(OFFSET([@[Book Cost]],-1,0),-(([@QTY]/[@[Current QTY]]) * OFFSET([@[Book Cost]],-1,0))),0))
However, having a separate worksheet for each stock becomes unwieldy with a large portfolio of stocks.
I'd prefer to stick with a single worksheet that records the buy and sell transactions for all stocks and have one column on it that can keep a running Book Cost total for all of them - i.e. the value shown in the column for each buy or sell transaction reflects the Book Cost total for that stock at that point in time (up to and including that trade).
On each row that represents a sale of stock, the book cost formula needs to pick up what the last Book Cost figure was for the stock in question and the amount of shares currently held in that stock, then subtract an amount from the Book Cost equal in proportion to the quantity of shares being sold against the total share quantity currently held in that particular stock. Simply summing up the total cost of all purchases of the stock and then reducing that value by a percentage equal to the total quantity of shares sold across all sell trades of that stock wouldn't work (the order of the individual buy and sell trades of each stock matters in calculating an accurate running Book Cost figure).
At its most basic, I think the Book Cost formula would take the below form, I just need help with the main bits in the middle:
=IF([Type],"BUY",………………,IF([Type],"SELL",…………………,"")
The list of trades are stored in an Excel table, so ideally need the Book Cost formula to use structured references. I'm using Excel 365 (latest version) on a Windows machine.
