Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation
  • Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation
  • Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation
  • Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation
Originally published: 12/05/2018 17:40
Last version published: 07/04/2019 14:02
Publication number: ELQ-93697-9
View all versions & Certificate
certified

Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation

Best FIFO Excel tool with Google sheet for calculation of trading gains in bitcoin, crypto and stocks using FIFO method.

Description
You will just have to put your trading details i.e. Transaction Type (buy/sale), Date & Time of Transaction, BTC Quantity in columns A:D and sheet will automatically calculate FIFO gains and other important data for every row. You will see Gain/Loss after every transaction on FIFO basis in column U. Besides this, spreadsheet will also show you other important data e.g. Cumulative Cost of BTC Purchased, Cumulative Earning from sell of BTC, Total BTC Purchased, Total BTC Sold, BTC in Hand after every transaction.

Columns P, Q, R and S contains formulas for calculating costs of coins sold in parts. These formulas are complex and doing the calculation on FIFO method. These contains intermediate results. You may show or hide them as per your choice.

You can set exchange commission for buy or sell transaction in 2 ways:

First method is to set a fixed percentage for all Buy and Sell transactions in cells B1 and B2. It will be used to fill amount of commission (in USD) for each transaction in column F and remove values from column E.

In second method you can manually fill amount of variable commission (in USD) for each transaction in column E. In that case, value in column F will NOT be used by sheet.

Formula to calculate Effective Price after Commission in column G will first look for commission amount in column E. If it is blank, then commission amount from column F will be used (which itself is derived from %age values from cells B1 and B2).

You can adopt both the methods together, but remember that value in column E always takes precedence over column F.

Google Sheet with same functionality is also available with this purchase. You will get share link for Google Sheet in attached PDF file.

Please let me know if you have any question or suggestion to improve this template. If you want further customization, I will be happy to make changes as per you requirement for a reasonable fee.

This business tool includes
1 Excel spreadsheet, 1 PDF for Google Sheet URL

Acquire business license for $10.00

Add to cart

Add to bookmarks

Discuss

Further information

Easily calculate trading gains in bitcoin or other stock trading using FIFO method.

Reviews


Discussion feed for Bitcoin Cryptocurrency FIFO Gain Excel Calculator For Taxation

The user community and author are here to help. Go ahead!

  • Steen Vitus Bering
    If you buy BTC for ETH or sell LTC and get XRP in return, how do you paste in the transactions? In this case you'r not using USD to buy and sell.
    arrow_drop_uparrow_drop_downReply reply
    • Tejasvita Soni
      Hi,

      Thanks for showing your interest in this tool.

      To do this, some manual work will be required. I have done similar calculations for another user and can do it for you. For each BTC/ETH pair transactions, 2 equivalent transactions of BTC/USD and USD/ETH need to be created. Same method may be used for other non-USD pairs to convert them to USD pair. It needs some (not much) manual work. If you send data with all the transactions to me, I can do it for you. I hope it makes sense.

      Regards
      Soni
      arrow_drop_uparrow_drop_downReply reply
    • Tree
      This looks like exactly what I'm needing, but I have a number of altcoins. Is there any special knowledge required to copy the FIFO pages into new workbooks or tabs for altcoins or does this use VBA that would need to be altered?
      2arrow_drop_uparrow_drop_downReply reply
      • Tejasvita Soni
        Hi,

        It doesn't have any VBA code, so you can just copy the tab and replace the name of coin in new tab.

        Please feel free to contact me, if you have any questions.

        Thanks
        Soni
        1arrow_drop_uparrow_drop_downReply reply
      • Max-M
        Hello Tejasvita,

        is it possible to use your "Bitcoin Cryptocurrency FIFO Gain Calculator" also with LibreOffice "Calc" instead of Microsoft Excel?
        I would be interested to buy a version working with LibreOffice Calc! Thank you. Max
        arrow_drop_uparrow_drop_downReply reply
        • Tejasvita Soni
          Let me try it with Libre office.
          arrow_drop_uparrow_drop_downReply reply
          • Max-M
            Did it work?
            arrow_drop_uparrow_drop_downReply reply
            • Tejasvita Soni
              Yes, it works with Libre office Calc.
              arrow_drop_uparrow_drop_downReply reply
              • Tejasvita Soni
                All the formulas are working in LibreOffice Calc in the same way as Excel. Everything looks fine.
                arrow_drop_uparrow_drop_downReply reply
        • Sahil Saggar
          Hi Tejas,

          I just purchased your fifo spreadsheet. I have noticed that if Coin in hand (Last Column) becomes 0 (by selling all stock), Then (Cum cost of Coin) & (Fifo Gain) gives an error - #ref!. It happens on all of your versions. Can you please fix that and let me know when you update the formulas. You can contact me at seguszone@gmail.com
          arrow_drop_uparrow_drop_downReply reply
          • Tejasvita Soni
            Hi,

            Thanks for your message. I have updated the formulas. Now it will display error only when the Qty in Hand is less than 0 (when a user, by mistake, put more quantity to sell than he/she actually holds in hand).

            I will email you the update version file and also upload here shortly.

            Regards
            Soni
            arrow_drop_uparrow_drop_downReply reply
          • M Mm
            It would be helpful also to be able to turn this into LIFO.
            2arrow_drop_uparrow_drop_downReply reply
          • M Mm
            Determining Short Term vs Long Term capital gains would be very helpful. Possible?
            1arrow_drop_uparrow_drop_downReply reply
            • Tejasvita Soni
              Thanks for your suggestions. I will try to add this feature also.
              1arrow_drop_uparrow_drop_downReply reply
            • Frank Taco
              With the alt coins sections and the ability to add our cryptos, are you able to use BTC or ETH or LTC as your cost basis paired with the app and realize that the gains in BTC then need to be converted to dollars for the overall cost basis for tax purposes.
              Say I purchase ADA with BTC that I bought with USD. in order for me to report gains/losses I need to know ADA gains based on the cost basis of BTC's USD price at time of purchase and time of sale.
              Does this sheet do this? I am very interested in purchasing but want to know if it does this?
              arrow_drop_uparrow_drop_downReply reply
              • Tejasvita Soni
                Hi,

                Thanks for showing your interest in this tool.

                To do this, some manual work will be required. I have done similar calculations for another user and can do it for you. For each ADA/BTC pair transactions, 2 equivalent transactions of ADA/USD and USD/BTC need to be created. Same method may be used for other non-USD pairs to convert them to USD pair. It needs some (not much) manual work. If you send data with all the transactions to me, I can do it for you. I hope it makes sense.

                Regards
                Soni
                arrow_drop_uparrow_drop_downReply reply
              • Tejasvita Soni
                * comment deleted *
                arrow_drop_uparrow_drop_downReply reply
                • Sami Nagata
                  I would really appreciate a LIFO version too! Great work!
                  1arrow_drop_uparrow_drop_downReply reply
                please wait...