Free videos to help you get the most out of the DeucesCracked experience. Quick tips on tools from RSS readers to next-gen trackers and everything in-between. Good stuff IMO.
Subscribe to this Series
DeucesCracked member teaches the use of Excel for mathematical analysis of poker statistics. He describes what information we can learn from winrates, standard deviations, and confidence intervals. He also uses Excel to analyze proper bankroll management decisions for the amateur and the pro.
Posted 8 months ago
tags: pygmyhero ipod friendly bankroll management excel poker math statistics risk of ruin standard deviation winrate confidence intervals tool time
Micro/Small Stakes,
42 min long
Comments for Excel for Poker Math
jml
Awesome vid. You didn't mention NL but everything seems to work out good for it if you have a good WR (at 3.5 with SD of 42, the BR reqs blew up though and said I needed 30k to play 1/2 :S w/ 5% RoR).
Posted 8 months ago
jajvirta
Great video Pygmy.
One minor note. You didn't explicitly mention this, though it's implicit in the professional bankroll requirement, is that the risk of being a losing player bankroll-wise increases significantly once you start to take out living expenses. (It's implicit in that it's in the equation for pro bankroll. So it's not like you ignore it, but just that I think it's worth mentioning explicitly.) If one constantly withdraws near the amount of historic winrate, it's pretty much certain that you're going to go broke, or at least have to go down in limits. Which might not be an option if you're a professional. After moving down, you're expectation decreases and you need to build up your bankroll in order to move back up.
By the way, The Mathematics of Poker has good discussion on sort of advanced concepts of bankroll management and risk of ruin if anyone wants to know more.
Posted 8 months ago
jajvirta
I'm pretty sure you got something wrong in the calculation. It's basically the same with NL. The big bet in NL (regarding Poker Tracker's std dev number, for example) is two times the big blind so in NL200's case it's $4.
Posted 8 months ago
Hypnotic
holy crap. awesome vid PH! Thanks
Posted 8 months ago
PygmyHero
Equations will follow but take longer to type up. For now, if you're interested...
Additional Reading
The idea for this video originated in this thread in the Beginner LHE Forum.
jajvirta suggests an alternative method for these calculations at the end of page one and iplaylimit suggests another method in the middle of page 2.
I would also highly recommend that you read this article by jajvirta.
~22 I bring in the bankroll equation. I mentioned a few sources:
Blackjack Attack, by Don Schlesinger.
A paper by Patrick Sileo.
A few 2+2 threads where BruceZ discusses the equation, including a thread where he and Mason Malmuth compare it to the bankroll equation in Malmuth's Gambling Theory and Other Topics.
I can try and find these last few items, but not unless someone let's me know they're interested in reading them. And even then I will not promise I can find them, but I will try.
Posted 8 months ago
PygmyHero
Equations
The format I'll use is as follows:
Description of the cell data (cell location)
Explanation of equation in words
The exact syntax that needs to be entered into Excel in quotes
The cell references will assume you've followed the form I've laid out and will make all entries into column B (since column A has the headings). If you formatted the data differently (like a horizontal layout) you'll need to adjust accordingly.
Uncertainty in big bets per one hundred hands (cell B5)
Standard deviation in big bets per one hundred hands divided by the square root of the number of hands divided by one hundred
"=B4/SQRT(B2/100)"
68% Confidence Interval Lower Bound (cell B6)
Win rate minus uncertainty in big bets per one hundred hands
"=B3-B5"
68% Confidence Interval Upper Bound (cell B7)
Win rate plus uncertainty in big bets per one hundred hands
"=B3+B5"
95% Confidence Interval Lower Bound (cell B8)
Win rate minus two times the uncertainty in big bets per one hundred hands
"=B3-2*B5"
95% Confidence Interval Upper Bound (cell B9)
Win rate plus two times the uncertainty in big bets per one hundred hands
"=B3+2*B5"
99% Confidence Interval Lower Bound (cell B10)
Win rate minus three times the uncertainty in big bets per one hundred hands
"=B3-3*B5"
99% Confidence Interval Upper Bound (cell B11)
Win rate plus three times the uncertainty in big bets per one hundred hands
"=B3+3*B5"
Recreational Bankroll (cell B14)
Negative natural log of risk of ruin times the standard deviation in big bets per one hundred hands squared divided by two times the win rate in big bets per one hundred hands times the big bet
"=-LN(B13)*B4^2/(2*B3)*B12"
N.B. The negative sign at the beginning of the equation. You can make the carrot by pressing SHIFT+6.
Total Budget (on the Budget tab, cell B13)
Sum of all expenses
"=SUM(B2:B12)"
Monthly Nut (cell B15)
All expenses in a month for the professional player
"=Budget!B13"
Professional Bankroll (cell B17)
This one really doesn't make any sense in words, especially because you need to have parentheses in the correct locations to ensure that order of operations is carried out as you want it, but this is about it: negative natural log of risk of ruin times the standard deviation in big bets per one hundred hands squared divided by two times the win rate in big bets per one hundred hands minus the monthly nut divided by the big bet divided by the number of hands per month divided by one hundred times the big bet
"=-LN(B13)*B4^2/(2*(B3-B15/B12/(B16/100)))*B12"
Living Reserve (cell B18)
Monthly nut times six
"=B15*6"
Starting Pro Bankroll (cell B19)
Required professional bankroll amount plus a six month living reserve
"=B17+B18"
For the Live Player
Note that I changed much of the cell formatting. Almost everything on this tab is formatted to be currency with two decimal places showing. Also note that the Big Bet row is removed, meaning the cell reference locations are slightly different at the bottom of the sheet. Most of the formulas are the same, except for as follows:
Uncertainty in dollars per hour (cell B5)
Standard deviation in dollars per hour divided by the square root of the number of hours
"=B4/SQRT(B2)"
Recreational Bankroll (cell B13)
Negative natural log of risk of ruin times the standard deviation in dollars per hour squared divided by two times the win rate in dollars per hour
"=-LN(B12)*B4^2/(2*B3)"
N.B. the slightly different cell locations.
Professional Bankroll (cell B16)
This one makes a little more sense in words, but not much. Also you still really need to watch that your parentheses are correct. It is: negative natural log of risk of ruin times the standard deviation in dollars per hour squared divided by two times the win rate in dollars per hour minus the monthly nut divided by the number of hours per month
"=-LN(B12)*B4^2/(2*(B3-B14/B15))"
N.B. again, slightly different cell locations.
That was a lot to type up, so if anyone spots an error in any of the equations please let me know.
Posted 8 months ago
PygmyHero
Miscellaneous
All NL players (and PL for that matter) you need to make an adjustment to the spreadsheet. Since your game has no big bet you need to enter the big blind*2 in cell B12. This will make all data in the sheet consistent in terms of the units being used.
Thanks to jajavirta for pointing this out. jml - I assume that will solve your problem. I would guess you have $2 in that cell right now, and it needs to be $4.
I do not take rakeback and bonuses into account in this spreadsheet. That's because they don't matter in terms of determining whether or not you are a winning player (i.e. I would consider a break even or small loser who makes money on rakeback and bonuses to be a 'profitable' player, but not a winner). They also then don't matter in terms of figuring out if you're beating a limit and should move up (assuming you use the metric I proposed - that you be 99% certain you are winning at your current limit).
However, they should come into play in determining your bankroll requirements. Basically the distinction I'm drawing here is between your win rate (which only attests to your skill as a player), and your earn rate (which is the more telling number for your financial situation).
I felt that adding those factors in went beyond the scope of this video. You'd need to account for what portion of the rake was generated by you, which varies by sites, what percentage you received, the bonus clearing structure for each site, deducting bonuses from MGR, how often you'd be clearing bonuses, etc. I do have a separate spreadsheet that does all of this for a few of the sites I play at, but again, it just seemed like including all of that was too much, especially since you need a different set up for each site.
Posted 8 months ago
bullyboh
does multitabling really change anything ?
that should be covered by a lower winrate or am i wrong ?
Posted 8 months ago
googs
Shit, I just failed my STATs test literally 15 minutes ago on this stuff. Wasn't expecting to see more of this stuff today, but I'm sure you guys are better than my professors.
Posted 8 months ago
Medrakil
SD for LHE is as you say appr 17.5 or so.. For NLHE its prob about 50.. Dont you have to take this into consideration?
And another thing..If you belive you are a 3BB/100 winning player at 100nl, playing 25000 hands a month with a SD of 50, and you have 1000$ a month for expences then you need a 7500$ bankroll for 100nl?Is that because you always wanna increase your bankroll while playing?
Posted 8 months ago
Sugar Nut
Hey Pygmy,
great stuff in here, thx a lot for it!
I do however get a negative $ amount for Professional Bankroll. I've been fiddling around with this for some time now trying to find my error, but just don't seem to be able to.
http://img73.imageshack.us/img73/6926/negativebankrollrg5.jpg
If you could mabe take a look at my equasion??
BTW, I'm obv not trying to become a 100NL pro but this is the limit where I have the most data from so I tried to get the spreadsheet to work with 100NL
THX,
Sugar Nut
Posted 8 months ago
Sugar Nut
Hmmm,
No edit function here...
FYI, the "Game" cell is B2
Sugar Nut
Posted 8 months ago
PygmyHero
Hey Sugar Nut, your formula looks correct and I got the same result using your data. I'm not really sure why this is yet. I'm going to try breaking the equation down a little bit. I really hope I didn't make a syntactical error in this video as that'd be pretty bad.
By the way, you can edit your post for up to 30 minutes after making it. The option is just to the left of the Reply buttons.
Posted 8 months ago
jajvirta
The bankroll doesn't have any resiliency for swings if you take most of your winnings out. Another thing is that if you estimate having 3BB/100 winrate and in actuality you have a 2BB/100 winrate and if you cash-out 2BB/100 monthly, then your bankroll doesn't have resiliency for swings at all and you will eventually deplete your whole bankroll (unless you move down in limits etc).
Posted 8 months ago
PygmyHero
Yes. I'm a LHE player and I have almost no experience with NL so I had no idea what an appropriate SD would be. I'll take your word for it.
Posted 8 months ago
jajvirta
I'd guess it's the fact that he's taking more out of his bankroll than his monthly expecation and this does some funny stuff to the equation.
Posted 8 months ago
Sugar Nut
THX for your effort Pygmy.
Sugar Nut
Posted 8 months ago
jajvirta
The spreadsheet does take this into account. Just fill in your std dev in BB/100 for the corresponding cell and you're good to go.
Or did I misunderstand your question?
Posted 8 months ago
jajvirta
I'm pretty sure it's the fact that you take out more than you make per month with those figures. You can't do that. :-)
In fact, you can't take out even near your monthly expecation, because that removes the resiliency from the bankroll. If you were to calculate say the classic risk of ruin for your bankroll, you'd have to calculate that from your net winrate (winrate from which any monthly expenses are reduced).
Posted 8 months ago
PygmyHero
Okay Sugar Nut, I think I've solved the problem here. The part of the formula (from your spreadsheet, NOT mine) I want to look at is:
Your win rate just isn't high enough to support that monthly nut (if you were a pro that is).
"C4-C16/C13/(C17/100)"
In words this is your win rate minus the nut divided by the big bet (big blind*2 since you play NL) divided by hands per month divided by 100.
The problem is that nut/big bet/hands/100 = 6.25, which is a larger number than your win rate of 4.7. That negative number propagates through the rest of the formula, which is why you have a negative result for bankroll requirement. This of course doesn't mean you need no money to play that game professionally, it rather means you CANNOT play that game professionally given the stakes, your win rate, and the monthly nut you input.
For the Excel geek who really wants to solve this problem I would recommend re-writing the bankroll formula to include an IF statement. Going back to my sheets format, cell B17 might read something like:
"=IF(B15/B12/(B16/100)>B3,"lol not a pro",-LN(B13)*B4^2/(2*(B3-B15/B12/(B16/100)))*B12)"
Edited to add: so jajvirta is right again. And faster than me.
Posted 8 months ago
PygmyHero
Sugar Nut, since your sheet is laid out differently I think you'll want the formula in C18 to read:
"=IF(C16/C13/(C17/100)>C4,"lol not a pro",-LN(C14)*C5^2/(2*(C4-C16/C13/(C17/100)))*C13)"
And no worries - I get the same message if I try to enter a reasonable nut for the stakes I'm playing.
By the way, I really like using IF statements so you'll have to forgive me for being silly here. I was considering writing an IF statement into the video to look at whether or not the data showed you were 99% certain you were a winning player and then advise you to either move up or that you suck and needed to play more hands.
Posted 8 months ago
PygmyHero
jajvirta - I think he's saying I didn't recommend an appropriate SD for a NL player and we both recognize it'll be different then the 17-18 I recommended for the LHE player.
Posted 8 months ago
Medrakil
What Im saying is that if you have 100 000 hands that say you play 3bb/100 at 100NL 100 000 hands that say you play 3bb/100 at 100L, then you will ned 2.5k$ bankroll for NL, and 324$ for L just by changing the SD from 50-->17.. Is this standard?
Posted 8 months ago
Medrakil
I get the bankroll for NL is correct, but is 350 enough for 100L?
Posted 8 months ago
PygmyHero
Medrakil, I agree that the formula shows you need just under $2,500 to play 100 NL recreationally given the numbers you've proposed. I don't understand what you intend "L" to signify in your last two posts.
"...324$ for L..."
"...is 350 enough for 100L?"
Sugar Nut - did everything that jajvirta and I posted make sense?
Posted 8 months ago
jrbick
Awesome vid tyvm for sharing this.
Looking foward to some clarification for NLHE if there needs to be any.
Posted 8 months ago
pkr_brat
Really intresting video i was wondering what stats i would need to make an sng 1 any ideas?
Number of hands would be Number of tourneys
Win rate would be ROI
SD in BB/100???
Uncertanity in BB/100???
Posted 8 months ago
Medrakil
NL = No Limit
L = Limit
Posted 8 months ago
durkadurka33
Good vid.
Here are some numbers that I ran for NL people.
100NL, 4ptbb/100 winner with SD of 50ptbb/100. 100k hands played.
Risk of ruin of 2% (which I think is correct for full time pros).
A recreational BR is $2445.
A professional BR depends entirely on your nut. Assuming a nut of 2500, your BR is $6250 = 62BI.
Jump to 200NL, with the same numbers (incl monthly nut).
Recreational BR = $4890
Professional BR = $7240 = 36BI
Jump to 400NL.
Rec BR = $9780, ~24BI
Pro BR = $11677, ~29BI
So it's clear that at lower stakes, your nut has a huge impact on your BR requirements. It's pretty dramatic.
Posted 8 months ago
Medrakil
THats pretty obv though durka beacause your nut will often be closer to your actual earnings pr. month..
How many hands did you type in as an expected hands/month?
Posted 8 months ago
Jake123
Holdem manager doesn´t show Standard deviation, what is a good approximate for me playing 2-4$ with the stats 23/20/3,7 and mostly at 5-max. Hard to tell maybe but give me something.
Posted 8 months ago
jajvirta
Hard to tell if you don't specify the game you're playing. But I guess one can assume from the stats that it's NL so I'd say something in the order of 50BB per 100 standard deviation.
Posted 8 months ago
PygmyHero
Clarification for NL and PL players
You will need to enter your SD in BB/100, same as a LHE player. I'm not a NLHE player so I don't know what a standard number is, but 50 BB/100 has been tossed around in this thread. If you need a more precise estimate just contact a NL player you know with a large database and ask them what they think (or what their db says).
In cell B12, the Big Bet, you need to enter 2*the big blind. So if you're playing 100NL enter $2 here.
Everything else should work out fine.
Posted 8 months ago
PygmyHero
Sugar Nut - does everything make sense now? I didn't hear back from you. Sorry if I offended you - I wasn't trying to make fun of the fact that you're not a pro. I'm not either and at the stakes you play I'm sure you make more than I do in a month. I was just having some fun - sorry if it came out the wrong way.
Jake123 - If you're playing limit I would suggest you use 17-18 BB/100. If you're playing NL I would suggest 50 BB/100 unless you get better advice from a NL player. I don't use HEM but I'm surprised that it doesn't show SD. If you searched pretty thoroughly and couldn't find it I'd suggest you write HEM support as they definitely should show it and I understand that the HEM team is very good about responding to these sorts of things and updating their product.
Posted 8 months ago
PygmyHero
Medrakil - I'm with you now on the NL versus LHE stuff. So I already said I agree with your calculation for NLHE given the numbers you suggested.
The problem is that there's really no such game as 100 limit. If I leave all the stuff you wrote in for the NLHE player then the big bet is $2, meaning the game is really 1/2 LHE. In that case the spreadsheet suggests a BR of just over $300, or 150 big bets.
That is small from the perspective that most people advocate 300+ (really more like 500 now as I mention in the video) big bets. However, I don't think it's terrible small from the perspective that you have the player crushing the game for 3 BB/100 over a pretty decent hand sample size of 100,000 hands.
Note especially in that case how low their uncertainty in win rate is (~0.5 big bets). And we're over 99% sure they win at a rate of almost 1.5 BB/100. In other words, they're very confident they're a very solid winner. So it shouldn't be so surprising that their BR requirement is low.
Posted 8 months ago
PygmyHero
pkr_brat, I just wanted to let you know that I'm working on this and that I did see your post and am not ignoring it. I'm not a tournament player so I'm not very familiar with the stuff that goes into it. I'm going to do some research and see if I can get you some answers.
Posted 8 months ago
PygmyHero