G’day, lads!
Welcome to the third article in my series on building an NBA Player Prop Model in Excel. So far, we’ve calculated the xRebounds, xPoints and xAssists for a player. These metrics are the player’s expected value or the average of what they are projected to score across a number of games.
In this article, we’ll take things a step further by applying a probability distribution to these markets to calculate the likelihood of a player going over or under a bookmaker’s line. This will allow us to determine the expected value (EV) of potential bets, helping users decide whether a wager is worth making.
Want to use this model every day? Become an Official Member of the Excel LADZ Patreon! Membership grants you access to this model and every other one featured on YouTube, including the NBA, MLB, NHL and European Football Model.
xRebounds and xAssists
In this model, I will assume that both a player’s rebounds and assists follow the Poisson probability distribution. This is because these events are count-based, occur reasonably independently, and typically happen at a consistent average rate during a game. The Poisson distribution is well-suited for modelling such discrete occurrences over a fixed interval - in this case, a 48 minute basketball game. By using this distribution, we can estimate the probability of a player achieving a specific number of rebounds or assists, based on their expected amount (xRebounds & xAssists).
To validate the use of the Poisson distribution for modelling rebounds and assists, I plotted the theoretical Poisson probabilities against Lebron James’ actual performance in these metrics during the 2023-24 season. Using his average rebounds and assists per game as the mean for the Poisson distribution, I generated probability values and compared them to this observed game-by-game data. While the sample size is relatively small, the distribution provides a rough fit to the data, capturing the general variability and central tendencies of his performance.
Statistically, this approach is both straightforward and practical for the model as it balances simplicity with reasonable accuracy. While it may not perfectly account for game-specific factors or outliers, the Poisson distribution is still able to effectively model the discrete nature of rebounds and assists.
Using a player’s xRebounds or xAssists as the mean (lambda) in the POISSON.DIST function in Excel, we can calculate the probability of a player going over or under a bookmaker’s line. The function POISSON.DIST(x, lambda , cumulative) computes the Poisson probability for a given value x - in this case, x is the line.
When the cumulative parameter is set to TRUE, the function returns the cumulative probability P(X <= x), which is the probability of the player achieving x or fewer rebounds or assists. To find the probability of going over the line, I can use the complement: 1 - P(X <= x), or equivalently, 1 - POISSON.DIST(x, lambda, TRUE).
The resulting probability provides a clear percentage chance of the player going under or over their line. From there, I can use a formula to convert this percentage into equivalent American or decimal odds, with the format easily adjustable using a dropdown list. Consider the example below of Embiid’s calculated xRebounds when playing the Knicks.
Embiid’s xRebounds is at 7.76, with the bookmaker’s line set at 7.5 rebounds. The listed odds for the over (-105) and under (+105) markets are calculated from the probabilities derived using the Poisson distribution. TO assess if either market presents a value bet, we can compare these Poisson-based probabilities with the implied probabilities from the bookmaker’s odds.
For example, if the bookmaker offers +120 for the under market but the model’s odds suggest that it should be priced at +105, the actual implied probability is lower than the calculated probability, indicating a positive expected value (EV) and a potential value bet. In simpler terms, the bookmaker has underestimated the likelihood of the under market hitting. As a result, the return for bettors is higher than it should be. In the long term, assuming the model is accurate in its probabilities and all trades are executed with an edge, a bettor could expect a profit.
xPoints
Unlike rebounds and assists, a player’s points cannot be accurately modelled using the Poisson distribution because scoring is not a simple count of independent, identical events. Points result from a combination of different shot types (e.g. free throws, two-pointers and three-pointers), each with its own success rate and frequency. To model this accurately, it’s necessary to break points down into these components and simulate each shot type separately, accounting for the number of attempts and success rates.
In the model, I’ll create a new worksheet called “Points Simulation”, where I’ll simulate a player’s points for a single game and run the simulation 1,000 times to generate a distribution of results. A player’s simulated points can be generated using the formula below.
That looks simple enough… To simulate a player’s points contributed from two-pointers though, it becomes a bit more complicated. While the formula is exactly the same for three pointers and free throws, the formula is a bit lengthy. Don’t worry lads, I’ll unpack it below.
BINOM.INV Structure
The BINOM.INV function in Excel simulates the outcome of a binomial distribution. Its general structure is BINOM.INV(n, p, k) where:
“n” is the number of trials (e.g. shot attempts)
“p” is the probability of success for each trial (e.g. shooting success rate)
“k” is a random probability input generated by RAND() which creates randomness in the simulation.
First BINOM.INV
The first BINOM.INV generates the number of two-point opportunities a player will have in a game, considering their expected number of total shots. This expected figure is simply their average number of shots in a game (Av. FGA + FTA) adjusted for the multiples in the formula above. The large number of trials (10,000) and the division by the number of trials in the success rate ensures that the probabilities are properly scaled. This allows the BINOM.INV function to approximate the Poisson distribution.
This output of this formula will be the player’s simulated number of total shots for the game.
Second BINOM.INV
The output from the first BINOM.INV represents the number of shot opportunities available to the player. The second BINOM.INV refines this by considering 2PA Poss., the probability that a shot results in a two-point attempt (rather than a different action like attempting three-pointer or a free throw). Again, the random input of RAND() ensures variability in this decision.
Third BINOM.INV
The third BINOM.INV determines the number of successful two-point shots based on the number of two-point attempts (output of the second BINOM.INV) and the player’s two-point success rate (2P %). This models the variability in shooting efficiency with the random input of the RAND().
Final Calculation
By multiplying the result of the final BINOM.INV by 2, the formula converts the number of successful two-point shots into total points scored.
All done! The same thing is done for three-pointers and free-throws, and the total sum is the player’s simulated points for a single game. Running that simulation 1,000 times, I can count how many times the player’s points were over or under the line, and divide by 1,000 (the number of trials) to get their percentage. Once I’ve converted those percentages into odds, I’ve now completed the model!
Thanks for reading, lads! If you have any questions, feel free to comment below. Video will be uploaded to YouTube shortly.