How to calculate cost basis for list of incongruent batches of buy and sell transactions (forex trading)

External question — Personal Finance Stack Exchange Author: qualityfreckle Original post: https://money.stackexchange.com/questions/152967 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. How can I calculate the cost basis for my forex portfolio at any given time? I'm trying to build a spreadsheet to track forex trading of cryptocurrency pairs. Specifically I'm looking at XMR-BTC https://finance.yahoo.com/quote/XMR-BTC/ I'm not using any derivitives or limit orders. The idea is farily simple: Buy Monero (XMR) with Bitcoin (BTC) when the price is low. Sell Monero (XMR) for Bitcoin (BTC) when the price is high. I want my spreadsheet to tell me something fairly simple: what's the average price (in BTC) that I've spent on Monero so far? This is very simple when you have only made a few purchases. It's also fairly simple when you've made sells of exactly the same quantity of your buys. But when you have a bunch of different buys and sells of different quantities, this is non-trivial. As best I can tell, there is no precise way to measure this. There's many, including: FIFO LIFO High Cost Low Cost Average Cost Specific Identifier Others?? So my question is, how can I calculate the cost basis of my portfolio at any point in the following set of transactions. Note I'm not just interested in knowing how to determine the cost basis at the end of this set of transactions. I want to understand how to calculate the cost basis after every step of the following transactions. I'm also not interested in tracking the cost basis in fiat. I only care about calculating the cost basis of my base currency (XMR) in the quote currency's units (BTC). Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR Tx 4 - sell 3 XMR at 0.007678 BTC/XMR Tx 5 - sell 2 XMR at 0.008008 BTC/XMR Tx 6 - sell 2 XMR at 0.007912 BTC/XMR Tx 7 - buy 1 XMR at 0.007084 BTC/XMR This data can also be portrayed in a table: Tx Num Buy Qty Buy Cur Sell Qty Sell Cur Price (BTC/XMR) Date 1 3 XMR 0.011787 BTC 0.003929 2021-10-19 2 1 XMR 0.003847 BTC 0.003847 2021-12-15 3 3 XMR 0.011223 BTC 0.003741 2022-01-31 4 0.023034 BTC 3 XMR 0.007678 2022-09-01 5 0.016016 BTC 2 XMR 0.008008 2022-09-05 6 0.015824 BTC 2 XMR 0.007912 2022-09-08 7 1 XMR 0.007084 BTC 0.007084 2022-09-02 My understanding is that, following either FIFO or LIFO, the cost basis after Day 4 would be calculated using the 3 XMR purchased either on Day 1 or Day 3 . What's especially interesting is: how do you calculate the cost basis after Day 5 (which would be a combination of two distinct buy transactions)?? How can I calculate the cost basis for my above-described portfolio after each of the 7 days shown?

External answer — Personal Finance Stack Exchange Author: S Spring Original post: https://money.stackexchange.com/a/153007 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. If XMR is the base currency then use XMR numbers in place of dollar numbers. The cost basis of a transaction is just the number of XMR. If BTC is the base currency then use BTC numbers in place of dollar numbers. The cost basis of a transaction is just the number of BTC. Do a supplemental overall year-ending conversion to dollar result if wanted. In the U.S., forex is mark-to-market accounting but not to be included on a Form 6781, for 60/40 long-term/short-term designation, unless the forex is also available on regulated futures exchanges. However, crypto is not mark-to-market accounting and so it might be necessary to apply for mark-to-market accounting. But with mark-to-market accounting then the overall year-ending conversion to dollar result is all that is required. Or itemized transactions might also be required and then just use your own method along with a letter of explanation. Well, forex goes on either "Other income" of a Form 1040 Schedule 1, or goes on a trader-status Form 4797, or goes on a Form 6781. Crypto goes on a Form 8949 .

External answer — Personal Finance Stack Exchange Author: qualityfreckle Original post: https://money.stackexchange.com/a/153009 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. This answer will show how to calculate: the average buy , the average sell , and the break-even price These calculations were determined using cointracking.info Unfortunately, it's still a mystery how one would calculate the cost basis using FIFO or LIFO. I welcome other answers that show how to do these calculations :) Transaction 1 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR After the first day, the calculations are simple. You currently have a total of 3 XMR. You spent a total of (3 XMR) * (0.003929 BTC/XMR) = 0.011787 BTC Average Buy For the "Average Buy," we want to know the average price that we paid (in BTC) for each 1 coin of XMR. To calculate the "Average Buy," we take the sum of the all of our weighted buys (obtained by multiplying the quantity of XMR bought by the price of 1 XMR in BTC) and divide it by the sum of the quantity of XMR bought. Note For the "Average Buy" do not include any sell transactions The algorithm for this is [ (tx 1 buy qty) * (tx 1 price) + (tx 2 buy qty) * (tx 2 price) + ... + (tx n buy qty) * (tx n price) ] / [ (tx 1 buy qty) + (tx 2 buy qty) + ... + (tx n buy qty) ] [ (3 XMR) * (0.003929 BTC/XMR) ] / [ (3 XMR) ] = 0.003929 BTC/XMR Therefore, your average buy at this point is 0.003929 BTC/XMR Average Sell For the "Average Sell," we want to know the average price that we sold (in BTC) for each 1 coin of XMR. To calculate the "Average Sell," we take the sum of the all of our weighted sells (obtained by multiplying the quantity of XMR sold by the price of 1 XMR in BTC) and divide it by the sum of the quantity of XMR sold. Note For the "Average Sell" do not include any buy transactions The algorithm for this is [ (tx 1 sell qty) * (tx 1 price) + (tx 2 sell qty) * (tx 2 price) + ... + (tx n sell qty) * (tx n price) ] / [ (tx 1 sell qty) + (tx 2 sell qty) + ... + (tx n sell qty) ] At this point, you haven't sold anything. There is no average sell price. Break-Even Price For the "Break-Even Price," we want to know at what price you would need to sell all of your XMR in order to break even (profit = 0 BTC). To calculate the "Break-Even Price," we take the sum of the all of our weighted buys and sells (obtained by multiplying the quantity of XMR bought/sold by the price of 1 XMR in BTC) and divide it by the sum of the quantity of XMR bought/sold. Note The Break-Even Price is very similar to the "Average Buy" and "Average Sell," except that it includes both buy and sell transactions. The algorithm for this is [ (tx 1 qty) * (tx 1 price) + (tx 2 qty) * (tx 2 price) + ... + (tx n qty) * (tx n price) ] / [ (tx 1 qty) + (tx 2 qty) + ... + (tx n qty) ] [ (3 XMR) * (0.003929 BTC/XMR) ] / [ (3 XMR) ] = 0.003929 BTC/XMR Therefore, your break-even price at this point is 0.003929 BTC/XMR Transaction 2 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR After the second transaction, you've completed two buy transactions and currently hold a total of 3 XMR + 1 XMR = 4 XMR . Average Buy For the "Average Buy," we simply sum how much we spent on XMR purchases (in bitcoin) and divide it by the sum of XMR that we own. [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) ] / [ (3 XMR) + (1 XMR) ] = 0.0039085 BTC/XMR Therefore, your average buy at this point is 0.0039085 BTC/XMR Note that the previous buy price was 0.003929 BTC/XMR . It makes sense that this new average buy price is slightly lower because our second purchase was made at a slightly less expensive price. Average Sell At this point, you haven't sold anything. There is no average sell price. Break-Even Price For the "Break-Even," we simply sum how much we spent or received on all XMR purchases & sales (in bitcoin) and divide it by the sum of XMR that we've bought and sold. [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) ] / [ (3 XMR) + (1 XMR) ] = 0.0039085 BTC/XMR Therefore, your break-even price at this point is 0.0039085 BTC/XMR Transaction 3 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR After the third transaction, you've completed three buy transactions and currently hold a total of 3 XMR + 1 XMR + 3 XMR = 7 XMR . Average Buy [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) ] = 0.003836714 BTC/XMR Therefore, your average buy at this point is 0.003836714 BTC/XMR Average Sell At this point, you haven't sold anything. There is no average sell price. Break-Even Price [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) ] = 0.003836714 BTC/XMR Therefore, your break-even price at this point is 0.003836714 BTC/XMR Transaction 4 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR Tx 4 - sell 3 XMR at 0.007678 BTC/XMR After the fourth transaction, you've completed three buy transactions and one sell transaction. You currently hold a total of 3 XMR + 1 XMR + 3 XMR - 3 XMR = 4 XMR . Average Buy [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) ] = 0.003836714 BTC/XMR Therefore, your average buy at this point is 0.003836714 BTC/XMR Note Because this sell transaction is not computed in the "Average Buy," there is no change from the previous transaction's average buy of 0.003836714 BTC/XMR Average Sell [ (3 XMR) * (0.007678 BTC/XMR) / [ (3 XMR) ] = 0.007678 BTC/XMR Therefore, your average sell at this point is 0.007678 BTC/XMR Break-Even Price [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) - (3 XMR) * (0.007678 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) - (3 XMR) ] = 0.00095575 BTC/XMR Note For the sell transactions, we subtract both the weighted transaction amount from the top of the denominator and the quantity from the bottom of the denominator Therefore, your break-even price at this point is 0.00095575 BTC/XMR Note that because we've now had our first sell transaction, the "Average Buy" and "Break-Even" prices begin to diverge. Transaction 5 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR Tx 4 - sell 3 XMR at 0.007678 BTC/XMR Tx 5 - sell 2 XMR at 0.008008 BTC/XMR After the fifth transaction, you've completed three buy transactions and two sell transaction. You currently hold a total of 3 XMR + 1 XMR + 3 XMR - 3 XMR - 2 XMR = 2 XMR . Average Buy [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) ] = 0.003836714 BTC/XMR Therefore, your average buy at this point is 0.003836714 BTC/XMR Average Sell [ (3 XMR) * (0.007678 BTC/XMR) + (2 XMR) * (0.008008 BTC/XMR) ] / [ (3 XMR) + (2 XMR) ] = 0.00781 BTC/XMR Therefore, your average sell at this point is 0.00781 BTC/XMR Break-Even Price [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) - (3 XMR) * (0.007678 BTC/XMR) - (2 XMR) * (0.008008 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) - (3 XMR) - (2 XMR) ] = -0.0060965 BTC/XMR Therefore, your break-even price at this point is -0.0060965 BTC/XMR Note Your break-even price becomes negative here because you've been so profitable that, even if the value of XMR drops to zero, you'd still have net positive gains Transaction 6 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR Tx 4 - sell 3 XMR at 0.007678 BTC/XMR Tx 5 - sell 2 XMR at 0.008008 BTC/XMR Tx 6 - sell 2 XMR at 0.007912 BTC/XMR After the sixth transaction, you've completed three buy transactions and three sell transaction of equal amounts. You currently hold a total of 3 XMR + 1 XMR + 3 XMR - 3 XMR - 2 XMR - 2 XMR = 0 XMR . Average Buy [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) ] = 0.003836714 BTC/XMR Therefore, your average buy at this point is 0.003836714 BTC/XMR Average Sell [ (3 XMR) * (0.007678 BTC/XMR) + (2 XMR) * (0.008008 BTC/XMR) + (2 XMR) * (0.007912 BTC/XMR) ] / [ (3 XMR) + (2 XMR) + (2 XMR) ] = 0.00781 BTC/XMR Therefore, your average sell at this point is 0.00781 BTC/XMR Break-Even Price [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) - (3 XMR) * (0.007678 BTC/XMR) - (2 XMR) * (0.008008 BTC/XMR) - (2 XMR) * (0.007912 BTC/XMR) ] / [ (3 XMR) + (1 XMR) + (3 XMR) - (3 XMR) - (2 XMR) - (2 XMR) ] = infinity!! BTC/XMR Therefore, your break-even price at this point is infinity!! BTC/XMR Note Your break-even price cannot be calculated here because you've sold all your XMR and you cannot divide by zero Transaction 7 Tx 1 - buy 3 XMR at 0.003929 BTC/XMR Tx 2 - buy 1 XMR at 0.003847 BTC/XMR Tx 3 - buy 3 XMR at 0.003741 BTC/XMR Tx 4 - sell 3 XMR at 0.007678 BTC/XMR Tx 5 - sell 2 XMR at 0.008008 BTC/XMR Tx 6 - sell 2 XMR at 0.007912 BTC/XMR Tx 7 - buy 1 XMR at 0.007084 BTC/XMR After the seventh transaction, you've bought and sold all your XMR for a profit, then you re-bought at a relative low. You currently hold a total of 3 XMR + 1 XMR + 3 XMR - 3 XMR - 2 XMR - 2 XMR + 1 XMR = 1 XMR . Average Buy [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) + (1 XMR) * (0.007084 XMR/BTC) ] / [ (3 XMR) + (1 XMR) + (3 XMR) + (1 XMR) ] = 0.004242625 BTC/XMR Therefore, your average buy at this point is 0.004242625 BTC/XMR Average Sell [ (3 XMR) * (0.007678 BTC/XMR) + (2 XMR) * (0.008008 BTC/XMR) + (2 XMR) * (0.007912 BTC/XMR) ] / [ (3 XMR) + (2 XMR) + (2 XMR) ] = 0.00781 BTC/XMR Therefore, your average sell at this point is 0.00781 BTC/XMR Break-Even Price [ (3 XMR) * (0.003929 BTC/XMR) + (1 XMR) * (0.003847 BTC/XMR) + (3 XMR) * (0.003741 BTC/XMR) - (3 XMR) * (0.007678 BTC/XMR) - (2 XMR) * (0.008008 BTC/XMR) - (2 XMR) * (0.007912 BTC/XMR) + (1 XMR) * (0.007084 XMR/BTC) ] / [ (3 XMR) + (1 XMR) + (3 XMR) - (3 XMR) - (2 XMR) - (2 XMR) + (1 XMR) ] = -0.020933 BTC/XMR Therefore, your break-even price at this point is -0.020933 BTC/XMR