Unit 17

Question: You walk into a casino with $1,000 and place a series of 20 bets. Each bet has
a 30 percent chance of winning and a 70 percent chance of losing. Your first bet is for
$1. Thereafter, if you win you bet $1 the next time, but if you lose you “double-up,”
betting twice as much the next time. How can you expect to do overall?

Note: The idea behind this popular betting strategy is that if you double-up long enough,
you are guaranteed to win. For example, if you bet $1 and lose, the next time you will
bet $2. If you win this time, you are ahead $1. If you lose, you are down $3, but your
next bet will be $4, enough to cover all your losses and still put you ahead by $1. If you
lose five times in a row, your total losses will be $1+$2+$4+$8+$16 or $31, but your
next bet will be $32. If you win, you will still be ahead by $1. So long as you eventually
win, it doesn’t matter how often or how many times you lose. When you do finally win,
you will be up by $1.

Solution:

1. Enter 1 and 2 in B4 and B5. Select these two cells and drag down to Row 23 to keep
track of the 20 bets.

2. In E3, enter $1,000, your initial bankroll.

3. Column C will show if you win or lose each bet. We want to arrange things so that 1
indicates a win and –1 indicates a loss. The question is how to make 1 appear at
random 30 percent of the time and –1 appear at random 70 percent of the time.

3.1. In C4 enter: =rand(). Press F9 a few times. The number that appears in the cell
is chosen at random between 0 and 1, with all possibilities equally likely.

3.2. Change C4 so that the formula reads: =rand()<.5. The formula tests if a random
number between 0 and 1 is less than.5 or 1/2. If it is less than 1/2, the cell shows
TRUE. If not, it shows FALSE. Press F9 a few times. Since the random
number has an equal chance of being above or below 1/2, you should see TRUE
and FALSE about equally often.

3.3. Change C4 so that the formula reads: =rand()<.3. Now the cell will show TRUE
if the random number is less than .3. Since this is 30 percent of the way from 0
to 1 and all numbers in this range are equally likely, you should now see TRUE
about 30 percent of the time. To get a better sense of the relative number of
TRUEs and FALSEs, select C4, drag down to Row 23 and then press F9 a few
times. On average, you should expect to see about 14 FALSEs and only about 6
TRUEs.

3.4. All that’s left to do is change the formula so that it gives 1 instead of TRUE and -
1 instead of FALSE. The final formula for C4 is: =if(rand()<.3, 1, -1). When we
 write “if” in a formula, we follow it by parentheses containing three things
separated by commas. The first—in this case rand()<.3—must be something that
is either true or false. The second is the value we want as a result if it is true and
third is the value we want if it is false. If the first item is true, we get the second
item as a result, otherwise we get the third. Here, rand()<.3 has a 30 percent
chance of being true, so have a 30 percent chance of getting 1 and a 70 percent
chance of getting –1.

3.5. Select C4 and drag down to Row 23. Press F9 a few times to verify that about 70
percent of the items in Column C are –1.

4. The “if” also works to make the bet twice as big as it was the last time, if the last time
was a loss. We’ll use Column D for the bets.

4.1. In D4, enter $1 for the first bet.

4.2. In D5, enter: =if(C4=-1, 2*D4, 1). That is, if the last try was a loss, the bet this
time is twice as much as then, otherwise it’s $1.

4.3. Format D5 as Currency, with no decimal places. Then select D5 and drag down
to Row 23 to get the rest of the bets.

5. Now we can see what happens to your bankroll. It goes up if the number in Column
C is 1 and down otherwise.

5.1. In E4, enter: =if(C4=1, E3+D4, E3-D4). That’s your previous bankroll plus the
bet if you win and minus the bet if you lose.

5.2. Format D4 as Currency with no decimal places. Then select E4 and drag down
to Row 23.

6. Try pressing F9 repeatedly and watch your bottom line in E23. You should find that
you end the night ahead about 2/3 of the time and that your average winnings are
about $5. The problem is that the other roughly 1/3 of the time, when you lose, your
losses average much higher. They may easily amount to hundreds of dollars. If you
make 20 bets this way every night, you will win more often than you lose, but overall
you will lose big.

Comments:

1. One way to see the problem with the doubling-up strategy is to consider the limits
that make it impossible to apply in real life. In casinos there is a limit to the amount
you can bet; if you lose 20 times in a row, you will need to bet more than a million
dollars on your next bet to recoup your losses and put you ahead. But the casino will
not accept such a large bet.

2
 A separate limit is the size of your bankroll. If you press F9 enough, you will
eventually find a case in which one of the amounts in column E is negative. In real
life, this would mean that your $1,000 bankroll ran out. No matter how much money
you have to start with, there is a point at which you can’t continue to double up—
even if the casino would let you—because you can’t afford it.

Finally, even if you had infinite stores of money and a casino willing to accept bets of
any size, your time is limited. No matter how fast you bet, there is a limit to the
number of bets you can make in an evening. Sometimes your time will run out before
you win the big bet that would have put you ahead. In our example, it was this time
element that made the strategy lose.

If you have more money in your bankroll, a casino that will accept larger bets and
more time, the result is that you will win more often. Instead of winning 2/3 of the
time, you may arrange to win 90 percent or even 99 percent of the time. But the more
often you win, the larger your losses will be when you do not.

2. A second way to understand what is happening is to look at each bet individually. At

this casino, the odds are stacked against you. In the case of any single bet, you are
more likely to lose than to win. But a series of losing propositions can’t be combined
to make a winning one. If you bet $1, you expect to lose. If you do lose and then bet
$2, that bet is a losing proposition also—in fact you should expect to lose twice as
much.

3. The formula rand()<.2 is true 20 percent of the time, rand()<.65 is true 65 percent of
the time and so on. We can use this approach to get any probability we like.

Additional questions:

1. A popular investing strategy is “buying on the dips”—that is, purchasing a stock

when its price has gone down to maximize the chance of buying low, selling high and
netting a profit. Consider a stock that has an equal chance of any annual price change
from a fall of 10 percent to a rise of 20 percent. Which tends to do better over a ten-
year period, a strategy of buying $5,000 worth of stock only when the price has fallen
in the past year or one that invests the same total amount of money as a lump sum at
the beginning of the period?

2. How long will it take you to pay off a 6.25 percent, fixed-rate mortgage of $200,000
if you begin by paying $1,231.43 a month and increase your monthly payments at the
rate of inflation, assuming inflation is steady at 3 percent a year? If you look back at
Unit 10, you’ll find out that if you don’t increase your payments, it will take 30 years
to pay off this mortgage.

3. A savings account has an APY of 5.05 percent, but you are charged a monthly service
fee of $5 at the end of any month in which your balance is below $2,000. The fee is

3
 charged after interest is credited, but it is based on the balance during the month, that
is before interest is credited. If you start with $1,800, how many years will it take
your balance to grow to $2,100? Make sure to calculate the fee rather than entering it
manually.

4. In the previous question, by what percentage does your account grow in the first
year? By what percentage does it grow in the last year?

5. Suppose a stock is really worth $100. When its price is above $100 at the beginning
of a year it tends to fall—the change ranges randomly between a rise of 10 percent
and a fall of 20 percent. When the price at the beginning of a year is $100 or less, it
tends to rise—the change ranges randomly between a fall of 10 percent and a rise of
20 percent. Now how does the “buying on the dips” strategy of Question 1 fare over
ten years? Assume that the stock price is exactly $100 two years before investing
begins.

Money topics covered Spreadsheet topics covered

Doubling up IF worksheet function
Buying on the dips