With the addition of some simple price information within bitcoin wallets, the change in purchasing power of one’s bitcoins would be immediately apparent to the owner and would enable a better understanding of value when making spending decisions. In my opinion this knowledge would prompt more people to hold bitcoin and would drive adoption.
Before the introduction of HD wallets (HD means Hierarchically Deterministic and these wallets seamlessly and automatically generate a new public key each time you receive bitcoin), you had to manually generate a new public key for each and every transaction if you wanted to preserve any of your own privacy and the privacy of the Bitcoin network.
An Average Cost Calculator Template
The advent of HD wallets was a unique turn in the bitcoin space because it meant that the entire network of users benefited from a seamless feature that nobody had to think about. It just happened automatically. As a result of this new standard, everyone in the network had more privacy and security.
But what if there was a way to add a wallet feature that enabled seamless profitability to the entire network of users? Or at least allowed people to make more educated spending decisions without having to do any extra work? Well, let me show you how I propose that this can happen. I can’t wait for the day when we all look back at how we calculated the average cost of our coins manually? That was so 2017! Hahahaha.”
In order to know the exact cost of your coins, you need to know the exact amount that you paid and the exact amount of coin that you received (all the way out to the satoshi). You then divide the amount that you spent by the amount of bitcoin that you have, to get your average cost. So, here’s how a BASIC cost calculator works. Note: I am not trying to make super complex calculators that will confuse the average user. I am using basic arithmetic (addition, subtraction, and division in this case) so if you are some sort of Wall Street math genius, please take it easy with any comments you may have. This is just what I personally propose to us normal folk who want to better manage our bitcoin.
If you are a merchant, please use a system like this rather than exchanging your bitcoin transactions at the point of sale! Tools like this calculator will allow you to know exactly how much your coins are worth and when you should hold them and when to spend them. Bitcoin’s economic value lies in savings, not in selling to an exchange at point of sale to avoid a 2-3% payment processor fee. Those savings are significant, but that’s not going to change your business revenue drastically. Implementing math calculators like this will. I also believe that more merchants will have an incentive to hold onto the coins they receive when they see the increased purchasing power of BTC which will likely put even more upward pressure on the price. This is the deflationary power of sound money.
Creating Your Own Spreadsheet
To get started, you are going to need a spreadsheet of some sort. For this tutorial I am going to use a Google Spreadsheet because it’s free, makes automated calculations and can easily import a live BTC price via Google Finance. If privacy is super important to you, you can manually calculate these numbers with a pen and paper so no online entity can see how many coins you have or how much you’ve paid for them. I am only using an electronic spreadsheet to automate this process as much as possible so you can focus more on what you’re going to buy with all of your increased purchasing power.
The first thing you need to know is how to get the spreadsheet to make automated calculations. To do this, all you need to do is use an equals sign “=” When you do this, you are basically programming the spreadsheet to execute a very basic math command. We will be using this command quite a bit in this tutorial, so please feel free to experiment with this in your own spreadsheet.
You can do all of this however you want, but I have just a few columns to do everything. Again, this is all simple arithmetic from elementary school.
Box A2: =GOOGLEFINANCE(“CURRENCY:BTCUSD”) This uses Google Finance to pull a feed of the Bitcoin price every few minutes or so. This is very important because this is half of what will automatically calculate your profit/loss per coin. You can use whatever fiat currency you want in here by replacing USD with the three letter currency code of your choice. Great Britain Pounds, =GOOGLEFINANCE(“CURRENCY:BTCGBP”) Brazilian Reals, =GOOGLEFINANCE(“CURRENCY:BTCBRL”) Euros, =GOOGLEFINANCE(“CURRENCY:BTCEUR”) and so on. The spreadsheet will automatically display the exchange rates for those corresponding currencies. Do some experimenting to see the rates of multiple fiat currencies in real time. It would be awesome if Google Finance were able to pull the price of all of the cryptos from CoinCap.io so you could use a spreadsheet like this to easily trade between cryptocurrencies using this method but we will just have to wait til Google includes such a feature.
Column A4-A?: The Date – This is easy. The date of every transaction is locked in the blockchain forever, so this should be pretty easy to find if you have a record of your transactions in your wallet. Unfortunately, you will have to manually enter this for each buy and sell transaction. This is optional, but it helps to find transactions later for tax reasons or any other reason that you may need.
Column B: Fiat Currency – The spreadsheet formula for this is =sum(B4: B) This simply adds every number in this B column starting with row 4. This is where you learn the total amount of money (fiat) you have spent to acquire all of your bitcoins. I use USD, but you can enter anything you want. USD, GBP, EUR, BRL, Argentine pesos, Zimbabwe dollars or even French assignats if you want. When you accept bitcoin as payment or buy bitcoin with fiat, you will need to add that transaction to a line.
In this column, you will need to add a “-” anytime you spend fiat for BTC or receive BTC for goods/services and use a positive number when you spend bitcoin for goods/services or sell coins. I have color coded the lines to make it easier to identify acquisition of BTC (buy/receive) vs. utilization of BTC (sell/spend). GREEN boxes are receive/buy, and RED boxes are spend/sell. If you have positive or negative numbers in both columns, then something is wrong, and this will mess up your end result.
Column C: Bitcoin – The spreadsheet formula for this is =sum(C4:C) and this is just like the sum we created earlier. This simply adds all of your Bitcoin in the C column starting with line 4.
Column D: Average Cost per Bitcoin – The spreadsheet formula for this is =-(B2/C2). The formula for this is simply fiat spent divided by Bitcoin received. Note: The – (negative sign) is VERY important because this is what will give you a positive cost per coin since you are going to be dividing a negative number (fiat spent) by a positive number (BTC received)
Column E: Profit/Loss or Purchasing Power! – This is where the magic happens! The spreadsheet formula is “=A2/D2” (Current Price/Average Cost). This means that you are dividing the current price by your average cost per coin. If this number is more than 100%, then you are in the black and ready to find something awesome to buy with the world’s best sound money.
Spending Your Bitcoins
If you spend some bitcoin while the price is above your average cost, you will notice that your average cost will actually come down because you are tapping into your increased value. Note: You might notice a euphoric feeling or have a moment of clarity when you realize the potential here. Let that good feeling soak in and enjoy it because it only gets better over time.
In my example here I currently have an additional 541% of purchasing power (you need to subtract 100% for your initial investment) which means that bitcoin has enabled me to purchase 541% more of whatever I buy. 541% more groceries at Whole Foods, 541% more computer at NewEgg, 541% more of whatever goods and services you need and want. This is the true economic power of bitcoin and why it’s not just some dumb internet money. This means that a $250 Playstation4 would only cost $38.97 …plus tax. Just to put this further into perspective, 20% annual gains in the stock market is considered a very good return on investment.
Bitcoin’s Network Effect on Purchasing Power
If millions of bitcoin users around the world implemented this practice, it would further stimulate the deflationary nature of bitcoin. It would enable more people to make educated purchasing decisions, stimulate more people holding their bitcoins — and more people would see purchasing power gains far greater than 541% but rather thousands and even tens of thousands of percentage gains in purchasing power. That is why I believe that it’s so important for wallet developers to build this functionality into every wallet on the planet. So the entire Bitcoin network can benefit from more people holding their coins instead of selling them to an exchange so they can just spend fiat.
It is important to note that this calculator is not perfect because after receiving/buying low and spending/selling high, your average cost per coin will actually fall below $0 and so the math presented here will change and start to give you all sorts of crazy numbers that won’t make sense. This will probably require some sort of IF…THEN… command to provide a positive number or negative number. I will work on some sort of workaround for this issue. If you know how to fix this, please leave a comment below.
Disclaimer: My example shown is not the best example because I used the price from the 1st of every month, but the price often fell lower than on the first of the month so you would be better off placing manual purchase orders so you could potentially end up with even more purchasing power than what is shown above. Yes, I said, even MORE, PURCHASING POWER than 541% gains.
Let us know what you think about using simple math like this to increase the network effect of bitcoin’s purchasing power with a comment below.
Images via Shutterstock
How much do you want to know? Bitcoin.com has live data feeds with the latest world price indexes and trends (in three currencies) plus statistics on all the interesting facets of the bitcoin network.