I'm looking at your excel, but what am i seeing here?
I don't see the difficulty being brought into your equation to begin with (and inserting this is a no-brainer....). I see a column "BTC / Mean Hash Rate" in your DailyBTCdataFrom09Jan2009 tab, and this column is appearing in your other tabs aswell... But the number fluctuates each and every day whilst the difficulty is adjusted every 14 days...
If you want the real number, what you should do is use the formula
BTC earned per day = Block Reward / (Difficulty * 2**32 / hash rate / seconds in a day) (source:
https://bitcointalksearch.org/topic/m.8233023)
So, you'll have to do this calculation for each and every day, then convert the BTC earned per day into USD (or EUR, or Yen, or ....) and substract the electricity price.
You'll have to repeat this for every single day:
- the block reward costsists of the coinbase reward plus the sum of the transaction fees of every transaction in this block... you can take the average per day
- the difficulty stays the same every 2016 blocks, but then it is readjusted
- your hashrate stays the same
- the number of seconds in a day stays the same
- the exchange rates changes constantly... you can take the average exchange rate for that day
Let's make an example entry for an S17 on the 1st of januari 2020:
BTC earned per day = Block Reward / (Difficulty * 2**32 / hash rate / seconds in a day)
- On this day, the coinbase reward was 12,5 BTC/block . When we look at blocks like https://www.blockchain.com/btc/block/610700 and look at some earyer and some later blocks, we see an average fee of ~0.2 BTC/block (guesstimation, not actually calculated). This brings the block reward to 12.7 BTC/block.
- On this day, the difficulty was 13,691,480,038,694 (https://btc.com/stats/diff)
- An S17 hash a hashrate of 56 Th/s while drawing 2200 Watts (that's 56.000.000.000.000 hashes/s)
- A day has 86400 seconds
BTC earned with a S17 on the 1st of januari 2020 (on average) = 12.7/(13,691,480,038,694 * 2^32 / 56.000.000.000.000 / 86400)
BTC earned with a S17 on the 1st of januari 2020 (on average) = 12.7/12153.7
BTC earned with a S17 on the 1st of januari 2020 (on average) = 0,00104495
https://coinmarketcap.com/historical/20200101/ says Bitcoin's price on the first of januari 2020 was $7200/BTC
This means your gross income was $7200*0,00104495 = $7.5
Your machine is drawing 2200 Watts 24 hours per day, that's 52.8 KWh (let's round it to 53).
You pay 7 cents/Kwh... 0.07 * 53 = $3.71.
Your net income for the 1st of januari 2020 would have been $7.5 - $3.71 = $3.79.
That's about 20 cents more than you calculated... Not much, but still significant, especially when you're doing this calculation for 818 days (818 * 0.2 = >$160).
What i also mis is the resell value of the hardware... I'd personally rather sell off my ASIC than turn it off for many months on end...