How to calculate your Bitcoin gains and taxes with Luno

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 (hopefully for a handsome gain).

Many people, myself included, found themselves in a peculiar situation: we made some money (not crazy amounts, but enough to start worrying about our tax compliance status), and even if we don’t always love paying over taxes, it’s a whole lot easier to do so than going to jail.  The 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, Britain 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. ☕️

Requirements:

• Ability to sign in to your Luno account
• A Google account (to crunch your trading data in Google Sheets, but feel free to use Excel or any other spreadsheet)
• A lot of patience
• Reading this article (if you’re from SA): SARS’s stance on the tax treatment of cryptocurrencies

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 due to other circumstances (like age and income). You might need to register for provisional filing – where you need to file more than once per year – or maybe you can get away by just doing so once a year. You might be eligible to make certain 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.

Since so many people asked: I have been very happy with the South African Business Accounting Network. I filled out a form on their site stating my accounting needs and they matched me with a professional accountant that I’ve used for years now (she’s unfortunately at-capacity and not looking for more clients). Since it’s a network of accountants, results might vary, but you can always ask for a replacement. My favourite part was that all of their accountants were able to meet in person or –even better for me– online and that I have been able to do everything via email ever since.

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

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 Luno 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 types (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:

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 data in the sheet
2. Click the add filter button (or select Data > Filter in Google Sheets)
   
3. Select the filter dropdown caret on the Description column
4. Select Filter by condition > Text starts with > Bought > OK
   
   This 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.
   

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

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:

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

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, like me, 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 (some charged in a local currency, others in digital currency which we’ll need to convert to 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 probably still good to know how much you’ve moved around in the year. If other people sent you crypto in exchange for goods or services rendered (i.e. as a payment for something you did or sold) or vice versa (you paying them), then you will need to separately mark those send and receive transactions, as they could be considered income or expenditure.

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 wallet (say, ETH, ZAR, MYR, EUR 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.

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 quarterly (or annual) accounting, I simply change the format to show the month. Note that this still retains the detailed timestamp when you click on the date, 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 (or similar) and click apply

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 wallet’s tab) by hitting CTRL-A twice.

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

Use the following:

• Add Transactions as the Rows
• Add Currency as the Columns
• Add Balance delta as the Values (summarised as Sum — meaning they are added together)

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 should look.

Fix the fees

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

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 levied 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:

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:

This means I paid an equivalent of ZAR 1.52, ZAR 124.44 and ZAR 0.06 in fees on those three dates. But since they’re in text format (with other information in the field), I can’t easily add them up.

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:

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)

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:

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:

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 these formula hacks 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
  
  

 

• 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
  
  

You should now see the summary of the fees in local currency 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, you’ll deduct it).

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 in local currency 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 (0.0002 BTC at the time of writing). 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 deposits, I’m just disregarding them, since it’s too much of a headache for me to calculate and deduct these trivial amounts.

In summary

Add the sum of what you received in local currency, like when you sold digital currency, received promo amounts of money or got paid for selling goods or providing a service (income) and deduct from that the sum of what you paid to acquire it, the purchase price, trading fees, deposit fees and so on (expenses). The difference between the two is either a reportable income/loss (or capital gains/loss).

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. Click on File > Save a copy to edit it with your own info.

 

Some final thoughts

Again, speak to a 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.
• You may end up with a scenario where you sold more crypto in a tax year than you have bought (e.g. sold 2 BTC for a lot of local currency but you didn’t buy any or bought a small amount). If you’ve never accounted for those, you will need to repeat the same thing for the previous tax year (i.e. go and figure out the purchase price of the asset). If you don’t do that, your bookkeeping will show that you sold 2 BTC (for a lot of money, I hope) but that there is no acquisition price (in other words: zero) which translates into a large taxable gain. Go figure out what the purchase price of your crypto was.
• You may also end up with a scenario where you bought exclusively in a tax year (or just bought more than you sold). This will likely mean that you will see a large trading loss carried over to the next year. When you sell in the following tax year(s), you will already have accounted for the purchase of your crypto and the losses from this year will be carried over and deducted from your gains in the following year.
• 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 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 (“selling yesterday’s bitcoin today” $10,001 – $10,000) but that you made $9,901, as per FIFO (“selling my old bitcoin today” $10,001 – $100).
• 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. Somewhat related: note that it seems the South African Reserve Bank isn’t comfortable with digital assets being sent from a South African platform (like Luno) to an international one (like Binance).
• 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 probably 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 buying coffees using Bitcoin.
• If you are a Bitcoin miner in South Africa, 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 –say, 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 rate 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 (trading buys), the disposal price (trading 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!