Back to The Blackjack Page

The Blackjack Page Archive

Excel for the Blackjack Player - Part 2

Beside an obvious interest in "risk of ruin", which I covered in part 1 of this series, most Blackjack players - amateur and pro alike - also have a desire to know what to expect, from a financial point of view, by playing the game for a specific period of time or number of hands. From a pro's point of view, the possible results from, say, a day's worth of play determines how much cash one should carry and for the casual player the ability to calculate the possible outcome of a day's play is a good way to see how their results stack up against the mathematical probabilities, if for no other reason than budgeting for a trip to play Blackjack.

Professional Blackjack players know they'll win the $$$ sooner or later, but it's not just a question of sitting down at any table and waiting for the chips to come rolling in. Obviously the game being played must be beatable, in that it offers decent rules, good penetration and the player is using a bet spread that's not only appropriate for the game, but also can be used long enough in the particular casino involved to make a visit there worthwhile. Naturally, pros are going to do their "homework" on a particular game before playing it, so I won't preach any more.

But I brought it up because most recreational players patronize the game closest to their home or the game at their favorite hotel in Vegas, etc., without regard to the playing conditions being offered. Even though most recreational Blackjack players are not counters, they can still improve their overall results by choosing games that have favorable rules: the dealer standing on soft 17 is better than the dealer hitting on soft 17; fewer decks are better than more decks and double after splits is better than no double after split. Penetration has no impact on a non-counter's game and if one bets above the minimum without an advantage, it only hastens the loss.

In these days of war against terrorism, carrying large amounts of currency raises suspicions far beyond what's reasonable, in my not-so-humble opinion, but it's something we must deal with. Many, many innocent poker and Blackjack players have been delayed for questioning by airport authorities here in the U.S. and abroad simply because they were carrying the "tool" of their trade - cash. Because of that and other, more general security issues, it's in one's best interest to carry just the amount of cash needed for a visit to a casino and leave the extra at home. Of course none of us are psychic, so how will we know we have enough cash with us?

Professional Blackjack players know pretty much what to expect from a session of play, so the possibilities of gain and or loss are fairly predictable, which is where Microsoft's Excel program comes into play. For example, I wrote a lesson sometime ago that provided a pro's point of view on playing a good double deck game, which can be found in various parts of the U.S. Entitled "$100 an Hour at Blackjack", it outlines the bet size and spread needed, the expected hourly profit (duh - $100), the bankroll requirement to avoid risk of ruin (ROR) and other factors that will serve as a good demonstration of what Excel can do for planning a trip. So let's make up a trip.

Let's say we plan on going to Tunica for a three-day visit during which we expect to play Blackjack for a total of 20 hours. This means our mathematical expectation is to make $2000 at the tables, but we all know that variance (or "luck" as most people call it) will likely produce a much different result. Within certain parameters, that result can be mathematically predicted, which should give us a good idea of how many $$$ we need to bring along. The total bankroll I recommended in my article for a professional player exploiting this type of game is $50,000 because I'm assuming the pro must pay all of his or her monthly living expenses from what's earned at the tables, so a very low possibility of losing it all must be a prime consideration. If we use the formula I showed in Part 1, it shows a risk of ruin in the area of 1.3%. This assumes a $50-$400 bet spread, which will produce an overall advantage of 1.37%. The average bet is 2.26 units (the unit is $50, so it's $113) and multiplying 2.26 by 1.37% us a value for "w" (which is explained in Part 1) of 0.0310; a number that's needed for the calculation.

Because the game of Blackjack sees us making bets of more than 1 unit - because the count has gone up or we're doubling or splitting - it's impossible to be 100% precise in our calculations, but the average bet size will get us close. The same is true about the number of hands we'll play during a 20-hour trip. If we were to go on a weekend, it's reasonable to assume that the tables will be crowded, which will cut down on the number of hands actually played, plus that comely cocktail server may catch our eye and dinner with her will cut down on our playing time - just kidding. In any event, we must guesstimate the number of hands we'll play, so let's call it 75 per hour or 1500 hands on the trip. If our average bet per hand is $113, that means we'll be betting 75 x $113 = $8475 per hour. You can see that translates into 1.37% x $8475 = $116 per hour in expected income; allowing for playing mistakes and so forth I've conservatively rounded that down to $100 per hour. We've determined that we'll play about 1500 hands on our trip, with average earnings of $1.33 per hand or 75 x $1.33 = $100 per hour. If 1500 hands will produce $2000 in income, that's our "expected value."

Okay, let's go to Excel and enter some numbers to see how much cash we should bring with us on the trip. First, we want to determine the square root of the number of hands we'll play, so choose a cell in Excel and enter =(1500^0.5). The "=" tells Excel to expect a formula and the "^" indicates the power, like 2 for squaring, 3 for cubing, etc. Well, in Excel, to the power of 0.5 equals the square root. I put that in parens so Excel will figure it first and then do the other calculations. The other number we need is the Standard Deviation for a one unit bet on a hand of Blackjack, which in this case is 1.16. That number, which is "normalized" or always compared to 1 basically tells us that the rules of the game will cause us to bet more than 1 unit per hand, which comes about because of doubling and splitting (don't confuse that with the average number of units bet per hand, which is much larger). The SD per round per unit may range from 1.12 to 1.18, again depending upon the rules. You can see if doubling is restricted to 10 or 11 only, no double after split is allowed and so on, the SD per round per unit will be smaller. The more player options - insofar as getting more $$$ on the table - are available, the higher the SD will be. If surrender is offered, the SD will be lower, as an example. Anyway, if you were to use 1.16 for most games, you wouldn't be far off the mark.

Back to our formula. We need to multiply the square root of the number of hands, which is 38.79 in this case, by 1.16 and that equals 44.92. The formula now looks like this: =(1500^0.5)*1.16. Do you remember that "*" means multiply in Excel? Good, nearly finished. We now need to multiply the resulting number, 44.92 by our average bet, which we already know is $113. That equals $5077, which is the dollar amount of one Standard Deviation for 1500 hands of play at the game described with an average bet of $113 per hand. If you don't already know, the statistic of 3 Standard Deviations will cover what can happen 99.7% of the time - it's very rare that you'll experience a result beyond that, if all of your assumptions are correct: you actually do have an edge at the game, you're not overbetting, etc., etc. The formula in Excel should now look like this: =(1500^0.5)*1.16*113 and give you an answer of 5076.70657. Multiply $5077 by 3 and you get $15,231. Of course we have an expectation of winning $2000, so 99.7% of the time our result will be somewhere between a loss of $13,231 and a profit of $17,231. You can see that I got this range by subtracting the expected profit from the loss side and adding it to the profit side, because variance will, well, vary but expectation remains the same. In plain language, we might play every hand correctly, make all of the proper bets and still lose $13,000 on the trip, which would be a very, very bad trip. On the other hand, we have the equal possibility of winning $17,000+, but reality will bring a result somewhat closer to a profit of $2000.

Speaking of reality, I've personally have never experienced a losing session worse than minus 2.5 SD, which in this case would be a loss of 2.5 x $5077 = $12,692.5 minus the $2000 expected profit or about $10,000. In fact, I find that carrying enough cash to cover a 2 SD downswing is usually more than enough for a trip of this length. That amount would be 2 x $5077 = $10,154, not counting the expected profit, because we may start the trip by losing and not have use of the $$$ we'll win until near the end of our session. And that's what we call this - a "session" bankroll. If the total bankroll is $50,000 you can see that it's not necessary to take all of it with you on a trip, but if you plan to play more than 1500 hands, you'll need to do a new calculation. Of course if it's just a "day trip" and you'll be playing only about 500 hands total, just plug that number into the formula so it looks like this" =(500^0.5)*1.16*113. You should get a result of 2931.037905, which translates into a 1 SD of $2931. In 500 hands of play, the expected profit is $667, so a worst-case scenario of a 3SD loss will see you down 3 x $2931 = $8793 - $667 or $8126. Again, from a practical point of view, the $$$ to cover a 2 SD downswing or $6000 should be okay, so that's what I'd carry in this situation. A 2 SD "event" covers what will happen 95% of the time, so unless the Blackjack gods are pissed off at you, the bankroll to handle such a downswing should easily be enough.

For those of you who are not counters, much of what's been said here applies, but you do not have an expected profit - you have an expected loss. Using the numbers from our primary example, by playing 2000 hands at a disadvantage of 0.40% (the casino's edge for the game described earlier) and making an average bet of $113 (which is a little weird, but I'm trying to keep everything fair for comparison), your expected loss is 2000 x $113 x 0.40 = $904. I made that comment about the average bet size because, if you're not counting, you really have no reason to alter the size of your bet from hand to hand. In fact, buy "upping" your bet after a win and lowering it after a loss - something that has no basis of fact - will likely increase the size of your average bet, which will cause you to lose more quickly. Progression betting and other schemes like that cannot make you a long-term winner at Blackjack, period. If you win - and it's a big "if" - it's because of the variance that I'll show you how to calculate in Excel.

The calculation of Standard Deviation is the same for non-counters as for the counter. If you were to play 2000 hands at an average bet of $113, the SD per unit per round is still 1.16, so the $$$ value of one SD is the same: $5077. The expectation is to lose $904, so a 1 SD result will be a loss of $5077 + $904 = $5981 or a win of $5077 - $904 = $4173. Notice that I added the expectation to the loss side and subtracted it to the win side; the exact opposite of what I did for the counter. For a 3SD calculation, the numbers look like this: 3 x $5077 = $15,231; to which we need to add or subtract the expected loss, as appropriate. Sure, a non-counter can win at Blackjack, but it's just the effect of variance, which is a short-term phenomenon. In the long run, a non-counter will lose all of his or her $$$.

But knowing what can happen on a trip serves the non-counter as well as the counter - at least you can get an idea of how many $$$ to bring. Now I'm going to assume that you have much less than $50,000 to risk because if you did, I certainly hope you'd invest some of that into learning how to count. Also, if you're not a counter, you have no need for a fixed bankroll - it's more a case of taking some "spare" $$$ with you to the casino. With that in mind, let's see what might happen if you were to take $1000 with you on a trip where you expect to play 1500 hands of Blackjack - same game - but your average bet is $25, which basically means you'll place that amount on every hand. (Yes, I know that's not a lot of fun, but the mathematics say that's what you should do.) Okay, let's say you vary your bets from time to time, but it works out to be an average of $25, either way our calculations will be the same: =(1500^0.5)*1.16*25 = $1123, which is one Standard Deviation. For 1500 hands of play at this game (a 0.40% house edge) and an average bet of $25, the expected loss is $150. (See that? 1500 hands times $25 per hand is $37,500 in total bets, times 0.004 = $150. If you want to be absolutely sure your $$$ will last for the entire trip, you need to bring 3 times $1123 = $3369 plus $150 or about $3500. The $1000 you intended to bring will easily cover your expected loss, but it represents only about 1 SD of "coverage", so to speak, which essentially means that there's about a 55% chance of losing all of your $$$. Let me explain. One SD covers what will happen roughly 68% of the time, 2 SD covers 95% and 3 SD covers 99.7%. On any given trip, it's reasonable to assume your results will fall within 1 SD, which we determined to be $1123, plus or minus $150 as appropriate. So, you'll likely end down $1273 or up $973 or somewhere in between. Since that covers nearly 68% of the possibilities, the other 32% must be outside that range.

Now remember that variance is random, thus you can just as easily win as lose, so only half of all of the probabilities apply to losses. In other words, you don't mind winning $1000 but losing $1000 will wipe you out. We already know that 32% of our possible results are outside the range of 1 SD, so half that or 16% must be losses of more than $1000. We also know that 68% of the time our results will be in the $1123 neighborhood but only half of those are losses, so 34% applies there. Add up 16 and 34, you get 50; throw in a few extra points for the expected loss of $150 and the fact that you only have $1000, not $1123 and we get a probability of about 55% - close enough for government work as they say. Yes, this calculation can be done precisely by Excel, but that's a different lesson.

I'll see you here next time.