How to calculate weighted average cost while taking into account both buys and sells?

Question

I'm trying to write a script that will analyze all of the buys and sells I have made for each cryptocurrency I own, and then tell me the average cost for a coin so I know the cost at which it is profitable to sell.

If all I had done was buy coins, it would be a simple matter of adding up my purchases and then dividing by the number of coins I hold. But, I did a lot of selling as well as buying. I am having a hard time wrapping my head around how to account for the selling. For the sake of giving a simple example, let's say I have the following transaction history for a particular coin:

Date        Transaction Type    Units    Price in BTC        Total Transaction size
Aug 5       Buy                 23       .002222             0.051106 BTC
Aug 6       Sell                11       .002227             0.024497 BTC
Aug 7       Buy                 47       .022391             1.052377 BTC

Obviously after that activity I will have 59 coins left. But, what is the average cost that I paid for those 59 coins? I think this might be called the "cost basis" but it's unclear whether I'm using that term correctly.

There are two ways that I can see to do this:

Option A - divide net amount spent by net coins remaining

average = ( (0.051106 − 0.024497 + 1.052377) / 59 ) = 0.018287898

or

Option B - divide total transaction sizes by total unit volume

average = ( (0.051106 + 0.024497 + 1.052377) / 81 ) = 0.013925679

Which of these formulas is correct?

Note: I know there are other ways of calculating the cost basis, such as FIFO. In this case, I thought that using a weighted average was the best and simplest way to determine whether or not it'd be profitable to sell a particular coin at the current market price. I am not using the calculations for tax filing or anything like that. I just want to know my current break-even point for each coin.

Answer 1

Option A is correct. You need to account for the sells as a negative amount/price. Adding your sells makes no sense if you're wanting net average transaction costs. Think about it this way - if you sold those 59 coins for 1.078986 (0.018288 per coin) then you'd break even.

If you wanted to sell specific lots and break even, then you'd have to calculate the breakeven for each lot separately. Your break-even on the first 12 coins (the 23 you bought less the 11 you sold), your break-even would be (0.051106 - 0.024497)/12 = 0.002217.

Answer 2

I know when I was first building out my personal spreadsheets it was really helpful to think of what is literally happening rather than looking at the data.

If you buy 10 of something for $20. Then next month you sell 3 of them for $15. Then next month you buy 5 more for $20. Etc. The three you sold had a $9 gain and you now hold 12 units at a cost of $34 for an average unit price of $2.83.

LIFO and FIFO are more than just cost basis calculation philosophies. Which of your units are sold, in literal terms, matters both in terms of calculating your gain or loss on the sale, but more importantly because if you sell 2 more units now using the numbers above did you sell $2 units or $4 units? After your next sale it will impact your average price. In the case of $2 units your new average unit price will be $3.00 (5 * $2 + 5 * $4 ÷ 10) ; in the case of $4 units your average is $2.40 (7 * $2 + 3 * $4 ÷ 10). You could value the 2 units at the average price of $2.83 and that would maintain your average price after the sale, $34 - $5.66 = $28.34; you have 10 units remaining for an average of $2.83. You need to be consistent in your accounting method because as you can see it can have a material effect on the value of your holdings. You've mentioned this is just for your own accounting purposes but for an actual security holdings IRS tax scenario the method you choose can materially change your flexibility for future sales (and obviously impact your tax liability).

Separately, the correct way to determine weighted average is in your example A, sold units are netted off and no longer contribute to the average unit price. To determine the break even sale price of your existing holdings/inventory the price received in past partial sells is irrelevant.

Answer 3

Option A is not correct. When selling, total cost should be reduced based on the avg. purchase cost, not the sale price. This becomes clear with a simpler example:

1. Buy 2 coins for $2 ($1 each)
2. Sell 1 coin for $2

Using the methodology described in "option A", the avg cost here would be $2 - $2 = $0, which is clearly not correct. The remaining 1 coin still costs the same $1 paid for it. Selling never changes the avg cost. Instead, when you sell, the total cost must be reduced based on the avg cost up to that point:

(total_cost - (avg_coin_cost * num_coins_sold)) / (num_coins_purchased - num_coins_sold) = avg_cost

In our simplified example this translates to:

($2 - ($1 * 1)) / (2 - 1) = $1 / 1 = $1

Careful however, as this cannot be done in aggregate. The avg cost must be calculated and stored after each transaction to be used in subsequent transactions. For example, let's add a BUY to our simplified example:

1. Buy 2 coins for $2 ($1 each)
2. Sell 1 coin for $2
3. Buy 1 coin for $3

The way to calculate the avg is step-by-step:

After step 1, we have 2 coins and a total cost of $2, giving us an avg of $1 per coin.

After step 2, we have 1 less coin from our previous total (2 - 1) giving us 1 coin remaining. And our total cost decreases by the number of coins sold (1) times the previous avg cost of $1, equaling a $1 reduction in cost (not $2). We end up with 1 coin at $1, correctly maintaining our $1 avg cost.

At step 3, we gain 1 additional coin for a price of $3, giving us 2 coins total, at a cost of $4 making our new avg cost $2/coin.

Any new transactions should continue to use the results from the previous row.

Answer 4

I just came across this post and I am trying to do exactly the same thing.

Your Option A example will work only as long as the total sell prices are less than your total buys (i.e. up until the break even point).

Once break even is reached, your formula should return 0, as you could sell at $0 since your initial investment has already been returned from your sells.