1

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

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:

  1. FIFO
  2. LIFO
  3. High Cost
  4. Low Cost
  5. Average Cost
  6. Specific Identifier
  7. 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?

Improve this question
12
  • See also money.stackexchange.com/questions/86221/…
    – qualityfreckle
    Commented Oct 3, 2022 at 5:12
  • As you've identified, there are many different answers to this question depending on how you are matching up buys and sells. You'd first need to determine that answer. Which would depend on things like whether you are trying to match the calculations of some other entity (i.e. your broker's site) or whether you are trying to match the calculations you are doing for tax purposes.
    – Justin Cave
    Commented Oct 3, 2022 at 5:42
  • This is to help determine the best price to sell above and buy under based on current buy-in (I have X Monero and I've paid an average of Y Bitcoin for it at this point in time). This is not for tax purposes.
    – qualityfreckle
    Commented Oct 3, 2022 at 16:37
  • See also economics.stackexchange.com/questions/20464/…
    – qualityfreckle
    Commented Oct 5, 2022 at 15:33
  • 1
    So you want to calculate 6 different answers on each day for the 6 different methods you've listed? Or do you want to calculate one answer on each day? The latter would be far more sensible in the vast majority of cases but then you'd need to tell us what algorithm you want to use.
    – Justin Cave
    Commented Oct 5, 2022 at 20:50

2 Answers 2

Reset to default
0

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 .

Improve this answer
11
  • Sorry, this doesn't make any sense. I'm asking for how to calculate things, and I didn't see anything calculated. This question is also not related to taxes, so I don't know why that was mentioned.
    – qualityfreckle
    Commented Oct 5, 2022 at 22:53
  • If XMR is the base currency then use XMR numbers in place of dollar numbers. The cost basis of a position is the number of XMR. You can't have two different types of cost basis and then say that nothing is calculated.
    – S Spring
    Commented Oct 5, 2022 at 22:56
  • I specifically asked for the basis in BTC. That's my main currency that I'm trying to stack. The valid answer to this question should provide the cost basis after each transaction and show how that was calculated. The reason I'm asking this question in the first place is because the internet is full of answers like yours, which are useless to actually understand how the basis is calculated.
    – qualityfreckle
    Commented Oct 5, 2022 at 22:57
  • Well, the cost basis of XMR can be in BTC with BTC used as the base currency.
    – S Spring
    Commented Oct 5, 2022 at 22:59
  • I don't understand what you mean. Can you please show what you mean by typing out the mathematical equations after each transaction ?
    – qualityfreckle
    Commented Oct 5, 2022 at 23:00
0

This answer will show how to calculate:

  1. the average buy,
  2. the average sell, and
  3. 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

[ (tx1 buy qty) * (tx1 price) + (tx2 buy qty) * (tx2 price) + ... + (txn buy qty) * (txn price) ] / [ (tx1 buy qty) + (tx2 buy qty) + ... + (txn 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

[ (tx1 sell qty) * (tx1 price) + (tx2 sell qty) * (tx2 price) + ... + (txn sell qty) * (txn price) ] / [ (tx1 sell qty) + (tx2 sell qty) + ... + (txn 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

[ (tx1 qty) * (tx1 price) + (tx2 qty) * (tx2 price) + ... + (txn qty) * (txn price) ] / [ (tx1 qty) + (tx2 qty) + ... + (txn 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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .