Key takeaways
- A working NFL betting model needs only three data inputs: game results, closing lines, and a schedule, all available free from nflfastR.
- Convert power ratings to a spread with the formula (home rating minus away rating) plus home-field advantage, then compare against the no-vig closing line.
- An edge of 2+ points on a liquid main market is worth a bet; gaps under 0.5 points are noise.
- Backtest against closing lines first: a beat-the-close rate of 55%+ over at least 200 picks signals a model worth developing.
- Use fractional Kelly sizing (quarter or half) and re-run power ratings weekly, since a backup QB alone can shift a true spread 4-7 points.
You can build a working NFL betting model in a weekend: pull game results, fit power ratings, convert them to a spread, and compare that number to the market line to find +EV bets. This guide builds the same power-rating model twice, once in Excel and once in Python on identical data, so you can cross-validate your spreadsheet against code before week one.
What data do you need to build an NFL betting model?
Three files: game results, closing lines, and a schedule. Everything else is optional.
Minimum fields: team, date, score, closing spread
| Field | Example | Why |
|---|---|---|
| team, opponent | KC, BUF | pairing sides |
| date/week | 2025-09-07, wk1 | dedupe, injuries |
| score | 27-20 | actual margin |
| closing spread | BUF -2.5 | CLV benchmark |
Free data sources vs paid feeds
nflfastR publishes free play-by-play and game-level data for every NFL season, loaded straight into R or Python. Excel users can export the same game-level CSV. Paid feeds add injury reports and live lines, but you do not need them for a first power-rating spread.
Why closing lines belong in your dataset from day one
CLV is the only honest grading bar. Store the closing spread with every game now, or you cannot backtest later. We audit every pick the same way at MVP Capping: auto-graded, losses kept, CLV tracked on each play.
How do you calculate power ratings from NFL game results?
Start with average margin, not fancy stats
A workable baseline is each team's average margin of victory, adjusted for opponent strength. For every game, compute the winner's margin, then credit each team a share based on the opponent's current rating. Iterate: ratings feed the game adjustments, adjusted results update the ratings, repeat until numbers stop moving. Three or four passes is usually enough.
Regression-to-mean damping factor
Raw margin ratings overreact. A 40-point Week 1 blowout shouldn't swing a team's rating by 40 points. Apply a damping factor of roughly 0.7 to each team's new rating, blending 70% of the updated value with 30% of the prior value. This is the same intuition behind regression to the mean in CLV tracking: one result is a sample, not a trend.
Setting home-field advantage as an explicit parameter
Keep home-field advantage as a single named constant, not baked into the ratings. Start at 2.5 points for 2025 conditions and adjust per stadium if you have the data. Explicit HFA means you can test 2.0 vs 3.0 later and see which fits your backtest. The betting cheat sheet lists the current default alongside the no-vig math you'll need for the market comparison.
How do you convert power ratings into a point spread and compare it to the market line?
Model spread = rating difference + HFA
Your model spread is (home power rating − away power rating) + your home-field advantage term. If the home team rates +3.2, the visitor −1.8, and you set HFA at 2.0, your spread is home by 7.0. Keep HFA as a single tunable parameter; NFL home advantage has compressed in recent seasons, so re-fit it every year rather than hardcoding 3.
Removing the vig to get a fair market number
Market lines are shaded. Convert both sides to implied probabilities, sum them, and normalize by dividing each by the total to get the no-vig line. The Vigorish entry covers the math. Do this on every market number you compare against, or you are measuring the book's margin, not your edge.
When a gap is signal vs noise
Edge = model spread minus fair market spread. A 0.5-point gap is noise; 2+ points on a liquid main market is worth a bet sized by Kelly. Track which gaps closed in your favor post-kick, and audit the misses in our weekly recaps.
How do you build the same NFL betting model in Excel step by step?
Sheet 1: raw game log
Create columns: Date, Season, Week, Away Team, Home Team, Away Score, Home Score, Closing Spread, Closing Total. Paste every 2023-2024 game as one row. Add a Margin column (=AwayScore-HomeScore) and a HomeMargin column (=HomeScore-AwayScore). Freeze the header row so lookups stay clean later.
Sheet 2: power ratings with AVERAGEIFS
List all 32 teams in column A. For each team, compute average home margin and average road margin across the log:
=AVERAGEIFS(Games!$H:$H, Games!$F:$F, $A2) for home margin, and =AVERAGEIFS(Games!$G:$G, Games!$E:$E, $A2) for road margin.
Power rating = (avg home margin + avg road margin) / 2. Then normalize: subtract the league-wide average so ratings sum to roughly zero. A team at +4.2 is 4.2 points better than average on a neutral field.
This normalization step is where most Excel models silently break. If you skip it, every projected spread inherits a bias equal to the league's average margin, which inflates home favorites and deflates road dogs, and your model will disagree with the market for the wrong reason. Normalizing forces your ratings to measure relative strength only, so any residual gap between your projected spread and the no-vig closing line is a candidate edge rather than an arithmetic artifact. Check the sum of all 32 ratings: anything far from zero means a formula is dropping games.
Sheet 3: weekly spread generator
Input Week and the two teams per matchup. Pull ratings with INDEX/MATCH, add 1.5-2.0 for home field, and output projected spread. Compare it to the closing line in the adjacent column and flag differences of 2+ points. Definitions for every term used here live in the glossary.
How do you build the same NFL betting model in Python step by step?
Loading the game log into pandas
Start with the same CSV your Excel sheet uses: one row per game with week, home, away, margin (home score minus away score). Load it and pivot to long form so every team-week appears twice, once as home and once as away:
import pandas as pd
df = pd.read_csv("nfl_results.csv")
home = df[["week","home","margin"]].rename(columns={"home":"team"})
home["val"] = home["margin"]
away = df[["week","away","margin"]].rename(columns={"away":"team"})
away["val"] = -away["margin"]
log = pd.concat([home, away]).sort_values("week")
Groupby power ratings and damping
The rating is a cumulative average of margin, damped the same way your Excel POWER decay does. After week 1, blend each new week's performance at 20%:
log["rating"] = log.groupby("team")["val"]\
.transform(lambda s: s.expanding().mean() * (0.8 ** (s.index - s.index[0])))
Cross-check a few teams against the spreadsheet before trusting the full run. If the numbers diverge, the damping exponent is usually the culprit.
Generating the weekly spread table
Merge each week's home and away ratings, compute home_rating - away_rating + 2.5 for home field, and compare to the closing line. Bet sizing stays honest: size stakes by the Kelly criterion formula, not gut feel. The full working scripts and data templates live at MVP Capping's guides.
How do you validate your Excel model against your Python model?
Run both implementations on the same week of results and compare the power-rating spread for every game. Identical inputs should produce identical spreads to the decimal. If Excel says Chiefs -2.8 and Python says -2.4, one of them has a bug, and you want to find it before you trust either against the market.
Diffing the two spread tables
Export both spread columns side by side in one sheet with a third column for the delta. Sort by absolute delta descending and work top-down: usually one or two root causes explain every mismatch. Games with zero delta confirm your data load is clean.
Common reconciliation bugs: rounding, HFA sign, week boundaries
Excel's ROUND and Python's round() behave differently at .5; pick one convention and apply it everywhere. Check the home-field sign: if Python adds 2.5 and Excel subtracts it, every spread flips. Week boundaries bite when Excel's filter includes Monday night in the wrong week or Python's groupby drops a bye-week adjustment one implementation applies. Fix the diff, re-run, and only then take the model to closing lines.
How do you backtest an NFL betting model against closing lines?
The first backtest is CLV, not profit. Run every model-generated pick against the closing line for the same game and record whether your number beat the close. Beating the close consistently is the standard proxy for long-term betting skill, since closing lines aggregate the sharpest money in the market (Pinnacle betting resources).
Beat-the-close rate as a first filter
Tally your beat rate across a full season, minimum 200 picks. A model hitting 55%+ against the close is worth further work; anything near 50% means your power ratings add nothing the market doesn't already price.
Simulated record vs actual closing spreads
Grade each pick at the closing spread, not your model spread, using flat one-unit stakes. This isolates whether the edge existed at close, independent of the number you actually grabbed.
Why a model that beats the close can still lose money
CLV predicts profitability, it doesn't guarantee it. Variance over one season swamps a small edge, so treat a losing 1-0.5 unit season with a 56% beat rate as noise, not a broken model.
What are the most common mistakes that make an NFL betting model lose money?
Four failure modes kill most models: overfitting, stale ratings, ignoring the vig, and full Kelly on a noisy edge.
Overfitting to one or two seasons
A model that backtests at 60% on 2023 but breaks on 2024 has memorized noise. Keep your feature count low, use out-of-sample validation, and treat any edge above ~3% as suspect until it survives a second dataset.
Stale ratings after injuries and QB changes
Power ratings baked in September are dead by November. A backup QB alone can shift a true spread 4-7 points. Re-run ratings weekly and apply manual adjustments for injuries before comparing your number to the market.
Full Kelly on a noisy edge
Your model's edge estimate has error bars. Betting full Kelly on an overestimated edge guarantees oversized draws. Use fractional Kelly, quarter or half, and cap stake size per game regardless of what the formula spits out.
And the quiet one: always price against the no-vig line. A model spread of -3.2 versus a market -3 with a -110/-110 vig is not an edge; it is a coin flip paying a toll.
FAQ
Can I build a free NFL betting model without paying for data?
Yes. nflfastR publishes free game-level and play-by-play data for every NFL season, covering everything a first power-rating model needs: scores, teams, dates, and weeks. Paid feeds add injury reports and live lines, but you can pull results, compute ratings, and compare against free closing-line history before spending anything.
Is Excel or Python better for an NFL betting model?
Neither is strictly better; they suit different goals. Excel is faster to inspect, since every AVERAGEIFS and lookup is visible on a sheet, and it works fine for a weekend build. Python scales better for weekly re-runs, larger datasets, or automated backtests. Building both on identical data is best: mismatches expose bugs.
How many seasons of data do I need before my model is trustworthy?
Two seasons is enough to build and sanity-check ratings, but judge the model on out-of-sample results, not the seasons it was fitted on. A backtest needs at least 200 graded picks against closing lines before the beat-the-close rate means anything. An edge that survives a second, unseen dataset is the real threshold.
How do I turn my model spreads into bet sizing with the Kelly criterion?
Convert your edge into a win probability, then apply the Kelly formula to get the stake fraction of your bankroll. Because your edge estimate carries error, bet fractional Kelly, a quarter or a half, and cap the stake per game no matter what the formula outputs. Full Kelly on an overestimated edge produces oversized, ruinous draws.
How often should I update my power ratings during the season?
Weekly at minimum. Ratings fixed in September go stale as injuries and quarterback changes accumulate, and a backup QB alone can move a true spread by 4-7 points. Re-run the ratings after each week's results, apply manual adjustments for known injuries, and only then compare your projected spread to the no-vig market line.