The following is a conceptual (explanation of why each calculation is being performed) as well as practical explanation of what I am hoping someone can help with.
If the conceptual part makes it confusing, please ignore and just pay attention to the practical parts.
Or let me know, and I will try my best to rewrite it just listing transactions to be calculated only.
I hope this is understandable.
I'm going to attach Before and After samples.
I hope this is not too confusing - I tried to make it clear. Please ask questions.
I used Bitcoin (BTC) for trading/making short term investments.
The Columns which are empty and have to be calculated are R/S (one or the other),
AB, AC, AD.
The info is already in the other Columns to use in the calculations.
Starting out:
AB5 shows the Total Amount of BTC to begin.
AC5 shows the Cost Basis per BTC of $557 (the $ amount I paid divided by the number of BTC).
AD5 shows the Total Value of my BTC (AB5 times AC5).
The first trade is shown in Row 6.
Column N shows the USD price per BTC on the day of the trade.
Column O shows the amount of BTC traded.
1) So the first thing to do is:
I am selling some BTC.
Re: the sale of the BTC - For every Row I need to figure out if I made a Gain (sold the BTC on a day when it's market value was higher than the Cost Basis of my BTC), or a Loss (sold the BTC on a day when its market value was lower than the Cost Basis of my BTC).
N6 (Price of BTC on that day) minus AC5 (Cost basis of my BTC prior to this trade - NOTE this is on the prior Row) times O6 which is
0.14 (Amount of BTC sold).
If the product is a positive number - which this one is - it goes into R6.
This Column shows the profit of selling the 0.14 of BTC to make the trade.
In this case, I profited 15 (dollars):
N6 (666 - Cost of the BTC that day) minus AC5 (Cost basis of my BTC prior to the sale) times O.14 (amount of BTC traded).
So 15 goes into R6.
Column R is for positive numbers.
Had the product been a negative number it goes into Column S.
2) The second thing to calculate is how many BTC I now have depending if my investment Gained or Lost [I'm not referring to the Gain or Loss on the sale of the BTC referred to above, but on an investment I made with the proceeds of the sale of the BTC previously referred to], and the new cost basis of my BTC if I had a Gain.
So, continuing with Row 6:
In AB5 We see I have 5.36 BTC prior to the sale.
I am selling 0.14 BTC in O6, so I am retaining 5.22 BTC at a Cost Basis of $557/BTC = 2908.
(5.22 times AC5 $557 = 2908 rounded).
[I imagine I'll have to add a Column to retain this figure, which will be used in another calculation].
My investment succeeded - in Z6 we see it returned $496 total.
BTC that day was valued at $666 (in N6).
So the investment returned 0.74 BTC.
Dollar Amount Returned Z6 divided by BTC price that day N6 = 0.74 BTC
So I retained 5.22 BTC, and am now adding 0.74 BTC to it for a total of 5.96 BTC in Column
AB6.
The BTC I retained had a value of $2908, and I am adding value of Z6 $496 so Value of my BTC AD6 is now $3404.
Finally, for Column AC, my Cost Basis of my BTC is now Value of my BTC AD6 divided by Total Amount of BTC AB6 equals $571 which goes in AC6.
_________________________________________
___________________________________________
Now I will show an example when the investment loses and I receive nothing back, using Row 7:
(it's a simpler calculation)
P7 I sell 0.12 BTC.
BTC is still 666 in N7 and my Cost Basis AC6 is now $571 so that day's BTC price N7 minus BTC Cost Basis prior to the sale AC6 times Amount of BTC sold 0.12 = 11 (rounded) which I put into Column R7 (it is a gain).
Z7 shows nothing back - the trade lost all.
AB6 I have 5.96 BTC minus P7 0.12 sold = 5.84 into Total Amount of BTC AB7.
The cost basis hasn't changed - the pool of coins are still worth the same because I haven't gained any additional coins - I simply lost the 0.12 I withdrew from the pool of coins and sold.
So all that has to be done is insert the previous Cost Basis from AC6 into AC7.
Total Amount BTC AB7 times Cost Basis BTC AC7 and enter the product into Total Value My BTC AD7.
Bookmarks