As the Powerball jackpot grows to over $300 million, we start to wonder if maybe buying a ticket is “worth it.” While the lottery is “worth it” in that ticket sales goes to things like state education, buying tickets is typically not worth it for yo because the projected payoff is far less than the ticket price.

The concept of Expected Value is a central idea in probability and statistics and refers to a weighted average outcome. For our Powerball example, the expected value equals the probability of getting each combination of winning numbers, multiplied by the payoff of the combinations.

Spreadsheet Inputs

The Powerball website has a table with the odds and the payouts of each winning combination of numbers, making our job relatively easy.

We can transcribe these into a spreadsheet:

Spreadsheet output

There isn’t much logic to this one. First we convert the odds column into probability, by taking one over the odds plus one.

Then we multiply the probabilities with the respective payouts by using the Sumproduct() Function:

Since Powerball tickets cost $2, it seems we’re still losing about 65 cents on each ticket even with the jackpot above $300 million.

It looks even worse given the $302 million is the simple sum of annuity payments, while the cash value is much less at $187.3 million.

That takes us down to 96 cents.

Taking into account a 35% tax rate would then take us down to 62 cents. And then there’s the chance that there are multiple jackpot winners, resulting in a split (we won’t get into that one here, but check out this fivethirtyeight post for more). Finally, let’s use the Goalseek function in excel to see when it becomes worth it to buy a lottery ticket taking into account a 35% tax bracket.

Pull up Goalseek from the Data Tab –> What-If Analysis –> Goal Seek

Then we set the after tax expected value cell (D16) to $2, by changing the jackpot cell (B5). Basically Goalseek is Excel’s functionality for “guessing and checking”, for those who are not as algebraically inclined…

The answer? $806 million! And that isn’t accounting for potential splits which is pretty likely once the jackpot gets that high.

Find the spreadsheet here: Expected Value of your Powerball Ticket