Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
  • Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
  • Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
  • Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
  • Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
Originally published: 18/01/2020 07:27
Last version published: 26/04/2020 13:01
Publication number: ELQ-72375-3
View all versions & Certificate
certified

Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator

Best Excel tool for calculation of long and short term 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 Long Term, Short Term and Total Gain/Loss after every transaction on FIFO basis. 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:S and W:AF 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.

You can also see the quantity and purchase cost of balance coins in a particular price range. For this you can input Lower and Upper Price Ranges in cells AN4 and AO13 to see the Balance Quantity in hand with their cost in a particular price range.

You can also evaluate the possible FIFO gain or loss for a proposed sell at a preferable price in easy way. To do so, you need to input proposed Quantity to Sell and proposed Price to Sell in cells AV2 and AW2 and sheet will show all salable coins with their long term or short term gains or losses for the proposed Sell transaction.

Column AL indicate status of a particular coins batch/slot e.g. Sold, In Hand or Partially Sold. Sheet highlights the rows in relevant colors accordingly.

Summary sheet shows the summary of gains from all the separate coins's sheets.

This Best Practice includes
1 Excel sheet

Acquire business license for $20.00

Add to cart

Add to bookmarks

Discuss

Further information

Calculate Long term, Short term and Total gain or loss using FIFO method

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • Sheyla Summers(last updated: 23/09/2020 17:41)
    Top quality stuff
    Top quality stuff and tool. You will not be wrong if you need something for calculate your Tax Return. Tejasvita Soni did some changes per request that I needed with no extra cost and very fast. The tool deserves every cent that it costs and is clear Excel has no secrets for her. Amazing job. Congratulations, Soni.
  • Lou Fuller(last updated: 19/05/2020 14:02)
  • Pemi Place, LLC(last updated: 12/05/2020 17:04)
    Great for Determining Long and Short Term gains for Cryptocurrency Trades!
    For those of you who want a relatively simple spreadsheet to calculate long- and short-term capital gains, I highly recommend this application.
  • William Brindle, EA(last updated: 26/04/2020 16:49)
  • turboblast(last updated: 10/04/2020 12:47)
    great product for crypto tax
    this is a great add on tool I need for my crypto tax filing. very easy to fill in and also tells me buckets of crypto costs which is a nice feature. great job

More Best Practices from Tejasvita Soni

keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator

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

  • Pemi Place, LLC
    How is this product different from your "Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation"? Is it just that you added long term gains?

    Thank you,

    James
    arrow_drop_uparrow_drop_downReply reply
    • Pemi Place, LLC
      I meant to ask, how is"Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator" different from ""Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation"? Is it just that you added long term gains?"

      Thank you,

      James
      arrow_drop_uparrow_drop_downReply reply
      • Tejasvita Soni
        Hi,

        Thanks for reviewing the tool!

        Long Term FIFO calculator provides some additional features:

        1. It shows Long Term, Short Term and Total Gain on FIFO
        2. It also shows quantity and purchase cost of balance coins in a particular price range (range AN4:AO13), which helps the trader to evaluate bucket/stock in hand.
        3. You can also evaluate the possible FIFO gain or loss for a proposed sell at a preferable price in easy way (columns AU:BA).

        Please let me know if you have any other questions or suggestions.

        Regards
        Soni
        arrow_drop_uparrow_drop_downReply reply
        • Pemi Place, LLC
          Hi Soni,

          Thank you for your response, and for the excellent application! I think this application will be great for my purposes.

          I noticed in other comments for a previous version of this application that you describe how to create "Buy" and "Sell" transactions when trading cryptocurrencies and not using dollars (USD).

          You stated the following

          "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."

          I think I understand how to do this, but I have two questions.

          1. To enter an exchange of 2 cryptocurrencies, I convert the buy and sell transactions for each cryptocurrency into USD. Should the variable commission (USD) be included in both the buy and sell transaction? I believe this is "yes", but wanted to make sure because it lowers "effective price after commission (USD)" for Sell transactions, and increases the "effective price after commission (USD)" for Buy transactions, which makes sense to me.

          2. If done correctly, I believe the "Cost of ABC Purchased" [Column J] for the Buy transaction should be equal to the "Earning from sale of XYZ Purchased" [Column K] for the Sale transaction. Is that correct? If so, that would make sense to me because it shows that crypto ABC was exchanged for an equivalent amount (in USD) of crypto XYZ.

          Thank you for any assistance you can provide.


          Regards,

          James
          arrow_drop_uparrow_drop_downReply reply
          • Tejasvita Soni
            Hello Sir,

            Thanks for your kind words of appreciation and a nice review! It really helps and motivates.

            Your assumptions are very much correct. However, let me try to make it clearer by an example.

            Suppose you bought 1 BTC in lieu of 50 ETH, and exchange charged $1 as commission (it actually might charge in USD, BTC, ETH or BNB, but we assumed/converted it in USD). In this case, you need to show a buy transaction in BTC sheet and a Sell transaction in ETH sheet for equal amount of USD (say $10000).

            You can consider commission of $1 to show in following methods:

            1. Split equally in Buy and Sell transaction
            BTC Sheet : “Cost of BTC Purchased" [Column J] = $10000 + $0.5
            ETH Sheet : “Earning from sale of ETH" [Column K] = $10000 - $0.5

            2. Commission with only Buy transaction
            BTC Sheet : “Cost of BTC Purchased" [Column J] = $10000 + $1
            ETH Sheet : “Earning from sale of ETH" [Column K] = $10000 - $0

            3. Commission with only Sale transaction
            BTC Sheet : “Cost of BTC Purchased" [Column J] = $10000 + $0
            ETH Sheet : “Earning from sale of ETH" [Column K] = $10000 - $1

            In all these methods, Total commission = (“Cost of BTC Purchased” - “Earning from Sale of ETH”) = $1

            Although I am not an expert in accounting or taxation, so please consider my suggestion as simple mathematical calculations.

            Regards
            Soni
            arrow_drop_uparrow_drop_downReply reply
            • Pemi Place, LLC
              Hi Soni,

              I sincerely appreciate your quick response. Your example is very helpful.

              -James
              arrow_drop_uparrow_drop_downReply reply
    • M Johnson
      On the download page, it says full explanatory video is 3 hours, but it is only about 6 and a half minutes.
      arrow_drop_uparrow_drop_downReply reply
      • Tejasvita Soni
        Hi,

        Length of the explanatory video is 6-7 minutes. The 3 hours you mentioned is not duration of the video. It is the time spent option selected (more than 3 hours) for creating the tool.

        Regards
        Soni
        arrow_drop_uparrow_drop_downReply reply
      • Anthony Halliwell
        How do you manage crypto currency to cypto curence y like 1 bought some BTC using already purchased ETH ?
        arrow_drop_uparrow_drop_downReply reply
        • Tejasvita Soni
          Hi,

          We assume USD as the base currency to find profit/loss so each BTC/ETH transaction can be treated as 2 separate transactions (one for Sell and another for Buy) of BTC/USD and USD/ETH, which can be processed in separate tabs/sheets for BTC and ETH. I hope it makes sense.

          Regards
          Soni
          arrow_drop_uparrow_drop_downReply reply
          • Anthony Halliwell
            Thank you yes it does.

            Is it possible to add a Financial Year Capital Gain/Loss summary on the front sheet which can be adapted to Australian Financial year 1st July to 30th June?

            Great Spreadsheet !
            arrow_drop_uparrow_drop_downReply reply
            • Tejasvita Soni
              Thanks for the appreciation!

              Yes, it can be added as per your requirement.

              Regards
              Soni
              arrow_drop_uparrow_drop_downReply reply
              • Anthony Halliwell
                Thank you.
                An excellent spreadsheet that does 99% of what I need.
                The summary on the first sheet shows you totals for all dates.
                If there was a date picker ie 1/7/19 to 30/6/20 it would be ideal as I could then easily see any gains or losses for each financial year.
                Thanks once again!!
                arrow_drop_uparrow_drop_downReply reply
                • Tejasvita Soni
                  My pleasure, Sir!

                  I will try to implement your valuable suggestion in it and you will get the updated version when I upload it.

                  Regards
                  Tejasvita.
                  arrow_drop_uparrow_drop_downReply reply
                  • Anthony Halliwell
                    Thank you very much!
                    This will make it so much easier.

                    I have 100's of trades and apart from having to double enter trades between currencies and figuring out the csv formats from multiple exchanges this will mean it doesn't take as long to calculate things.

                    Thank once again for the excellent spreadsheet and your response so far has been outstanding !
                    arrow_drop_uparrow_drop_downReply reply
                    • Anthony Halliwell
                      I have also modified the headers to include the current tab name so when you insert other currencies and rename the tab they update:
                      For example C3 header now is :

                      =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&" QTY"

                      I found it time consuming changing the coin name in the headers and a find and replace would change everything in the workbook including the raw data.

                      Hope this helps?
                      arrow_drop_uparrow_drop_downReply reply


        4.8 / 5 (5 votes)

        please wait...