# Calculate Payout Schedule for a Poker Tournament

#### cehjr43

##### New Member
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?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Travis

##### Well-known Member
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
1TotalPlayers1001stPlace1,250.0025%
2EntranceFee50.002ndPlace959.7519%
3#ofWinnersPaid103rdPlace736.9015%
4FirstPlacePrize1,250.004thPlace565.7911%
55thPlace434.419%
6TotalPot5,000.006thPlace333.547%
77thPlace256.095%
8Changebetweenpayouts23%8thPlace196.634%
99thPlace150.973%
1010thPlace115.922%
1111thPlace-0%
1212thPlace-0%
1313thPlace-0%
1414thPlace-0%
1515thPlace-0%
1616thPlace-0%
1717thPlace-0%
1818thPlace-0%
1919thPlace-0%
2020thPlace-0%
21
22TotalPaid5,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.
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
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
5TotalPot5000.00TotalPot9000.00
6Winnings,lastplace75.00(1.5*x)Winnings,lastplace112.50(1.5*x)
7%increasefrom%increasefrom
81placetonext39.06%1placetonext63.93%
9
10Payout%ChangePayout%Change
1110thPlace75.00--8thPlace112.50--
129thPlace104.2939.06%7thPlace184.4263.93%
138thPlace145.0339.06%6thPlace302.3163.93%
147thPlace201.6839.06%5thPlace495.5663.93%
156thPlace280.4539.06%4thPlace812.3463.93%
165thPlace390.0039.06%3rdPlace1331.6463.93%
174thPlace542.3339.06%2ndPlace2182.9063.93%
183rdPlace754.1539.06%1stPlace3578.3363.93%
192ndPlace1048.7239.06%Total9000.00
201stPlace1458.3439.06%
21Total5000.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

##### New Member
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

##### New Member
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
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

Replies
1
Views
86
Replies
3
Views
126
Replies
2
Views
618
Replies
2
Views
204
Replies
2
Views
103

1,130,281
Messages
5,641,293
Members
417,202
Latest member
AndyVBA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back