I'm dumb so this is the simple easy Google sheet and formula explained for crypto traders feeling a little lost about how to calculate break-even point.
First what is break-even?
If you walked into a crypto exchange and you had $10 in your pocket and you bought $10 worth of Bitcoin and Bitcoin went up and is now worth $20. You might want to get that $10 back. So you should sell enough so that it also covers for both the buy and sell fees and you get back the exact $10 back in your pocket that you started with. You have not won, you have not lost, you have broken even! That $10 is called the principle and is an accounting term. Because your little bit of Bitcoin is worth $20 you still have about $10 worth left on the exchange (minus the fees paid). You have taken the $10 out that you risked in the first place and now the price has gone up you have taken that $10 principle back out. And you have your winnings still in the exchange to rise or fall in the future. This profit it what we call "FREE coins".
Here's the formula if you're in a hurry and can't be bothered reading. This world is full of to much information. Dump this on a Google sheet cell and it's good to go. I don't use excel. Maybe you can adapt it and post some excel formulas in the comments section below to help people!?
The Formula:
Copy & paste this code onto any single Google sheet cell formula bar and it will tell you how many coins to sell at the exchange (exactly) to break-even.
=SUM(1/(1-.1%)*((100*20)+(100*20)*.1%)/30)
The same formula as above explaining what the numbers represent.
=SUM(1/(1-sell_percent_fee)*((qty*price)+(qty*price)*buy_percent_fee)/current_price)
... and here in use in an actual example Google sheet screenshot.
Here is the link to the Google sheet to make a copy with and learn the formulas and adapt it to your own sheets:
Basic steps:
- [Click link to open Google Sheet](https://docs.google.com/spreadsheets/d/1zuZE4E4Ze8-qu5p7wpIRL5r7lDvccQMUNmP3hPrRjok/edit?usp=sharing)
- In the Google Sheet goto top menu area and in File > Make a copy....
- You should now have your own copy of these break-even examples to learn from.
It took me a long time to work this out so I hope you enjoy it. I done my head in a number of times and spent many late nights up working on this. I'm not that gifted as math but once i worked it all out i wanted to make a detailed post and share it for other.
I searched a lot of places and couldn't find any accurate answers anywhere. Something wasn't right about the formulas I was trying... they were not returning to zero! Most places around, the way people are calculating the crypto trade break-even point is close but inaccurate if you want it to be absolutely right and to break-even to exactly zero not a cent more, not a penny less. If you have a number of different buys it gets even more difficult to work out and track. For smaller trades it makes little difference, only a few cents in the dollar and people don't worry about it. It's not going to break the bank balance. But for larger trades it can be out by hundreds of dollars. If you want it to be right so that break even is ZERO every time this is the formula to get your head around!
So this is it! Just dump this on your cell.
Example 1).
=SUM(1/(1-G2)*((C2*D2)+(C2*D2)*E2)/F2)
Where G2 is the sell fee percentage charged by the exchange normally about .1%. C2 is the quantity of coins you bought, D2 the buy price, E2 the sell fee percentage charged at the exchange say .1% again. And F2 is the price at which the coin has reached on the chart when you decide you want to sell to get back the total amount of money you have put in the trade plus fees. This is called break-even.
If you bought a quantity of 100 EOS/USDT coins at $20 and the coins price has gone to $30 how many coins do you sell to break even? This is the tricky question! If you put this formula on a cell in a Google sheet it will tell you the answer.
=SUM(1/(1-.1%)*((100*20)+(100*20)*.1%)/30)
=SUM(1/(1-sell_percent_fee)*((qty*price)+(qty*price)*buy_percent_fee)/current_price)
The answer should be 66.80013347. You might decide to just round it up in your head quickly to 67 and sell that many at the exchange. Binance exchange expect only two decimal places to sell coins amounts so you would round up to say 67 or 66.81. When you go to the exchange and sell this amount of coins you will not lose any money and you will not make any money you will be at true break even (zero) if you can put exactly 66.80013347 as seen in this particular example.
Example 2).
Another simple way to reach ("close to") break even point is to add up the total amount you spent on your trade(s). Still using the above example I would have spent 100 EOS @ 20c = a total of $2000. Then just divide $2000/coin_current price. 2000/30= 66.67. So you sell 66.67 coins of EOS.
See how the answer is a little less then the first example of 66.80013347 compared to 66.67 that's because the first example covers the buy fee and the sell fee and the formula of which it is calculated. OK, so you could add a buy and a sell fee and get "closer" but it still won't be exact as in the first example.
Example 3).
This is how most people do it, they will take the total spent. =SUM(2000+(2000*.1%))/30 = 66.73
Often the fee isn't even added and is done fast. In theory slowly over a lot of trades you would wither away at the principle and have none left eventually.
Example 4).
Some might go as far as adding a sell fee and generally the way it's calculate it will be so close to correct that it will actually round to the right dollar amount for small transactions. I tested all four different ways as described here in this post and i have provided the sheet for you to make a copy. On a hypothetical transaction of one million the results of the second example is out by -$2,000. So using that way when you think you have broken even would be down -$2,000.
I put a large amount in here because there is whales out there doing big trades and they might want to know.
- Example $0.00 (correct)
- Example -$2,000.00
- Example -$1,001.00
- Example -$1.00
So there you have it.
To wind up the quickest way if you're in a hurry is to simply add up the total you have spent and add the fee and add the same fee amount again. So if you work out your fee is $20 double it to $40 and add that to your total spend and divide that by the coins current price to get the amount of coins to sell. It will only be out -$0.10c in the dollar in a trade up to $100,000 and that is nothing worth worrying about plus most common trades will be much less than this.
The reason i went into so much detail to do this was because i was making a Google sheet and wanting to track a QFL "Panic trade" 1 - 8 layered buys and 1 - 4 layered sells (to break-even), of various amounts, as the price was dropping and trying to determine what amount of coins to sell at the current price so that i was break-even and leave nothing but free coins in an offline wallet. If you are familiar with the QFL methodology i wanted to track "Hot potatoes" where you sell 30% at an all time high selling some of the FREE coins that use saved in a cold storage wallet after you had broke even!
My sheet kept not exactly equalling 0% or $0.00 dollars and each layer wasn't right. And because i intended to sell little bits as the price was rising (layering out with sells as the price is rises back to base!). It got all the more difficult to know. So each sell layer would calculate if you wanted to sell anything from 0-100% (of the total spent & fees). If you selected 50% it should recover half of the total money i spent in the trade. Then 80% would be 80% of the 50% remaining principle (total money spent). That's a post for you to search out for and make a free copy of that sheet. I find it an educational and handy tool, i use it daily. Even if i do say so myself. I put months into building it and improving it.
The sheet become multifunctional and can be used for any trade in crypto from as little as one $5 trade to up to eight buy layers and eight sell layers. The first four of the sell layers works specifically on getting the total principle invested back covering all fees going in and out. As this is the risk money you have put in the trade. That break-even money that you recover from the trade can be used in other QFL panic and so the process repeats.
Is there something I've missed? Can I improve on anything or do you know a better way to do this? I look forward to reading your comments about various ways to break even. I'm also interested to know of any mistakes i may have made and ways that you calculate break-even.
Crypto break-even formula for a Google sheet cell.
Jaffasoft ~ =SUM(1/(1-sell_percent_fee)*((qty*price)+(qty*price)*buy_percent_fee)/current_price)