Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator
Originally published: 18/01/2020 07:27
Last version published: 13/06/2021 20:03
Publication number: ELQ-72375-4
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
This tool uses separate sheets for each coin or stock to show detailed transaction data and a summary sheet to show combined summary of all sheets. You can easily copy COIN sheet to create new sheets as per your requirement for coins or stocks you trade. Formulas and error messages in coin sheets will precisely guide you to identify and avoid mistakes in filling the transaction data.

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 and display FIFO Long Term, Short Term and Total Gain/Loss, Cumulative Cost, Cumulative Earning, and Stock in Hand after every transaction. Column AK indicates current status of a particular coins batch/lot whether it is Sold, In Hand or Partially Sold. Sheet highlights the rows in relevant colors accordingly.

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 fiat currency) for each transaction in column F and remove values from column E. In second method you can manually fill amount of variable commission (in fiat currency) 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 buying costs of balance coins in a particular price range by filling Lower and Upper Price Ranges in cells AN4 and AO13 to see the Balance coins in hand with their costs 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 saleable coins with their long term or short term gains or losses for the proposed Sell transaction.

Summary sheet shows the summary of gains from each coin's sheet. In Summary sheet formulas are set to show you all the important data by putting just the coin’s code. You can see summary data for all period or any selected year and FIFO gains year-wise for 10 consecutive years.

This Best Practice includes
1 Excel sheet

Acquire business license for $24.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

  • Daniel Vilinsky(last updated: 10/05/2021 11:10)
    A must have for any share/crypto trader
    Is exactly what I have been looking forKeeps track of all my trades together with running profits/cost prices for each tradeHighly recommend for anyone needing an accurate overview of their current position
  • Anthony Halliwell(last updated: 25/04/2021 00:47)
  • Partha Mishra(last updated: 17/03/2021 12:32)
  • Mark J. Cipolloni, PMP, PE(last updated: 06/03/2021 11:36)
  • 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 Soni Excel Solutions

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
      • Soni Excel Solutions
        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
          • Soni Excel Solutions
            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
    • Chidchanok Winyoopradist
      Hi,

      Is it possible to choose the lot we want to sell? Or is it fixed FIFO method?
      arrow_drop_uparrow_drop_downReply reply
    • Mogan Naidoo
      I as seeking a FIFO methodology spreadsheet for Cryto. You have a few and not sure which would suit best. My goal is to have a one sheet tracking system but also i need to calculate the capital gains tax in AUD . I am not sure which spreadsheet suits best. Recommendations would assist.
      How is the "Bitcoin Cryptocurrency Long Term FIFO Capital Gain Excel Calculator" different to "
      Bitcoin Cryptocurrency FIFO Gain Excel Calculator with Yearly Summary" and again diffirent to "Multiple Equity Stocks Trading FIFO Gain Excel Calculator"
      arrow_drop_uparrow_drop_downReply reply
      • Soni Excel Solutions
        Hi,,

        Thanks for your interest.

        1. Long Term FIFO calculator shows total gain, long term gain, and short term gain for every Sell transaction.

        2. FIFO Gain calculator shows only total gain for every Sell transaction (no long or short term part separately). It shows yearly summary for all transactions in Summary page.

        3. Multiple stocks calculator allows to put all transactions for various stocks in a single tab, which is better for share/stock traders as they get a combined transaction report for all stocks they trade from exchange. So they don't need to arrange the transactions separately for every stock. Although it can be used for crypto also.

        All these tools can be for any base currency, no matter of AUD, USD, GBP, EURO.

        For capital gains the first one Long Term calculator is suggested.

        Regards
        Soni
        arrow_drop_uparrow_drop_downReply reply
        • Mogan Naidoo
          Thanks for your response. I did not see this message as there is no date order and was in the middle of other messages.
          arrow_drop_uparrow_drop_downReply reply
      • M Johnson
        Hello,
        Is it possible to add a feature that shows the P/L for each tax year? Or how do you recommend we calculate this using the existing sheet? Also, how do we add additional coins to the summary page?

        Thanks for your help.
        Margaret
        arrow_drop_uparrow_drop_downReply reply
        • Soni Excel Solutions
          Hi,

          I will work on to implement this feature to calculate P/L for each tax year. You will get the update whenever I upload it - although it will take some time. Meanwhile you can create separate copies of existing sheet for each tax year.

          To add a new coin, you need to copy existing sheet into a new one and rename it, replace the existing coin name with the new coin name in all formulas. Then insert a new row in Summary sheet and again replace the existing coin name with the new coin name in all formulas. This video may be helpful to you.

          https://www.youtube.com/watch?v=nsujNyvViN0

          Regards
          Tejasvita
          arrow_drop_uparrow_drop_downReply reply
        • d d322
          I have submitted the summary data to my accountant but they seem confused by the results. I am an Excel guy; not a Tax guy and need to be able to explain some of the data. Why are there gains on "BUY" transactions?
          arrow_drop_uparrow_drop_downReply reply
          • Soni Excel Solutions
            Can you please send me the file or screen shot to fifolifocalc@gmail.com with indication of the cells where your accountant seems confused?
            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
            • Soni Excel Solutions
              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
            • Julie Sumpter
              I am loving the spreadsheet! I do have a couple of questions:

              How do you record Coinbase rewards, such as a $10 reward for opening an account or rewards for watching videos of new currencies?

              Similarly, how do you record staking rewards? Are they a "buy", but with 0 cost?

              Thank you!
              arrow_drop_uparrow_drop_downReply reply
              • Kerri Levitanus
                Julie, did you figure out a workaround for this? I have been putting my interest payments/rewards in as BUY transactions, but it seems there is an error when there is no cost, so I enter $0.01 (one cent) and it seems to do the trick. Curious if you have found a better way.
                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
                • Soni Excel Solutions
                  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
                    • Soni Excel Solutions
                      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
                        • Soni Excel Solutions
                          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?
                              1arrow_drop_uparrow_drop_downReply reply
                              • Julie Sumpter
                                Awesome suggestion. I did this and it worked great! Thank you!
                                arrow_drop_uparrow_drop_downReply reply
                                • Anthony Halliwell
                                  I also changed the formulae in Coins AM column from=YEAR(B6) to =YEAR(B6+184), this now gives me the financial starting in July. This makes the Summary much more useful.
                                  arrow_drop_uparrow_drop_downReply reply
                • Kerri Levitanus
                  Hello, wondering how everyone handles fees to move Crypto to cold storage. Is this considered a sell? Or a reduction of the related buy quantity? Or something else.
                  arrow_drop_uparrow_drop_downReply reply
                  • Soni Excel Solutions
                    Hi Kerri,

                    I am not sure how people handle this. I think it can be considered as a sell of transaction fee quantity for $0. Alternatively that coin quantity can be increased for a sell or reduced for a buy transactions.

                    Regards
                    Soni
                    arrow_drop_uparrow_drop_downReply reply
                  • Summer B.
                    Hi,
                    I love this product! Are you able to add live currency feeds that can be refreshed somewhere on the summary sheet so that gains/losses can be compared when the "refresh all" button is clicked?
                    arrow_drop_uparrow_drop_downReply reply
                    • Summer B.
                      I would also like to see a column labeled "notes" next to each transaction. I would use this column to note any important detail about the transaction like where the transaction occurred (Binance, Voyager, Coinbase, etc)
                      arrow_drop_uparrow_drop_downReply reply


                    4.6 / 5 (9 votes)

                    please wait...