Free Bitcoin Trading Profit/Loss MS Excel Calculator for Crypto Traders

This is a simple bitcoin trading profit/loss calculator app to help you know the number of units of a coin you bought that you should sell at a particular price level in order to pull out your investment capital plus a little extra percentage profit in Microsoft Excel. It will also help you calculate the unit price of the coin after a certain percentage loss, so as to help you know the unit price value to use as your stop-loss. You will also learn how to manually calculate the Satoshi value that will yield any percentage profit/loss in this article.

How to Use the Bitcoin Trading Profit/Loss Calculator Excel Spreadsheet Template

One of the recommended crypto trading practices is to calculate percentage profit or the unit price of the coin in Satoshi (or in BTC) that will give you a certain percentage profit. Since crypto trading is highly volatile and price of coins can move either up or down, you also need to calculate the highest percentage loss that you can tolerate in that trade, above which you quit the trade (say 5 or 10%). For long term trades, you might not want to sell all your bags at a particular profit level. You might decide to calculate the number of units of the coin to sell in order to recover your investment capital, alongside a little profit. Then you allow the rest to ride. Once you have got all these parameters, you now go ahead to set a sell order in your exchange app.

NOTE: This bitcoin trading calculator assumes you didn't buy at more than three (3) price levels. If you bought at more than three price levels, then you need to calculate the average unit price using the cumulative average formula (i.e. Your Total Investment Capital divided by Total Units of the Coin bought) before using this calculator. You also need to calculate the total number of units of the coin you bought.

This calculator also works only for BTC trade pairs. For example, ETH/BTC, XRP/BTC, NEO/BTC, ZEC/BTC, etc.

Here is a simple challenge you can solve with this calculator: 
Assuming you first bought 100 units of a coin, say Akropolis (AKRO) at 150 satoshi (0.00000150 BTC). Then the price came down further to 120 satoshi (0.00000120 BTC), you bought another 100 units. Also the price tanked further to 100 satoshi (0.00000100 BTC), you bought another 100 units, how do you know the number of units of this coin to sell in order to extract your capital plus 10% profit after the price of the coin has doubled? Also, what will be the unit price of the coin at 5% loss?

The parameters you need to enter are: 
  • Coin name (e.g. AKRO)
  • Unit entry price (in BTC) of the coin and the Number of units of the coin bought at each buy level (first buy, second buy and third buy, as the case may be). If you bought the coin only at a particular price, use only the For First Buy section.
  • Capital pull out percentage profit level (e.g. 100%). Long term traders always pull out out their capital after the price of a coin has risen to a certain percentage. If you wish to pull out your capital after the price of the coin has doubled, then you enter 100 for 100%.
  • Extra percentage profit to pull out alongside your total investment capital (e.g.10%)
  • Maximum Percentage Loss to Tolerate (e.g. 5%). Beyond this percentage loss, your stop loss will trigger.
The bitcoin calculator will calculate the following: 
  • Total Investment Capital (in BTC)
  • Cumulative Average Unit Price (in BTC)
  • Total Number of Units of the Coin you Bought
  • Number of units of the coin to sell in order to recover your capital plus the specified extra capital. 
  • Selling Price to Sell the Coin (in BTC) in order to meet your target.
  • Number of units of the coin that will remain after you have pulled out your capital plus some profits. 
  • Unit price of the coin at a particular percentage loss (in BTC). This is to help you know the value to use when setting stop loss in your exchange app. Once the price of the coin goes beyond this price, your stop loss should trigger. You sell off the coin and bear the loss.
See:
Below is the screenshot of the excel calculator with the solution to the above challenge (the units are in Satochi).
crypto trading profit loss calculator app
From the screenshot, if you sell 165 units of the of the coin, you will recover your total investment capital plus 10% extra profit. 

Then you will 135 units of the coin left in your bag.

At 5% loss, the unit price of the coin will be 117.17 Satoshi = 0.0000017717 BTC.

NOTE: The calculator app was recently updated so that the units are now in BTC. See the screenshot below.
crypto trading profit loss calculator app
If you are a long term bitcoin trader, you will appreciate this bitcoin trading calculator. 

The excel workbook has two sheets: Crypto trading calculator and Crypto trading records.

Crypto trading calculator sheet contains the profit/loss calculator. If you wish to use the calculator to calculate the percentage profit/loss of another coin, simply click the Reset button and then enter the required parameters. 

Crypto trading records sheet is where you can record the important details of each of your trade. Make sure you save the spreadsheet after entering a new record in that sheet before closing the excel app.

NOTE: 
Ignore any error displayed in some cells while entering the parameters. Once you provide all the required parameters, the errors will disappear.


NOTE: 
Once you download the spreadsheet template, upload it back to your own google drive. This is to ensure that any record you add to the spreadsheet will be saved automatically and you can access the spreadsheet template easily.

If you are finding it difficult to use the spreadsheet app, you can use the web app version, Free BTC/USDT Trading Pairs Profit/Loss Calculator App.

To learn more about cryptocurrency trading, check out this book on Amazon - How to Make Money from Crypto Trading & Investing for Beginners: Complete Guide on How to Buy & Sell Bitcoin & Altcoins for Profit, DeFi Yield Farming with Cryptocurrency Investments & Security Tips. The book also explained how to calculate profits for other crypto trading pairs like ETH, NEO, BNB, KCS, USDT, etc. both manually and with Excel spreadsheet.

How to Calculate Crypto Trading Profit/Loss Manually

Make sure you note down your entry price for any coin you buy. You may also need to know your total investment capital. Once you know these 2 parameters, you can calculate the unit price that will yield any percentage profit manually.

I will show you two ways to calculate your crypto trading profit manually. One is through financial mathematics concept, while the other is a shortcut.

Method 1: Business Mathematics Concept

From the knowledge of business mathematics;
Unit Profit = Unit Selling price - Unit Cost price

In crypto trading, your unit cost price is your entry price, while your unit selling price is any price at which you wish to sell your cryptocurrency. Also note that if the value is negative, it means you sold at a loss.

Percentage Profit = ((Unit Selling Price - Unit Cost Price) / Unit Cost Price) * 100

Total Investment Capital = Unit Cost Price * Number of Units bought; Note that the trading fee is assumed negligible here.

The Unit Selling Price to Sell a coin in order to Earn a Desired Percentage Profit is calculated as:
Entry Price + (Desired Percentage Profit * Entry Price)

Example: If you bought 100 units of a crypto coin at 150 Satoshi, at what Satoshi value will you sell it to make 20% profit?

Solution

Entry price = Unit cost price = 150 Satoshi
Desired percentage profit = 20% = 0.2

Total Investment Capital = Entry price * Number of unit bought
150 Satoshi *100 Units = 15,000 Satoshi

Unit selling price that will yield 20% profit
= 150 + (0.2 * 150) = 150 + 30 = 180 Satoshi

So if you sell the 100 units of the coin at a unit selling price of 180 Satoshi, you will make 20% profit.

NOTE: 
1 Satoshi = 0.00000001 BTC
Therefore 180 Satoshi = 180 * 0.00000001 BTC = 0.00000180 BTC

See:

Method 2: Crypto Profit/Loss Calculation Shortcut

If you find the first method a bit cumbersome, here is an easier way to calculate percentage profit/loss in crypto trading.

For the above example, we can calculate the unit selling price that will yield 20% profit using this shortcut:
1.2 * 150 = 180 Satoshi or 0.00000180 BTC

The same applies for any other percentage profit.
  • For 10% Profit: Multiply your entry price by 1.1
  • For 30% Profit: Multiply your entry price by 1.3
  • For 50% Profit: Multiply your entry price by 1.5
  • For 100% Profit: Multiply your entry price by 2
  • For 200% Profit: Multiply your entry price by 3
  • For 900% Profit: Multiply your entry price by 10
  • For 1000% Profit: Multiply your entry price by 11 ....etc.

Crypto Trading Percentage Loss Calculation Shortcut

Here, you want to know the unit price of the coin after a certain percentage loss. This will help you know the Satoshi value to use when setting your stop loss in your exchange app.

For example, if you bought a coin at a unit price of 120 Satoshi and the price tanked by 5%, the unit price of the coin after the 5% loss is:

(1 - 5%) * 120 Satoshi =  95% * 120 Satoshi = 0.95 * 120 Satoshi
= 114 Satoshi = 0.00000114 BTC

You can use the same concept for any other percentage loss.
  • After 10% Loss: The Unit price of the coin will be (1 - 10%) * Unit entry price in Satoshi
  • After 30% Loss: The Unit price of the coin will be (1 - 30%) * Unit entry price in Satoshi
  • After 50% Loss: The Unit price of the coin will be (1 - 50%) * Unit entry price in Satoshi
  • After 100% Loss: The Unit price of the coin will be (1 - 100%) * Unit entry price in Satoshi
Check:
Knowing how to calculate your profit or loss in cryptocurrency trading is very essential. It will help you manage your portfolio and also know when to exit a trade; either for profit or loss. I shared how you can use our simple MS Excel Bitcoin trading calculator to calculate your crypto profit/loss. I also shared how you can calculate your trading profit/loss manually without any calculator.

If you wish to learn how to trade cryptocurrencies and make money, grab this book: How to Make Money from Crypto Trading & Investing for Beginners

Always trade on a secure exchange platform to avoid losing your capital. Here are some secure exchange platforms for crptocurrency trading.

I know you have learnt a new thing from this article. Share this article with your social media friends by clicking any of the share buttons below. 

Drop your comments below. Also click Here to subscribe to our blog via email so that we will update you when new WordPress themes are released by these sites. 

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!