ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 04:57:56 PM |
|
Hi Guys,
I am trying to create an excel formula to calculate bitcoins earned per day.
I would like to incorporate the following factors.
Current Difficulty Current Network Hash Rate Current Block Reward Current Block Frequency
Once I have got the base formula working for bitcoins per day, I would then like to calculate bitcoins earned over a 12month period with a Difficulty increase which I can input. For example, I would input 5% increase per month, I would then see the result of that.
I know this is not anything other than a vague guide, but I would like to get it worked out.
Any help on this would be fantastic.
|
|
|
|
juju
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 05:06:05 PM |
|
Hi Guys,
I am trying to create an excel formula to calculate bitcoins earned per day.
I would like to incorporate the following factors.
Current Difficulty Current Network Hash Rate Current Block Reward Current Block Frequency
Once I have got the base formula working for bitcoins per day, I would then like to calculate bitcoins earned over a 12month period with a Difficulty increase which I can input. For example, I would input 5% increase per month, I would then see the result of that.
I know this is not anything other than a vague guide, but I would like to get it worked out.
Any help on this would be fantastic.
https://blockchain.info/statsThe above stats are for each 24 hour period You can utilize the API that Blockchain.info and other sites have to calculate this stuff in other applications. Alternatively you could just run bitcoin-d and get information from it periodically.
|
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 05:46:32 PM |
|
Thanks juju,
I would really like to sort the formula out so that I can incorporate it into my spreadsheet.
If anyone can help, I'd be really grateful.
Thanks
|
|
|
|
cp1
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 06:04:18 PM |
|
Mining start date (fill in) Difficulty (fill in) Hash Rate / GHsec^1 (fill in) BTC/hr = 25/((B2/(B3*1e9))*2^32/3600) days at this difficulty (fill in) BTC at this difficulty level = B4*24*B5
Fill right with difficulty increasing by whatever you predict and you can set the days at this difficulty to be 12, 14 whatever you predict.
|
|
|
|
ensurance982
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 06:28:06 PM |
|
Hi Guys,
I am trying to create an excel formula to calculate bitcoins earned per day.
I would like to incorporate the following factors.
Current Difficulty Current Network Hash Rate Current Block Reward Current Block Frequency
Once I have got the base formula working for bitcoins per day, I would then like to calculate bitcoins earned over a 12month period with a Difficulty increase which I can input. For example, I would input 5% increase per month, I would then see the result of that.
I know this is not anything other than a vague guide, but I would like to get it worked out.
Any help on this would be fantastic.
Do you want to calculate the bitcoins you would mine given a specific hash rate? Or do you want to calculate the bitcoins that will probably be mined in that amount of time? To calculate the latter would be really simple, since you'd only have to divide the time by 10 minutes and multiply that number by 25 (current reward). Since 25 BTC are being created every 10 minutes (mean)
|
We Support Currencies: BTC, LTC, USD, EUR, GBP
|
|
|
FUR11
Sr. Member
![*](https://bitcointalk.org/Themes/custom1/images/star.gif) ![*](https://bitcointalk.org/Themes/custom1/images/star.gif) ![*](https://bitcointalk.org/Themes/custom1/images/star.gif)
Offline
Activity: 378
Merit: 250
FURring bitcoin up since 1762
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 08:22:57 PM |
|
Hi Guys,
I am trying to create an excel formula to calculate bitcoins earned per day.
I would like to incorporate the following factors.
Current Difficulty Current Network Hash Rate Current Block Reward Current Block Frequency
Once I have got the base formula working for bitcoins per day, I would then like to calculate bitcoins earned over a 12month period with a Difficulty increase which I can input. For example, I would input 5% increase per month, I would then see the result of that.
I know this is not anything other than a vague guide, but I would like to get it worked out.
Any help on this would be fantastic.
Do you want to calculate the bitcoins you would mine given a specific hash rate? Or do you want to calculate the bitcoins that will probably be mined in that amount of time? To calculate the latter would be really simple, since you'd only have to divide the time by 10 minutes and multiply that number by 25 (current reward). Since 25 BTC are being created every 10 minutes (mean) yeah exactly. The amount of bitcoins that's being generated or distributed as a block reward is designed to stay the same, at least approximately. If the hash rate is rising, the amount of bitcoin that are being generated, is actually higher than 25 per 10 minutes.
|
|
|
|
odolvlobo
Legendary
Offline
Activity: 4424
Merit: 3325
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 10:37:56 PM |
|
The network hash rate is not known. The value is estimated based on the rate at which blocks are being found.
You must determine the rate at which bitcoins are being generated by looking at the rate in the recent past and extrapolating that into the future.
Bitcoincharts.com says that blocks have been produced at the rate of 6.7 per hour. That translates to 4020 BTC per day.
If you want to know how much a miner will mine per day at the current difficulty, use this formula:
Daily revenue = H x 25 x 600 x 65535 / 248 / D x 6 x 24
where H is the hash rate (H/s) and D is the difficulty
|
Join an anti-signature campaign: Click ignore on the members of signature campaigns. PGP Fingerprint: 6B6BC26599EC24EF7E29A405EAF050539D0B2925 Signing address: 13GAVJo8YaAuenj6keiEykwxWUZ7jMoSLt
|
|
|
Omikifuse
Legendary
Offline
Activity: 1820
Merit: 1009
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 06, 2014, 10:43:19 PM |
|
There are some online calculators for that, for example: http://www.bitcoinx.com/profit/Not sure if it helps, or if it is wht you want
|
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 01:05:42 PM |
|
The network hash rate is not known. The value is estimated based on the rate at which blocks are being found.
You must determine the rate at which bitcoins are being generated by looking at the rate in the recent past and extrapolating that into the future.
Bitcoincharts.com says that blocks have been produced at the rate of 6.7 per hour. That translates to 4020 BTC per day.
If you want to know how much a miner will mine per day at the current difficulty, use this formula:
Daily revenue = H x 25 x 600 x 65535 / 248 / D x 6 x 24
where H is the hash rate (H/s) and D is the difficulty
In this example, can you clarify a couple of items so that I can fully understand the calculation? Is H = the hash rate of my miners? 25 = the number of BTC per block 600 = ? 65535 = ? 2^48 = ? D = network difficulty 6 = ? 24 = ?
|
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 01:33:31 PM |
|
Mining start date (fill in) Difficulty (fill in) Hash Rate / GHsec^1 (fill in) BTC/hr = 25/((B2/(B3*1e9))*2^32/3600) days at this difficulty (fill in) BTC at this difficulty level = B4*24*B5
Fill right with difficulty increasing by whatever you predict and you can set the days at this difficulty to be 12, 14 whatever you predict. Could you clarify this for me. If I were to use this in excel, for your formula to work, would it be as follows. Cell B1 Mining start date Cell B2 Difficulty Cell B3 Hash rate of my gear Cell B4 BTC Per Hour Cell B5 Days at this difficulty level In order to be as accurate and clear as possible, could you attach a sheet with the formula working? Thanks.
|
|
|
|
cp1
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 01:37:06 PM |
|
Yep that's it
|
|
|
|
odolvlobo
Legendary
Offline
Activity: 4424
Merit: 3325
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 03:11:01 PM |
|
Daily revenue = H x 25 x 600 x 65535 / 248 / D x 6 x 24 where H is the hash rate (H/s) and D is the difficulty
In this example, can you clarify a couple of items so that I can fully understand the calculation? 65535 / 2 48 / D is the probability of a single hash solving a block 600 x 6 x 24 is the number of seconds in a day
|
Join an anti-signature campaign: Click ignore on the members of signature campaigns. PGP Fingerprint: 6B6BC26599EC24EF7E29A405EAF050539D0B2925 Signing address: 13GAVJo8YaAuenj6keiEykwxWUZ7jMoSLt
|
|
|
jonnybravo0311
Legendary
Offline
Activity: 1344
Merit: 1024
Mine at Jonny's Pool
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 03:11:34 PM |
|
The formula is very simple: BTC earned per day = Block Reward / (Difficulty * 2**32 / hash rate / seconds in a day)
So, if in Excel, just put each value in a cell: 25 is block reward 18,736,441,558 is difficulty 2**32 is constant your hash rate (or whatever hash rate you want) 86400 % increase in difficulty days between increases total days to consider As has been pointed out, you can fetch the difficulty from calls to blockchain.info, or you can just change it manually for when you want to do the calculation. Then, you'd just run the formula over and over again having it change the difficulty and recalculating earnings per day for the time period you specified. There are any number of calculators online that already provide this level of information. For example, bitcoinwisdom.com has a mining calculator to show ROI with any number of editable inputs, including conversion from BTC->USD, pool fees, power costs, etc. Not sure why you're looking to reinvent the wheel, but given the formula above and the values for the cells, you certainly can.
|
Jonny's Pool - Mine with us and help us grow! Support a pool that supports Bitcoin, not a hardware manufacturer's pockets! No SPV cheats. No empty blocks.
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 03:29:44 PM |
|
Thanks Guys,
I am working out bills for us and the best way to optimise our power usage.
It is actually to aid my father and make a bullet proof calculator that given the correct variables can provide accurate data on power usage, electricity cost, BTC earned per day, value of BTC earned in £ and so on all in one form.
It’s optimised for the information he cares most about.
Once I have the formula updated, Ill post a copy on here so you can see where I’m at and provide any input / advice on modifications I should make to increase accuracy.
Thanks again.
|
|
|
|
jonnybravo0311
Legendary
Offline
Activity: 1344
Merit: 1024
Mine at Jonny's Pool
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 04:28:54 PM |
|
Thanks Guys,
I am working out bills for us and the best way to optimise our power usage.
It is actually to aid my father and make a bullet proof calculator that given the correct variables can provide accurate data on power usage, electricity cost, BTC earned per day, value of BTC earned in £ and so on all in one form.
It’s optimised for the information he cares most about.
Once I have the formula updated, Ill post a copy on here so you can see where I’m at and provide any input / advice on modifications I should make to increase accuracy.
Thanks again.
Be sure to explain to your father that there is no "bullet proof" calculator because there are too many changing variables. Even the formula I posted provides nothing more than "expected" earnings per day in an ideal world under perfect circumstances. It does not take into account pool luck, transaction fees associated to blocks, etc. Everything is luck, and as such the best we can provide are probabilistic calculations. The calculations become less and less accurate over time because they take more and more guesses. For example, nobody can tell you what the next difficultly will be. You can guess that it will increase 5% or 10% or 20%. Do you keep that same increase for every jump from now until the last block is mined? Do you put in variable jumps? Whatever you do, you're just making a guess ![Smiley](https://bitcointalk.org/Smileys/default/smiley.gif) What about BTC->fiat? That changes every second. Electricity rates are variable. Power usage depends on the efficiency of the power supply, the power delivery, the miners themselves. All variables. TLDR: it's guess work. Make sure dad gets that.
|
Jonny's Pool - Mine with us and help us grow! Support a pool that supports Bitcoin, not a hardware manufacturer's pockets! No SPV cheats. No empty blocks.
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 06:58:06 PM |
|
Daily revenue = H x 25 x 600 x 65535 / 248 / D x 6 x 24 where H is the hash rate (H/s) and D is the difficulty
In this example, can you clarify a couple of items so that I can fully understand the calculation? 65535 / 2 48 / D is the probability of a single hash solving a block 600 x 6 x 24 is the number of seconds in a day In your example my formula looks like this =29650*600*65535/2^48/18736441558*6*24 That produces a result of 7.9 BTC per day. I am inputting my hash rate as GH/s. What have I got completely wrong?
|
|
|
|
odolvlobo
Legendary
Offline
Activity: 4424
Merit: 3325
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 07:00:21 PM |
|
248 is 281474976710656, and not 248.
|
Join an anti-signature campaign: Click ignore on the members of signature campaigns. PGP Fingerprint: 6B6BC26599EC24EF7E29A405EAF050539D0B2925 Signing address: 13GAVJo8YaAuenj6keiEykwxWUZ7jMoSLt
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 07:13:54 PM |
|
248 is 281474976710656, and not 248.
I edited. It still looks wrong :/ The stats i'm working with are as follows. Could someone help me incorporate these stats into a working formula that results in an BTC per day estimation. Current Difficulty : 18736441558 Current Network Hash Rate TH/s : 138086.19 Coin Reward Per Block : 25 Block Frequency mins : 10 BitCoin Value £ : 344.52 Your Current Speed GH/s : 29650 Your % total of Network Hash Rate : 0.000214721 (calculated by spreadsheet from tats in putted above) Total BTC Released Per Day : 3600 (calculated by spreadsheet from tats in putted above) I'm anticipate a result between 0.77299547 / 0.79585047BTC Per day Thanks guys.
|
|
|
|
jonnybravo0311
Legendary
Offline
Activity: 1344
Merit: 1024
Mine at Jonny's Pool
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 07:24:46 PM |
|
248 is 281474976710656, and not 248.
I edited. It still looks wrong :/ The stats i'm working with are as follows. Could someone help me incorporate these stats into a working formula that results in an BTC per day estimation. Current Difficulty : 18736441558 Current Network Hash Rate TH/s : 138086.19 Coin Reward Per Block : 25 Block Frequency mins : 10 BitCoin Value £ : 344.52 Your Current Speed GH/s : 29650 Your % total of Network Hash Rate : 0.000214721 (calculated by spreadsheet from tats in putted above) Total BTC Released Per Day : 3600 (calculated by spreadsheet from tats in putted above) I'm anticipate a result between 0.77299547 / 0.79585047BTC Per day Thanks guys. 25 / (18736441558 * 2**32 / 29650000000000 / 86400) = 0.79585047[btc] per day
Your percentage of the total hash rate, total BTC released per day and value in Pounds is not necessary to figure out your expected earnings. Just change the difficulty and/or hash rate (which is hashes per second - not GH/s, TH/s or anything else... HASHES per second) as necessary.
|
Jonny's Pool - Mine with us and help us grow! Support a pool that supports Bitcoin, not a hardware manufacturer's pockets! No SPV cheats. No empty blocks.
|
|
|
ukzerosniper (OP)
Newbie
Offline
Activity: 43
Merit: 0
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
August 07, 2014, 07:35:59 PM |
|
248 is 281474976710656, and not 248.
I edited. It still looks wrong :/ The stats i'm working with are as follows. Could someone help me incorporate these stats into a working formula that results in an BTC per day estimation. Current Difficulty : 18736441558 Current Network Hash Rate TH/s : 138086.19 Coin Reward Per Block : 25 Block Frequency mins : 10 BitCoin Value £ : 344.52 Your Current Speed GH/s : 29650 Your % total of Network Hash Rate : 0.000214721 (calculated by spreadsheet from tats in putted above) Total BTC Released Per Day : 3600 (calculated by spreadsheet from tats in putted above) I'm anticipate a result between 0.77299547 / 0.79585047BTC Per day Thanks guys. 25 / (18736441558 * 2**32 / 29650000000000 / 86400) = 0.79585047[btc] per day
Your percentage of the total hash rate, total BTC released per day and value in Pounds is not necessary to figure out your expected earnings. Just change the difficulty and/or hash rate (which is hashes per second - not GH/s, TH/s or anything else... HASHES per second) as necessary. When I use this exact formula Excel changes it as follows =25/(18736441558*2E+32/29650000000000/86400) It gives the result 1.70908E-23 What does "* 2**32" mean in this formula? I think this may be where its going wrong :/
|
|
|
|
|