Calculate Payout Schedule for a Poker Tournament

cehjr43

New Member
Joined
Apr 29, 2004
Messages
1
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

Well-known Member
Joined
Feb 26, 2002
Messages
1,711
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
ABCDEF
1TotalPlayers1001stPlace
2EntranceFee50.002ndPlace
3#ofWinnersPaid103rdPlace
4FirstPlacePrize1,250.004thPlace
55thPlace
6TotalPot 6thPlace
77thPlace
8Changebetweenpayouts23%8thPlace
99thPlace
1010thPlace
1111thPlace
1212thPlace
1313thPlace
1414thPlace
1515thPlace
1616thPlace
1717thPlace
1818thPlace
1919thPlace
2020thPlace
21
22TotalPaid
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.
Code:
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

Well-known Member
Joined
Jan 15, 2003
Messages
564
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
ABCDEFG
1Tournament#1:Tournament#2:
2TotalPlayers(Y)100TotalPlayers(Y)120
3EntranceFee(x)50EntranceFee(x)75
4WinnersPaid(Z)10WinnersPaid(Z)8
5TotalPot TotalPot
6Winnings,lastplace (1.5*x)Winnings,lastplace (1.5*x)
7%increasefrom%increasefrom
81placetonext 1placetonext
9
10Payout%ChangePayout%Change
1110thPlace --8thPlace --
129thPlace 7thPlace
138thPlace 6thPlace
147thPlace 5thPlace
156thPlace 4thPlace
165thPlace 3rdPlace
174thPlace 2ndPlace
183rdPlace 1stPlace
192ndPlace Total
201stPlace
21Total
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

New Member
Joined
May 15, 2011
Messages
1
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!!:biggrin:
 

michaelbarry

New Member
Joined
Jul 18, 2011
Messages
5
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

New Member
Joined
Aug 23, 2006
Messages
2
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
 

Forum statistics

Threads
1,078,241
Messages
5,339,041
Members
399,275
Latest member
amrita17170909

Some videos you may like

This Week's Hot Topics

Top