Increasing Bet Size

[InspiriaX]

I have to complete an Excel assignment but I don't know the relevant formula that I have to use in my question! It goes like this:

In the gambling game of heads or tails, a coin is tossed. You win if it is heads and lose if it is tails. A win means you keep the bet plus and additional bet amount given to you by the banker, and a loss means the banker takes your bet.

In this case, the probability of winning,p, is 0.5 and therefore the total return (inclusive of the bet) to the winner is two times the bet. After a few rounds, your net balance or win is the amount you currently have minus the amount you initially had.

a. A simple strategy is to bet the same amount each time until money runs out or completing round 40, whichever comes first. Note the last bet, depending on what is available, may not be equal to the first bet amount. Using this strategy, with an initial fund of $1000 and $10 bet size, what are the average and standard deviation of the last net balance? Do the same for $50 and $100 bets.

b. A "better" strategy, supposedly a sure win, goes like this: You first bet a fixed amount. If you lose, you double the bet and keep doing that until you win. Once you win, you quit the first round and begin again from the start if there is still time. Again with an initial $1000 and start bet sizes of $10, $50, $100, evaluate the average and standard deviation of the last net balance.

I have written columns and formulas for part (a) of the question: Heads/Tails, Outcome(used lookup table), bet returns(first value obtained by "total initial fund + bet return" obtained from using index and match from a table and later on, using the "first value + bet return" to find subsequent bet balance.

for part (b) however, it is much trickier because the Bet sizes do not stay the same. This gives me a headache because my bet returns formula initially depended on having a one formula taking the bet size of 10 and filling the formula for the rest of the trials (rows). How do edit my part (a) working to double the bet size after losing the bet?

Also, is the inputting of the column/row number in index and lookup functions, Eg. INDEX($G$3:$H$4,MATCH(D11,$G$3:$G$4,0),2) , considered hardcoding? Because hardcoding is not allowed in my assignment?

 

[Haikal]

Can you please upload the excel file with illustration so i can help?

Also, what do you mean by hard coding?

 

[mikerickson]

You could set it up like this.
Note that the formulas start in row 3

Also note that if toss in row 10 had gone the other way, you're net would have been negative and large men with bad haircuts would have beaten you senseless for not being able to cover your bet.

The double if you lose strategy is not useful in real life since the likelihood of winning is lower than .5 and you are very likely to run out of money before you get a win.

Book1
	A	B	C	D
1	My Bet	Coin Toss	Win/Loss	My Money
2				100
3	1	H	1	101
4	1	T	-1	100
5	2	T	-2	98
6	4	T	-4	94
7	8	T	-8	86
8	16	T	-16	70
9	32	T	-32	38
10	64	H	64	102
11	1	T	-1	101
Sheet1

Cell Formulas
Range	Formula
A3	=IF(B2="T",2*A2,1)
A7	=IF(B6="T",2*A6,1)
B3	=IF(RAND()>0.5,"H","T")
B7	=IF(RAND()>0.5,"H","T")
C3	=IF(B3="H",1,-1)*A3
C7	=IF(B7="H",1,-1)*A7
D3	=D2+C3
D7	=D6+C7

 

[InspiriaX]

Hi Haikal, I'm not allowed to post attachments.

I hope this will help

number of trials: 40
Probability of winning,p: 0.5
Initial Total: 1000

____________________________________________
_________________ lBet Returns for bet sizes: l
l Coin toss l Outcome l___10_____l 50 l 100 l
l Heads l Win l 20 l 100 l 200 l
l Tails l Lose l -10 l -50 l -100 l

Bet size: 10
Total Earnings: 200
______________________________________
l Trial l Coin Toss l Outcome l Bet Balance l
l 1 l Tails l Lose l 990 l
l 2 l Heads l Win l 1010 l
l 3 l Heads l Win l 1030 l
l 4 l Tails l Lose l 1020 l
l 5 l Tails l Lose l 1010 l
All the way to trial 40 you get the idea..

Documentation
#Trials 1000 Input
Probability of winning,p Input
Coin Toss F3:F4 <table,input>
Outcome G3:G4 <table,input>
Initial Total Input of 1000
Bet size Input<table,input>
Bet Returns for Bet size H3:J3 (table) =Bet size/probability of winning
Coin Toss C11:C50 =IF(RAND()<0.5, "Heads", "Tails")
Outcome D11:D50 =LOOKUP(C11,$F$3:$F$3,$G$3:$G$4)
Bet Balance E11 = 1000+INDEX($G$3:$H$,MATCH(D11,$G$3:$G$4,0),2)
E12:E50 =E11 + INDEX($G$3:$H$,MATCH(D11,$G$3:$G$4,0),2)
Total Earnings =last bet amount - initial total
Average =AVERAGE(E11:E50)
Standard Deviation =STDEV(E11:E50)

Hardcoding refers to fixing the data in the excel cell so that they cannot be altered without modifying the program.
For instance, I have to put 1000 in a cell and reference that particular cell into a formula. My documentation shows me putting 1000 directly into the formula for Bet Balance (hardcoding when I could have chosen not to) as I hope this will help you understand my working without typing all the cell references.</table,input></table,input></table,input>

 

[InspiriaX]

Hello mikerickson,

I've looked through your reply and I understand it. I'm extremely thankful and grateful for you taking the time to read and break it down into a simple template and formulas.

I don't know how express my thanks better but

THANKS A MILLION for helping me