Bitcoin Cryptocurrency FIFO Gain Excel Calculator with Yearly Summary for Taxation
Originally published: 12/05/2018 17:40
Last version published: 21/06/2021 07:02
Publication number: ELQ-93697-11
View all versions & Certificate
certified

Bitcoin Cryptocurrency FIFO Gain Excel Calculator with Yearly Summary for Taxation

Easy to use Excel calculator for trading gains in bitcoin, crypto and stocks using FIFO method with year-wise summary.

Description
This is an easy-to-use Excel calculator for calculation of profits in cryptocurrency trading using FIFO method. It calculates and shows the useful trading data for every transaction and summary for all trades year-wise and all combined.

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 Proceeds from sell of BTC, Total BTC Purchased, Total BTC Sold, BTC in Hand after every transaction.

Every Sell trade shows the earliest and the latest buying dates of the lot sold along with the Cost of coins sold.

You can input exchange commission for buy or sell transaction on either percentage basis or currency amount or mix of both methods.

You can create as many sheets as you require for each coin (or stock) just by copying and renaming the COIN sheet.

Sheet automatically checks for some common data entry mistakes and highlights those cells to help you easily identify and correct them.

Sheets highlights the rows in different colors automatically to help you easily distinguish between Lot Sold, Lot in Hand, Partial Sold, Sell with profit, Sell with loss, etc.

It also shows some interesting data about any prospective sell. Along with the estimated profit or loss for a prospective sell at proposed sell price, sheet will show you all the lots which will be sold with quantities, buying prices and costs. It will show this data in a separate table for each lot.

You can also see the buying price ranges and quantities of coins (or stocks) you have in hand in a separate table.

I believe that you will find this Excel calculator very handy in analyzing and keeping records of your trades and perfect in calculating the gains with 100% accuracy for taxation purpose.

This Best Practice includes
1 Excel spreadsheet,

Acquire business license for $22.00

Add to cart

Add to bookmarks

Discuss

Further information

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

Reviews


    • 4.9 / 5 (14 votes)

      • 86%
      • 14%
      • -
      • -
      • -
    • Rate this Downloadable Best Practice

      Write a review

  • Mark J. Cipolloni, PMP, PE(last updated: 06/03/2021 11:38)
  • Abraham Schemel(last updated: 23/02/2021 22:01)
  • Jeff Parker(last updated: 02/06/2020 15:24)
    Works like a charm
    I did a bit of searching for a crypto/stock tracker and came across the Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation. I wanted something to use for both crypto and stocks. I purchased the tool and it works great for both. I even reached out to Tejasvita for a few updates I was looking for and she turned it around in a day. She is responsive and did a really good job.
  • Thomas Felter(last updated: 08/03/2020 23:32)
  • Carl Johnson(last updated: 04/02/2020 03:07)
  • turboblast(last updated: 09/01/2020 02:51)
  • Deg Akal Singh(last updated: 18/07/2019 09:25)
  • Tami Sinko(last updated: 10/04/2019 02:00)
  • Ly Nguyen(last updated: 04/04/2019 04:38)
  • Brad Jackson(last updated: 20/03/2019 23:15)
  • Michelle Shackleton(last updated: 30/01/2019 17:04)
  • Sami Nagata(last updated: 19/10/2018 08:19)
  • Jamie Lee(last updated: 11/10/2018 04:02)
  • Syed Gardezi(last updated: 13/07/2018 13:03)

keyboard_arrow_downShow all


More Best Practices from Soni Excel Solutions

keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Bitcoin Cryptocurrency FIFO Gain Excel Calculator With Yearly Summary For Taxation

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

  • Jeff Parker
    Great tool. Thanks for putting it together. There are a few things I would love to see added. I use this for both cryptos and stocks. In the Summary tab it would be great to see not only the current balance held but also the cost basis for those held shares/coins. It would be nice if it had another column that showed cost basis for shares held divided by # of shares held to show the current cost per share. Could you add this functionality in?
    arrow_drop_uparrow_drop_downReply reply
    • Soni Excel Solutions
      Thanks foe the appreciation and your valuable suggestion. I have made the changes for you. Please let me know, how can I send it to you.
      arrow_drop_uparrow_drop_downReply reply
    • Carl Johnson
      Hi! I just bought Bitcoin Cryptocurrency FIFO Gain Excel Calculator How can I get the latest version? I have version 7
      arrow_drop_uparrow_drop_downReply reply
      • Soni Excel Solutions
        Sir, this is the latest version.
        arrow_drop_uparrow_drop_downReply reply
        • Soni Excel Solutions
          Actually, for every minor update, Eloquens system reviews the file and considers it as new version. Next time , when I will upload the updated version, I will suffix the version number with file name in same manner as followed by Eloquens to avoid any confusion.
          arrow_drop_uparrow_drop_downReply reply
    • Life span
      Is there a way that I can determine if the coins sold have been held for 12 months or more?
      arrow_drop_uparrow_drop_downReply reply
    • 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
      • Soni Excel Solutions
        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
        1arrow_drop_uparrow_drop_downReply reply
      • Peter Maguire
        Hi - I just bought Bitcoin Cryptocurrency FIFO Gain Excel Calculator for Taxation
        by Tejasvita Soni

        Version 1 (Original Version): 12/05/2018 17:40 GMT
        Version 2: 13/07/2018 06:23 GMT
        Version 3: 20/07/2018 11:43 GMT
        Version 4: 08/11/2018 11:39 GMT
        Version 5: 04/12/2018 13:27 GMT
        Version 6: 13/02/2019 08:32 GMT
        Version 7: 31/03/2019 06:45 GMT
        Version 8: 04/04/2019 11:04 GMT
        Version 9: 07/04/2019 14:02 GMT
        Version 10 (Current Version): 24/10/2019 11:20 GMT
        Publication Number: ELQ-93697-10


        Its fantastic - The portal provided version 7 for Excel - How do I get the latest version. - I messaged you also
        arrow_drop_uparrow_drop_downReply reply
        • Soni Excel Solutions
          Hi,

          Thanks! Version 10 (Current Version): 24/10/2019 11:20 GMT is the latest. If you have any problems, please send your sample transactions file, so I can show you how to fill it.

          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
          • Soni Excel Solutions
            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
          • georgesgeorgiou
            I just download the Taxation calculation tool. Should be noted that the version I received is Version 7. I understand from the comments below that a Version 10 is available that could address the following issue I have. If this is the case can I please have access to the recent Worksheet and disregard my comment below?

            I noticed that there is no designation as to what sales are Short or Long Term (ST / LT). Without this feature and without special attention given a particular sale that could be both partly ST and LT in nature the tool as a "taxation aid" is unfinished and the user could be misreporting sales.

            The issue you may face Tejasvita is that different jurisdictions have varying time ranges for what destinates LT or ST. You would need to add a cell that the user manually changes. For example, after hold for x days designate sales as LT.

            Looking forward to your quick reply.
            arrow_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
            • Nhon Ma
              Very helpful tool. Would be nice to have the average cost after several BUY/SELL transactions tough...
              arrow_drop_uparrow_drop_downReply reply
              • North Remembers
                Hey Soni
                Would you be able to do a customized version of your tool for an additional fee?
                arrow_drop_uparrow_drop_downReply reply
                • Soni Excel Solutions
                  Hi,

                  Yes, I would be happy to customize it as per your requirement. Please let me know more details.

                  Regards
                  Soni
                  arrow_drop_uparrow_drop_downReply reply
                  • North Remembers
                    That's wonderful, thank you!
                    Can we take the discussion into private (e.g. email)?
                    I've got a few things I'd like to see added and I know a few people, who'd be willing to pay for an upgraded version.
                    arrow_drop_uparrow_drop_downReply reply
                    • North Remembers
                      Soni
                      What's your email?
                      Better yet, can you message me on telegram @NorthRem, please - it will be more efficient to communicate that way.
                      Thank you.
                      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
                  • Soni Excel Solutions
                    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
                  • Michael Klein
                    Hello,
                    Thank you for the great tool! It works very nice!

                    I have only one question:
                    In Germany, there is no FIFO-Tax on Coin that been held for more than 1 year. How can I switch the calculation to show FIFO Gains only for Coins that I sold under one 1year?

                    Thank you very much!
                    arrow_drop_uparrow_drop_downReply reply
                    • Jim Oskiballs
                      Hi Soni, thanks for the spreadsheet. Looking at column U on the BTC sheet (FIFO Gain USD), it looks like the gain shown in each row is cumulative through the latest sale? I need to track and report the FIBO gain/loss and cost basis for EACH sale individually. It should be a simple modification but before I break it I wanted to ask your thoughts. How would you recommend I modify the sheet? Thanks!
                      arrow_drop_uparrow_drop_downReply reply
                      • Soni Excel Solutions
                        Hi Jim, It is easy. To find gain/loss for each sale individually, you can subtract cumulative gain shown in the row of previous sale from the cumulative gain shown for current sale. If you need further help, please let me know.
                        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
                      • Ram Mohan
                        Can i use this for a custom FIFO calculator of stocks not in the open market? Is there a demo version of the sheet that I can use to see if it fulfills my need or is there a refund policy if this does not solve my needs?
                        arrow_drop_uparrow_drop_downReply reply
                        • Soni Excel Solutions
                          Yes, you can use this calculator for Crypto, Share, Forex or any other stocks where you need to find gains/loss on FIFO basis. For stocks you can also use Multiple Equity Stock FIFO Excel calculator in which you can place transactions for multiple stocks in single sheet.

                          There is no demo version or return policy. You can go through product description and youtube video. Please ask me if you have any questions.

                          Thanks!
                          arrow_drop_uparrow_drop_downReply reply
                        • *del* Training Basket
                          * comment deleted *
                          arrow_drop_uparrow_drop_downReply reply
                          • Alexander Levshin
                            Hello, great file, thanks! I was wondering if it is possible to add automatically separated per year results for FIFO/LIFO on summary page?
                            arrow_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
                              • Soni Excel Solutions
                                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
                              • Soni Excel Solutions
                                * 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
                                • Salil Gangal
                                  Hello,

                                  I used following details:

                                  BUY 3-Jun-21 2.0000 1.00
                                  BUY 4-Jun-21 2.0000 1.00
                                  SELL 5-Jun-21 -3.0000 2.00
                                  BUY 6-Jun-21 2.0000 1.00
                                  SELL 7-Jun-21 -3.0000 2.00

                                  For the first Sell the FIFO gain is 3 as expected. However for the second Sell the FIFO gain is coming-up as 6. (I was expecting it to be 3.)

                                  Can you please help?

                                  My email is : salil_gangal@yahoo.com

                                  Thanks.
                                  ~ Salil
                                  arrow_drop_uparrow_drop_downReply reply


                                4.9 / 5 (14 votes)

                                please wait...