By the end of this page you'll have a two-tab bet log that works out your profit, ROI, units, running bankroll and closing line value every time you enter a bet. Building it takes about half an hour. One thing to know first: the column that matters most isn't wins and losses. It's the closing price, and most bettors leave it blank.
What you need
-
A spreadsheet program
Google Sheets or Excel. Both are fine, and every formula below works in either.
-
A starting bankroll figure, in dollars
Money set aside only for betting. The Wizard of Odds puts the practical minimum at about $1,000.
-
A unit size
Usually 1% of the bankroll. That's $25 on a $2,500 bankroll.
-
Access to the closing price for each prop
Check the line at the same book just before the game starts, and write it down before the result comes in.
Building the log, column by column
The column list here combines the nine fields DeucesCracked recommends with a running bankroll and a CLV calculation, which none of the template pages explain. Make two tabs. Call one Bets and the other Summary.
-
Put the headers in row 1 of the Bets tab
A Date, B Sport/League, C Market (for example, NBA points over 24.5), D Sportsbook, E Odds taken, F Stake ($), G Stake (units), H Closing odds, I Result (W, L or P), J Profit/Loss, K Running bankroll, L CLV, M Notes. Check: thirteen headers, A to M.
-
Enter your starting bankroll in K1
Replace the header in K1 with the number itself, for example 2500, and use the cell's note to label it. Every later row builds on this figure.
-
Add the profit formula in J2
=IF(I2="W",IF(E2>0,F2*E2/100,F2*100/ABS(E2)),IF(I2="L",-F2,0)). This applies the American odds rule: plus odds pay stake × odds ÷ 100, and minus odds pay stake × 100 ÷ the odds. Check: a $25 win at -110 shows 22.73. -
Add the running bankroll in K2
=K1+J2, then fill it down the column. Check: the last filled cell matches your betting balance, as long as you haven't deposited or withdrawn anything. -
Add the units formula in G2
=F2/(unit size), written with your unit as a fixed number, for example=F2/25. Check: a standard bet shows 1.00. -
Add CLV in L2
=IF(H2>0,100/(H2+100),ABS(H2)/(ABS(H2)+100))-IF(E2>0,100/(E2+100),ABS(E2)/(ABS(E2)+100)). That's the closing implied probability minus the implied probability you took. The plus-odds part is the formula DeucesCracked gives, and the minus-odds part is the same idea turned around. Format the column as a percentage. Check: a -110 bet that closes -125 shows about +3.2%. -
Build the Summary tab
Total staked
=SUM(Bets!F:F), P&L=SUM(Bets!J:J), ROI as P&L ÷ total staked, win rate=COUNTIF(Bets!I:I,"W")/(COUNTIF(Bets!I:I,"W")+COUNTIF(Bets!I:I,"L")), average CLV=AVERAGE(Bets!L:L), and beat-the-close rate=COUNTIF(Bets!L:L,">0")/COUNT(Bets!L:L). Check: all six cells fill in once there's a bet in row 2. -
Add a max drawdown cell
On Bets, add N (Peak)
=MAX(N1,K2)with N1 set to your starting bankroll, and O (Drawdown)=K2/N2-1. On Summary, max drawdown is=MIN(Bets!O:O). Check: the cell reads 0% until you've had a losing run. -
Chart the running bankroll
Select column K and insert a line chart. Check: you see one line, and it moves with every settled bet.
One week, run through the math
Five props on a $2,500 bankroll, 1 unit = $25
A hypothetical week of flat 1-unit props. The CLV column is closing implied probability minus implied probability taken, with the vig left in.
| NBA points over, -110, closed -125, Won | +$22.73 |
|---|---|
| NFL receiving yards under, -115, closed -110, Lost | -$25.00 |
| MLB strikeouts over, +120, closed +105, Won | +$30.00 |
| NBA rebounds under, -105, closed -115, Lost | -$25.00 |
| NFL receiving yards over, -110, closed -120, Won | +$22.73 |
Staked $125, profit $25.46, so ROI is 20.4%, up 1.02 units, with a 60% win rate. Four of five bets beat the close, and average CLV is +1.96%. DeucesCracked recommends taking the vig out of the closing price before you compare, and the figures here would shrink a little if you did.
Look at bet four. It lost, but it was arguably the better decision of the week, because the market moved 2.27 points toward it. That's the case for logging CLV. Most people prefer win rate because it's the number they can feel, and it isn't a silly preference. Over enough bets, a good bettor's wins do show up. The trouble is that win rate ignores price. DeucesCracked's example is a 45% hit rate on plus-money underdogs earning more than a 60% hit rate on heavy favorites. For a fuller look at the vig-free calculation and the cases where the close is a poor benchmark, the closing line value formulas page works through both.
And none of these five bets proves anything. The Wizard of Odds works out that a real 55% bettor at -110 still has about a 30% chance of being behind after 100 bets. DeucesCracked says several hundred bets give a rough picture and 1,000 or more give a clearer one. The Rocky Mountain Collegian puts the random spread in observed win rate at about 1.6 percentage points even after 1,000 bets. Twenty or thirty logged props isn't enough to change anything over.
Checking the numbers against your unit rules
The log matters because it lets you check your behavior against the rules you set. Give the Summary tab a current unit cell (1% of the latest running bankroll) and compare it with the unit you're actually staking. TheSpread suggests recalibrating only when the bankroll has moved roughly 25% to 33%, and cutting the unit from 1.0% to 0.75% after a drop of 25% or more. Anything bigger between those resets is unit creep, and a units column shows it straight away: an average stake drifting from 1.00 to 1.4 after a hot fortnight. The fuller set of unit-sizing rules, including daily exposure caps, is in the step-by-step pillar.
If you'd like to size bets by edge, add one column: Kelly % =(b*p-q)/b, where b is the net decimal payout, p is your estimated win chance and q is 1 − p. At -110 with a 55% estimate, the Wizard of Odds gets 5.5% of bankroll at full Kelly, which is roughly 1.4% at quarter Kelly. Its warning is the reason to stay fractional: mistaking a true 52% for 55% can make the bet about eight times too large. It also suggests capping any single bet at 2% to 3%. The bet sizing calculator does this sum and simulates a season of losing streaks at the unit you choose.
Spreadsheet, template or app?
| Option | Cost | What you get | The catch |
|---|---|---|---|
| Build your own (above) | Free | Every formula visible, CLV and drawdown included | Half an hour to set up |
| Lido free Google Sheet | Free | A ready-made tracker you copy with File > Make a copy | Dated as a 2025 update. The facts here don't say whether it has a CLV column |
| Sourcetable CLV tracker / bankroll manager | Not listed | CLV by sport, Kelly sizing, drawdown rules | Web-hosted, may need a signup, and formulas aren't published |
| Dedicated tracker app | Varies | Automatic splits by sport and market | You can't always see how it calculates the numbers |
If you go with a template, Lido's sheet is the quickest start, but check its columns against the list above and add closing odds if they're missing. Sourcetable's bankroll manager bundles Kelly sizing and drawdown protection without showing the math behind them, which is an odd trade for someone trying to learn it. My view is that you should build it yourself, at least once.
When the sheet lies to you
You now have a log that tells you three things your gut can't: whether you're beating the close, how deep your worst drawdown has been, and whether your unit has crept. Keep entering every bet for a full season before you trust any of the results.
Should you strip the vig before measuring CLV?
Both CLV formulas worked through, plus the times the closing line is a bad benchmark.
Read the CLV explainer
Comments
No comments yet. Be the first to comment!
Leave a Comment