How to calculate your Bitcoin gains and taxes with Luno

H

This is a work in progress. Please send me feedback if you have any questions, updates or spot any mistakes. I’ll update this and the demo spreadsheet as things evolve.

Odds are that if you’re an early Bitcoin adopter – tinkering, investing or otherwise getting involved in the industry, anytime before October 2017 – your digital tokens have probably increased quite a bit in value. And, unless you really have HODL nerves of steel and managed to ignore all the sensational and panic-inducing news, you probably sold some of it for a handsome gain.

Many people, myself included, found ourselves in a peculiar situation: we made some money (not crazy amounts, but enough to start worrying about our tax liability), and even if we don’t always love paying over taxes, it’s a whole lot easier than going to jail.  Problem for most of us is that we’re just not sure how much (or little) exactly we’ve made.

This article is probably of most use to people in South Africa, where I’m from, but people from Luno’s other countries of operation –Malaysia, Nigeria, Indonesia and the Eurozone–  may also find it useful. I’ll include some extra information on how to make those calculations if you’ve also traded on other platforms at a later stage.

This is going to be a long piece. Grab yourself a cup of coffee and follow through until the end.

Required:

Step one: Hire a professional

I’ll start with a big disclaimer: this article is not legal, financial or tax advice. These are my personal opinions and experience. You should, like me, employ an accounting and/or tax professional to help you stay compliant. It’s all good and well to read informative articles about how to treat a wound, but you would still see a medical professional if it was severe enough.

Every person’s situation, income and therefore tax treatment are technically unique. You might fall into a higher or a lower tax bracket. You might need to register for provisional filing – where you need to file more than once per year – or maybe just once a year. You might be eligible to make deductions. You might need to pay capital gains tax (unlikely) or income tax (very likely). Get a professional with experience in digital currencies to help you make sense of your tax affairs. If you need a recommendation how to get started, send me a message.

Download your statements

You can now export your trading statements for a defined period of time, on each of your digital and local currency wallets, which makes the whole process a whole lot easier. This is, at the time of writing, only available from the Luno website (not the mobile app).

Luno wallet statements

  1. Navigate to your Luno account, select Wallets > BTC wallet > Transactions > Download statement
    (Or go here: https://www.luno.com/wallet/statements)
  2. Select the time range. The South African tax year runs from the first day of March until the last day of February each year, so I’ll select that.
  3. Repeat this process for each of the wallets you have. These may include your local currency wallets (ZAR, EUR, MYR, NGN, IDR and SGD) and your digital currency wallets (BTC, BCH and ETH).
  4. If the files are in .ZIP format, you’ll need to extract them first to get the .CSV (comma separated value) files

Import and fix your statements

You are welcome to use any other spreadsheet tool (MS Excel, Apple Numbers, LibreOffice). I simply use Google Sheets because it’s easier to share things with my accountant this way.

The statements have most of the information we require, but there are a few (annoying) things we’ll need to manually calculate. These include the transaction type (so that we can filter for all “Bitcoin send” transactions, for instance), and the local currency value of the fees we incurred (since these may be tax deductible).

Note: you can compare your spreadsheet with this demo sheet.

Importing your BTC wallet statement

  1. Navigate to sheets.google.com
  2. Create a new blank spreadsheet
  3. Give the spreadsheet a snappy title (top of page)
  4. Rename the tab (at the bottom of the page) to BTC
  5. Navigate to cell A1 in the current tab
  6. Click File > Import > Upload
  7. Upload your first .CSV file (not the .ZIP ones)
  8. Select “Replace data at selected cell” (cell A1)

Google sheets should give you something that looks like this:
Bitcoin trading sheet

Making sense of the BTC transaction descriptions

As mentioned above, Luno doesn’t have a field for what transaction type you completed. They give a text description of each transaction (like “Sold 0.000596 BTC/ZAR @ 17,219”) but not a field that just says “Sell” for all transactions where you sold some BTC. So, let’s add them manually.

  1. Select all the trading data
  2. Click the add filter button (or select Data > Filter)
    filter
  3. Select the filter dropdown caret on the Description column
  4. Select Filter by condition > Text starts with > Bought > OK
    Google sheets filterThis will only show you a list of all your BUY transactions.
  5. Insert a column to the right of Description and call it Transaction
  6. Copy-and-paste BUY into all the visible fields down. This will allow you to filter the Transaction column in future to isolate only your BTC purchases.
    BUY transactions

Here’s a quick animation, if you’re still stuck:

Filtering

Repeat this process for your SELL transactions, by changing the filter in the Description column to text that starts with “Sold”. I’ll call this transaction SELL:
SELL transactionsKeep checking your Description field (you might have to disable and reenable the Filter by clicking it twice) and add a transaction type for each line item.

Note that If you entered a description in your Luno transaction, say, when you sent money to a hardware wallet and you gave it the description “Savings to Trezor”, it won’t start with the “Sent Bitcoin to xxx” text string. Yes, you will need to manually review all of your transactions. Not ideal.

You may end up with following transaction types:

  • BUY
  • SELL
  • TRADING FEE
  • SENT
  • SENDING FEE
  • RECEIVED
  • RECEIVE FEE
  • PROMO INCOME
  • RELEASED
  • RESERVED
  • DEPOSIT
  • DEPOSIT FEE
  • WITHDRAWAL
  • WITHDRAWAL FEE

The most important things are buys and sells (acquisition and disposal of an asset in a local/fiat currency) and the relevant fees you might be able to deduct (also in a local currency). I completely disregard reserved and released payments. Deposits, withdrawals, send, and receive transactions aren’t taxable events if it’s just the movement of your own capital or trading stock, but it’s good to know how much you’ve moved around.

Repeat for all other currency statements

Add additional tabs at the bottom of the page by clicking the plus button for all of the wallets you have on Luno. Give it the name of your second currency (say, ETH, ZAR, MYR etc.) and repeat the process we did on our BTC wallet above by a) adding the trading data, b) filtering the description field and c) adding a transaction type for each transaction.

Add tab

Once done, we’ll be able to filter for the transaction types on each wallet.

Fix the date

Luno trading statements give very detailed timestamps, which is handy if you’re an active trader, but can make for tricky filtering if you’re using a big time range. Since I do monthly (or quarterly) accounting, I simply change the format to show the month. Note that this still retains the detailed timestamp, it just displays it in a bit more accountant-friendly format.

  1. Select all of the fields in the Timestamp column (column C)
  2. Format > Number > More formats > More date and time formats
  3. Select the “Aug-1930” (MMM-YYYY) format and click apply

Filtered date

You’ll now be able to play around with the data even more, by filtering, say, only the BUY transactions in March, 2017.  If you want to earn your Bitcoin black belt, you’ll need to learn how to use pivot tables, however. So…

Let’s pivot!

Select all the fields in your first wallet tab (say, your BTC trades) by hitting CTRL-A twice.

Select Data > Pivot table. You’ll be taken to a new tab where we’ll create and the pivot.

Use the following:

  • Add Transactions as the Rows
  • Add Currency as the Columns
  • Add Balance delta as the Values

Pivot details

Optionally, you can add a filter on Timestamp to show only a certain month, instead of data on your entire year of trading. Again, look at my demo sheet to see how it could look.

Fix the fees

You should now see a summary of your BTC trades that might look like this:

Fees pivot

This is already a nice summary of our trades. This only solves half of our accounting problems, since all the values are in BTC. It’s great to know that you bought a total of, say, 4.12 BTC over the past year, but you still don’t know what you paid (in your local currency for it). Let’s take it step by step.

First, we need to focus on the fees. It’s nice to see a summary of the fees you paid, but again, they are in BTC and you can’t do proper accounting in digital currency, you’ll need to get a fair price for it in your local currency. You can also, depending on your tax situation, deduct fees as a trading expense from your trading income – speak to your accountant.

Go back to the tab with the BTC trades and filter in your Transaction column for the transaction types that incurred a fee and hit OK:

Fees filter

You’ll notice the sheet has a column for “Value” (column M on my sheet) provided by Luno, which shows a text summary of what the fee was that you paid, calculated as the amount of BTC at the exchange rate at the time of trading. This is what it shows on three hypothetical trading fee transactions:

Fee values in spreadsheet

Add a column to the right of “Value” and call it “Trim” (you might need to disable your filter first). Use the following formula to trim out the trailing space and bracket (basically, everything after the local currency amount), where field M20 is our Value column, below:

=TRIM(LEFT(M20,FIND("(",M20)-1))

Your Trim column should look like this:

Trim field

Now, give column O the title of Prepend, where we’ll remove the prepended currency information. Since they are all 5 characters (one tilde, three for “ZAR” and one for the trailing space), we can use the formula:

=RIGHT(N20,LEN(N20)-5)

Prepend field

Note that you might need to change the data in Prepend to numbers, since Google Sheets might still read it as text.

Create (yet another) column called “Local currency value” (column P). Copy all the fields in Trim and using paste special to paste the values only in the Local currency value column:

Paste special

You will also need to reformat it as number by selecting all the fields in Local currency value and selecting Format > Number > Number.

It should now look something like this:

Local currency value

If you only have a handful of these “Value” descriptions, you might find it faster to just write or copy them out manually, rather than using this manual hack of mine.

Pivot your fees

Note that this is how to create a pivot just for your fees. My demo sheet has these incorporated into a single view, but it is still useful to know how to do it.

Since we added some columns (N, O and P) it’s a good idea to go and remove the filter and make sure it is applied on all the new fields.

Select all the data in the sheet once more and click Data > Pivot table again.

Set your pivot table up with the following parameters:

 

  • Transactions as the Rows
    Pivot of fees

 

  • Balance delta and Local currency value as the Values
  • Optionally add a filter to only show the relevant transactions, by excluding blank values for Local currency value
    Remove zero values

You should now see the summary of the fees you can (probably) deduct from your trading income. Note that you might want to manipulate your data to show your local currency values as negative numbers (since it was an expense you paid).

Fee pivot final

The SUM of Balance delta will show the amount of BTC you paid in fees. The SUM of Local currency value will show how much that BTC was worth at the time of the trade.

Note that there isn’t (at time of writing) an easy way to see the local currency value of your BTC deposit fees. You might have to use the average BTC/ZAR exchange rate on the date the deposit fees were charged, if you had a large amount of these. Since I had very few, I’m just disregarding them, since it’s too much of a headache for me to calculate these trivial amounts.

Demo sheet

As mentioned many times above, I created a demo spreadsheet to show you what the completed document might look like. Have a look at it and fiddle with the formulas.

Completed demo sheet

 

Some final thoughts

Again, speak to your tax professional, but here are a few things I’ve found from conversations with tax professionals in different countries:

  • Generally speaking, if you buy something – anything – and sell it at a higher price, you will be taxed on the gains. Discuss your situation with your tax professional. Disagreeing with what happens with the taxes you pay is a political discussion. Not paying taxes is a very different legal or criminal discussion.
  • This article focused on trading gains, but trading losses are also reportable. You might be able to offset other income tax (say,  income tax paid from your salary) when you incurred trading losses. Ask your accountant.
  • Some countries allow you to do inventory management using either first-in-first-out (FIFO) or last-in-first-out (LIFO) methods of pricing. You’ll probably have to use FIFO, but ask your accountant. If, for instance, you bought your first 1 BTC on 1 January 2015 at $100, your second 1 BTC on 1 January 2017 at $10,000 and then sold your first 1 BTC on 2 January 2017 at $10,001, you probably can’t claim that you only made $1 in trading gains as per LIFO ($10,001 – $10,000) but that you made $9,501, as per FIFO ($10,001 – $500).
  • Sending and receiving money you own (say, between your cheque account and your savings account, even if at different banks) are not taxable events. Sending and receiving your own digital currency, say, between your Luno wallet and your hardware wallet or another exchange, are not considered taxable events.
  • Local currency (ZAR, EUR etc.) deposits and withdrawals from your bank account to a shares trading platform (EasyEquities, RobinHood etc.) are not taxable events. Making deposits and withdrawals to and from Luno are not considered taxable events.
  • The date when you bought and sold assets (like Bitcoin) are important events when it comes to calculating gains or losses (and therefore taxes). When you purchase Bitcoin you incurred an expense (the cost of the digital currency including the fees to acquire it). When you sell that Bitcoin, you may realised an income or a loss (minus the trading fees). The difference between these income and expenses is how much you made (or lost) and how much income tax you need to pay.
  • When you trade on equity trading platforms, there are certain deductions you might be able to make (which add to the cost of acquiring and/or disposing the asset and reduces the taxes you need to pay). Trading fees are generally considered deductible. Speak to your tax professional about these and about other fees like custodial fees (keeping your digital currency securely stored) or transactional fees (fees levied by banks, digital currency platforms or the Bitcoin network for sending and receiving money or making deposits and withdrawals).
  • Generally speaking, If you purchase and sell equities on a trading platform with the goal of realising short term gains (usually seen as anything less than three years in South Africa), income tax rates will apply. If you bought some equities with a “get it and forget it” mindset and sold it more than three years later (without any other active trading in-between), capital gains tax (or CGT) will apply. CGT is usually at a lower tax rate than income tax. Speak to a tax professional if you really think you can prove that CGT applies to you (if you bought Bitcoin in 2013 and sent it to a hardware wallet and you didn’t touch it for many years before disposing of it, you might have a case). Odds are that if you are reading this, you are a trader or speculator and that income tax will apply.
  • If you bought Bitcoin and used it to buy something (say a car), that date will be considered the disposal of your Bitcoin.  It is still considered a taxable event. These fall under the Sars “barter rules”. You will need to price the disposal of the BTC at a “fair market value”, which will probably be the price that Bitcoin was trading at at that exact time. This might be easy to calculate on a big ticket item like a car or a house, but it can quickly become a nightmare if you made lots of small daily purchases using Bitcoin.
  • If you are a Bitcoin miner, you will need to pay income tax. Again, please read SARS’s stance on the tax treatment of cryptocurrencies.
  • If you purchase or dispose of items in a foreign currency –buying software with US dollars overseas and selling it locally in South African rand– Sars allows you to use spot rates at either the end of the day of the transaction or the average rate at the end of the month of the transaction. You should, however, use the one or the other exchange rate consistently throughout your books. With the recent rand-to-dollar volatility, many businesses make their calculations using both the daily and monthly rate and see which one has the best tax advantage for them. This probably doesn’t apply to Bitcoin, which is a) incredibly volatile (so the exchange rate changes by a big amount each day, week and month) and b) unlike big local currencies like USD and EUR, the Bitcoin exchange rates isn’t provided by Sars. I suggest that you use the exchange rate at the exact time of transacting as provided by the Luno trading statements.
  • If you traded on other Bitcoin platforms in a currency pair that isn’t your home currency (say, trading the BTC-USD pair on Coinbase) you probably still have to pay income tax on the income derived. Speak to your tax professional. You’ll need to calculate the acquisition price, disposal price (trading buys and sells) and relevant deductions (fees) into South African rand, the currency where you have a tax liability.

Did you make some money with digital currency and paid your taxes? Did you experience any issues? Did you spot an error in this article? Please let me know in the comments below, or send me a private message, above 🙏

Thanks for reading!

 

About the author

Werner van Rooyen

Formerly Business Development and Marketing at the fast-moving Bitcoin Exchange, Luno (South Africa, Nigeria, Malaysia, Indonesia, Singapore and Europe). Currently travelling and doing research on a round-the-world trip.

Add comment

About me

Werner van Rooyen

Formerly Business Development and Marketing at the fast-moving Bitcoin Exchange, Luno (South Africa, Nigeria, Malaysia, Indonesia, Singapore and Europe). Currently travelling and doing research on a round-the-world trip.