Calculate Payout Schedule for a Poker Tournament

[cehjr43]

In a poker tournament there are Y players, each of whom contributes $x to the prize pool. The tournament director will determine the number of players, Z, from the field of Y who will be paid. The number may vary depending on the desires of the tournament director at a particular venue. I want to provide the director with a spreadsheet into which he can plug Y, $x and Z, and generate a listing of percentages and dollar amounts to be paid to each of the Z players in order to achieve the following results:

1. The sum of the amounts paid will total Y * $x., or 100% of the available prize pool.
2. The last paid position will receive 1.5 x.
3. Each higher finishing player will receive a “uniformly” increasingly greater amount of prize money. That is, the percentage increase the next to last finisher receives over the last place finisher should be the same as the percentage increase the winner receives over the player who finishes second. Obviously the dollar amounts will increase as you move upward among the finishers toward reaching the winner of the tournament.

Is there a formula I can use to generate the series or the percentage for each of the Z players?

 

[Travis]

Welcome to the board!

Wow...I know Im going to get a beating for this but heres what I came up with. Its a little buggy becuase it utalizes the goal seek function which we all know can throw some whacked results sometimes...So if you get undesired results just reset the percentage and try again. (Edit) Most of the time it will work fine. You also need another parameter for the top prize in order to derive the percent. (Edit) This wasnt mentioned in your post. Ok here goes...

Book1
	A	B	C	D	E	F
1	TotalPlayers	100		1stPlace	1,250.00	25%
2	EntranceFee	50.00		2ndPlace	959.75	19%
3	#ofWinnersPaid	10		3rdPlace	736.90	15%
4	FirstPlacePrize	1,250.00		4thPlace	565.79	11%
5				5thPlace	434.41	9%
6	TotalPot	5,000.00		6thPlace	333.54	7%
7				7thPlace	256.09	5%
8	Changebetweenpayouts	23%		8thPlace	196.63	4%
9				9thPlace	150.97	3%
10				10thPlace	115.92	2%
11				11thPlace	-	0%
12				12thPlace	-	0%
13				13thPlace	-	0%
14				14thPlace	-	0%
15				15thPlace	-	0%
16				16thPlace	-	0%
17				17thPlace	-	0%
18				18thPlace	-	0%
19				19thPlace	-	0%
20				20thPlace	-	0%
21
22				TotalPaid	5,000.00
Sheet1

In addition to this, you need a little bit of code. This is a worksheet change event that will fire when any of the cells highlighted in yellow above are changed. Im no programmer so Im sure this can be made better by someone else that wants to look at it. Im sure named ranges or Dim'ing variables in place of the ranges would make it easier to read.

Sub worksheet_change(ByVal Target As Range)
Dim rng As Range
Set rng = Application.Intersect(Target, Range("B1:B4"))
If rng Is Nothing Then Exit Sub
If Range("B4").Value > Range("B1").Value * Range("B2").Value Then
Range("B4").Value = Range("B1").Value * Range("B2").Value
MsgBox "Top Prize has been chaged to " & Range("B4").Value
Else: End If
If Range("B4").Value< Range("B6").Value / Range("B3").Value Then
Range("B4").Value = Range("B6").Value / Range("B3").Value
MsgBox "Top Prize has been chaged to " & Range("B4").Value
Else: End If
Range("E22").GoalSeek Goal:=Range("B6").Value, _
ChangingCell:=Range("B8")
End Sub

Hope thats what you were looking for...

(Edit) PS- You may need to crank your maximum iterations for Goal Seek to find the solution. I set mine to 1000 for this. Tools-->Options-->Calculation

 

[rrdonutz]

Hi cehjr43,

I think your problem can be likened to that of an annuity, since the payout is the "same" for each place, it's just that the first place finisher gets his payout compounded Z-1 times more than the last place finisher. What you're trying to do is find out what the compounding rate is, if this makes any sense! Maybe the following examples will help:

MrE4304.xls
	A	B	C	D	E	F	G
1	Tournament#1:				Tournament#2:
2	TotalPlayers(Y)	100			TotalPlayers(Y)	120
3	EntranceFee(x)	50			EntranceFee(x)	75
4	WinnersPaid(Z)	10			WinnersPaid(Z)	8
5	TotalPot	5000.00			TotalPot	9000.00
6	Winnings,lastplace	75.00	(1.5*x)		Winnings,lastplace	112.50	(1.5*x)
7	%increasefrom				%increasefrom
8	1placetonext	39.06%			1placetonext	63.93%
9
10		Payout	%Change			Payout	%Change
11	10thPlace	75.00	--		8thPlace	112.50	--
12	9thPlace	104.29	39.06%		7thPlace	184.42	63.93%
13	8thPlace	145.03	39.06%		6thPlace	302.31	63.93%
14	7thPlace	201.68	39.06%		5thPlace	495.56	63.93%
15	6thPlace	280.45	39.06%		4thPlace	812.34	63.93%
16	5thPlace	390.00	39.06%		3rdPlace	1331.64	63.93%
17	4thPlace	542.33	39.06%		2ndPlace	2182.90	63.93%
18	3rdPlace	754.15	39.06%		1stPlace	3578.33	63.93%
19	2ndPlace	1048.72	39.06%		Total	9000.00
20	1stPlace	1458.34	39.06%
21	Total	5000.00
Sheet1

Note that the key formula is in cell B8 (for Tournament #1, and F8 for Tournament #2). It is:

=RATE(B4,B6,0,-B5)

This compounding rate is then used in cells B12:B20 to come up with the winnings for each place (cell B11 has the minimum winning amount for last place).

Finally, there are "slicker" ways to determine the winnings for each place once you have determined the rate, but I don't think that's the focus of your problem.

HTH,

Tom

 

[etsija0]

Hey. This threads a bit old. But thanks anyway. This may help in the application I am making now. It's a poker analyzer with winning money streak counter. In VB of course. Microsoft really rocks!!

 

[michaelbarry]

Even though it is an older thread, I am happy someone commented on it to bring it towards the top. Now this is exactly the kind of excel help that I can use at home. I throw a large poker tournament at my place a few times a year, and calculating buy ins and payouts are always a hassle. This way, I can just leave my laptop up which is already running the clock and blind structures, and now it can show the payouts and total prize pool as well. Genius!

 

[cclingan]

Hi cehjr43,

I think your problem can be likened to that of an annuity, since the payout is the "same" for each place, it's just that the first place finisher gets his payout compounded Z-1 times more than the last place finisher. What you're trying to do is find out what the compounding rate is, if this makes any sense! Maybe the following examples will help:
(table removed)
Note that the key formula is in cell B8 (for Tournament #1, and F8 for Tournament #2). It is:

=RATE(B4,B6,0,-B5)

This compounding rate is then used in cells B12:B20 to come up with the winnings for each place (cell B11 has the minimum winning amount for last place).

Finally, there are "slicker" ways to determine the winnings for each place once you have determined the rate, but I don't think that's the focus of your problem.

HTH,

Tom

Tom,

Thanks for that example! Do you (or anyone else reading this) have the actual math behind the RATE() function? I am trying to get the same results in a java program that (as far as I know) doesn't have this function. Thanks!

Craig