Home › Templates › Kelly criterion spreadsheet
Kelly Criterion Spreadsheet
A free Kelly criterion calculator for Excel and Google Sheets. Log up to 500 closed trades and it works out your win rate W, your average win/loss ratio R and the Kelly fraction f* = W − (1 − W) / R. It then gives half or quarter Kelly, a suggested risk per trade under your own cap, and the growth rate you give up or put at risk by betting less or more than Kelly.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-in, no paid tier: every formula is in the free file.
| # | Date | Market | Setup | Side | P/L | Result | Equity | Drawdown |
|---|---|---|---|---|---|---|---|---|
| 1 | 2026-01-05 | FX pair | Range fade | Long | −$270.00 | Loss | $19,730.00 | 1.35% |
| 2 | 2026-01-08 | FX pair | Pullback | Long | $415.00 | Win | $20,145.00 | 0.00% |
| 3 | 2026-01-09 | Commodity future | Trend follow | Long | $715.00 | Win | $20,860.00 | 0.00% |
| 4 | 2026-01-12 | Index future | Trend follow | Short | $770.00 | Win | $21,630.00 | 0.00% |
| 5 | 2026-01-14 | Large-cap stock | Pullback | Short | $755.00 | Win | $22,385.00 | 0.00% |
| 6 | 2026-01-19 | Commodity future | Trend follow | Long | $0.00 | Scratch | $22,385.00 | 0.00% |
| 7 | 2026-01-21 | Commodity future | Range fade | Long | −$190.00 | Loss | $22,195.00 | 0.85% |
| 8 | 2026-01-22 | Crypto pair | Range fade | Long | −$295.00 | Loss | $21,900.00 | 2.17% |
| 9 | 2026-01-26 | FX pair | Breakout | Long | $555.00 | Win | $22,455.00 | 0.00% |
| 10 | 2026-01-28 | Index future | Range fade | Long | −$160.00 | Loss | $22,295.00 | 0.71% |
Full Kelly: growth peaks2% cap: the risk actually suggestedZero growth: above this, capital tends to shrink
The Kelly formula in one line
For a bet that pays b for each 1 you risk, wins with probability p and otherwise loses the stake:
f* = p − (1 − p) / b
For a trading record, use the win rate W as p and the ratio of average win to average loss R as b:
f* = W − (1 − W) / R
Here f is the share of equity you lose when a trade hits its average loss, so it is your risk per trade, not the position size. To turn a risk percentage into a number of shares, contracts or lots, divide the dollar risk by the distance to your stop, for example with the position size calculator.
John L. Kelly Jr. introduced the idea in 1956 at Bell Labs. He showed that a gambler who bets everything maximizes expected wealth but "would be broke with probability one if he continued indefinitely", and that a gambler who bets the growth-maximizing fraction "will, with probability one, eventually get ahead and stay ahead" of one using any other fixed fraction (Kelly, "A New Interpretation of Information Rate", Bell System Technical Journal, July 1956). Edward O. Thorp later applied it to blackjack, sports betting and the stock market and gave the general form f* = (bp − q)/b used here (Thorp, "The Kelly Criterion in Blackjack Sports Betting, and the Stock Market").
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, formulas in plain words, colour key, limits, sources, disclaimer |
| Kelly calculator | Win probability p, payoff b, Kelly fraction (preset 50%), optional cap and bankroll | Break-even win rate, edge, full and fractional Kelly, suggested risk % and $, growth rate g(f), bets to double, the zero-growth point, a growth table from 0.1× to 3× Kelly, drawdown odds, a native chart of g(f) |
| Trade log | Up to 500 closed trades: date, market, setup, long/short, profit or loss in $, notes; starting equity, Kelly fraction, cap | Win/loss/scratch per trade, cumulative P/L, equity, peak and drawdown; W, average win and loss, R, expectancy, largest drawdown; Kelly at your W and at W minus 1 and 2 standard errors; everything the calculator tab shows, fed by W and R |
| Example | Already filled: 80 fictional trades, $20,000 start, half Kelly, 2% cap | The trade log with every result visible before you enter your own trades |
How to use it
- Open Trade log. Enter your starting equity, your Kelly fraction (preset to 50%, half Kelly) and, if you want one, a cap: the most you will ever risk on one trade.
- Enter one row per closed trade. Only Profit / loss ($) is required: positive for a win, negative for a loss, 0 for a scratch. Scratches are counted but left out of W and R.
- Check that the status in column O says OK, then read W, R and Kelly. Look at the two "standard error" rows: they show how much Kelly drops if your true win rate is a little lower than your log says.
- Use the suggested risk % as an upper limit on what one losing trade may cost. Size each position from it and your stop distance.
- For a single bet with known odds, use the Kelly calculator tab instead: type p and b.
The formulas, explained
Win rate, win/loss ratio and Kelly from a trade log
W counts wins over wins plus losses. R divides the average winning trade by the average losing trade (as a positive number). Full Kelly is W − (1 − W)/R, the binary-bet formula with each trade treated as either an average win or an average loss. Fractional Kelly multiplies it by your Kelly fraction, and the suggested risk takes the smaller of that and your cap.
Growth rate g(f)
Kelly picks the fraction that maximizes the expected logarithm of wealth. Per bet, that expected log growth is
g(f) = p × ln(1 + b × f) + (1 − p) × ln(1 − f)
Thorp derives it this way and notes that maximizing g(f) "is the same as maximizing E log Xn". g(f) is zero at f = 0, peaks at f*, falls back to zero at a point called fc and is negative beyond. The sheet finds fc with 50 halving steps between f* and 100%. Bets to double are approximately ln(2) / g(f).
Drawdown odds
Thorp's continuous approximation gives the chance that capital ever falls to a fraction x of its starting value when betting c times Kelly: x(2/c − 1). At full Kelly (c = 1) the chance of ever halving is 1/2; at half Kelly it is 1/8. The sheet shows this for 90%, 75%, 50% and 25% of capital. It is an approximation for small, frequent bets, not a forecast for a particular trade sequence; for a sequence-based view, try the risk of ruin calculator.
Exact formulas from the Trade log tab. In the file most are wrapped in IF($O$27="OK", …, "") (or $O$9 for the log statistics) so cells stay blank until the inputs are valid.
| Field | Formula |
|---|---|
| Wins (O11) | =COUNTIF($F$6:$F$505,">0") |
| Win rate W (O14) | =$O$11/($O$11+$O$12) |
| Average win (O15) | =SUMIF($F$6:$F$505,">0")/$O$11 |
| Average loss, positive (O16) | =-SUMIF($F$6:$F$505,"<0")/$O$12 |
| Win/loss ratio R (O17) | =$O$15/$O$16 |
| Full Kelly f* (O30) | =$O$14-(1-$O$14)/$O$17 |
| Fractional Kelly (O31) | =$O$6*$O$30 |
| Suggested risk after cap (O32) | =IF(ISNUMBER($O$7),MIN($O$31,$O$7),$O$31) |
| Growth rate per bet at f* (O35) | =$O$14*LN(1+$O$17*$O$30)+(1-$O$14)*LN(1-$O$30) |
| Bets to double (O38) | =LN(2)/$O$35 |
| Standard error of W (O22) | =SQRT($O$14*(1-$O$14)/($O$11+$O$12)) |
| Kelly at W − 1 standard error (O23) | =($O$14-$O$22)-(1-($O$14-$O$22))/$O$17 |
| Growth rate at a multiple of Kelly (P46) | =IF($O46>=1,"n/a: 100%+ of equity",$O$14*LN(1+$O$17*$O46)+(1-$O$14)*LN(1-$O46)) |
| Chance of ever halving (S46) | =IF($N46>=2,1,0.5^(2/$N46-1)) |
| Drawdown from peak, trade 1 (L6) | =IF(J6="","",IF(K6<=0,"",(K6-J6)/K6)) |
Worked example from the sample data
The Example tab logs 80 fictional trades starting from $20,000: 32 wins, 45 losses and 3 scratches. Total P/L is $8,695 and the largest drawdown from a peak is 5.4%.
- Win rate: W = 32 / 77 = 41.56%. The break-even win rate at this payoff is 1 / (1 + R) = 27.78%.
- Win/loss ratio: average win $592.03 ÷ average loss $227.78 = R 2.599. Expectancy is $112.92 per trade.
- Full Kelly: 0.4156 − (1 − 0.4156) / 2.5992 = 19.07% of equity per trade. Half Kelly is 9.54%.
- Cap: the example's 2% cap is below half Kelly, so the suggested risk is 2% of the current equity of $28,695: $573.90 per trade. That keeps 21.2% of the maximum growth rate in this model.
- Estimation error: with 77 decided trades, the standard error of W is 5.62%. If the true win rate were 1 standard error lower, full Kelly would be 11.30%; at 2 standard errors lower, 3.52%. A small change in W moves Kelly a lot.
- Zero-growth point: growth falls to zero at fc = 39.47%, 2.07 times full Kelly. Above that, the model's capital tends to shrink.
Growth at each multiple of Kelly (Example tab)
| Multiple of full Kelly | Risk per trade | Growth rate per trade g(f) | % of maximum growth | Trades to double (approx.) | Chance of ever halving (approx.) |
|---|---|---|---|---|---|
| 0.1× | 1.91% | 0.885% | 20.3% | 78 | 0.0% |
| 0.25× | 4.77% | 2.000% | 45.8% | 35 | 0.8% |
| 0.5× | 9.54% | 3.345% | 76.6% | 21 | 12.5% |
| 0.75× | 14.31% | 4.116% | 94.3% | 17 | 31.5% |
| 1× (full Kelly) | 19.07% | 4.365% | 100.0% | 16 | 50.0% |
| 1.25× | 23.84% | 4.124% | 94.5% | 17 | 66.0% |
| 1.5× | 28.61% | 3.409% | 78.1% | 20 | 79.4% |
| 1.75× | 33.38% | 2.223% | 50.9% | 31 | 90.6% |
| 2× | 38.15% | 0.553% | 12.7% | 125 | 100.0% |
| 2.5× | 47.68% | −4.358% | −99.8% | Never: shrinks | 100.0% |
| 3× | 57.22% | −11.757% | −269.4% | Never: shrinks | 100.0% |
| 0.10× (your 2% cap) | 2.00% | 0.925% | 21.2% | 75 | 0.0% |
Half Kelly keeps 76.6% of the maximum growth rate and quarter Kelly 45.8%, while the approximate chance of ever halving drops from 50% to 12.5% and 0.8%. At 2× Kelly growth is down to 12.7% of the maximum; at 2.5× it is negative. With R above 1, as here, the zero point sits a little above 2× Kelly; for even-money bets it sits a little below.
Check against Thorp's coin example
Thorp's Example 2.1 is a coin that wins even money with p = 0.53. He gets "f* = p − q = .53 − .47 = .06" and "a growth coefficient of g(f*) = f(.06) = .001801", with "an expected time of about n = 385 to double the bankroll". Typed into the Kelly calculator tab, the sheet returns f* = 6.00%, g(f*) = 0.001801 and 385 bets to double. At 2× Kelly (12%) its growth rate is already negative (-0.000017).
Why full Kelly is risky: estimation error
Kelly is optimal only if p and b (or W and R) are the true values. In practice they are estimates, and an estimate from a short record is often too high. Thorp is direct about the cost:
"using too large an f* and overbetting is much more severely penalized than using too small an f* and underbetting."Thorp, section 6, on the sports betting system
"To the extent me is an uncertain estimate of mt, it is wise to assume mt < me and to choose f < fe* by enough to prevent g ⩽ 0."Thorp, section 7.3, "The case for fractional Kelly"
He also reports that "half Kelly" has "3/4 the growth rate but much less chance of a big loss". The sheet's growth table shows the same shape for your numbers. Two practical consequences:
- Use a fraction. Half or quarter Kelly gives up a modest part of the growth rate and leaves room for an edge that is smaller than measured.
- Use a cap. A hard limit per trade protects against a log that looks better than reality, for example after a short winning run.
Drawdowns at full Kelly are large
Even when the edge is known exactly, full Kelly is a rough ride. In Thorp's continuous approximation, "The chance of ever losing half the starting capital is 1/2 for f = f* but only 1/8 for f = f*/2." The drawdown table in each tab shows the same calculation for falls to 90%, 75%, 50% and 25% of capital at 0.25×, 0.5×, 1× and 1.5× Kelly and at your own risk level. Real results can be worse than the approximation when trades are lumpy, correlated or gap through stops.
Common mistakes
- Using Kelly as position size. Kelly here is the share of equity at risk on a loss. A 2% risk with a stop 5% away is a position worth 40% of equity, not 2%.
- Trusting a short record. With 77 trades the example's Kelly falls from 19.1% to 3.5% if the win rate is 2 standard errors lower. Fewer trades means wider uncertainty.
- Mixing strategies. W and R should come from one repeatable setup. Pooling different strategies blends different edges.
- Leaving out costs. Enter P/L after commissions, fees, funding and slippage, or the edge looks larger than it is.
- Adding up open trades. Kelly is per bet. Several open positions at once, especially correlated ones, combine into one larger risk.
- Raising risk after a winning streak. A hot run lifts W and R in a small sample; the cap is there for that moment.
Limits
- Two-outcome model: each trade is treated as an average win or an average loss. Real P/L has a spread of outcomes, so this is an approximation of Kelly for a trading system.
- Independent trades with a fixed edge are assumed. Markets change, and Thorp notes that securities returns follow a "non-stationary process".
- The halving and drawdown odds come from a continuous approximation; the bets-to-double figure is an approximation too.
- No position sizing by stop distance, no leverage or margin rules, no tax.
- Recalculation was tested in LibreOffice. The file uses only functions that exist in Excel 2016+ and Google Sheets.
FAQ
What is the Kelly criterion formula?
For a bet that wins b for every 1 staked with probability p, and loses the stake otherwise, the Kelly fraction is f* = p − (1 − p) / b, the same as (b × p − q) / b in Thorp's notation. For trading, use your win rate W and the ratio R of average win to average loss: f* = W − (1 − W) / R. The result is the share of equity to lose on a losing trade, not the size of the position. If it is zero or negative there is no edge and Kelly says not to bet.
How do you calculate the Kelly criterion in Excel?
Put the win rate in one cell and the win/loss ratio in another, then use =W-(1-W)/R. From a trade log, W = COUNTIF(P/L range,">0") / (wins + losses) and R = average win / average loss, where average loss = -SUMIF(P/L range,"<0") / number of losses. This template does that for up to 500 trades and adds fractional Kelly, a cap, the growth rate g(f) and a chart.
What is half Kelly and why do traders use it?
Half Kelly risks half the Kelly fraction. Thorp writes that "half Kelly" has 3/4 the growth rate but much less chance of a big loss, and that the chance of ever losing half the starting capital is 1/2 at full Kelly but only 1/8 at half Kelly (in his continuous approximation). It also leaves room for an overestimated edge, which is the normal case with a short trade history.
What happens if you bet more than the Kelly fraction?
Growth slows, and past a point it turns negative. The growth rate g(f) = p ln(1 + b f) + (1 − p) ln(1 − f) peaks at full Kelly and falls back to zero at a fraction close to twice Kelly (a bit below for even-money bets, a bit above for larger payoffs). Above that point, Thorp's theorem says capital tends to zero almost surely even though each bet has a positive expected value. The spreadsheet finds the exact zero-growth point for your numbers.
Is the Kelly criterion good for trading?
It is a useful upper limit, not a target. Kelly assumes the win rate and payoff are known and that trades are independent. A trading record is a small sample, edges change, losses can exceed the planned stop, and open positions can be correlated. That is why the sheet shows Kelly at a lower win rate, uses a Kelly fraction and lets you set a hard cap. No sizing rule makes a strategy profitable.
Does this Kelly criterion spreadsheet work in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. It uses only IF, OR, AND, NOT, LEFT, ISNUMBER, N, COUNT, COUNTIF, SUMIF, SUM, MIN, MAX, SQRT, LN, which exist in Excel 2016 and later, Google Sheets and LibreOffice. No macros.
Related tools and templates
- Position size calculatorTurn a risk % into shares or lots
- Risk of ruin calculatorOdds of a deep drawdown
- Position size spreadsheetSize trades from risk and stop
- Trading journalLog trades and review them
- Trading risk management spreadsheetLimits per trade, day and account
- Backtesting spreadsheetTest a setup before trading it
- Risk/reward calculatorReward versus risk on one trade
- All templatesFree spreadsheets
Educational template only, not investment advice. Example data is fictional: the trades, markets, amounts and settings are made up to show the formulas and say nothing about any real strategy or future result. The Kelly criterion assumes a known edge; estimates from past trades are uncertain, and no sizing method guarantees a profit or prevents losses. Sources: Kelly (1956), Bell System Technical Journal (doi); Thorp, Handbook of Asset and Liability Management, Vol. 1, Elsevier, 2006 (doi; earlier version), retrieved 6 November 2026.