Graphing Bankroll Fluctuations: Any Excel Experts??

[gaming_mouse]

I have a friend who plays nothing but no-limit SnG's, a winning player for over a year, who's been having a bad streak. I want to run a monte carlo sim in excel and graph the results, to give him an idea of the natural fluctuations of the game. A simplified, but pretty accurate, approx. to his distribution would be (ignoring entry fees for now):

X=4, p=(1/10)+e, where e is his edge
X=2, p=(2/10)+e
X=1, p=(3/10)+e
X=-1, p=(6/10)-3e

Can anyone help me out with this? I could do it in another language, but I want to do it in excel so my friend can use it too.

Thanks,
gm

[Paul2432]

I can do this. PM me your e-mail address and I'll send you an Excel file some time tomorrow.

Paul

[gaming_mouse]

Paul, thanks.

BTW, The above dist is wrong. the p should be (1/10)+e for each of the winning possibilities.

[SumZero]

This is actually really easy to do (and I'll include numbers of someone who is barely profitable, because that makes the prettiest graphs), and it is better to learn to fish then be given a fish.

First the parameters of the players that you will be free to tweak:
In cell C1 put 1
In cell C2 put prob of at least first (try 0.12 to start)
In cell C3 put profit of first (3.9 units)
In cell D1 put 2
In cell D2 put prob of at least second (try 0.24 to start [which is a 12% chance when 1st is also 0.12])
In cell D3 put the profit of second (1.9 units)
In cell E1 put 3
In cell E2 put the prob of at least third (try 0.36 to start [which is a 12% chance when 1st and 2nd are as above])
In cell E3 put the profit of third (0.9 units)
In cell F1 put OTM
In cell F2 put 1
In cell F3 put the profit of OTM (-1.1 units)
If you want put the players profit rate in cell G3 (the formula is "=C3*C2+D3*(D2-C2)+E3*(E2-D2)+F3*(F2-E2)", and in mythic player the player has a profit of 0.1 units for an approx 9% ROI)

Next the simulation part:
Column G is for the game numbers
Column H is for the random numbers
Column I is for the result
Column J is for cumulative totals

In G5 put 1
In G6 put "=1+G5"
In H5 put "=rand()"
In H6 put "=rand()"
In I5 put "=IF(H5<C$2,C$3,IF(H5<D$2,D$3,IF(H5<E$2,E$3,F$3)))" (this is why the above probabilities were of at least 2nd or at least 3rd so that this calculation would work. The $'s here are to keep these numbers the same when we fill down)
Highlight I5 and I6 and hit ctrl-d to fill down
In J5 put "=SUM(I$5:I5)"
Highlight J5 and J6 and hit ctrl-d to fill down
Scroll down to row 2004. Highlight cells G2004, H2004, I2004, J2004 (hold down shift and use the arrow keys). Hold down ctrl and shift while these are highlighted and hit the up arrow. This will highlight your simulation region. Now hit ctrl-d to fill in the whole row. You could do more than 2000 if you want, but excel sometimes gets upset cpu wise if you try to do 65,000 of these.

Next the presentation of results (max bankroll, min bankroll, and visual chart of bankroll):
In J2 put "=MAX(J5:J2004)"
In J3 put "=MIN(J5:J2004)"

Make a line chart where the x-data series is G5:G2004 (the chart wizard will help with this) and the data series is J5:J2004. Make the chart appear in your worksheet near the top of the summary information.

If you want to know what the biggest down swing is in the 2000 hands:
In L5 put "=MAX(J$5:J5)"
In M5 put "=L5-J5"

Go back down to the 2004 row (easiest way is to select J5 and hit ctrl and down arrow), highlight L2004 and M2004 with the key board, do the ctrl-shift-up arrow to select region, and then hit ctrl-d to fill down.

In M2 put "=MAX(M5:M2004)"

This represents your biggest down swing during the 2000 SNG. For our mythical 9% ROI player in multiple trials I had mostly down swings from as small as 24 to as large as 105 but mainly in the 35-55 range. If I change to the person to an 40% ITM, ROI ~27% with 16% 1st, 12% 2nd, and 12% 3rd, I see the biggest down swings more like 15-40 with the vast majority in the low to mid 20s (hence the 30 SNG buyin bankroll recommended by many people - although I did see 2 50s and a 60 in my 30 or 40 tries).

Any time you change a cell the random functions will recalculate. In addition any time you hit F9 they will too. If you want to stop the autoupdate you can edit the chart options.

Hope this helps.

[That guy]

Are your unit assumptions correct here??

3.9
1.9
.9
-1.1

those sum to 5.6 total units... can you explain this part..

thanks for the sim... that is cool

[gaming_mouse]

[ QUOTE ]

Are your unit assumptions correct here??

3.9
1.9
.9
-1.1

those sum to 5.6 total units... can you explain this part..

thanks for the sim... that is cool

[/ QUOTE ]

What's wrong with that? These are the payouts. They don't need to sum to anything in particular. I think you are confusing this with the constraint that probabilities must sum to 1.

[That guy]

no I understand that they are the payouts and not probabilities.. but I was wondering if the payouts are off --
just curious for a double-check on that. I admit I could be wrong but those payouts are slightly off compared to party poker payouts and I was wondering how to make it exact.

thx

[That guy]

is 1 unit = to 1 buy in?

if so, on Party Poker for a $50 SNG ($55 buy-in), the payoffs are:
1st: $250 = 250 / 55 = 4.5
2nd: $150 = 150 / 55 = 2.7
3rd: $100 = 100 / 55 = 1.8
OTM: = -1

Total Pool = $550 so,
1st = ~45% of the total
2nd = ~27%
3rd = ~18%
juice = 10%

I am not trying to be a jerk here and I am not saying I am necessarily right, I am just trying to understand it fully...

thx in advance

[Paul2432]

You are both correct, but using a different basis. Your figures are the gross payout. Sumzero's figures are net payout in units of buy-in (not including entry fee).

For example in a 50+5 first place is $250. Net gain is 250-55=195. In terms of buy-ins, net gain is 195/50 = 3.9.

Paul

[gaming_mouse]

Yes,

But you want the net payout (at least I did). That is how you see your bankroll fluctuations. Using the gross only confuses things IMO.

Also, if you want to be truly exact, the vig get slightly less for 100 and 200 SnG's, so you'd need to know the exact level you were playing at.

gm

[That guy]

ok. that is clear now... I was wondering how the math was working using $50 -- not the actual $55... but I see what the equation is doing now - it is pulling the vig out of the profit and not out of the buy-in...

So:

=(Gross Payout - Total BuyIn) / Net Buyin

(250 - 55)/50
(150 - 55)/50
(100 - 55)/50
OTM: -55

3.9
1.9
.9
-1.1


cool, thx...