My original formula is overly simplified, and I was just taking the average of all three of the reinvestment success probability projections and I used that average number of 41.67%..
Actually, if you look at the formulas in my Google Sheets, you see that I take the average as well.
O.k.. Yes.. Now, I can see the formulas in there, and for some reason, I did not look at your formulas in my earlier response. .and so I should have been able to figure out the difference between what we were doing by looking at your formulas as compared with mine. Whoops. My bad.
It looks to me the first difference between our sheets is in cell X89, in your spreadsheet.
My value computation is pretty simple:
BTCprevious period-Rake BTC previous period+Rake BTC previous period*Reinvestment success avg
OR
10-2+2*0,4167
I might have misunderstood some of your explanations if I cannot reconcile the first step in your spreadsheet.
Ok. Now I see where we are different because you are using the whole rake amount and assigning the success probability to it, which ends up completely ignoring the specific locations of the each of the projected reinvestments (which in this table I am taking from the reinvestment reserves - and you got the reinvestment reserves and also each of the reinvestment amounts the same as mine in row 88 .. Anyhow, my cell X89 formula looks like this for the earlier version:
=SUM(X88-AA88)+((AH88+AL88+AP88)*AI$82) or (10-2)+((0.59259259+.074074074+1.05820106)*0.4167)
After I saw that your numbers were different from mine, I thought (without properly analyzing your formulas) that you did something like this second formula with yours.
=SUM(X88-AA88)+((AH88*AI$79)+(AL88*AI$80)+(AP88*AI$81))
.. but, yeah my revised formula did not resolve the discrepancy.. so I should have spent more time studying your cell formulas to catch the specifics... whoops..
So, yeah, I think that the second formula is more accurate than the first one since it uses the projected success rate for each of the periods of 70%, 40% and 15% rather than using the success rate average of 46.67%.
It does seem more accurate to use each of the increments rather than the average, so I am o.k going with that, if that might be what might resolve the differences in our numbers.
I don't know what is accurate; these just work Hypotheses, once we agree o. those, we can start figuring out the rest.
Even though I was kind of guessing when i put 70%, 40% and 15%, I think that we should use the second formula rather than the average of 41.67% because I do believe that using the percentage for each of the buy back intervals/increments allows for getting at making more accurate / realistic estimates...and yeah, who knows if anyone is going to use these formulas.. but I do see that I some folks are going to become less confused if they look at the formulas to make sure they understand what the spreadsheet is doing..