Spreadsheet: Invest in BTC or mining hardware?

[SgtSpike]

I have generated a spreadsheet that should allow user input of all potential variables to consider when deciding whether to invest in mining hardware or in BTC directly.

Play with it, and tell me what you think.  It seems that the wisest choice could be either, and it greatly depends on difficulty levels and expected increase (or decrease) in the price of BTC.

v1.1 - Difficulty should now accurately affect BTC production.
v1.2 - Added in calculations to show profits if production is traded daily.
v1.3 - Added invest/withdrawal/pool fees fields, added uptime percentage field.
v1.31 - Calculation/bug fixes
v1.32 - Calculation/bug fixes

Download the spreadsheet (v 1.32)
If you find any errors in the calculations, PLEASE let me know so I can fix it!

If you found this helpful, please consider donating BTC to 1CjoEtypZhqkBLSdfpnphNYjWyokourkxe

[trentzb]

Glancing at your photo, I don't see how that could possibly be accurate unless I am misunderstanding what C9 (Total BTC generated) means and what value you are referring to in C10.

[Veritus101]

well there is a reason why I'm selling my rigs now. For me the extra little bit is not worth the hassle of managing hardware and keeping em up and what not. I was gonna just sit on the coins for a while anyways so I'm just gonna buy. Been buying at a steady 3.3ish for a while now which was nice.

Another article on a popular site or a media outlet covering bitcoin on news and it will be another 50% difficulty IMO.

[SgtSpike]

Glancing at your photo, I don't see how that could possibly be accurate unless I am misunderstanding what C9 (Total BTC generated) means and what value you are referring to in C10.

Total BTC generated (C9) includes difficulty increases.  By the end of 4 months, at 40% difficulty increase every 2 weeks, you aren't generating very much.

Total value generated is simply what you would have in USD at the end of 4 months if you kept all bitcoins generated.  This also takes in to account the rise (or fall) in value of BTC to make it worth whatever it is at the end of 4 months.  At 10% every week, one BTC would be worth $19.6 at the end of 4 months.

[trentzb]

Ahh, I got it now. Thanks!

[SgtSpike]

Another interesting note...

Assuming the difficulty is increased to 154068, as bitcoincharts.com is predicting, it would only take an average rise of 30% difficulty in the future to make buying bitcoins more profitable than mining.  So many of the naysayers are right, provided the difficulty levels continue to rise at 40% rate.

I need to make the difficulty calculations more concrete and accurate though.  Will work on that now...

[SgtSpike]

Fixed the difficulty level issues.  You now have the option to set how many days from now the next difficulty level will change.  Also, difficulty now only changes BTC production at each difficulty level changeover period - so it's no longer divided into daily increments.  The time period until the next difficulty level is also adjusted according to difficulty increase/decrease.  The higher the next difficulty level, the more quickly that difficulty level will be reached.

TL;DR:  Difficulty should be accurate now.

[phelix]

It is quite interesting to see that after four months or so there is not much happening anymore to the amount of coins you own. You just have two different amounts of BTC - and the one bought seems to be always higher than the one mined for current hardware prices.

You could include a "sell immediately" strategy for comparision, though it has much lower performance.

I will put a link to this thread on my site.

[SgtSpike]

Yes, but again, those values shown in the screenshot (and by default in the file) include very high difficulty rates, which may or may not be the case in the future.  It seems that the flip-flop between whether it is smarter to buy or to mine comes down to difficulty rate, and there will eventually be a difficulty level reached when it will be pointless to invest in new hardware, as the only way it would maintain a better profit than investing directly into BTC is if the value of BTC rises very slowly (< 2%/week).

Thanks for the link phelix!  I do have a sell immediately calculation in my latest version, just haven't uploaded it yet.  It's fascinating to see that as another comparison point...  It's not always the worst option.

[JJG]

Very nice work. Thank you for sharing!

Some comments: The next difficulty jump is actually over 41%, not the 20% like the default in this model. This alone makes a massive difference.

Also, you might consider adding some fields (to both sides) for exchange rate fees. For example, the 0.65% Mt. Gox charges going each way, then any fees for moving your local currency into and out of the bitcoin ecosystem.

A field for 'pool fees' would be great for those mining in a pool like deepbit, where 3% of the take goes to the pool operator.

An 'uptime' factor is also a worthy addition, especially for anyone who plans on gaming a few hours per day. Even dedicated systems may only get 98% due to connection issues, pool disruptions, etc.

Again, excellent work!

[SgtSpike]

Cool, thanks for the suggestions JJG!  I think they're all worthy of adding in as extra fields to further increase accuracy of the calculations.

[SgtSpike]

Updated to 1.3... includes all of JJG's suggestions and phelix's suggestion to include calculations for selling immediately (or in this case, daily, since hourly would be a bit insane and impractical.

Next update, I'm going to notate all of the calculations in comments on each calculated field, so that others can more easily double-check my work (and I can double-check it myself while writing out the notations as well).

[Enky1974]

I recommend 6% for Expected weekly rise (fall) in USD/BTC
this will lead to a 20$ btc value for jan 2012. with 10% i doubt we will reach 70 usd/btc for january 2012.

I'll also recommend to use 8.6% for difficulty increase, it is the historical value so far.

[SgtSpike]

I recommend 6% for Expected weekly rise (fall) in USD/BTC
this will lead to a 20$ btc value for jan 2012. with 10% i doubt we will reach 70 usd/btc for january 2012.

It's a tough call to say where BTC might end up by the end of the year.  But, that's why the fields are changeable. 

[Enky1974]

I recommend 6% for Expected weekly rise (fall) in USD/BTC
this will lead to a 20$ btc value for jan 2012. with 10% i doubt we will reach 70 usd/btc for january 2012.

It's a tough call to say where BTC might end up by the end of the year.  But, that's why the fields are changeable. 

with those 2 numbers you will see that it's more profitable to mine and hold. with my 5870 i've a 12 month profit of 27000$ (mining) against 17300$ (investing)

[JJG]

I recommend 6% for Expected weekly rise (fall) in USD/BTC
this will lead to a 20$ btc value for jan 2012. with 10% i doubt we will reach 70 usd/btc for january 2012.

It's a tough call to say where BTC might end up by the end of the year.  But, that's why the fields are changeable. 

with those 2 numbers you will see that it's more profitable to mine and hold. with my 5870 i've a 12 month profit of 27000$ (mining) against 17300$ (investing)

What difficulty rise did you use?

Also, I'd strongly suggest that everyone run many different scenarios to see how each decision will fare across several cases. For example, don't forget to run scenarios in which BTC value falls slightly over time as well.

[phelix]

I recommend 6% for Expected weekly rise (fall) in USD/BTC
this will lead to a 20$ btc value for jan 2012. with 10% i doubt we will reach 70 usd/btc for january 2012.

I'll also recommend to use 8.6% for difficulty increase, it is the historical value so far.

how do you calculate the 6% USD/BTC rise per week and the 8.6% / 2 weeks difficulty increase?

I get quite different values:

usd/btc mtgox
2010-10-08 ~0.1
2011-05-08 ~3.8
~30 weeks
increase factor: 38
--> 1.129^30 ~ 38
--> 12.9% per week

difficulty (after huge gpu jump)
2010-08 ~200
2011-05 ~150000
~39 weeks / 2 = 19.5
increase factor: 750
--> 1.404^19.5 ~ 747
--> 40.4% per two weeks

[Enky1974]

how do you calculate the 6% USD/BTC rise per week and the 8.6% / 2 weeks difficulty increase?

I considered a target price of 20 for jan 2012, with 6% you have it, 20$/BTC for jan it's expected if prices will continue to rise with an exponential growth. The growth isnt linear, i simulated a cycle with 4 weeks of big expansion and 8 weeks with price retracement as we seen in the last months, i think that the most probable target it's 18-20 for jan 2012.

8.6% it's calculated forecasting an exponential increase of difficulty using historical values as a starting point, we will probably hit 1,400,000 difficulty for jan 2012 with this pace.
Indeed 6% for usd/btc weekly growth factor it's a conservative value, if a real boom kick in then 10% isnt impossible thus 70$/btc for jan 2012:) instead of 20.
cheers

[JJG]

8.6% it's calculated forecasting an exponential increase of difficulty using historical values as a starting point, we will probably hit 1,400,000 difficulty for jan 2012 with this pace.
Indeed 6% for usd/btc weekly growth factor it's a conservative value, if a real boom kick in then 10% isnt impossible thus 70$/btc for jan 2012:) instead of 20.
cheers

8.6% for your difficulty increase values doesn't even begin to make sense in the current environment. You can't include the pre-GPU era and the pre-BTC/USD-runup era in your forecasts, as it was a different game back then.

The last increase was 30%. This increase is over 42%. The rate does not appear to be slowing down one bit.

If you had used 8.6% in your forecast 1 period ago (before the 30% jump), it would predict difficulty would be jumping up to 17.9% more (for the 2 periods) at this next jump.

Instead, after the 30% and 42% increases it will be up over 82%.

That's 82% actual vs. your 17.9% predicted. And every other increase will be on top of the increases in the near future.

Another example: 4 difficulty increases of your 8.6% yields a total difficulty increase of 39%.   4 difficulty increases at the current 40% rate yields a total difficulty increase of 384%. Even if growth cools off to 8.6% after these increases, that 8.6% is still building on top of that 384%.


In short: Any predictions must use the current difficulty increase rate for the near future, otherwise they are invalid.

[Enky1974]

Yes you're right, i'm checking using the last few data points of difficulty increase.
this is what i obtain


around 1,7 mln difficulty for november, 6 months from now. i used the "gpu" historical data.
To match this new value, i've to use 18% instead of 8.6% as "Expected average change in difficulty" in the spreadsheet.